How to Highlight in Excel (6 Suitable Examples)

In this article, you will learn about how to highlight cells in Excel. Here, we will describe how you can highlight a text, an entire row, or even alternative rows. Then, you will get the concept of how to highlight cells based on condition.

Furthermore, we will show you the use of Format Painter, make a change to the highlight color, and how to color the active row/column.

Highlighting cells or ranges of data makes it easier to identify and differentiate the important information.

By applying various highlighting techniques, you can draw attention to specific data points, trends, or outliers. Moreover, you can make your dataset more visually appealing and easier to interpret with the help of these highlighting.

Here, we will explore different examples to highlight data in Excel, starting with simple cell formatting options and progressing to more advanced conditional formatting techniques.

Download Practice Workbook

You are recommended to download the Excel file and practice along with it.

Highlighting Cells.xlsm

How to Highlight in Excel: 6 Suitable Examples

1. Using Cell Styles to Highlight Cells

Select cells >> from Home tab >> go to Styles group >> select Cell Styles >> choose your preferable style. Here, I have chosen Accent1.

Below, I have attached the output.

2. Use of Font Color for Highlighting Text Within a Cell in Excel

Press the CTRL key >> select the target cells one by one >> from the Home tab >> go to Font group >> set the color for highlighting.

As a result, you will get the highlighted text.

3. Applying Format Cells to Highlight Entire Row

Click on the row index >> press CTRL+1 to get the Format Cells dialog box.
In the Format Cells dialog box >> go to Fill menu >> choose a background color >> click OK.

Here, you can see the output.

Read More: Highlight a Row in Excel

4. VBA Code to Highlight Alternative Rows in Excel

From the Developer tab >> go to Visual Basic.

Then you will get the VB Editor.
Now, from Insert tab >> go to Module >> write the following code in Module1.

Sub ColorEvenRows()
Dim my_Rng As Range
Dim my_i As Integer
Set my_Rng = Selection
For my_i = 1 To Selection.Rows.Count
    If (my_Rng.Cells(my_i, 1).Row) Mod 2 = 0 Then
        Range(my_Rng.Cells(my_i, 1), my_Rng.Cells(my_i, my_Rng.Columns.Count)). _
        Interior.Color = RGB(50, 190, 150)
    End If
Next my_i
End Sub

Code Breakdown

Here, I created a sub-procedure named ColorEvenRows.
Then declared my_Rng as Range and my_i as Integer.
Here I used the Selection property. So, this code will run for the selected cells.
The For Nex loop will go till the last row of selected cells.
Then the If End If statement will check whether the row number is even or odd.
If the row number is even then the filled cells will be colored with the RGB color combination of 50,190 and 150.
Here, RGB is a function that defines color by combining red, green, and blue. The RGB function takes three arguments: the red value, the green value, and the blue value. Each of them is ranging from 0 to 255.

Now, save the code and go back to the worksheet.
Then from the Developer tab >> go to Macros >> in the Macro dialog box >> chose the name of the macro >> press Run.

Below, you can see the result.

5. Using Fing and Replace Feature for Highlighting Cells

Select whole data >> press CTRL+F to get the Find and Replace dialog box.
In the Find and Replace dialog box >> go to Find section >> at Find what: box >> write Pencil >> at Look in box >> select Values >> click on Find All >> go down >> press CTRL+A to select all the getting result.

Now, from the Home tab >> go to Font group >> from Fill Color >> choose a color. So, you will get cells having “Pencil” word are highlighted.

6. Use of Conditional Formatting to Highlight Cells Based on Criteria in Excel

6.1 Using Text that Contains Feature for Highlighting Text Values

Select dataset >> from the Home tab >> go to Styles group >> from Conditional Formatting >> Highlight Cells Rules >> select Text that Contains.

Then, you will get the dialog box named Text That Contains >> write “Notebook” in Format cells that contain the text >> choose a color >> press OK.
So, you will get the cells having “Notebook” are marked.

6.2 Using New Rule to Highlight Cells Having Values That Satisfied the Formula

Select dataset (don’t select headers) >> from the Home tab >> go to Styles group >> from Conditional Formatting >> select New Rule.

In the New Formatting Rule dialog box >> select Use a formula to determine which cells to format >> go to Format values where this formula is true >> write the formula or logic >> press Format >> go to Fill section >> set a color >> press OK to Format Cells >> press OK to New Formatting Rule.

Following, I have attached the output.

How to Use Format Painter to Highlight Other Cells

Select the cell having format >> from Home tab >> go to Clipboard group >> double-click on Format Painter.
After double-clicking on Format Painter, you will get a plus icon along with brush.

Then go to every cell and click there to copy the format.
After you have done >> click on Format Painter again to stop copying format.

Note: Until you have the plus icon along with brush, while you are clicking any cells >> you will get the format copied to these cells. So, you must click on Format Painter again to get the cursor in general mode.

How to Change the Format Color of Highlighted Text

Select the cells for which you want to change the highlighted color.
Now, from the Home tab >> go to Font group >> from Font Color >> set a new color.

How to Highlight Active Row and Column in Excel

Go down to the worksheet >> right click on the name of the sheet >> from the Context Menu Bar >> select View Code.

Now, copy the code in the VB Editor.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Cells.Count > 1 Then Exit Sub
  Application.ScreenUpdating = False
  'This will clear all the Fill color.
  Cells.Interior.ColorIndex = 0
  With Target
    .EntireRow.Interior.Color = vbYellow
    .EntireColumn.Interior.Color = vbYellow
  End With
  Application.ScreenUpdating = True
End Sub

Code Breakdown

This code will work while you change the selection.
At first, you must keep the interior color of cells 0 to remove the previous color.
For the active row/column, set your preferred color.
Now, go to the defined sheet and click on any cell >> get the related row and column highlighted.

Frequently Asked Questions

1. What is the fastest way to highlight in Excel?

Use SHIFT+Right/Left/Up/Down Arrow to select adjacent cells.
Use CTRL key to select nonadjacent cells.
Or you may use CTRL+SHIFT+Right/Left/Up/Down Arrow for selecting the all cells of the same row/column.
Then, from Fill Color >> set the color.

2. How do you highlight a column in Excel?

Click on the column index number >> from Home tab >> Fill Color >> set the color.

3. How do I highlight part of text in an Excel cell?

Select that cell >> go to Formula Bar >> select the text portion >> from Font Color >> choose a color.

How to Highlight in Excel: Knowledge Hub

Conclusion

So, this article provided a basic guide on highlighting cells in Excel. Here, we tried to cover some methods for highlighting text, entire rows, and alternative rows. Also, we explained how to highlight cells based on conditions and demonstrated the use of the Format Painter to copy formatting across cells.

Hopefully, you got these methods. If you have any query then please comment below. For more Excel related content, you may visit our site.

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!