Excel macro to identify cells containing formulae

Quite often, we come across large excel sheets with huge volumes of data and a whole lot of formulae. It becomes very tricky to check the validity of each formula.

Even greater is the risk that someone might have deleted a formula and entered a figure manually.

Here is a setup in excel which can help you to identify all the cells having formulae and potentially identify any manual interventions.

The logic / reasoning follows at the end of the solution.

Open the excel file you want to use

Select all the cells which you want to cover under this control.

Select Insert à Name à Define à a dialog box will pop up

In the Names in Workbook field, enter this value: CellHasFormula

In the refers to field enter this formula: =GET.CELL(48,INDIRECT(“RC”,FALSE))

Press Ok to close the dialog box.

Select Format à Conditional Formatting à a dialog box will pop up

In the dialog box, select “Formula is” in the first field.

Type this value in the second field: =CellHasFormula

Click on the format button and select the formatting you want.

Then press ok to exit the dialog boxes.

 

As you can see below, the fields which contain formula have a different formatting.

 

If you change any of the fields manually or add a formula anywhere, you will see the formatting changes automatically

Step by Step analysis:

The 2 key steps in this solution are:

Naming the range with a formula è =GET.CELL(48,INDIRECT(“RC”,FALSE))

I’m not sure of the roots of this formula, but apparently it is one of the old excel macro commands “Get.Cell” from the time before VB macros were included in excel.

Get.Cell is used to retrieve the metadata (or properties) of a cell and use them in the macro.

“48” is probably the command used to check whether it is a formula or not. I’m still trying to find what other commands are possible with Get.Cell.

“Indirect(“RC”,…” is the normal excel formula which is used to refer to the cell itself.

“CellHasFormula” is just a name given to this range. It can be anything you want.

Adding Conditional formatting è In the second stage, we just make a reference to “CellHasFormula” and decide what formatting we want if the cell has a formula.

You will notice that we have not written any macros here, but still when you open the file next time, excel will give you a macro warning. That is because we are using “Get.Cell”. You will need to enable macros to be able to use this solution.