Help Docs

Overview

MART is composed of 9 review and 4 clean-up functions. This page elaborates in detail the uses and limitations of each of these functions, Use the left menu to navigate directly to the description of the function of your interest.

Review Functions

These group of functions are designed to help you understand models more easily and to provide you useful tools for a review process.

Data Flow

Detects all formula links between worksheets and displays the results in an interactive summary table in a new window. The summary table displays, for each worksheet, how many formulas link to every other worksheets in the same workbook. By clicking on individual cells of the summary table, the user can directly navigate to the cells where the links are found.

This function can be useful to quickly understand the dataflow of large workbooks which you might not be familiarized with. In the context of a review, it will help in the identification of ‘input’, ‘calculation’ and ‘output’ type of worksheets. It can also help spotting unwanted links during model construction.

Instant Map

Creates an visual ‘map’ of the content of a worksheet in a new worksheet. The map allows you to visualize the presence of ‘formula’, ‘text’, ‘numeric’ or ‘error’ cell types and to detect formula inconsistencies.

Best practice of financial modeling is to keep formula consistency across rows and columns to the extent possible. This type of model structures minimize the number of distinct formulas and reduce review effort typically allowing the reviewer to check (1) the correctness of first column/row formulas and (2) the proper use of ‘$’ locks when dragging formulas.

The instant map created by this function indicates whether the formulas in each cell are ‘inconsistent’ or ‘consistent’ with the formulas in nearby cells. Inconsistent formulas are indicated by red dots. A model review will typically focus on the ‘inconsistent’ formulas. The instant map is interactive in the sense that it allows the user to click on the red dots to directly navigate to the respective inconsistent formula in the source worksheet.

You can map multiple worksheets simultaneously by selecting more than one worksheet tabs before running this function.

Map in New Workbook

Displays the instant map results in a new worksheet in a new workbook. Use this function is the structure of the source workbook is protected or if you want to document the result of the map in a separate file. Unlike ‘Instant Map’, this function is not interactive, so it does not link to source inconsistent formulas.

Formula Consistency

Colors a preselected range of formulas to indicate the presence of formula inconsistencies, which normally require special attention in model reviews. You might want to use this function instead of ‘Instant Map’ if you only want to review formulas in a specific range. Additionally, some reviewers find it more convenient to spot inconsistent formulas directly in the source worksheet to avoid repeatedly switching between source and review worksheets. Please note that this function cannot be undone.

Formula Explorer

Displays an interactive tree diagram to visualize hierarchy of complex nested formulas. This function also displays the results of intermediate components of the formula and allows navigation to all cells referenced in the formula by clicking on the respective base elements of the hierarchy.

Log New Finding

Automatically logs your model findings in a predefined ‘log file’. This function can be useful to quickly document model reviews in a separate workbook that can be shared with the author(s) of the model in a formal review process.

If no ‘log file’ is open when attempting to log a new finding this function will ask you whether you want to create a new ‘log file’. When creating a new ‘log file’ MART will automatically include information about the review such as model’s file name, author name, reviewer name and review date. For each logged finding MART will automatically fill a new line in the log file and include reviewer’s comment, finding type and priority, worksheet name, range and formula.

From version 2.50 ownwards it is possible to load a customized review template with formatting predefined by you. If you want to use this feature you should open a new review file, cutomize it as desired and save it as an excel template (“.xltx”) in the following directory with the following filename: %localappdata%\MART\CustomReviewFile.xltx.

You can use the customized review file to change colors, formarts and pictures/ logos. Please do not modify the position of the rows & columns otherwise logged information will be saved in the wrong position of the worksheet.

Freeze & Delta

Freezes the current values of the active worksheet in a new ‘frozen’ worksheet and creates a new ‘delta’ worksheet that calculates the value increments between the original worksheet and the frozen worksheet. This function can be useful to understand how a particular worksheet is affected by changes of inputs or assumptions. It can also be used to perform sensitivity analysis around the frozen case.

Compare Worksheets

Searches all formula differences among selected worksheets and lists the findings in a table in a new workbook. This function can be useful to review worksheets that have common structures and formulas by easily identifying any differences. Before you run this function, you must select the two (or more) worksheets that you want to compare.

Compare Workbooks

Searches all formula differences among selected workbooks and lists the findings in a table in a new workbook. When using this function, a new window will ask you to select two (or more) workbooks among all opened workbooks in the current Excel instance. This function can be useful to compare different versions of a workbook by easily identifying any differences without having to check every single cell of every worksheet.

Clean-Up Functions

This group of functions is intented to help you identify hidden elements or data in workbooks. It can also be used to detect problems in worksheets in the clean-up process prior to sharing a file.

Inspect File

Searches for hidden and potentially problematic elements in the active workbook and displays the findings in a new window. Use this function to check for hidden data or to clean-up dirty elements from a model.

Hidden Sheets

Reports the number of hidden sheets in the active workbook. If the workbook contains one or more hidden worksheets, an ‘unhide all’ button will appear.

Very Hidden Sheets

Reports the number of very hidden sheets in the active workbook. If the workbook contains one or more very hidden worksheets, an ‘unhide all’ button will appear.

Password Protected Worksheets

Reports the number of password protected worksheets in the active workbook. If the workbook contains one or more password protected worksheets, an ‘unprotect all’ button will appear. Unprotecting password protected worksheets might require closing and reopening the workbook. Before this happens, MART will warn the user and ask whether changes should be saved before closing the file.

Hidden Named Ranges

Reports the number of hidden named ranges in the active workbook. If the workbook contains one or more hidden named ranges, a ‘remove all’ button will appear.

Visible Named Ranges

Reports the number of visible named ranges in the active workbook. If the workbook contains one or more visible named ranges, a ‘remove all’ button will appear. The user can remove individual visible named ranges using the ‘Name Manager’ located in the ‘Formulas’ menu of Excel’s ribbon.

#REF! Error Named Ranges

Reports the number of #REF! error named ranges in the active workbook. If the workbook contains one or more #REF! error named ranges, a ‘remove all’ button will appear. Note that these can be either hidden or visible. The user can find out more information about visible #REF! error named ranges using the ‘Name Manager’ located in the ‘Formulas’ menu of Excel’s ribbon.

External Named Ranges

Reports the number of external named ranges in the active workbook. If the workbook contains one or more external named ranges, a ‘remove all’ button will appear. Note that these can be either hidden or visible. The user can find out more information about visible external named ranges using the ‘Name Manager’ located in the ‘Formulas’ menu of Excel’s ribbon.

External Links

Reports the number of external source workbooks to which the active workbook has links. If the workbook contains one or more external links, a ‘remove all’ button will appear. External links can normally be broken by clicking on the ‘Break Link’ button in the ‘Edit Links’ function located in the ‘Data’ menu of Excel’s ribbon. In cases where Excel’s ‘Break Link’ function does not work, MART will search external links in workbook’s special elements in an attempt to remove all external links from the file.

Custom Styles

Reports the number of custom styles present in the active workbook. If the workbook contains one or more custom styles, a ‘remove all’ button will appear. In some cases, removing custom styles might require closing and reopening the workbook. Before this happens, MART will warn the user and ask whether changes should be saved before closing the file.

Custom styles are often unwanted inherited elements that increase file size and cause delays when switching windows from one workbook to another. In some cases, a user might not be able to move or copy worksheets into a workbook that had a large number of custom styles.

Conditional Formatting

Reports the number of cells containing conditional formatting in the active workbook. If the workbook has one or more cells containing conditional formatting, a ‘list’ and a ‘remove all’ button will appear. The ‘list’ button displays a new window that allows the user to navigate to the cells that contain conditional formatting.

Data Validation

Reports the number of cells containing data validation in the active workbook. If the workbook has one or more cells containing data validation, a ‘list’ and a ‘remove all’ button will appear. The ‘list’ button displays a new window that allows the user to navigate to the cells that contain data validation.

Comments

Reports the number of cells containing comments in the active workbook. If the workbook has one or more cells containing comments, a ‘list’ and a ‘remove all’ button will appear. The ‘list’ button displays a new window that allows the user to navigate to the cells that contain comments.

Metadata

Displays all metadata contained in the active workbook. Metadata are properties included in the file and might contain personal information such as the name of the original author or the last user who modified the file. This function can be useful to quickly check for the presence of such data. To fully remove this data, access the file’s properties in Windows Explorer, go to the ‘Detail’ tab and click on ‘Remove Properties and Personal Information’.

Used Range

Identifies the last used row and column for each worksheet of the active workbook and displays the results in a new window. This function can be useful to identify unnecessarily used areas in a worksheet, which might result on increased file sizes or on slow-loading files. Occasionally, it might help you identify corrupt worksheets to target during the clean-up process of a model.

Sheet Size

Estimates the size (in Kb) of every single sheet of the active workbook and displays the results in a new window. This function can be useful in cases where a workbook’s file size appears to be too large for its content and will assist you to identify any problematic or corrupt worksheets to target during the clean-up process of a model.