Working with Live Maps

Live map data is generated by using the Formula Map assistant. Use live map data to review Excel® workbooks directly in place.

Best practice

Live map data provide an easy way to review complex Excel® workbooks in place, without an additional map workbook. Potential issues can be tracked down directly in the workbook without switching between Excel® windows.
Live map data in Excel® workbook

Live Map Instructions

  • If live map data has been generated during the Formula Map assistant the results will be available in the respective tab of the sheetRush sidebar
  • When selecting one of the categories from the dropdown menu at the top left the list below will show all results for the active worksheet falling in that category.
  • The first column always shows the address of the cell on the active worksheet.
  • The second column shows the formula, if available. The language of the formula can be configured in the settings.
  • The third column shows the resulting value in the respective cell, if available.
  • Usually if any row in the list is selected sheetRush will select the correspodning cell on the Excel worksheet. the jump behaviour can be changed to double click in the settings.
  • If a huge amount of cells fall into the selected category not all cells may be loaded and displayed in the list immediately to speed up the process.
    • The status text below the list indicates if all cells have been loaded
    • If not all items have been loaded they can simply be added by hitting the Load all items button
    • Another reason why not all affected cells are displayed may be that during the Formula Map assistant limits have been set for the analysis. If this is the case a yellow exclamation mark is show next to the status text.
    • The number of cells that should initially be loaded can be set in the settings of the live data. Please see the explanations below.
  • By default cells next to each other which fall into the same category are grouped
    • If cells have been grouped it is shown by the small arrow on the left side of the cell address
    • By expanding the respective node all affected cells can be shown
    • Grouping can be configured in the settings
  • Please be aware live data does not update automatically if changes to the workbook are made. To update live data the fomrula map assistant has to be rerun.

The following categories are available in the drop down menu

  • Dirty formula: 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.
  • 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)
  • Array wrapper: Array wrappers, similar to array formulas, slow down structural changes of workbooks. Array wrappers are integrated functions in Excel that are basically a function around an underlying array function to simplify the application for the end user. Examples for array wrappers are SUMIF/SUMIFS, COUNTIF/COUNTIFS, SUMPRODUCT etc.
  • Containing error: Any cells that contain formulas containing errors. Please note: Cells are only shown here if the error is visible in the formula, for example #REF! after deleting a worksheet. Formula parts are not evaluated for errors. If you want to evaluate formula parts for errors please use the Formula Analyzer feature of sheetRush.
  • 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.

Options

Live Map: Options
  • Auto refresh: If this option is enabled the list will automatically refresh when selecting another worksheet
  • Refresh list: Hit this button to refresh the list manually
  • Open workbook analysis: Hit this button to open the formula map assistant
  • Open formula map: Hit this button to open the formula map
  • Jump on selection: If this option is enabled selecting an item in the list will also select the corresponding cell on the worksheet. If this option is not enabled the corresponding cell can be selected by performing a double click on the item.
  • Group by row: If this option is enabled cells matching similar criteria next to each other will be grouped. Only the first cell will be shown in the list. All other cells can be shown by hitting the expand arrow next to the cell address.
  • Number of items: This selection determines how many cells are loaded by default. Note that loading a high amount of cells may lead to lags when switching worksheets (if Auto refresh is enabled). You can also load more cells by hitting the Load more cells button at the bottom of the list.
  • Language: This determines the language used for the formulas in the list. Note that English is always available. All other available languages depend on your settings in the formula map assistant and the available languages within your Excel.
  • Live items data management: Please see the following part

Live data managemnt

Live data is generated in the formula map assistant, if selected on the first page of the assistant. In the assistant it can be chosen if live data should be saved to the Excel file persistently. Please note that live data may take up a high amount of space in the file. Ususually live data will increase the filesize by 50% to 100%, depending on the complexity of the workbook.

If live data is not saved to the workbook live data will only be available until the workbook is closed. If live data should be used again aftern reopening the workbook again the formula map assistant needs to be launched again. If live data is saved to the workbook the data will still be available after closing and reopening the workbook.

Whether the workbook contains any live data or not can be seen by using the Workbook Analyzer:

Size of live data

Live data can be managed by using the context menu or the buttons in the smartRush ribbon:

Live data controls in the smartRush ribbon and the smartRush sidebar

the following buttons are available:

  • Remove live data from file: Hitting this button will persistently remove all live data from the workbook. The workbook size will shrink to its original size.
  • Export live data to file: Hitting this button enables you to export the live data to an XML file on your harddisk. This is useful if you do not want to include the live data in the workbook but still want to be able to reuse the data later
  • Load live data from file: Hitting this button enables you to load a previously saved live data file