Formulas & Data

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).

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.

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.

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.

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.