Today, we’ll look at Excel tools for complex workbooks. These tools can help you manage links to other files, track formulas, and do other helpful tasks.
Complex Workbook Challenges
This week, I got an email from someone who sets up complex Excel workbooks, and has a few challenges:
- Some files have so many sheets that it’s tough to find the sheet you need.
- Files have links to other files, and sometimes the links don’t refresh correctly
- It’s difficult to track formulas, and see how things are connected.
Are there any tools could help with these challenges? I suggested a few things, some free tools, and some paid tools. If you have other suggestions, please add them in the comments.
Free Tools for Managing Excel Files
Navigator
Dave Peterson and Ron de Bruin created a Navigator add-in that creates a list of all the sheets in the active workbook. The commands are added to the Ribbon’s Home tab, at the far right.
Click on a sheet name, to go to that sheet. You can also sort the sheets in alphabetical order.
TIP: You can add the “Select a Sheet” command to the Quick Access Toolbar, so it’s even easier to navigate. Right-click on the command on the Ribbon, and click Add to Quick Access Toolbar.
Inquire
To see all the connections between workbooks, you can use Microsoft’s Inquire add-in, in some versions of Excel 2013. Here’s a screen shot with the commands that are on the INQUIRE tab, after you install the add-in.
Spreadsheet Studio
Another free Excel add-in, Spreadsheet Studio, was created by chartered accountant, Joseph McDaid. Use it to review and audit your Excel files, and create comments with priority ranking and task allocation. Here is a screen shot of its tab on the Ribbon.
More Free Add-Ins
There’s a page on my Contextures website that lists more free Excel add-ins:
http://www.contextures.com/excelfreeaddins.html
Paid Tools for Managing Excel Files
There are paid tools as well, and some have a trial version, so you can how the tool works, before investing in it.
FastExcel
Charles Williams sells an Excel add-in package — FastExcel V3 Bundle — that is terrific for sorting out your workbooks, and seeing where you can streamline things.
Here is a screen shot of some of the tools on its Ribbon tab – in the FastExcel Manager section. There are too many tools to show then all!
RefTreeAnalyser
Jan Karel Pieterse sells a formula auditing tool – RefTreeAnalyser – which has Ribbon commands, as well as a handy pop-up if you right-click a cell.
Excel Tools
My Contextures Excel Tools add-in has a few features that help with large workbooks, such creating a list of all the worksheets (with links), listing all the formulas on a sheet, and making a quick backup copy of a file. Here’s a small section of its Ribbon tab.
_________________