Microsoft Excel — 5 Tips for Understanding a Complicated Spreadsheet
Eliminate The Pain of This Typically Painful Process
Most Excel users can relate to the following scenario.
Someone leaves your organization. Guess who picks up the Excel workbooks and is tasked with taking them over? You.
This is when it gets ugly — this is a complicated file and there is no trail or documentation to help me figure this thing out. Sound familiar?
Along with Excel’s built in auditing tools, which I won’t cover here, this effort can be cut down to a minimal effort using shortcuts and some basic macro code!
Depending on the workbook complexity, here are the areas that I have found cause the most pain…
- Worksheets with formulas and hard-coded values — getting a quick overview is essential.
- Links to external workbooks — getting data into the current workbook using linking.
- Data Range Names used — within formulas, to identify cells, Tables, etc.
- Extensive worksheet tabs in use — workbooks with 10 or more tabs can be hard to navigate (imagine 240! I’ve seen it.).
- Heavy macros used for workbook automation and task simplification.
So, here is my approach to tackling these challenges!
I will demonstrate each of these with references to web resources, or using a sample workbook that I have developed for training purposes. The workbooks can be much more complicated than this example, but this will give you an idea!
Tip — Anywhere I include VBA code snippets, it is recommended that you copy and paste it into a personal macro workbook (personal.xlsb) or a custom Add-In. This way, it will be accessible whenever you need it — and it doesn’t have to be in your current workbook!
1 — Worksheets with Formulas and Hard-Coded Values
To quickly see all formulas in a worksheet, invoke the keyboard shortcut “CTRL+`"
To return to the “normal” view, just toggle the same shortcut! Extremely easy and you get a quick “global” view of the worksheet you are looking at.
Here is what that looks like in my sample file:
- “Normal” display of a financial schedule
- Formula display of worksheet (“CTRL+`")
2 — Links to External Workbooks
For me, these are the most challenging and I try not to use workbook links — it’s confusing and impossible for others to figure out in any kind of reasonable time frame.
I use a macro to generate a list of workbook links — this let’s me know all the SOURCES. This is a “global” view for a sample workbook.
I then use the “Find And List All Links With Find Command” to locate specific links that I want to find.
The article below explains this very well!
The VBA snippet to generate this list is below:
3 — Data Range Names Used
Another snippet of VBA takes care of this task as well…
Tip — Include the VBA code in your personal macro workbook (personal.xlsb) or your own custom Add-In, so that it is available whenever you need it!
Go to a blank worksheet and run the macro. Here is what it generates (showing my sample workbook).
4 — Extensive Worksheet Tabs in Use
If you run into this, it’s helpful to create a LIST of worksheets included in the workbook. I have a macro that performs this OR I use the ASAP Utilities (which makes it easy to create a HYPERLINKED index sheet).
The VBA snippet to generate a list of workbook sheets is here:
Here is a list from my sample file:
If you want a HYPERLINKED list of worksheets, ASAP Utilities makes that task really easy. I wrote about that process in an earlier @Medium post here.
5 — Macros Used for Automation and Task Simplification
Workbooks that contain macros can be tricky to figure out what is happening and why. A decent awareness of VBA, however, makes it less daunting.
I use a macro to generate a list of macros used in a workbook. This will generally provide a “sense” of what is happening based on the macro name. You can then explore specific items that require further investigation!
Here is that VBA snippet:
To access the VBA Editor, use the “ALT+F11” keyboard shortcut.
Here is a list of macros from my sample file:
Those are my “big 5” tools I use when I need to figure out a spreadsheet that gets passed over to me. This can be from co-workers, clients, vendors, etc.
With just a little bit of advance preparation, you won’t struggle with figuring out someone’s work again. Good luck!
Are there any other techniques you would recommend? Include them in the comments — I would love to make this process even easier!
About Don
“It’s time for different”
Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.
“What Do You Do?”
I frequently get this question. My response (it’s not what you think!)? Check it out here!
