In most cases when you start to work at a new company, you take over the existing Excel workbooks of your predecessor and continue to update and expand on these. Normally, these Excel files are quite large and contain many tabs and it’s not always clear if there is hidden information such as comments, personal information, hidden rows and objects in the file.
Some hidden information can be deliberate whereas some might be unintentional. To find all this out manually can be time consuming. That’s where you can use the Inspect document feature. A great tool to use before distributing workbooks. This video takes you through the steps.
Below is an overview of what Excel inspects. Listed are mainly the key ones – more functions have been added since Excel 2010.
Comments in your workbook.
Document properties – This includes personal information—has anyone filled the Author Name, the subject Title, or some additional custom information.
PivotTables, external links (links to other files) and existing macros.
Headers and footers – Generally, if you are working on a file you might not notice that you have fixed headers or footers until you go and print preview or you print out your document.
Hidden rows, columns and worksheets – Some of these might be intentionally hidden, some unintentional. It might just be carried over from other users and it actually might not be necessary anymore.
Invisible content – This is especially useful since objects and charts can be made invisible from view.
To check for these, go to File > Check For Issues > Inspect Document. In Excel 2007 you have the Office Button and then you go to Prepare and you’ll find this option there. You are then asked if you want to save the document before inspection or not. You don’t necessarily need to save. But it is suggested that you save your file before you start telling the Inspector to remove any information.
Using the attached sample spreadsheet (Download HERE), you will see in the Document Inspector window that a long list. Once you click on Inspect you will see a summary of what it finds, such as comments, Author, PivotTable, some hidden rows and columns. It found a hidden worksheet, that’s one, and some invisible content—so two objects that are invisible.
Now, the downside of this is that it doesn’t tell you exactly where you can find things. Some of them can be obvious, like Document Properties. You know where to go for that. But comments, they could be anywhere in your sheet. Some are harder to find because it doesn’t tell you which particular tab and cell they are found. But it’s not that hard to find things. I’m gonna show you simple ways of getting around this and finding the information yourself.
Excel Document Properties
For Document Properties, if you go to Advanced Properties, you will be able to see the Name, the General Summary, Statistics and so on. And there might also be some custom information as well.
For the sample spreadsheet, you can find an Author and a Company Name written down. You can check for this information and see if it’s okay before you send this out
Check for comments is something that you have to do sheet by sheet. You don’t have to cruise around and find them. You can do it faster if you go to Find & Select.
For the sample spreadsheet (Download Here) there is a comment on the tab which says, “Hello there”. You will not be able to do all tabs all at once by highlighting all of them and clicking on Find & Select. Rather, you will have to actually do it tab by tab. Another way to do this is by using the Go To Special. It’s shortcut keys are CTRL + G. And then you click on Special and you see a window with options.
Looking for hidden worksheets or tabs is easier. So you just have to right mouse click anywhere down at the Sheet Tabs at the bottom portion of the screen. Click on Unhide and you will see if there are any sheets that are hidden. In the case of the sample spreadsheet, T11 is hidden. You can also get to it by going to Format > Hide & Unhide and then you can see Unhide Sheet or Hide Sheet.
Hidden Columns and Rows
Finding hidden rows and columns is trickier because even if you highlight the entire worksheet you still be unable to see if something is hidden. Highlighting the entire sheet and going to Format > Unhide Rows & Unhide Columns will just open everything up. But if you would only want to see if there is anything hidden or not, what you can do is to go back to Go To Special (CTRL + G). Click on Visible Cells Only > OK. A visible thick gray line would then indicate if there are any hidden columns or rows on the sheet. To see the hidden columns, highlight the two columns to the left and to the right of the thick gray line, do a right mouse click and select Unhide.
If you do this on the sample spreadsheet you will see bonus figures for the years 2014 to 2016 which you might not want to unintentionally send out.
In the sample spreadsheet there are two hidden objects on the first sheet. Go to Find & Select under the Editing ribbon and go to Selection Pane, you will see a list of all the objects on this tab, including the comment. If you click on the blank square on the right of each object, you will see an eye icon. This eye icon means that the particular object or comment visible.
In the sample spreadsheet you can find objects named “mybonus” and “confidential”. Clicking on the blank squares beside the object names would make these objects visible.
Once you have the Selection Pane open, you can quickly jump through your tabs and see if you have anything in each tab that is hidden. That’s a good one to check as well before you distribute any workbooks.
Once you know the details of the findings from the Inspector, you can then go back to the File > Check for Issues > Inspect Document and remove them. At this point you would want to have a back-up file, so once you are prompted to save, click on Yes. On the Document Inspector window, click on Inspect. You don’t have to remove everything all at once. You can decide which ones to remove or retain by clicking on Remove All.
For the sample spreadsheet, the comments are fine but you would want to remove all of the Document Properties. And now that you know what are in the hidden rows and columns, you would definitely want to remove those as well, together with the hidden worksheet. And the same goes for the two hidden objects.
Make sure you save a copy of your workbook before removing
After doing so, you should see that when you go back to your spreadsheet the items that were removed are no longer there. Check on the objects, hidden columns and properties. Whereas the items that you have chosen to retain, such as the comments, are still found on the spreadsheet.
As you can see it’s a good idea to inspect your spreadsheet for these types of hidden information before you share or you distribute your workbook.
Click HERE for more information on the complete course Advanced Excel: Top 10 Tips & Formulas to Work Smarter. (It just went live in April and the early reviews have been great – take a look)