Create Formula Map

Use this assistant to create a visual map of the structure of your workbook. Formula maps are a great way to review complex Excel® workbooks.

Related resources

• How to work with formula maps
• How to work with formula map live data

Best practice

It is highly recommended to create a formula map for every complex Excel® model to ensure formula integrity and to minimize the risk for errors in the workbook.

Formula Map Assistant

Please note: The formula map assistant does not support Excel® files saved on Sharepoint®. If you want to analyze a workbook saved on Sharepoint® please copy the file to a local path first.

Step 1: Source and output files

1. Excel® workbook to be analyzed
   • Active workbook: This option will analyze and create a formula map for the currently active workbook. The full path of the workbook is shown in the text box next to the option. Please note: Subject of the analysis is always the latest saved version of the workbook. If changes have been made to the workbook after the last saving operation a warning will appear in the top right and it is highly recommended to press the Save button right below the warning.
   • External workbook: This option will analyze any closed Excel® workbook that can be selected by pressing the “Browse” button
     
2. Backup settings
   • This section provides the possibility to create a backup of the workbook before the formula map is generated. It is highly recommended to create a backup.
   • Automatically save backup file in the same folder as the original workbook: This option will automatically generate a backup file in the same folder. Note that write permissions in the directory are necessary for this operation
   • Select file: This option provides the possibility the choose where the backup file should be saved. The file path for the backup can be selected by hitting the Browse button
     
3. Options
   • Create map: If this option is checked a new workbook containing the formula map will be created. The file path of the formula map can be selected with the Browse button next to the option.
   • Create data for live analysis: If this option is checked the necessary data for live analysis will be captured. Live analysis will be available within the sheetRush task pane after the workbook has been analyzed. Please note: Live data can only be captured if the workbook has been saved. If the option is not available press the Save button on the top to enable the live data option.
   • Save analysis data in workbook: If this option is checked the live data will be saved within the workbook file. Please not that this option may increase the file size of the workbook. Live data can be removed later. If this option is not selected the live data will only be available until the workbook is closed. Live data can be removed at any given time by using the Remove live data button in the ribbon. Using the Workbook Analyzer the size of the live data within the workbook can be determined.
   • Stop after row number: If this option is checked the analysis of any sheet will stop if the used range on the sheet exceeds the number of rows selected. If this option is not checked an unlimited number of rows will be analysed. It is highly recommended to set a maximum amount of rows as the analysis may take very long if eg a formula has been accidently copied down a sheet.
   • Stop after column number: Equivalent to previous option, but for columns. Column 1 is A, column 2 is B and so on

Step 2: Worksheets to be analyzed

1. Scan all worksheets in workbook: Selecting this option will include all worksheets in the analysis. This option leads to the same result as selecting option 2 and checking all worksheets in the list (default setting).
2. Scan selected worksheets: This option will only include the checked worksheets in the analysis. The list gives a first indication of the size of the worksheets. If there is a yellow exclamation mark next to the name of a sheet it means that it dimensions are larger than the limits selected in the previous step. Therefore not all cells of this worksheet will be analyzed properly. A high number of warnings can be the result of to low limits or worksheets with extremely high used ranges.

Step 3: Output configuration for Formula Map

Select configuration

smartRush provides several predefined formula map configurations for the most common needs. Predefined configurations cannot be deleted or edited. However, a predefined configuration may be loaded and adjusted for the active session without saving. In addition custom configurations can be saved. Custom configurations can be created based on predefined configurations.

The following controls are available:

• Configuration dropdown: Select any predefined or custom configuration here to load the settings into the controls below
• Save configuration: Save the currently active configuration to the existing or a new profile. A new dialog will pop up asking you if you want to overwrite the current configuration or add a new one. Please note that it is not possible to ovewrite predefined configurations.
• Set as default: Sets the current configuration as the default configuration. Next time the formula map assistant is loaded this configuration will be loaeded by default.
• Load default: Resets all controls to the default configuration.
• Delete configuration: Delete the current configuration. Please note: It is not possible to delete predefined configurations.

The following predefined configurations are available:

• smartRush Default: This is the default setting for the formula map and the best choice in most cases, especially for beginners. This option will perform a comprehensive analysis of the workbook.
• smartRush Details: This configuration is based on the smartRush default configuration and offers even more details. Different letters are used for different formula criteria and concatenated providing the most comprehensive analysis possible. This configuration usually is the best choice for advanced users.
• smartRush Simple: This configuration performs a simple analysis of the workbook. Only formulas, external links, cells resulting in errors and hardcoded values are shown. For hardcoded values there is no distinction by data type.
• smartRush Only Formulas: This is the most simple analysis of the predefined configurations. Only formulas will be analyzed, everything else will be ignored.
• smartRush Speed Analysis: This analysis focuses on formulas that may slow down calculation speeds in the workbook. Mainly this analysis looks for volatile formulas, cells resulting in errors and array formulas / array wrapper.
• smartRush Hardcoded: This configuration performs an analysis of hardcoded values, ignoring all formulas.
• smartRush Formula Break: This configuration will only show formula breaks, ignoring all other analysis.

Cell background and borders

• This section defines the design of the formula map
• If an option is checked the respective formula type will be analyzed
• The selected color represents the cell background (for formulas and hardcoded values) or the color of the border around the range (for named ranges, print areas, filter areas, merged cells and analysis limits)
• The number within each category represents its priority. A lower number means a higher priority. If a single cell fulfils more than one criteria the criteria with the lowest number will determine the background color and, if concatenating is not activated, the letter. For example if a formula contains a volatile part and an array wrapper but it is also resulting in an error according to the selection in the picture above the cell will be colored as “resulting in error”. The priority within each category can be changed by selecting a new number. If any given priority is changed the category will change place with the corresponding category.
• The letter next to each criteria represents the letter that will be added in front of any unique formula. More than one letters can be concatenated if the respective option is checked. By default, letters are not concatenated. If the same letter would be concatenated more than once it will only be added once.
• If Label is checked the name of the named range will be added next to the border in the formula map. Please not that the name will be included within a rectangle shape.
  
• Explanation of criteria:
  • Formula: All cells containing any kind of formula (starting with =, including array formulas). A simple example for a valid formula cell is =SUM(A1:A10)
  • Dirty link: Cells containing formulas including the name of their own sheet. E.g. if a formula to cell A1 on Sheet1 is written as =Sheet1!A1 this will be a dirty link, if the same formula is written as =A1 it will not be a dirty link.
  • Array formula: All cells containing array formulas (surrounded by {}). Please note this criteria will also include any kind of array wrappers like SUMIF, SUMIFS, SUMPRODUCT etc.. Array formulas slow down structural changes to the workbook like inserting or deleting sheets or columns/rows.
  • Volatile formula: All cells that contain volatile formulas. Volatile formulas, other than normal Excel® formulas, are recalculated every time any cell in a workbook is changes. Normal formulas do only recalculate if they are affected by the respective operation. Volatile formulas slow down the calculation speed of workbooks. Some examples for volatile formulas are OFFSET, INDIRECT or TODAY.
  • Cross worksheet: All cells containing links to other worksheets within the same workbook
  • External link: All cells containing links to worksheets or named ranges in other workbooks
  • Formula break: All cells containing formulas that do not match the formula of the cell to the right. For example if cell A2 contains the formula =A1 and the cell B2 contains the formula =C1 this will be a formula break. If the cell B2 contains the formula =B1 the cell would not be considered to be a formula break as the formula was copied from left to right. Formula breaks may be an indicator that formulas have not been copied across properly.
  • Formula resulting in error: Any cell which formula results in an error like #REF! or #VALUE. Note: This criteria only looks at the total result of the formula. If any part of the formula results in an error but the overall formula does not it will not be considered as a error cell. The following formula for example results in an error as 1/0 is not defined =IF(1=1, 1/0, 0) but the following formula does not result in an error, because the IF statement selects the False part =IF(0=1, 1/0, 0)
    
  • Hardcoded number: Any cell containing a number that is not the result of a calculation and is not formatted as text
  • Hardcoded text: Any cell containing text that is not the result of a formula and numbers (inlcuding date values) formatted as text
  • Hardcoded date/time: Any cell containing hardcoded date, time or datetime values as long as they are not formatted as text
  • Hardcoded boolean: Any cell containing hardcoded TRUE or FALSE values. Please note, this does not include 1 or 0.
    
  • Defined worksheet name: This option will place a border around any defined range name with visibility on the respective worksheet. If Label is checked, the name will be placed in a rectangle next to the border.
  • Defined workbook name: This option will place a border around any defined range name with visibility in the workbook. If Label is checked, the name will be placed in a rectangle next to the border.
  • Print area: This option will place a border around the print area on the worksheets
  • Filter area: This option will place a border around all filter areas on the worksheets
  • Analysis limits: If analysis limits have been selected in step 1 of the assistant, this option will place a border around these limits
    

Cell contents and general settings

• Show unique formulas in cell
  • This option will place one or more letters followed by an increasing number in any formula cell
  • Same numbers indicate equivalent formulas. For example if cell A2 contains =A1 and cell D7 contains =D6 both cells will show the same formula number as the formulas are equivalent – both are referencing the cell directly above. To determine if formulas are equivalent their relative references are compared, not their absolute references.
  • Indicator left: This character is used to indicate the formula in the cell is equivalent to the formula in the next left cell
  • Indicator top: This character is used to indicate the formula in the cell is equivalent to the formula in the cell above
  • Indicator both: This character is used to indicate the formula in the cell is equivalent to the formula in the next left cell AND ALSO to the formula in the cell above
  • Font color formula: This color is used for the text within cells containing formulas or hardcoded values
  • Font color array wrapper: This color is used for the font of cells containing array wrappers. Font colors and background colors may be combined. Array wrappers, similar to array formulas, slow down structural changes of workbooks. Examples for array wrappers are SUMIF/SUMIFS, COUNTIF/COUNTIFS, SUMPRODUCT etc. The usage of the letter is equivalent to the formula section.
  • Font color containing error: This color is used for the font of cells containing errors. The usage of the letter is equivalent to the formula section. Please note: If both options “containing error” and “array wrapper” are selected the font color for “containing error” will always win if both criterias apply.
  • Add hyperlink to unique formulas: This option will add a hyperlink to every unique formula enabling the user of the formula map to quickly jump to the respective cell in the anaylsed workbook
    • Relative path: This option will create hyperlinks based on relative paths. If both files are moved, but the path relative to each other stays the same hyperlinks will still work.
    • Absolute path: This option will create hyperlinks based on absolute paths. If the analyzed file is moved the hyperlink will not work any more.
  • Concatenate prefixes for different content: If this option is checked the letters for different criteria that match will be concatenated (each letter will only be added once). Using this option enables a very detailed analysis of workbooks.
  • Seperate prefix from formula number using: This letter will be used to seperate letters and numbers within the unique formula indicator
    
• Comment containing formula:
  • This option will add a comment to the cell showing the original formula of the cell
  • Author: The text box next to the option contains the author of the comment
  • Formula language: This will be the language of the formula in the comment. English language is always available, in addition any installed language for Excel® may be available.
• Apply column width from source workbook: This option will set the column with in the formula map to the same width as the original column in the analyzed workbook
• Add legend worksheet: This option will add an additional worksheet to the formula map workbook containing a legend to explain letters and color coding.
• Unhide hidden and very hidden worksheets: This option will unhide all hidden and very hidden (worksheets that have been hidden via the VBA editor) worksheets of the analyzed workbook in the formula map workbook
• Separators:
  • Separators only affect the formula map workbook but never the analyzed workbook. These settings are used for the formulas included in the comments
  • List: This character is used as the list separator, eg for parameters of a function like in =SUM(1,2,3)
  • Decimal: This character is used as the decimal separator, eg in 123.45
    

Step 4: Current progress of analyzing the workbook

This screen shows progress bars to indicate the current progress of the analysis. The percentage in the top row of the “Progress” column shows the total progress. Depending on the complexity and size of a workbook the analysis process may take several minutes.

Step 5: Summary of worksheet analysis

This page shows the summary of the analysis. The button in the bottom left corner can be used to open the formula map workbook. However, the formula map workbook can also be opened using the respective button in the sheetRush ribbon. The file can also be opened manually.