Master Excel: How to Highlight Non-Formula Cells

When working with large data sets in Excel, it’s often useful to highlight certain types of cells for better organization or visibility. One common task is identifying and highlighting cells that don’t contain formulas. This can be useful for data analysis, auditing, or simply cleaning up spreadsheets. In this post, we’ll explore two methods for highlighting cells without formulas in Excel, and explain why the first method is generally the better choice.

Method 1: Using Named Range and Conditional Formatting (Excel 2013 and later versions)

This method is efficient and modern, using Excel’s built-in functions for compatibility and ease of use.

Step 1: Define a Named Range

1. Go to the Formulas tab and click on Name Manager.
2. Click New to create a new named range.
3. In the Name box, enter a name for your range (e.g., NoFormula).
4. In the Refers to box, enter the following formula:

=NOT(ISFORMULA(INDIRECT("RC", FALSE)))

This formula checks whether a cell contains a formula and returns TRUE for cells that do not contain formulas.

Step 2: Apply Conditional Formatting

1. Select the range of cells that you want to format.
2. Go to the Home tab, click on Conditional Formatting, and choose New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter the formula:

=NoFormula

This will apply the formatting to cells where the named range NoFormula evaluates to TRUE (i.e., cells that don’t contain formulas).

5. Choose your desired formatting style, such as a fill color to highlight the cells.

Method 2: Using the GET.CELL Function (versions prior to Excel 13)

This method relies on an older Excel function that works by referencing the cell address and checking for formulas. Although it can be useful, it may not be as reliable or compatible as the first method.

Step 1: Define a Named Range

1. Go to the Formulas tab and click on Name Manager.
2. Click New to create a new named range.
3. In the Name box, enter a name for your range (e.g., CheckFormula).
4. In the Refers to box, enter the following formula:

=GET.CELL(48, INDIRECT(ADDRESS(ROW(), COLUMN(), 4)))

This formula retrieves information about the cell, and 48 refers to the formula type (TRUE for formulas, FALSE for regular values).

Step 2: Apply Conditional Formatting

1. Select the range of cells you want to format.
2. Go to the Home tab, click on Conditional Formatting, and choose New Rule.
3. Select Use a formula to determine which cells to format.
4. Enter the formula:

Copy=CheckFormula=FALSE

This will format cells where the CheckFormula value is FALSE (i.e., cells that do not contain formulas).

5. Choose your preferred formatting (e.g., cell fill color).

Why the First Method Works Better

While both methods are useful, the first method using ISFORMULA has several advantages:

1. Modern Excel Functionality: The ISFORMULA function is available in Excel 2013 and later versions, making it a more reliable and up-to-date solution.
2. Compatibility: Unlike the GET.CELL function, which is part of older macro functions, ISFORMULA is a native Excel function that works seamlessly across different versions of Excel.
3. Ease of Use: The first method is simpler and doesn’t require complex workarounds, such as indirect references or macro functions.
4. Better Performance: The ISFORMULA method tends to be faster and less prone to errors, especially when dealing with large data sets.

On the other hand, the GET.CELL function can be inconsistent, especially in newer Excel versions, and may require additional steps like enabling macros. Therefore, it’s generally better to rely on the ISFORMULA approach for highlighting cells without formulas.

Conclusion

Both methods allow you to highlight cells without formulas in Excel, but the first method using the ISFORMULA function is the more reliable and modern choice. It ensures compatibility across versions and is easier to implement. For most users, the Named Range and Conditional Formatting method will provide a smoother experience, making it the preferred option for working with Excel spreadsheets.