Microsoft

Three things you must do when you inherit an Excel workbook

When you inherit a workbook, don't put it into play without checking it out first with these three vital steps.

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?

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

20 comments
clcoronios
clcoronios

Format cell > Font Is this no longer available? I'm working on curriculum and would like to use this info. Could simply use the "white font on white background" trick, but if the other exists, I must be missing something. Carol

GSG
GSG

Do this on a COPY, not the original so that you don't inadvertently mess something up.

keith_eves
keith_eves

Some objects within workbooks can be password protected. Make sure you have them.

ssharkins
ssharkins

Sorry to have led you astray -- that happens occasionally when I'm discussing an attribute in Excel or Word that has a similar attribute in the other -- I just start writing about the other app! I've fixed the entry. Excel's Hidden format is a cell format and part of the sheet Protection scheme. When enabled, you can't see the cell's formula in the Formula bar -- something you'll definitely want to see when taking over someone else's work. I apologize.

gluhas
gluhas

The hyperlink "Removing the Hidden format" in item 2 jumps to instruction on how to remove all hidden text in Word.

TrueDinosaur
TrueDinosaur

The Font box in my copy of Excel 2010 does not have a Hidden option. Where is this option?

ian
ian

if you select the entire sheet and temporarily apply a garish font color, you still no better off because you cannot identify the hidden data. all data has the same font color. If, however, you use the same technique on the background, you will highlight the white text, which will be recognizable from all the other colored text. Good article.

gerdami
gerdami

Before inserting rows and cols, be sure that other workbooks are not linking to this one with silly formulas such as =[Book1.xls]Sheet1!$A$1 ... ;-)

tdowler
tdowler

After one particularly problematic workbook that my department inherited, I made a copy of the workbook and tinkered with the copy instead of making changes to the original. Once operating correctly we renamed the original and archived it just in case someone later discovered it to be useful. That workbook had a tremendous amount of hidden rows and columns, and several worksheets that seemed to lead nowhere and made no sense. After using the Trace Precedents, Trace Dependents and Show Formulas, we discovered that few of the hidden items were relavent and were able to delete a large portion of the experimentation "playground".

smhodge
smhodge

I used that wonderful Control +~ to display all the formulas or even to show if there is any. I have found it saves a lot of time to be able to look at the forumla to look for consistency.

tonycopp
tonycopp

Leave the inherited as it is. Lift the pure living cells that you intend to inhabit and don't look back or play the fool.

karan1070
karan1070

Hey rzagozda I also often use excel workbook but its was great post here thanks for sharing it with us.

J-R-Doe
J-R-Doe

I don't remember the number of text characters that can go into one cell, but it is a lot more characters than are able to be copied with a simple file copy command. For a While I often gave one of our vendors excel spreadsheets that had "over stuffed" cells only to have them copy the workbook with less than complete data. I tried to show them how to get a complete copy via copying the column and pasting into the new spreadsheet workbook, however, somehow the message didn't make it to the recipient. oh'well My suggestion is that to copy a flat file workbook and see if it looks like you have transferred all of the cells contents. The utility of the flat file excel workbook was that it was easily transferred into an Access database, if desired.

rzagozda
rzagozda

I like to run tests with data that is supposedly outside the "normal" range of inputs. Like negative numbers, zeroes, numbers higher that normal, to see what happens to results. Also, even a double of a normal input to see if results make sense.

Full Tao-er
Full Tao-er

Nothing worse than having to reinvent the wheel when you need to modify code and the person who wrote the original VBA password protected it and no longer works for the company. I have used Excel password crackers to get around this for protected sheets/workbooks, but VBA code is a different animal that the crackers don't work on.

GregGold
GregGold

I'm guessing that refers to simply making the text the same color as the background

ssharkins
ssharkins

This is a good way to troubleshoot when something isn't working as expected.

ssharkins
ssharkins

You sound like you've been burned!