Visualizations

Quickly identify formula errors and inconsistencies in large numbers of cells at once, eliminating the need to check each cell individually and tediously.

The following visualizations can be applied the from  Macabacus > Visualize menu.

You probably—and often—key the familiar F2 > Escape > Arrow sequence repeatedly to inspect formulas in adjacent cells for consistency. The Uniformulas tool performs a similar check in far fewer keystrokes by highlighting adjacent cells with the same formulaic structure and in the same contiguous range as the audited cell.

In the example below, we perform the Uniformulas operation on the cell computing gross margin in the first period. This selects adjacent cells in the same contiguous range with the same formulaic structure, as shown. The cell computing EBIT margin in the third period is not selected, however, because its formulaic structure differs from that of adjacent cells. This could (and in this case, does) indicate a formula error.

The Uniformulas tool is best accessed via shortcut keystroke, but can also be accessed via the Macabacus > Visualize > Uniformulas button. You can set the Uniformulas tool to enter Edit Mode on the audited cell (as shown above)—similar to keying F2 —from the Application Settings dialog. When this feature is set, the Uniformulas tool behaves like an enhanced version of the familiar F2 > Escape auditing sequence.

Formula Flow helps you visualize the formula structure of your spreadsheets using patterns to quickly highlight errors and other inconsistencies. As you change the contents of cells visualized using Formula Flow, the visualization updates automatically. Formula Flow uses the following rules to apply the turquoise patterns shown in the example below:

• Uninterrupted horizontal line patterns in a contiguous range of cells indicate that formulas are consistent across columns.
• Uninterrupted vertical line patterns in a contiguous range of cells indicate that formulas are consistent down rows.
• Uninterrupted horizontal and vertical line (i.e., crosshatched) patterns in a contiguous range of cells indicate that formulas are consistent both horizontally and vertically.
• Darker turquoise shading indicates a formulaic inconsistency within the inspected range (you can sometimes see Excel's green "inconsistent formula" indicators in the corner of these cells, too).

Changes made to your spreadsheet by Formula Flow cannot be undone, except as indicated below, and will clear Excel's Undo stack, so consider saving your work before using Formula Flow.

Dependency Density allows you to visualize link density as measured by the relative number of dependents for each cell in the selection. It accomplishes this by shading cells orange based on how many dependents each cell in the selection has—the darker the shading, the more dependents. In financial modeling, horizontally adjacent cells often have the same number of dependents. Thus, you would expect the Dependency Density visualizer to generally produce the same orange shading intensity within a row of cells.

Magnitude Map is an improvement on Excel's native conditional formatting color scale that utilizes more intuitive coloring when working with data containing both positive and negative numbers. This tool is great for identifying outliers, as cells with the largest magnitudes (positive or negative) are shaded darker.

Easily identify the roles cells play (i.e., formulas, inputs, partial inputs, etc.) by highlighting them according to your AutoColor settings.

Where possible, translate the formula in the selected cell (i.e., =F16 + F15) into plain text as indicated by row labels (i.e., EBITA = EBIT + Amortization).

Insert a summary of the selected cell’s formula below the selected cell to provide an at-a-glance snapshot of the audited cell’s precedents.

You can remove Formula Flow, Dependency Density, Magnitude Map, and Functional Map visualizations from the active worksheet by clicking the Macabacus > Visualize > Clear Visualizations button, or using native Undo functionality (except Formula Flow).

Note that Macabacus checks the entire "used range" on the worksheet for visualizations. If the used range is very large, clearing visualizations may take some time and appear to freeze Excel. If clearing visualizations takes too long, try using Macabacus' Clean Used Ranges tool to improve performance. If you still experience slowness, you can use native Excel functionality to remove visualizations (which are just cell fill patterns), or restrict the use of visualizations to worksheets with fewer used rows and columns.

_{This documentation refers to the latest Macabacus version. Some features and descriptions of these features may not apply to older versions of Macabacus. Update your Macabacus software to take advantage of the latest features.}