Navigating Through Formulas In Large And Complex Microsoft Excel Spreadsheets

When Microsoft Excel is used to construct large and complex spreadsheets containing multiple worksheets, it can become extremely difficult for someone to navigate, let alone track or trace numbers through the spreadsheet with all of the values and formulas contained within it. This is because in a large spreadsheet like a financial model, there can often be hundreds if not thousands of different formula down and across each page including calculations which may contain several links to other sheets within the workbook. The difficulty experienced with navigating through the formulas of large and complex spreadsheets exists even when you were the one who designed and built the spreadsheet.

Microsoft Excel does come with some basic functionality to help users navigate through a spreadsheets formulas. Some common methods are:

- Utilising the ‘Go To’ window [ctrl + G] and typing in cell addresses

- Entering edit mode [F2], thus outlining same sheet precedents in different colors

- Using formula auditing to draw arrows to precedent or dependent cells and double clicking to move back and forth between same sheet references and the ‘Go To’ window to move back and forth between off-sheet references.

This functionality and all of the inefficiency of double mouse clicks falls well short of the mark for many users who have large complex spreadsheets, such as a financial model with formulas that link to a number of cells or cells much further down/across the sheet or to cells on a variety of other worksheets, or even workbooks. The bottom line is that when it comes to formula navigation the existing functionality of Microsoft Excel is difficult to use, inefficient and lacking in functionality.

The main problem is that when trying to check or understand a formula you must first take a look at the precedents cells or ranges, but then be able to quickly jump to the other precedent cells and finally back to the original formula you were originally looking at. Navigating to these precedents and then back again using Microsoft Excel’s existing functionality is time consuming to say the least as many have felt the frustration of editing, checking, de-bugging, enhancing or auditing a formula that you, or worse, someone else has created in their spreadsheet when multiple precedents and sheets are involved.

This common complaint with Microsoft Excel is heard time and time again by engineers, accountants, management consultants, bankers and finance professionals who work with Excel spreadsheets on a daily basis. Many spreadsheet users including financial modellers (who seem to be leading the charge) are turning towards Excel Add-ins and software tools that plug into Microsoft Excel to help them navigate through formulas and complex spreadsheets more easily.

Probably the most popular and widely used Excel add-in for this purpose is ‘Formula Navigator’. An add-in created by the company ‘Spreadsheet Guys’. They have developed a unique add-in utilising a re-sizeable floating window and hyperlink system to help Excel users understand a formula and efficiently jump to all of its precedent and dependent cells and ranges no matter whether they are on a different worksheet or a different open workbook. An additional history window added as part of the products 2nd release also allows the user to click back to any cell previously looked at (traced) using the tool, during the current open session.

Whether the spreadsheet was designed by you or someone else, ‘Formula Navigator’ definitely fills a need and has already cured the frustration of many Excel spreadsheet users, helping them to more quickly understand the logic in a formula and links between sheets, thus helping to reduce spreadsheet errors, facilitate debugging and assist with spreadsheet auditing.