Highlighting Static Values

Bill has a large worksheet in which he would like to locate and highlight all the cells that contain static values. He wonders if there is an easy way to fill all those cells with yellow.

There are actually two easy ways, and the method you choose should depend on how you work with data in your worksheet.

The first method is great for data that doesn't change too much. Follow these steps:

1. Press F5. Excel displays the Go To dialog box. (See Figure 1.)



Figure 1. The Go To dialog box.

2. Click the Special button. Excel displays the Go To Special dialog box. (See Figure 2.)



Figure 2. The Go To Special dialog box.

3. Select the Constants radio button. The four check boxes under the Formulas option then become available. (This can be a bit confusing. Why Microsoft made the Constants radio button control some check boxes under a different radio button is not immediately clear.)
4. Make sure that all the check boxes under the Formulas radio button are selected. (They should be selected by default.)
5. Click OK. Excel selects all the constants (cells that don't contain formulas) in the worksheet.
6. Use the tools on the Home tab of the ribbon to fill the selected cells any way you desire. (Bill wanted to use a yellow fill color.)

If your data is more dynamic in nature and you want the fill color updated automatically, then you can create a conditional formatting rule to handle the highlighting. Follow these steps:

1. Select cell A1.
2. Press Ctrl+A to select all the cells in the worksheet. (If you want the conditional formatting rule to apply to just a range of cells, then select just those cells.)
3. Make sure the Home tab of the ribbon is displayed.
4. Click the Conditional Formatting tool in the Styles group. Excel displays a series of choices.
5. Click New Rule. Excel displays the New Formatting Rule dialog box. (See Figure 3.)



Figure 3. The New Formatting Rule dialog box.

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



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

7. In the Format Values Where This Formula Is True box, enter the following formula. (If cell A1 is not the active cell in your selected cells, then change A1 to the appropriate cell.)

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

8. Click Format to display the Format Cells dialog box.
9. Set the formatting options to highlight the Fridays as desired.
10. Click OK to dismiss the Format Cells dialog box.
11. Click OK.

The rule you specified in step 7 results in all non-blank cells that contain constants being highlighted. As your data changes in the worksheet, then the highlighted cells change as well.

Note that this conditional formatting approach will work just fine if you are using Excel 2013 or later, when the ISFORMULA function was introduced. If you are using an earlier version of Excel, then you will need to create a macro that returns TRUE if a cell contains a constant and then reference that function in the formula in step 7.

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