Problems can arise regardless of how you acquire an Excel file, but the most troublesome are usually those we inherit. If you’re lucky, someone else worked hard to remove all the kinks. On the other hand, starting from scratch is often easier than working with someone else’s problems. Before you start using an inherited file, spend a little time exposing problems you might not otherwise catch.
1. Unhide everything
Unhide all hidden sheets, columns, and rows. I don’t recommend hiding columns and rows, but many users and developers do so for a variety of reasons. Sometimes they truly want to hide something. Often, they hide obsolete data rather than delete it.
To unhide sheets, right-click any tab and choose Unhide or choose the appropriate option from the Hide & Unhide submenu under Format in the Cells group on the Home tab. (Or, run a simple macro.) To unhide all columns and rows in a sheet, select the entire sheet by clicking the cell selector (the grey rectangle at the row and column intersection). Then, right-click any column header and choose Unhide to unhide all hidden columns. Similarly, right-click any row header and choose Unhide to unhide all hidden rows. In the ribbon versions, you can choose Hide & Unhide from the Format dropdown in the Cells group on the Home tab.
2. Remove any Hidden formats
Hiding cells isn’t the same as hiding sheets, columns, and rows. The Hidden format hides the formula in the Formula bar, but the cell is still clearly visible. Excel’s Hidden format is part of its protection scheme. You’ll want to unhide any Hidden cells, to audit formulas. Simply unprotect the sheet to view formulas. To hide the actual contents of a cell, some users use a white font on a white background (the default). I discourage this trap, but many users rely on it. To find text hidden this way, select the entire sheet and temporarily apply a garish font color-something that will contrast well with the sheet’s background. Doing so should expose any text hidden this way.
3. Look for formula inconsistencies
Contiguous formulas to the right or below a data range should be similar. For instance, all the formulas in column G might sum values in rows 1 through 16. If you find an oddball formula in cell G12, it deserves scrutiny. Similarly, you’ll want to compare formulas below the data range; contiguous formulas below the data range should be similar in purpose. This rule has its exceptions, but in those cases, the exceptions are usually obvious.
There are the three things I suggest you do before using an inherited workbook. After exposing all data and reviewing formulas for consistency, the next step is reviewing the workbook’s results against known results, when practical. For instance, if you know the daily receipts for yesterday were $1,492, run it all again, just to make sure the file returns the same results. This isn’t always possible, but run a few tests with known data if you can.
What measures do you take to ensure that an inherited workbook is generating correct information?