Excel Tools for Complex Workbooks

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.

excelfiletools01

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.

excelfiletools06

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.

excelfiletools02

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.

spreadsheetstudio

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. You can download a free trial copy, and if you decide to buy it, you can get 50% off until July 31st, with the coupon code FXLV3Intro

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!

excelfiletools03

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.

excelfiletools04

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.

excelfiletools05

_________________

You may also like...

6 Responses

  1. Hi Debra,

    Excellent overview!

  2. Tony says:

    Hi Debra

    One of the best I have come across is the brilliant ASAP Utilities. Free for personal use. Find it at: http://www.asap-utilities.com/

    Would not be without it

  3. Jim Linnehan says:

    Thanks for this post, Debra.

    My two cents: When writing a blog post on an Excel add-in or series of add-ins, it may be worthwhile to mention whether a particular add-in is available in a 64-bit version.

    Like Tony above, I have loved using ASAP Utilities. Unlike Tony, however, I have a 64-bit environment now and am using Excel without it. I can’t say it’s much fun.

    BTW ASAP Utilities doesn’t commit to a date for a 64-bit Excel version:
    “We do have the plans, but we don’t have a scheduled date for a special version for 64 bit Excel. In order to support the 64-bit Excel versions, we will have to completely rewrite the entire program in a new programming language (from Office Developer VBA to Visual Studio .Net).”

  4. Hi Debra – Very informative. Thank you very much!

Leave a Reply

Your email address will not be published. Required fields are marked *