Working with Formula Maps

Use formula maps to quickly review Excel® files for common errors and document the quality control.

Best practice

It is highly recommended to create a formula map for every complex Excel® model that is sent to an external recipient or that forms the basis for an important report. Formula maps provide an easy way to check any Excel® model for common errors. You can configure your preferred formula map settings in the assistant.

Related topics

Basic structure of formula maps

A formula map is an independent Excel® workbook based on the Excel® workbook that should be analyzed. Formula maps show the structure of the underlying Excel® workbook and can be generated by using the smartRush formula map assistant. Like geographical maps in everyday life formula maps provide an abstract view of the underlying data by significantely reducing complexity and by highlighting potential errors in Excel® workbooks.

Example for a formula map created with the smartRush formula map assistant

smartRush provides a very easy way to navigate between the original Excel® workbook and the formula map. Just select a cell or a range in the original workbook and hit the Jump To button in the smartRush ribbon to jump to the equivalent cell or range in the formula map. In the formula map hit the Jump To button again to jump back to the original model. In case of formulas, if selected in the formula map assistant, you can also use the hyperlinks in the formula cells to jump from the map to the original model.

Switch between selected ranges in the model and the formula map by using the Jump To feature of smartRush

In general a formula map contains the same worksheets as the workbook that should be analyzed. However the smartRush formula map assistant allows you to select specific sheets for the analysis. Whenever possible it is recommended to create the formula map for the full workbook to get the most comprehensive overview. Creating formula maps for selected worksheets may make sense if the model has not been finished yet and if creating the map for the whole workbook would take a long time. Even if worksheets match the original workbook, cell contents in the formula map do not match the cell contents of the underlying workbook, based on the settings made in the formula map assistant.

Usually cells with hardcoded values show the respective value in the formula map, just as they do in the original workbook. Formulas are replaced by the results of the analysis. If using the default settings of the smartRush formula map assistant formulas are indicated by a leading F, followed by a dash and a number:

  • The leading letter, in this case the F, indicates that the cell contains a formula
  • The dash is used as a separator between the letter(s) and the unique formula ID
  • The number represents the unique formula ID

Indication of formulas and hardcoded values within formula maps

Unique formula IDs work as follows:

  • Every unique formula within a workbook gets a unique number, the so called formula ID
  • Formula IDs start at 1 and are increased by 1 for every additional unique formula
  • Unique formula IDs are determined from left ot right and then from top to bottom, beginning at the first worksheet proceeding to the last worksheet in the workbook, regardless if the worksheets are visible, hidden or very hidden
  • Similar formulas throughout the workbook, regardless of the worksheet they are on, share the same formula ID
  • If a formula is similar to another formula is determined by using their relative cell references. If the relative cell references match the formulas are recognized as similar:
    • The formula =A1 in cell A2 is similar to the formula =B1 in cell B2 and is also similar to the formula =G6 in cell G7 as they all share the same relative cell references. They all refer to one cell above the current cell.
    • However, the formula =A$1 in cell A2 is NOT similar to the formula =B1 in cell B2 and also not similar to the formula =G6 in cell G7 as the formula in cell A2 uses absolute references and the other two formulas use relative references. In this case the formulas in the cells B2 and G7 would be similar.
    • If a formula includes its own worksheet name similar formulas may be treaten as non similar formulas as their relative references do not match. Example: If cell A2 on Sheet1 contains the formula =Sheet1!A1 and cell A2 on Sheet2 contains the formula =Sheet2!A1 the two formulas are not treaten as similar as they include different parts.
    • However if formulas consistently refer to other worksheets they may be treaten as similar. Example: If cell A2 on worksheet Sheet1 contains the formula =Sheet3!A1 and cell A2 on worksheet Sheet2 contains the formula =Sheet3!A1 they are treated as similar as both formulas refer to Sheet3 cell A1. If in the same example cell A2 on Sheet3 contains the formula =A1 it would not be treated as a similar formula. This is because in relative terms the formula on Sheet3 refers to the cell above the current cell. The formulas on Sheet1 and Sheet2 refer to another worksheet, in this case Sheet3.
  • Unique formula IDs provide a very easy way to perform quick reviews of large Excel® models. If an Excel® model contains a block consisting of several formulas and this block is copied down on the same worksheet or to other worksheets it is enough to review the block once. If all other blocks match the formula IDs it is clear that they are also correct. Using unique formula IDs you only have to review one of the blocks.

Using different letters for different formula types:

  • smartRush provides the ability to define different letters for different formula types
  • If different letters are defined in the formula map assistant the resulting letter(s) will represent the content of the cell
    • If more than one formula type matches the content the letter represents the content with the highest prioirity
    • If concatenating letters was selected in the formula map assistant all letters are included as prefixes in the cell. However, if any criteria matches multiple times (eg. several volatile formulas in one cell) the letter will only be concatenated once.
  • The usage of unique formula IDs is independent from the leading letters

Using colors for formulas and hardcoded values:

  • The Formula Map Assistant provides the ability to select a specific color for each of the criterias of formulas and hardcoded values.
  • The cell background in the formula map is filled using the color of the criteria with the highest priority that matches the cell content
  • For some criteria instead of fill colors, font colors are used. In those cases the process is the same, the font color with the highest priority is winning.
  • Using colors it is very easy to spot any unwanted formula breaks or hardcoded values in the Excel model as the following example shows.
Formula break in cell J35

The picture above shows a formula break in cell J35 of the worksheet. The formula break can be recognized by the light red fill color of the cell. The unique formula ID 213 shows that the cell contains a different formula than the cell to the left. A closer look shows, that the formula from cell G33, with the formula ID 212, was copied to the left and downwards. Looking at cell J36 shows that the cell contains the formula from the cell to the left, but not the formula from the cell above, which means the cell contains the formula with the ID 212. Cell K35 also contains a formula break. However, looking at the indicator in cell K35 it shows that the cell contains the formula from the cell above but not the formula from the cell on the left (which is our formula break). This means the cell also contains the formula with the ID 212. Bringing all these facts together easily shows that cell J35 likely contains an unwanted formula break.

Another example for color coding are errors. Errors in Excel® may lead to wrong calculations or slow the workbook down. By default errors are highlighted by red fill color as shown in the following picture:

Finding errors in formulas with formula maps

The picture above shows that there is an error in cell K18. Navigating to the original workbook shows that the calculation in this cell results in a #DIV/0 error. Cell K18 is also marked as a formula break as the cell contains the formula ID 191 and not the formula ID 190 which is used in the block. However as the error has a higher priority setting than the formula break the cell is colored in dark red. Cell L18 also contains a formula break. However this formula break shows the formula ID 190, which means it is only the correction and not a real formula break, like in the example above. Cell K20 also contains an error, but without the formula break. This may be the case if the formula in the cell is corect, but the calculation itself leads to an error. In this case cell K20 contains the sum of the cells above, summing up the #DIV/0 error in cell K18 and leading to another error. It is highly recommended to focus on error-cells that also contain formula breaks as in most cases correcting these errors will also correct other errors in the workbook.

If concatenating letters had been selected in the formula map assistant cell K18 would not only show the error, but also the fomrula break, like the following picture shows:

Concatenated letters in formula map

The picture above shows the result of a formula map with concatenated letters. Cell K18 contains the term FBE-191 and is colored in red, which gives you the following information:

  • Letter F: This means the formula contains a formula
  • Letter B: This means the formula contains a formula break
  • Letter E: This means the formula is resulting in an error
  • Red fill color: This means the highest matching criteria was the error

Concatenating letters gives you a very powerful tool to analyze complex formulas and workbooks.

Using border colors for areas

  • Cell borders are used to indicate ranges in the Excel workbook
  • Ranges may include
    • Worksheet names
    • Worbook names
    • Print areas
    • Filter areas
    • Analysis limits
  • Borders provide an easy way to quickly spot any unwanted defined named ranges or any wrong print areas.
  • For worksheet and workbook names the name can be placed next to the border using a rectangel shape as the following example shows. Please note that those rectangles are shapes and may overlay any information contained in the cells behind them. You can easily move them to see what’s in the cell behind. If not needed, adding those shapes can be turned of in the formula map assistant.
Assumption sheet with several named ranges and their respective names in rectangles next to the bordered area

Review of complex Excel® workbooks using Formula Maps

Follow the following steps for an efficient review of complex Excel® models using formula maps:

  1. Start at the output sections/sheets and navigate backwards to the input sections/sheets. In some cases it may also be useful to navigate from the input section to the output section or even start with the calculation sections/sheets. In most cases it is recommended to navigate from output to input.
  2. Zoom out on the worksheet to get a quick overview of the structure. By zooming out you can easily spot any inconsistencies like formula breaks.
  3. If you spot any suspicious cells during the high level review zoom in and have a more detailed look
  4. Next review the worksheet from left to right and from top to bottom because this is the way the formula map was created. Unique formula IDs are incremented in the same way, first from left to right and then from top to bottom.
  5. Colors and letters help by reviewing rows and blocks.
  6. Next identify similar blocks on the same worksheet and check if the unique formula IDs as well as the formula structure match
  7. Repeat these steps for all worksheets
  8. Save the formula map for documentation purposes

Specific review activities

  • If you are working on very large Excel models you may encounter issues with calculation speeds. Slow calculations can have several sources. Two common sources for slow calculations are volatile formulas and errors.
    • Volatile formulas, other than normal formulas, recalculate every time anything in the workbook changes. This may cause very slow calculation speeds if volatile formulas are excessively used in the Excel model. Common examples for volatile formulas are OFFSET, INDIRECT or CELL. smartRush provides the ability to mark all volatile formulas. Try to replace volatile formulas with non volatile alternatives. OFFSET formulas for example can usually be replaced by a combination of two INDEX formulas concatenated by a :.
    • Errors are one of the most common sources for slow calculation speeds in Excel. However error handling has been significantely improved in the latest Excel versions. Try to avoid errors as results of formulas by using IF statements. In the latest Excel versions you may also use IFERROR or similar functions, however in older Excel versions this only helps to a certain extend regarding calculation speeds.
  • Type conversions can also be a source of slow calculation speeds. Type conversions occur if two different data types are combined in a calculation or a function. For example if you are using a SUMIFS function like =SUMIFS(A1:A10, B1:B10, “<>0”) to match cells that are not 0 for each cell a type conversion from string to decimal is performed. However, type conversions have been significantely improved in the latest Excel versions.
  • Array formulas and array wrapper may also be the source of slow calculation speeds. More relevant is that array formulas and array wrappers may cause very slow structural changes to the workbook. Structural changes occur when inserting or deleting worksheets or when inserting or deleting rows or columns. These operations can significantely be speed up by reducing the amount of array formulas and array wrappers in the Excel model. Examples for array wrappers are SUMIF, SUMIFS, COUNTIF, COUNTIFS or SUMPRODUCT.