Formula Analyzer

Use the Formula Analyzer to quickly understand complex formulas and easily navigate through the parts of a complex formula. The Formula Analyzer is also the easiest ways to jump to target ranges of formulas or formula parts.

Best practice

Targets of complex formulas like INDEX, MATCH, VLOOKUP, OFFSET or INDIRECT can easily be determined using the Formula Analyzer. You can jump to the result of a complex formula with just a single click.
Formula Analyzer: SUMIFS example

Formula Analyzer – basic functionality

  • If you select a cell that contains a formula the Formula Analyzer can be used to analyzse the parts of the formula
  • Once you hit the Refresh button the formula will be loaded into the Formula Analyzer and can be expanded part by part
  • If you want the formulas to be automatically be loaded into the Formula Analyzer when the selected cell changes enable the Auto toggle at the top right. Please note this may slow down navigation in the workbook.
  • If a cell has previously been loaded into the Formula Analyzer the formula is cached. This is shown by the clock icon next to the formula. If you want to reload the formula hit the Refresh button. Cached formulas are automatically reloaded if the underlying formula has changed since the last selection. However if a cell is selected and the formula is changed the Refresh button needs to be hit, to refresh the formula.
  • If you select any formula part in the expanded view the corresponding part in the formula will be shown in red letters in the formula at the top. In the example above the first parameter of the SUMIFS function has been selected. Therefore the range C1:C8 is highlighted in red.
  • If available, the result of each formula part will be calculated and shown in the second column. Results can have different types:
    • Values: If a formula part results in a value the value will be shown (eg 6 in the first row in the example above)
    • Single cell: If a formula part results in a single cell reference the value of the cell will be shown (eg A in cell E1 in the example above). In addition a blue arrow will be shown in the third column.
    • Multiple cells: If a formula part results in a cell range consisting of multiple cells a text will be shown stating how many cells are included in the range. The cell range can further be expanded. In addition a blue arrow will be shown next to the range. Cell ranges can be the result of defined ranges in formulas like A1:A8 in the example above, but also be the result of functions that return references like OFFSET or INDEX.
  • Every time a blue arrow is shown next to a formula part it means that you can jump to the result of the formula part by clicking this button in the row
    • Alternatively you can perform a double click on the row, use the menu item in the context menu (right click on row) or use the button Jump to at the top
    • If the target worksheet is not visible (e.g. in hidden or very hidden mode) a message box will appear asking if the hidden worksheet should be set to visible. Please note: If a worksheet is set to visible during a jump to operation the worksheet will stay visible after the operation has finished.
    • The Jump back button enables you to quickly jump back to the previously selected cell if you jumped to another cell using the Jump to functionality
    • The Jump back button also offers a drop down menu showing the latest cells which can directly be selected from there

Settings of Formula Analyzer

Settings can be accessed by clicking the drop down of the Refresh button.

Formula Analyzer: Settings
The following settings are available
  • Auto refresh: If this option is enabled the Formula Analyzer will automatically update whenever the selection changes.
  • Refresh: This button does the same as the Refresh button at the top, it reloads the current cell to the Formula Analyzer
  • Clear: This button clears the Formula AnalyzerPlease not, this may slow down selection changes in very large workbooks
  • Expand node/collapse node: This options expand/collapse the currently selected nodes
  • Limit ranges: This option states how many rows are shown when expanding ranges before the More cells information is shown. The higher the number the more cells are shown when expanding ranges. Increasing this number may lead to slower operations when expanding ranges
  • Color target: This option determines if the target of a formula (eg the SUMIFS parts in the example above) are colored in red
  • Evaluate formulas: This option determines if formula parts are evaluated. Usually this option should be enabled. However you can disable it if evaluating complex formulas take too long.
  • Evaluate external formulas: This option determines if formulas in external workbooks are evaluated. Usually this option should be turned off as evaluating external formulas may be very slow, especially if the external workbook is not opened
  • Cache cells: This option determines how many cells are cached in history. Cached cells show a small clock icon next to the formula. Cached cells load faster than uncached cells and keep their expansion levels. If you want to reload a cached cell you can always press the Refresh button. Please not that a higher number of cached cells may slow down operations in the Formula Analyzer.
  • Clear cache: This button clears all cached cells
  • Rounding: See explanations below
Rounding Settings

The smartRush Formula Analyzer offers comprehensive rounding capabilities. If rounding is active numbers within the Formula Analyzer will be rounded as configured. The following options are available:

  • Round numbers: If this option is enabled numbers within the Formula Analyzer will be rounded.
  • Number of decimals: This selection determines the number of decimals in the rounded numbers. If eg 2 is selected here 12.1234 would be displayed as 12.12, if 4 is selected 12.123 would be displayed
  • Percentage decimals: This selection determines the number of decimals for percentages in the rounded numbers. If eg 2 is selected the percentage 12.1234% would be shown as 12.12%.
  • Use decimals of cell: If this option is enabled the number of decimals from the resulting value in the cell will be applied, if applicable. If the cell does not contain a valid number the number of decimals selected in the dropdown menu will be applied. Please note the cell decimals are only applied to percentages if the cell contains a percentage. If the cell does not contain a percentage value the decimals will not be applied to percentages.
  • Always show decimals: If this option is selected all decimal values will always be show. If two decimals have been selected and this option is enabled 12.1 would be shown as 12.10
  • Decimals for integers: If this option is enabled integer numbers will show decimals as well. If two decimals are selected and this option is enabled 12 will be shown as 12.00. If this option is not enabled 12 will be shown as 12.
  • Round to decimals: If this option is enabled the number will be rounded to the number of decimals selected. If this option is not enabled the number will be truncated. If enabled 1.55 will be rounded to 1.6, if not enabled 1.55 will be shown as 1.5.
  • Show leading 0: If this option is enabled numbers greater than -1 and smaller than 1 will show a leading zero. The number 0.1234 with two decimals will be shown as 0.12. If this option is not enabled the number will be shown as .12.
  • Treat -1 to 1 as %: If this option is enabled all evaluated numbers between -1 and 1 will be treated as percentages. In this case a value evaluated to 0.02 will be shown as 2%.
  • Prefer cell format: If this option is selected whenever a cell format is available for a formula part this format will be preferred over the settings.

Advanced functionality

The Formula Analyzer offers additional functionality for a variety of Excel® functions. Please note not all functionality may be available for every function. In comprehensive tests the Formula Analyzer was able to resolve 99.84% of all formulas.

  • INDEX: Cells that are part of the result are shown in red
  • MATCH: The cell that matches in the lookup range is shown in red
  • VLOOKUP: The cell that matches in the lookup range is shown in red
  • HLOOKUP: The cell that matches in the lookup range is shown in red
  • LOOKUP: The cell that matches in the lookup range is shown in red
  • IF: The part of the IF that is active is shown in red
  • SUMIF, SUMIFS: The cells in the sum-range that match all search criteria are shown in red. In addition within every criteria-range all cells that match the specific criteria are shown in red.
  • AVERAGEIF, AVERAGEIFS: The cells in the average-range that match all search criteria are shown in red. In addition within every criteria-range all cells that match the specific criteria are shown in red.
  • CHOOSE: The active part is shown in red
  • IFS: The active part is shown in red
  • More function will follow in future updates

Example of advanced functionality based on a SUMIFS formula

For demonstration purposes the following simple SUMIFS formula with the two criteria A and C is used in this example. The sum of all matches is calculated from column C.

Formula Analyzer: SUMIFS example

The following picture shows the matches for all criteria in red. In this example the cells C1 with the value of 1 and the cell C5 with the value of 5 match, resulting in a sum of 6. These two cells match because they are the only cells matching both criteria A in column A and C in column B.

Formula Analyzer: SUMIFS example matches

For further investigation each criteria can be analysed seperately. The following picture shows an excerpt of the first criteria matching to the value A in column A. All matches are shown in red.

Formula Analyzer: SUMIFS matches for first criteria

Example of advanced functionality – INDEX

The following picture shows another common example consisting of a simple INDEX formula with two MATCH formulas. The picture shows that the overall formula evaluates to the value 7 in cell D3. Please note the blue arrow next to the 7. Because the INDEX formula returns a single cell you can easily jump to the result of the INDEX formula by using the blue arrow button.

In the example below the first MATCH part is selected in the formula analyzer. This is also shown by highlighting the corresponding part in the formula bar at the top. The red letters of C1 indicate that this MATCH, as a part of the INDEX/MATCH combination, results in the value B pulled from cell C1.

Formula Analyzer: INDEX example