Understand Complex Excel Spreadsheets in 3 Steps

Link to download file :

https://tinyurl.com/y3qg3z2k

***apologies for audio issues - should be fixed in future videos***

We have all been there. Our boss hands us a ‘tricky’ spreadsheet file, and tasks us with ‘understanding’ it. Sounds simple enough, until you open it to find it contains no fewer than 34 sheets, has no consistency between sheets, and no sense of a beginning and an end!

How do you even get started?

Here is the three step process I work through to understand a complex Excel spreadsheet.

1. Get an Overview
It can be tempting to get stuck into the detail, but to do so can compound your frustration – and you are probably already frustrated enough. Rather, try to get an overview of the file first. Working from out to in like this allows you to understand the role of each of its parts.

How do we get an ‘overview’? The biggest building block in an Excel file is a sheet, so let’s first try to ascertain how many sheets are in the file. That means a quick glance at the sheets tabs, right? Yes, but there may be more sheets in the file that are ‘hidden’ or, in particularly tricky cases ‘very hidden’!

2. Click around and categorise
Click on a worksheet and start clicking through some of the cells to ‘get a feel’ for what is going on. This can create a lot of cognitive load. Try not to get stressed and simply take a deep breath if it gets confusing, you will work this out in the end!

The following simple idea helps me see through the formula fog:

Almost all cells in any spreadsheet fall into one of three categories:
1. Input cell
2. Process cell
3. Output cell (including pivot tables)

The categories are described in the video - use these prompts to 'categorise' the cells, and build your understanding of the calculation chain.

3. Visualise the information
How to organise and retain these insights? With multiple sheets, and hundreds or thousands of dependencies, it is a lot to keep in your head. I recommend creating some kind of visualisation. The simplest option is a three column table with headers ‘input’, ‘process’ and ‘ouput’ under which you list the main sheets / cells that fall into each category.

I create structural diagrams of whole files with each sheet represented by a rectangle, with a short summary of the information on that sheet. You can colour code to distinguish input sheets from process and output sheets, assuming the Excel file is sufficiently well-organised! Or, depending on how the file is set up, you might distinguish different blocks within the same sheet. Whichever level you work on, creating a visualisation can really help the process; moreover, your boss, client or teacher is likely to love it! People tend to like talking spreadsheets if it does not involve looking at … a spreadsheet ?

Other Matters...
Unfortunately, there is a sting in the tail. By understanding the process cells, we have fully understood the calculation chain, right? Not quite. Cell interactions are often communicated through formulae, but, as any subscriber to the Tiger channel knows, they can also be programmed in VBA! If macros are creating interactions between cells, these interactions are not discernable in the spreadsheet.

How to understand what the code is doing? This should be familiar territory for those who have worked through Tiger’s videos: the F8 key on a Windows PC allows you to ‘step into’ the code and understand what is going on. Don’t forget you can search the VBA editor (CTRL + F on a Windows PC) to find important references such as output cells. Bear in mind that code can be organised into modules, but might also be assigned to worksheets in the VBA editor – if a routine is triggered on a worksheet change, for example. Look out for custom VBA functions; unlike routines, that execute without an apparent trigger mechanism. They can be particularly fiendish and difficult to understand. But, at least you now know to look out for them!

So that’s it: my three-step guide to understanding any Excel file.

Is it a straightforward process? 99% of the time, it is not. It takes serious mental engagement to work out a complicated file, which is why a lot of people tend to give up. The above road map should keep you on track and help you navigate the main features, and retain your sanity. It won’t be easy but, once you have mastered the file, you can embark on a redesign to make it easy for the next person (!)

Good luck!

Have you tried this approach? Did it work? Did you generate any new insights? Leave a comment below the video on the YouTube channel.

Get on the Tiger mailing list (link below) for privileged information, including:
– Details of livestreams in advance
– Discounts on Tiger Spreadsheet Solutions products
– Details of new YouTube video releases
https://tinyurl.com/y6jtnlk2

Follow Tiger on Facebook:
https://www.facebook.com/TigerSpreadsheetSolutions
Also find us on Insta