The Eight Things You Should Always Check Whenever You Receive an Excel File

by Matthew Kuo on October 2, 2015

in Error Checking, Excel

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

ID-10088975

Photo by

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 FileOptions to open the Excel Options menu.

New File 01

New File 02

Within the Options menu, click on Formulas and ensure that the radio button for Automatic is checked in the Calculation options menu.

New File 03

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.

New File 04

Macros

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.

New File 05

The first step to dealing with macros is just to make sure that your macro security is enabled.

Go to FileOptions 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.

NewFileCheck 06

Now go to the Developer tab and click on Macro Security.

Ensure that Disable all macros with notification is selected.

NewFileCheck 07

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:

NewFileCheck 08

ALT + F11

Hidden Sheets

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.

NewFileCheck 09

One you click on ‘Unhide…’ you’ll be able to unhide all of your hidden sheets.

NewFileCheck 10

Comments

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.

NewFileCheck 11

You can also use the shortcut sequence below to toggle on and off the Show All Comments option:

ALTRA

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

NewFileCheck 12

Formula Errors

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

NewFileCheck 13

This will bring up a more detailed menu.

Select the radio button for “Formulas”

Check the box for “Errors”

NewFileCheck 14

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.

NewFileCheck 15

Active Filters

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.

NewFileCheck 16

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.

NewFileCheck 17

You can also use the shortcut sequence:

ALTAT

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

NewFileCheck 18

Within the Page Setup menu, click on the Header/Footer tab to check if there is any information there.

NewFileCheck 19

To check for meta data in your Excel workbook, go to the File menu and you should see a Properties section towards the right.

NewFileCheck 20

Click on Properties, and then Advanced Properties to open up the Properties menu.

NewFileCheck 21

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.

NewFileCheck 22

{ 0 comments… add one now }

Leave a Comment

{ 1 trackback }

  • October 11, 2015

Previous post:

Next post:


Related pages


costing model excelexcel formula max lengthexcel formulas hlookuprandbetween without duplicatesvlookup errorsbar and line chart in excelmicrosoft excel nested ifbruinbillhow to make bar charts in excelexcel formula does not equalv look up formulaexcel vlookup match 2 columnsexcel maskxcel shortcutshow to remove duplicate rows in excelpercentage points abbreviationwebdings keyhow to make a percentage bar graph in excelvlookup matchlookup formulas in excelexcel normal distribution probabilityexcel functions tutorial with examplesvba excel samplecheck mark in wingdingsexcel database lookupindex formula in excel 2007matrix color formulasfind duplicates excelserenity dental culver cityformulas on excel spreadsheettypes of errors in ms excelisna with vlookupexcel vlookup example different sheetselect distinct values in exceltwo vlookupswindings tickgmat score lookupvba chart titleuses of macros in excelhomework agenda templateexcel count ifrandom name picker for teachersadvanced excel formulas tutorialexcel vlookup multiple conditionsusing hlookup in excelcolumn index number excelhow to show leading zeros in excelexcel blank cell valuefinding duplicate cells in excelhow to create a bar graph in excel 2007what does vlookup meanexcel double if statementassignment tracker templateucla gymchecked box symbolhow to do line graphs in excel 2010excel template builderoffset command in excelif statements excelexcel graph generatorvlookup sum multiple valuesucla anderson coursespricing matrix excelexcel trend graphhow to vlookup in excel 2007 with exampletoo many different cell formats excel 2010 fixfederal tax underpayment penalty calculatorucla goacool graphs in excelvlookup with if statementvlookup formula example
\n