Cell Highlights in Excel

Excel is a powerful and versatile spreadsheet application that allows you to store, analyze, and present data in various ways. One of the features that can help you make your data more visually appealing and easy to understand is cell highlighting. Cell highlighting is the process of applying different colors, patterns, or styles to the cells or ranges of cells in a worksheet based on certain criteria or conditions. Cell highlighting can help you:

Emphasize important or relevant data
Identify errors or outliers
Compare or contrast values
Group or categorize data
Track changes or trends

In this article, we will show you how to highlight cells in Excel using different methods and techniques. We will cover the following topics:

How to create a custom cell style to highlight cells
How to use conditional formatting to highlight cells based on their values, formulas, or rules
How to use the IF function to highlight cells based on logical tests
How to use the ISERROR function to highlight cells that contain errors
How to use the VLOOKUP function to highlight cells that match values in another table

How to Create a Custom Cell Style to Highlight Cells

Unlike other Microsoft Office programs, such as Word, Excel does not provide a button that you can use to highlight all or individual portions of data in a cell. However, you can mimic highlights on a cell in a worksheet by filling the cells with a highlighting color¹.

For a fast way to mimic a highlight, you can create a custom cell style that you can apply to fill cells with a highlighting color. Then, after you apply that cell style to highlight cells, you can quickly copy the highlighting to other cells by using Format Painter¹.

To create a custom cell style to highlight cells, follow these steps¹:

Click Home > New Cell Styles.
In the Style name box, type an appropriate name for the new cell style. For example, type Highlight.
Click Format.
In the Format Cells dialog box, on the Fill tab, select the color that you want to use for the highlight, and then click OK.
Click OK to close the Style dialog box. The new style will be added under Custom in the cell styles box.
On the worksheet, select the cells or ranges of cells that you want to highlight.
On the Home tab, in the Styles group, click the new custom cell style you created.

To use Format Painter to apply a highlight to other cells, follow these steps¹:

Select a cell that is formatted with the highlight that you want to use.
On the Home tab, in the Clipboard group, double-click Format Painter, and then drag the mouse pointer across as many cells or ranges of cells that you want to highlight.
When you’re done, click Format Painter again or press ESC to turn it off.

How to Use Conditional Formatting to Highlight Cells Based on Their Values, Formulas, or Rules

Conditional formatting is one of the most powerful and useful tools in Excel. It allows you to apply different formats (such as colors, icons, data bars, etc.) to cells or ranges of cells based on their values, formulas, or rules².

Conditional formatting can help you make patterns and trends in your data more apparent, such as highlighting the highest or lowest values, showing data above or below average, identifying duplicates or unique values, etc².

You can apply conditional formatting to a range of cells (either a selection or a named range), an Excel table, and in Excel for Windows, even a PivotTable report².

To apply conditional formatting in Excel, follow these steps²:

Select the cells or ranges of cells that you want to format conditionally.
On the Home tab, in the Styles group, click Conditional Formatting.
Choose one of the options from the drop-down menu. You can choose from predefined rules (such as Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, Icon Sets) or create your own rules (such as New Rule or Manage Rules).
Depending on the option you choose, follow the instructions in the dialog box or pane that appears. You can specify the criteria or conditions for applying the format, choose the format type and style, edit or delete existing rules, etc.

How to Use the IF Function to Highlight Cells Based on Logical Tests

The IF function is one of the most commonly used functions in Excel. It allows you to perform logical tests and return different values based on whether the test is true or false³.

You can use the IF function to highlight cells based on logical tests by combining it with conditional formatting. For example, you can highlight cells that contain text values that match a specific word, number values that are greater than or equal to a certain threshold, date values that fall within a specific range, etc³.

To use the IF function to highlight cells based on logical tests, follow these steps³:

Select the cells or ranges of cells that you want to format conditionally.
On the Home tab, in the Styles group, click Conditional Formatting > New Rule.
In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
In the Format values where this formula is true box, enter the IF function with the logical test and the values to return. For example, =IF(A1="Yes",1,0) will return 1 if the cell A1 contains the word “Yes” and 0 otherwise.
Click Format and choose the format that you want to apply to the cells that meet the condition (such as fill color, font color, border, etc.).
Click OK to close the Format Cells dialog box and then click OK again to close the New Formatting Rule dialog box.

How to Use the ISERROR Function to Highlight Cells that Contain Errors

The ISERROR function is a useful function that can help you identify and handle errors in Excel. It returns TRUE if a cell contains any error value (such as #N/A, #VALUE!, #REF!, etc.) and FALSE otherwise⁴.

You can use the ISERROR function to highlight cells that contain errors by combining it with conditional formatting. For example, you can highlight cells that contain errors in formulas, references, or data types⁴.

To use the ISERROR function to highlight cells that contain errors, follow these steps⁴:

Select the cells or ranges of cells that you want to format conditionally.
On the Home tab, in the Styles group, click Conditional Formatting > New Rule.
In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
In the Format values where this formula is true box, enter the ISERROR function with the cell reference as the argument. For example, =ISERROR(A1) will return TRUE if the cell A1 contains an error value and FALSE otherwise.
Click Format and choose the format that you want to apply to the cells that contain errors (such as fill color, font color, border, etc.).
Click OK to close the Format Cells dialog box and then click OK again to close the New Formatting Rule dialog box.

How to Use the VLOOKUP Function to Highlight Cells that Match Values in Another Table

The VLOOKUP function is another powerful and popular function in Excel. It allows you to look up a value in a table and return a corresponding value from another column in the same row.

You can use the VLOOKUP function to highlight cells that match values in another table by combining it with conditional formatting. For example, you can highlight cells that contain values that are also present in another table or list.

To use the VLOOKUP function to highlight cells that match values in another table, follow these steps:

Select the cells or ranges of cells that you want to format conditionally.
On the Home tab, in the Styles group, click Conditional Formatting > New Rule.
In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
In the Format values where this formula is true box, enter the VLOOKUP function with four arguments: lookup_value (the value you want to look up), table_array (the table where you want to look up), col_index_num (the column number where you want to return a value from), and range_lookup (a logical value that specifies whether you want an exact or approximate match). For example, =VLOOKUP(A1,B:C,2,FALSE) will look up the value in cell A1 in column B of table B:C and return a corresponding value from column C if there is an exact match.
Click Format and choose the format that you want to apply to the cells that match values in another table (such as fill color, font color, border, etc.).
Click OK to close the Format Cells dialog box and then click OK again to close the New Formatting Rule dialog box.

Conclusion

In this article, we have shown you how to highlight cells in Excel using different methods and techniques. We have covered how to create a custom cell style to highlight cells, how to use conditional formatting to highlight cells based on their values, formulas, or rules, how to use the IF function to highlight cells based on logical tests, how to use the ISERROR function to highlight cells that contain errors, and how to use the VLOOKUP function to highlight cells that match values in another table.

We hope that this article has helped you learn how to highlight cells in Excel and make your data more attractive and understandable. If you have any questions or feedback, please feel free to leave a comment below. Thank you for reading! 😊

Cell Highlights in Excel