Macabacus' Fast Fill Right/Down tools are similar to Excel's native Fill Right/Down functions, but far more versatile. With Fast Fill, you need not select the range you wish to fill; Macabacus will automatically determine how far right/down to fill, based on the presence of data surrounding the active cell. Fast Fill will also automatically skip empty spacer columns/rows between columns/rows of data. This tool also integrates and builds upon Excel's Fill Series Dates functionality, letting you enter a wide range of date formats, including those natively supported by Excel (e.g., 4Q13, 2014E, etc.), filling the selected range appropriately.
Intelligently fill data right.
Intelligently fill data down.
Adjust formulas of selected cells to replace errors from existing formulas with custom error messages (e.g., "NM," "NA") or values (e.g., 0) using an IFERROR wrapper. Repeat this keystroke to remove the IFERROR wrapper. Macabacus can prompt you for an error message or value each time, or you can set a default message/value to use automatically. Error Wrap options can be configured in the
Excel > Modeling section of the Settings dialog.
This tool trims extraneous spaces from cell formulas and values—double spaces, leading/trailing spaces, spaces between operators, etc. Extra spaces sometimes appear in imported data, preventing you from sorting or otherwise processing data. They can also result in unsightly data/text misalignment that requires correction before publishing. Macabacus cannot remove some extraneous spaces in formulas referencing other workbooks and worksheets.
Additionally, this tool removes worksheet names from formulas where not required. For example, Macabacus will change a formula on Sheet1 that reads =Sheet1!B3 to =B3, since the self-referenced sheet name in the formula is unnecessary.
Anchor each cell reference in selected cells' formulas to the referenced range. Repeat the operation to change the anchor point. For example, =A1, =$A$1, =A$1, =$A1.
Replace range names in a formula with their corresponding cell references.
This tool prepends an apostrophe to formulas and numeric inputs in selected cells to make formulas and numbers appear as text. Repeat the Comment Cells shortcut keystroke or click its ribbon button again to remove the apostrophes and restore the original formulas / values.
Invert the sign of numeric inputs and multiply formulas by -1 in the selected range. This is useful when changing the sign convention of expenses in an income statement, for example.
Wrap the selected cell's formula in parentheses and enter Edit Mode. This is useful when appending additional text to an existing formula.
This tool does not work if the native Excel option to edit formulas directly in cells is disabled (not recommended).
"Flattening" cells replaces their formulas with values, similar to copying the cells and then pasting values using Paste Special. These tools can be accessed from the
Formulas > Flatten menu.
Replace formulas in the selected range with values and color fonts according to AutoColor settings.
Replace formulas containing the specified function with values. Perform this operation on selected cells, selected worksheets, or the entire workbook. This can be useful when your formulas contain user-defined functions (UDFs) not available to colleagues with whom you want to share a workbook, for example.
Replace formulas that reference cells in other worksheets or workbooks with values. This allows subsequent separation of isolated worksheets from the workbook without breaking references (so that you can send a single worksheet from a workbook via email, for example). Note that this tool does not modify chart formulas and may not isolate formulas containing names that reference ranges in other workbooks.
You might assume that checking formulas for an exclamation point is sufficient to identify formulas that reference cells on another sheet or in another workbook, but this is not always true. Accordingly, Macabacus performs a more in-depth analysis to find formulas containing off-sheet references, which may have performance consequences. If you require faster execution and are confident that all of your off-sheet references include an exclamation point, it is trivial to write a macro that simply checks formulas for an exclamation point.
Paste copied cells exactly, preserving original formula cell references.
Paste number formats only.
Transpose paste copied cells as links, number formats, or exact formulas.
Paste copied cells preserving original formulas, except where relative cell references within the copied range refer to cells within the copied range, in which case the original formulaic relativity of the copied range is preserved within the pasted cells. This is sometimes a more intuitive alternative to Paste Exact.
Paste links to copied cells. This is similar to Excel's native Paste Link, except that Macabacus (a) provides a convenient keyboard shortcut, (b) does not paste links to empty cells, (c) more reliably applies number formats from the copied range, and (d) applies AutoColor font colors to the pasted range if AutoColor on Entry is enabled.
Insert copied or cut cells at the current selection. If you have copied/cut an entire row/column, Macabacus automatically inserts as many rows/columns as are required to accommodate the pasted rows/columns. If you have not copied/cut an entire row/column, Macabacus will prompt you for whether you want to insert rows or columns to accommodate the cells to be pasted.
For example, suppose you cut two entire rows and move the cursor up several rows to your intended insertion point. Upon the Paste Insert operation, Macabacus will insert two rows above the insertion point, paste the cut rows, and delete the original cut rows. Using native Excel functionality, you would have to manually insert the two rows above the intended insertion point before pasting, which involves twice as many keystrokes.
Capture row and column information (size, hidden state, and grouped state) from the selected range for pasting to another range.
Applies previously captured row and column information (size, hidden state, and grouped state) to the selected range.
Macabacus can replace certain Excel functions within your formulas with the cell references or values to which they evaluate, simplifying formulas and making it easier to understand and audit models. Although Macabacus' formula evaluation tools are extremely effective, Macabacus may be unable to evaluate certain formulas. If Macabacus cannot evaluate a formula, or if the evaluated formula does not produce the same result as the original formula, Macabacus will revert to the original formula and notify you.
Replace multiple Excel functions in selected formulas at once with the cell references or values to which those functions evaluate. You can specify which functions Simplify Formula replaces in the
Formulas > Evaluate Functions > Options menu.
Replace a TRANSPOSE formula array with direct links to source cells.
Replace the following Excel functions in formulas with the cell references or values to which they evaluate. Access these tools from the
Formulas > Evaluate Functions menu.
Quickly apply data validation commonly used in financial modeling to restrict input values in cells. The predefined data validation options found on the
Macabacus > Formulas > Data Validation menu are:
These predefined data validation rules can also be included in Macabacus styles.
Go to the cell with the minimum value in the selected range, or if only one cell is selected, in the contiguous range. This is a quick way to find a 52-week low in raw stock price data, for example. You can also access this tool with the
Macabacus > Formulas > Go To Min button.
Go to the cell with the maximum value in the selected range, or if only one cell is selected, in the contiguous range. This is a quick way to find a 52-week high in raw stock price data, for example. You can also access this tool with the
Macabacus > Formulas > Go To Max button.
This documentation refers to Macabacus version 9.1.1. 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.