ExcelAnalyzer - Excel audit and analysis software

From this article you'll learn about ExcelAnalyzer - a powerful Excel audit software that makes managing spreadsheet risk fun and easy. Find out how to get each and every detail about your workbook in 1 report and correct all possible spreadsheet issues really quickly. At the end of the post you'll find the download details for ExcelAnalyzer to make sure it's really worth reading such a long overview ;).  

Almost each advanced spreadsheet in Excel starts with a single data cell and ends up in several dozen sheets with all sorts of formulas and functions, constants and text cells, hidden and protected data, references, comments, VBA, named ranges and so on. Many professionals use Excel reports for enterprise-wide tasks, so there are lots of proofs that spreadsheet models contain plenty of errors that can be really dangerous for business and corporate image.

That's where ExcelAnalyzer comes to the stage. Using the state-of-the-art algorithm, this Excel audit software investigates and displays dependencies and inconsistent calculations in your workbooks. It has all the necessary options to help you understand spreadsheets and analyze all data-flows.

ExcelAnalyzer employs the power of visualization for you to easily review and mitigate spreadsheet risk. Below are just a few of the numerous benefits the tool gives you:

ExcelAnalyzer is really intuitive and flexible, and you can easily get all the needed powerful reports, despite your IT skill level. Apply this Excel auditing software to workbooks of any complexity and benefit from plenty of additional features described below.

Start Excel auditing with 2 powerful summary reports

Overview a spreadsheet of any complexity with summary report 1

The Summary reports button is the right option to start when you audit with ExcelAnalyzer. Click the button under the ExcelAnalyzer tab to specify if you want to place the auditing reports to the current or new workbook. Summary reports run really quickly and give you an overview of your spreadsheet showing if it's well-constructed and if anything needs to be changed. Some important details are marked in bold.
Overview a spreadsheet of any complexity with summary report 1

Summary report 1 lists all the sheets from the workbook and includes such blocks with details as visibility and protection, formulas and constants, last cells' addresses, hidden rows and columns, charts and Conditional Formatting, other options like tab color, zoom, max and min values. If you click on the link with the sheet name you can get straight to your original sheet for a closer look.

Excel audit - Visibility and protection details

When you audit an Excel spreadsheet, you should know how many sheets are empty, protected or password protected, hidden or very hidden, if they contain macros, VBA or charts.

This can be really helpful for managing spreadsheet risk, because it may be a good idea to delete empty sheets, unhide and unprotect certain tables before running the analysis.
Get visibility and protection details about your spreadsheet

Checking formulas and constants in Excel

This block displays the number of formulas and constants in each sheet including unique formulas and formula cells, empty cells, those containing constants and text. This section also shows the number of rows and columns with data on each sheet and the number of all the data cells in the range.

It's really helpful if you audit someone's Excel spreadsheet or need to compare the total number of all formulas vs. unique formulas in the workbook.
Check formulas and constants in Excel

Last cells' addresses

If your table is really huge, with ExcelAnalyzer you can easily see the addresses of the bottom-right cells on each sheet to get where the sheet with data ends.
Get last cells' addresses really quickly

Take the advantage of this possibility if you think that empty cells may contain formatting that makes the file size of the workbook larger and may result in excess printed pages.

When auditing with ExcelAnalyzer, it allows to locate the last cell that contains data or formatting on a worksheet, and then reset that last cell by clearing the formatting that may be applied in empty rows or columns.

You can also see how many empty rows and columns there are within your tables to understand if the sheet space is spread correctly.

Scan your Excel workbook for hidden rows and columns

When you try to minimize spreadsheet risk not a tiny detail should remain unnoticed. ExcelAnalyzer report will show if any of the sheets contain hidden rows or columns, or if any of the rows and columns are grouped. Thus you can easily get to the correct sheets and check the hidden information.
Scan your Excel workbook for hidden rows and columns

Overview Conditional Formatting, charts, merged cells, etc.

The 5th block with spreadsheet details pinpoints the number of Conditional Formatting rules, data validation cells, merged cells, those with comments, auto filters, chart objects, data or Pivot Tables.

Overview Conditional Formatting, charts, merged cells, etc.

Additional details you may need for Excel spreadsheet auditing

Block 6 gives you the colors of the tabs with the sheet names, zoom level, you learn if zero is shown in cells with the zero value, if the option to show row and column headers is on, the addresses of frozen panes, window split, if the sheet calculation is on, minimum and maximum sheet values.
Get additional details you may need for Excel spreadsheet auditing

Summary report 2 - audit Excel comments, references and VBA

Summary report 2 gives you even more details about comments, names and modules in your spreadsheet. You can see such information as the author of the workbook, file size, the date it was created and the last time printed. It also gives you the type of the file (csv, xls, xlsx, etc), the number of sheets, charts and macros. You'll get the total for hidden, very hidden, protected and password protected sheets as well.
Audit Excel comments, references and VBA

The Excel audit report 2 comprises the Comment, Name and Module reports.

Cell commenting is a really useful Excel feature. Adding a note to a cell allows collecting remarks and reviews and can be especially helpful if you share a workbook with colleagues. That's why the Comment report has all the necessary details about cell notes in your audited spreadsheet. You can see the sheet names, cell addresses that contain comments and the comment contents.
See the comment report with the details about cell notes in your spreadsheet

If you have names in your spreadsheet that refer to a single cell, a group of cells, a specific value, or a formula, the Name report will display them for you. This really helps in spreadsheet auditing if such Excel names are used in a formula instead of a constant value or cell references.
Instantly generate name reports in Excel

The Module report gives you the names of all VBA modules in the workbook. You will see the component names, procedure, start line and line count without opening the VBA Editor.
Analyze the names of all VBA modules in the workbook

As you can see ExcelAnalyzer summary reports collect each and every detail you may need for Excel audit which saves your precious time on putting this information together manually.

ExcelAnalyzer window for easy auditing in Excel

When you install the program, you can see the new ExcelAnalyzer tab with 3 buttons: Summary reports, Analyze and Options. Having reviewed the Summary reports option, let's check what is hidden behind the Analyze button.

Clicking on the Analyze button opens a new window. The window helps you locate the names of all sheets from the book, see if they are empty, hidden, very hidden, protected or password protected. It's possible to analyze your file for formulas, errors, functions and input cells. You can choose to display all the results in your original workbook, or create a backup copy of each sheet before processing.
See all settings on the ExcelAnalyzer window

It's possible to create both a general summary report based on the tab option you select (formulas, errors, functions or input cells) and separate color-coded reports for each sheet. Just press Start to see the results and get the Color index pane described further.

Mitigate spreadsheet risk with the Color index pane options

The Color index pane appears next to the sheet you analyze. To get it in Excel, you need to run the ExcelAnalyzer window, select the sheets you want to review, pick the location settings, select the analysis tab and click Start.

ExcelAnalyzer generates the reports based on your table data, you can click on the copy that is placed next to each sheet to see the ExcelAnalyzer Color index pane. This pane helps you navigate the audited sheet.
Mitigate spreadsheet risk with the Color index pane options

Click on the Last Cell button to get to the bottom-right cell of your table with data. This option is especially helpful when you have large sheets with advanced formatting and need to navigate to the end of your sheet.

To get a more compact view of your table set column width to 3 using the corresponding button.

Sometimes, checking formulas for accuracy or finding the source of an error can be difficult when the formula uses precedent or dependent cells. Precedent cells are cells that are referred to by a formula in another cell. Dependent cells contain formulas that refer to other cells. To assist you in checking your formulas, ExcelAnalyzer has the Tracing formulas and Tracing inputs buttons. Just use them to display the relationships between cells and formulas graphically with tracer arrows.

One more really helpful option is to show formulas in Excel cells. You will not need to click on each cell to find which contains formulas, just employ the Show formulas button.

If you want to navigate to your original sheet you can click Link to cell: A. Or press the Link to report button if you want to get to the general report that is generated according to the tab you select on the ExcelAnalyzer window.

Audit formulas in Excel and get a detailed formula report

The ability to create formulas is one of the most powerful features of Excel. As a rule spreadsheets are filled with them to calculate values, analyze data, and much more. However even a small mistake when entering a formula can give an incorrect result.

In addition, your spreadsheet will not always tell you if a formula is inconsistent. It just goes on and gives you the wrong result. The best thing it can do is to mark formula errors as #REF or #NAME. But it can take too much of your precious time to look through the entire workbook troubleshooting the cells one-by-one.

ExcelAnalyzer will double-check everything for you to make sure all formulas and cross references in your sheets are faultless.

Run the ExcelAnalyzer window and select the Formulas tab to scan the spreadsheet for formulas. The add-in generates 2 general overview reports named A and B. It also places color-coded copies of the current sheets next to the originals.
Audit formulas in Excel and get a detailed formula report

Formula report A

Report A displays all the necessary details about your cross references or formula links between worksheets. The sheets that are not audited are colored blue, those without links are light green, sheets with links are darker green, orange color occurs when another sheet is linked to this sheet, red stands for both linking and linked worksheets.

In report A you also see the number of formula links that refer and linked to certain table, as well as the number of formula links to another workbook.
Report A displays all the necessary details about your cross references

Formula report B

Report B gives you a list of all unique formulas used on your spreadsheet. For your convenience all sheet names are listed and contain a link to easily navigate and correct the issues in formulas or formula groups. The colors used for identifying unique formulas are the same as the ones used in the color-coded sheets.
Report B gives you a list of all unique formulas used on your spreadsheet

There is a nice possibility to filter specific formula features. Please find the entire list of options below:

  • A - #REF errors
  • B - References to other workbooks
  • C - Functions and combinations of functions
  • D - Numbers used in the formulas
  • E - Formula references to other sheets
  • F - Text and text symbols used in the formulas
  • G - Names used in the formulas
  • H - Operators used in the formulas
  • I - Absolute formulas
  • J - Array formulas
  • K - Table formulas
  • L - User defined functions
  • M - Formulas to the sheet itself
  • N - Formulas that occur on the sheet once.

If we ungroup all the sheet names we get each and every formula used on them and see if this formula is invalid. When you audit someone's Excel spreadsheet, you have a special place in the report where you can leave a comment for each formula and put a mark of approval or disapproval.

For example, on the screenshot below we can see that the sheet Budget contains 11 unique formulas and 225 formulas in total. We clearly see which formulas are used and addresses of the ranges where they are entered.
See which formulas are used and addresses of the ranges where they are entered

If we filter further we find out that the formulas used are the ones with one SUM function and 4 of them contain numbers. The operators are plus, minus and equal sign.
Get the full information about each formula on each sheet

Thus you get the full information about each formula on each sheet in one no-nonsense report.

Manage formulas in Excel with the Color index pane

Along with general Formula reports you get sheet-specific color-coded reports. To see them, click on the tab with the number next to the needed sheet name.
Click on the tab with the number next to the needed sheet name

You will see the Color index pane next to the sheet. Since we have already learnt about the buttons Last Cell, Column width to 3, Tracing formulas, Tracing inputs, Show formulas, Link to cell and Link to report, let's have a closer look at Filter options 1 and 2.

Filter option 1 lets you get the cells with:

  • Reference errors
  • External formulas
  • Formulas with functions
  • Formulas without functions
  • Numbers in the formulas
  • Intersheet formulas
  • Formulas with text, name, or operator
  • Absolute, array or table formulas
  • User defined functions or same sheet formulas.

Use filter option 1

The Filter option 2 buttons allow displaying and navigating to certain formula cells. First you can find the top-left cells of the ranges with constants or text cells by clicking on the green or dark grey buttons. Each time you click on these buttons it shows you a new range.
The Filter option 2 buttons allow displaying and navigating to certain formula cells

By default all cells are grouped by color according to the formula used in them. Unique formulas are red. Click on the icons with colors to navigate to the cell groups one by one.
Click on the icons with colors to navigate to the cell groups one by one

Or select the radio button next to the color icons to hide all colors except the one you need to see.
Select the radio button next to the color icons to hide all colors

The pane also shows the number of formulas of each type. There may be special marks next to the colored buttons such as Ex (external reference), Err (#REF error), N (number in formula) or I cross-reference) that show if this group contains any details you should pay attention to.
Display the number of formulas of each type

Instantly find errors in Excel formulas

The ExcelAnalyzer Errors tab contains 3 sub tabs: type 1, type 2 and type 3. Type 1 tab inspects error values shown when Excel cannot calculate a formula. These are #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A.

Tab type 2 works with text stored numbers, circular references and duplicate formulas.

Type 3 tab analyzes not best practice formulas and formulas that refer to another workbook.

Instantly find errors in Excel formulas

Check and handle Excel error values

When you run the scan for Errors type 1 you get report C as well as reports specific for each sheet you selected.

This report shows formula errors on your workbook. It lists the names of the sheets and problematic formulas. You get all formulas with Excel errors: #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A. You can also see if the errors were prevented from being displayed or if they are copied as text.
Check and handle Excel error values

Other potential errors group shows numbers formatted as text, circular references and duplicate formulas.
See numbers formatted as text, circular references and duplicate formulas

The third group displays not best practice formulas like =555+689, =sum(a1:a10)+285, =if(a1>10;"High;"Low"), =subtotal(1;a1:a10), plus formulas that refer to another workbook.
Display not best practice formulas

If you want to get to the error on the sheet, just click on the link with the sheet name.

If you want to see a sheet-specific report, click on the number next to the needed sheet name. When you open the report you will get the Color index pane. Use it to see and navigate to constants, text cells and unique formulas.
See sheet-specific reports

Color buttons let you display Excel errors like #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A!. You can click on the button with the necessary color to see the error cells on the sheet or you can select the corresponding radio button to highlight all of them.

The green button helps you find the suppressed error values on the Excel spreadsheet you audit.

Pinpoint text numbers, circular references and dupe formulas

If you work with Excel for more than several months, most likely you know that some errors and problematic formulas can cause many issues and mess up the entire sheet. If you run the reports for the errors tab type 2, you will get all the details about numbers formatted as text, formulas that calculate their own cells and information about 2 or more identical formulas on the sheet.
Pinpoint text numbers, circular references and dupe formulas

As with the options described above, you can use the colored buttons to see and handle these issues in your worksheet.

Check for hard-coded values in Excel formulas

Here is a common problem. Suppose you are auditing a complex Excel spreadsheet. Whoever designed it, they were in a hurry. The workbook has formulas like this, =SUM(Budget!A2:A100, 8500)+1000.

Where standard Excel features fail, ExcelAnalyzer works perfectly displaying all formulas with numbers which are not so good practice.
Check for hard-coded values in Excel formulas

In addition, type 3 tab lets you see formula links that refer to other workbooks. Though they don't present any spreadsheet risk, you may want to have this information at hand.

Thus with ExcelAnalyzer you can automatically audit your Excel spreadsheets for all possible errors to prevent any problems whether it's your file or you are reviewing it.

Analyze Excel functions and their combinations

It's fantactic how Excel functions and their mixes simplify solving certain spreadsheet problems. ExcelAnalyzer will help you find all functions used on the spreadsheet you are checking from the most popular SUM and COUNT functions to VLOOKUP, MATCH and their combinations.

When you run the search, you'll get Report E for all the selected sheets and separate reports for each sheet.

The general big report shows the number of formulas, unique, intersheet and formulas without functions. You will see if your workbook contains such functions as DATEDIF, IF, IFERROR, INDEX, MATCH, RIGHT, ROUNDUP, SM, SUM, SUMIF, VLOOKUP. Also it will show any combinations of functions.
Analyze Excel functions and their combinations

If you open a report next to certain sheet you will see all the details for the sheet on the Excel Analyzer pane. You'll see cells with constants and text, сells that don't contain functions and cells with functions and their combinations.
See all the details for the sheet on the Excel Analyzer pane

Easily find and check input cells when you audit Excel spreadsheet

The Input cells tab and report as the name suggests highlight information about the entered cells. You see if a cell is used or not used by a formula, the cells with used and unused constants, text cells and blank input cells.
Easily find and check input cells when you audit Excel spreadsheet

All the information is included into Report D. On the Color index pane you can use the Filter option to see all formulas, constants, text cells, input cells, the ones that are not in use, all constant and blank input cells. Colored buttons take you straight to the actual cells in the spreadsheet, so that you can see the flow of logic unfolding on the screen.

The colored buttons let you filter, navigate and correct certain groups of cells.
Filter, navigate and correct certain groups of cells

Use additional options to fine-tune the auditing results

You can find the Options button under the ExcelAnalyzer tab. It contains a number of helpful features to make spreadsheet auditing even easier.
Use additional options to fine-tune the auditing results

If you happen to close the Color index pane for any of the analyzed sheets, you can easily reopen it by clicking Options -> Open color index.

Suppose, you selected to place a copy of each sheet with the analysis next to the original list, corrected all the issues and need to delete the copies. You can remove them all at once using the Delete copied sheets option leaving only the big summary report(s).

If you want to delete both the copies of the original sheets and the summary reports, select the option Delete copied sheets and reports.

When you start the ExcelAnalyzer window you can see if there are any hidden sheets. If you want to make them visible for processing, you can save the time and unhide them all in one go by clicking Options -> Unhide all sheets.

Under this icon you'll also find the information about the tool, video and Conditions of use.

Download ExcelAnalyzer to make auditing in Excel fun and easy

At the single click of a button ExcelAnalyzer audits your Excel spreadsheet and performs all the necessary sophisticated tests helping you mitigate spreadsheet risk.

Feel free to instantly download ExcelFnalyzer and try it our free for 30 days.

Free download
30-day trial version, 10.6 MB

3 Responses to "ExcelAnalyzer - Excel audit and analysis software"

  1. Ashley Lambert says:

    The trial version immediately says that it has expired upon first installation.

    • Bob Smiley says:

      Confirmed; same thing for me. And clicking on the website link gets a 404 not found error. I think this software is no longer available in this form.

  2. Peter Whelan says:

    It installed fine into Office 365 for a 30 day free trial. However, the instructions above are "The Summary reports button is the right option to start when you audit with ExcelAnalyzer. Click the button under the ExcelAnalyzer tab" I see lots of options under both AbleBits Data and AbleBits Tools, but no analyzer or summary reports button.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard