Photo by adamr
Taking over another person’s Excel file always has the potential to become a difficult task. While some people actually create documentation, consider a new user’s point of view, and take the time to call out the mousetraps that you might encounter, most of the time, you’ll be getting a straight file dump. Therefore, it’s important even before you begin any analysis or modeling, to go through a checklist of items to make sure that you don’t make any errors with the new file you are working on.
A number of these items are covered by Excel’s Inspect Workbook feature found in the File menu. While Inspect Workbook is probably the most efficient way to determine whether or not these issues are present in your workbook, it doesn’t provide a good starting point to audit them; if you want to do a proper audit and fix the issues you find, most of the time you’ll still have to go into the feature itself and adjust it manually. Therefore, it’s good to understand how to begin the audit process for each of these items.
Below are the issues most likely to cause issues with your Excel file. Make sure to check for these items whenever you take over someone else’s file. Doing so will likely save you from major headaches down the road.
Workbook Calculation Settings
Turning off automatic calculations is both a useful and problematic feature within Excel. For the majority of Excel users, there is no need to turn off automatic calculation in Excel. However, for those of us who work with extremely large files, where the calculations can take several minutes to run, it’s helpful to turn off automatic calculations, so you aren’t forced to wait every time you make a change to your file.
As Excel has become more and more powerful, the need for turning off automatic calculations has gone down. However, you still will occasionally get a file sent to you with calculation set to Manual. Because of its rarity, we often don’t check the calculation settings first. It’s incredible the number of times I’ve changed a formula, didn’t see it update, and then start going down the path of auditing the formula rather than just checking the calculation settings.
To check your workbook calculation settings:
Go to File → Options to open the Excel Options menu.
Within the Options menu, click on Formulas and ensure that the radio button for Automatic is checked in the Calculation options menu.
You can also go to the Formulas tab in the ribbon and check the right-most panel, to ensure your calculation options are set to Automatic.
Macros are functions written in the Visual Basic for Applications (VBA) coding language that can initiate automatic tasks within your file. The vast majority of macros exist to enhance your Excel file. However, there’s always the chance that someone has put in malicious code that can do harm to both your file and computer. Additionally, if you run a macro without the proper context and setup, it’s possible you could delete or corrupt data in your file. Therefore, it’s always important to understand if you have VBA code in your file.
The awareness of macros in your Excel file is now a very transparent process because of Excel’s macro security settings. If you’re using Excel’s default settings, anytime you open a file with macros, Excel will disable the macros initially until you provide explicit permission to run them.
The first step to dealing with macros is just to make sure that your macro security is enabled.
Go to File → Options to open the Excel Options menu.
Within the menu, click on Customize Ribbon and ensure that the Developer tab is checked and enabled within your Ribbon.
Now go to the Developer tab and click on Macro Security.
Ensure that Disable all macros with notification is selected.
Now with these settings secure, you can investigate and review the macro code without risk of the macros causing errors. To review the VBA code, just go back to the developer tab and click the Visual Basic option. You can also get there with a shortcut by clicking:
ALT + F11
Hiding worksheets is probably one of my biggest pet peeves in Excel. I understand why people do it. If you’ve created a model with parts that are not relevant for a particular user, then it makes sense to hide the tabs you don’t expect them to use. This streamlines and simplifies the process.
However, if you are handing a file off to another person, and expect them to fully understand the inner workings of it, do not hide any of the tabs. Doing so only increases the likelihood that the receiver of the file will miss something and potentially create an error down the road.
Check for hidden tabs is fairly simple in Excel. Just right click on one of your available sheets at the bottom of the screen. If the ‘Unhide…’ option is available, this means that you have hidden tabs in your workbook.
One you click on ‘Unhide…’ you’ll be able to unhide all of your hidden sheets.
Inserting comments is common way for people to leave documentation about assumptions they’ve made and how they are building out their Excel model. In practice, it’s not uncommon for someone to add these comments initially, but hide them during the build process, as it’s easier to work in Excel without a yellow comment box in your way.
However, in many cases, the context provided by these comments is extremely important to understanding the file you’re working with. Therefore, whenever opening someone else’s Excel file, make sure that all comments are shown. Doing so is fairly simple:
Go to the Review tab and ensure the Show All Comments option is selected.
You can also use the shortcut sequence below to toggle on and off the Show All Comments option:
ALT → R → A
Links to Other Workbooks & External Sources
From a model building perspective, linking to other workbooks is a big no-no. I’ve seen some companies manage it well, by using clearly defined processes and frequent team member communication. The collaborators know exactly what they are linking to, they have defined rules about who can change a file, what files cannot be moved, what parts of the file to change, and when these changes can occur.
However, among those who link to external workbooks, this efficient state is a rarity as the majority of Excel users will not have these supports in place. Therefore, linking to external sources is generally a setup for problems in the future.
Finding external links is a pretty simple process, and uses Excel’s Find feature.
CTRL + F to open the Find feature
Input “[” as your search item
This works because, based on Excel’s syntax rules, all external references are denoted with an open bracket.
Change the Within setting to “Workbook”
This is so you can find all references in your workbook
Click the Find All button
This will show you a list of all the items in your workbook, rather than having to click through them one by one
Excel will then display a list of all of your external references
Formula errors are a common occurrence in Excel and can range from trying to divide a number by zero (#DIV/0!) to trying to reference a cell that no longer exists (#REF!). While formula errors don’t always mean something is wrong in your file, they always have the potential to create problems. Therefore, any formula errors that you have should be investigated.
Finding formula errors is a simple process:
CTRL + G to bring up the Go To menu
Then click the Special… button
This will bring up a more detailed menu.
Select the radio button for “Formulas”
Check the box for “Errors”
Once you click OK, Excel will select all cells on your worksheet that result in formula errors. A good trick after doing so is to click Fill Color icon to highlight all of these cells at once.
Filtering is a very useful tool for data analysis, but any time you leave them on before sending a file, you run the risk of causing data errors. The indication that a data table has been filtered is very subtle, so it’s something that’s very easy to miss when you’re jumping into a file for the first time. Once that happens, it can easily lead to continuing your analysis on a truncated data set, where important items have been filtered out.
First off, it’s important to know what the indication for an active filter is. At the top of your data table, if filtering is enabled, you’ll see a series of upside down triangles. This just means that filtering is possible on those fields, not that it is active.
For columns where you see BOTH the upside down triangle and the funnel icon, this means that a filter is active and you are not necessarily seeing all of the data in the table below.
To remove all active filters, go to the Data tab in the Ribbon. Within the sort and filter section, click the “Clear” button to remove active filters.
You can also use the shortcut sequence:
ALT → A → T
Header, Footer, & Document Properties
The header and footer of an Excel isn’t used very often because it’s generally out of view for the majority of time you use the program. We generally don’t check to see if it’s populated before working on and finalizing our Excel files. However, the header and footer information does immediately become relevant whenever you need to print your Excel file out. Additionally, your Excel file could also be storing meta data about the file’s background.
If you’re a consultant, this can lead to some embarrassing situations, especially if you have the wrong client’s name at the top of a printed out Excel sheet or in the properties menu of your file.
To check the header and footer information, go to the Page Layout tab of the Ribbon.
Within the Page Setup section, click on the expand button in the lower right hand corner to open up the full Page Setup menu
Within the Page Setup menu, click on the Header/Footer tab to check if there is any information there.
To check for meta data in your Excel workbook, go to the File menu and you should see a Properties section towards the right.
Click on Properties, and then Advanced Properties to open up the Properties menu.
Go to the Summary tab of the Properties menu, and you’ll be able to see all the meta data in your file. You can select each of these entries and delete them manually. You can also use Excel’s Inspect Workbook feature to delete these quickly.