Identifying Constants in a Column

Ellis has a large worksheet where formulas have been copied down thousands of rows. Over time, some of the formulas were accidentally replaced with explicit values. He wonders if there is a way to quickly identify the cells in the column that contain constants instead of formulas.

Yes, there is a quick way, but first you need to make a decision. Do you want to simply highlight constants, as they are now, or do you want to set up a conditional formatting rule that will highlight any constants that may be added in the future?

If you want to highlight the current constants, start by selecting the cells you want to check. For instance, if Ellis has his formulas (and occasional constants) in column D, then he can select column D. With this prep work done, follow these steps:

1. Press F5 or Ctrl+G. Excel displays the Go To dialog box.
2. Click the Special button. Excel displays the Go To Special dialog box. (See Figure 1.)



Figure 1. The Go To Special dialog box.

3. Click the Constants radio button.
4. Click OK. Excel modifies the selection so that only those cells containing constants are selected.
5. Use the controls on the Home tab of the ribbon to format the selected cells. For instance, use the Fill Color tool to fill the cells with yellow or another color.

At this point you can easily scroll through your worksheet and identify those cells in column D that contain constants instead of formulas.

If you want to use a conditional formatting rule to highlight constants, follow these steps:

1. Select the cells that should all contain formulas. For instance, if the cells are in column D, select all of column D. When you do so, then cell D1 should be the active cell in the selection.
2. Make sure the Home tab of the ribbon is displayed.
3. Click the Conditional Formatting tool. Excel displays a series of choices.
4. Click New Rule. Excel displays the New Formatting Rule dialog box. (See Figure 2.)



Figure 2. The New Formatting Rule dialog box.

5. In the Select a Rule Type area at the top of the dialog box, choose Use a Formula to Determine Which Cells to Format. (See Figure 3.)



Figure 3. Use a Formula to Determine Which Cells to Format.

6. In the formula space, enter the following formula:

        =AND(NOT(ISFORMULA(D1)),NOT(ISBLANK(D1)))

7. Click the Format button. Excel displays the Format Cells dialog box. (See Figure 4.)



Figure 4. The Format Cells dialog box.

8. Using the controls in the dialog box, specify how you want the cells containing constants to be displayed.
9. Click OK to dismiss the Format Cells dialog box.
10. Click OK to accept your conditional format.

That's it. Now, any cell to which the rule is applied that doesn't contain a formula will be highlighted as you specified in step 8. The conditional formatting rule makes sure that formulas, blanks, and error conditions resulting from formulas are not highlighted. Thus, only constants will be highlighted. You should note, as well, that the conditional formatting rule will only work if you are using Excel 2013 or a later version. (It was this version when the ISFORMULA function was first introduced.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13999) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.