IDENTIFY FORMULAS WITH CONDITIONAL FORMATING

You can use Conditional Formatting to apply special formatting to cells that contain formulas. With this technique you can set up your worksheet so that all formula cells get a yellow background, for example.

This is useful if you are using formulas to copy values from other worksheets, a common way to build an Excel spreadsheet from Revit schedule exports.

Follow these steps:

Formula tab, Define Name.

In the Define Name dialog box, enter the following in the 'Names in workbook' box:

CellHasFormula

Then enter the following formula in the "Refers to" box

=GET.CELL(48,INDIRECT("rc",FALSE))

Click OK.

Next create the Conditional Formatting rule.

Select all the cells to which you want to apply the conditional formatting.

Home tab, Conditional Formatting

Select New Rule...

Select Rule type Use a formula to determine which cells to format

Type following in Rule Description:

=CellHasFormula

Click the Format button and select the type of formatting you want for the cells that contain a formula.

Click OK.

After you've completed these steps, every cell that contains a formula and is within the range you selected will display the formatting of your choice.

Note that you will have to save the file in macro enabled format (.xlsm) to retain these settings (Excel will ask you when you save).