How to Highlight Selected Cells in Excel (5 Easy Ways)

Highlighting cells allows users to make entries stand out from the rest of the dataset and make sure entries get investigated. In this article, we use multiple Excel features, Formulas, and VBA Macro Code to highlight selected cells in Excel.

Method 1: Using Define Name to Highlight Selected Cells in Excel

To easily highlight the selected cell or range of cells, we can just assign a specific name to the selected cell or range of cells. We can assign a name using the Define Name feature available in Formulas Tab.

Step 1: Go to Formulas Tab > Select Define Name (in Define Names section).

Step 2: A New Name window will open.

In that window, type a name (i.e., Category) you desire to in the Name command box.

Select Define Name as Scope.

Click on the Icon right to the Refers to command box to select cells or a range of cells you like to assign the Name Category.

Step 3: Select the cells or range of cells (i.e., B4:B21) to define as Category.

Step 4: Tab ENTER to return to the New Name window, then Click OK.

Now, if you go to the Name Box (Left box to the Formula Bar box), you’ll see a name has been assigned.

Name Box

You can assign names simply using Excel’s Name Box (Left to the Formula Bar Box). Excel’s Name Box will do the trick as Define Name in Formula Tab does.

Step 1: Select cells or a range i.e., C4:C21) of cells, then go to the Name Box and type any Name (i.e., Product) you want. Hit Enter.

You assign Names by any means you want to, as we described above. After assigning Names, you can simply select any Names from the Name Box and the specific cells get highlighted.

You can customize the selection by assigning multiple names depending on your data types.

Method 2: Applying Excel Format Cells Feature to Highlight Selected Cells

Format Cells is a feature in Excel that offers various cell formatting such as Number Types, Alignment, Font, Border, Fill, and Protection. In this case, we use Fill to highlight selected cells or a range of cells.

Step 1: Select cells or a range of cells, then Right-Click on any of the selected cells. The Context Menu pops up. From the Context Menu options, select Format Cells.

Step 2: Format Cells command window appears. In the Format Cells window, Select Fill as the highlighting medium, then Choose any color (i.e., Yellow) you like.

Click OK.

You can bring up the Format Cells window by Pressing CTRL+1 altogether.

Execution of the steps highlights the selected cells or range of cells as you expected them to be.

Method 3: Using Cell Styles to Highlight Selected Cells in Excel

Excel’s Cell Styles offers custom Cell Formatting for selected cells or a range of cells. In the case you want to highlight the specific cells always in a fixed style, Cell Styles comes in handy.

Step 1: Go to Home Tab > Select Cell Styles (in Styles section) > Select New Cell Style.

Step 2: The Style Command Box pops up. In the Style command box, Type a Style name (i.e., Highlight). Click on Format.

Step 3: Click on the Fill section, then Choose any color media (i.e., Yellow) to Highlight the cells. Click OK.

Step 4: Again, Click OK on the Style dialog box.

You can select other cell formats, as we mentioned in Method 2. However, as we want to simply Highlight the selected cells, we use Fill to be the Highlight medium and other cell formats option as optional.

Step 5: Now, Select cells or a range of cells. After that, Go to Home Tab > Select Cell Styles (in Styles section).

You’ll be seeing a custom Highlight cell style containing all the highlight options you put into Step 3.

Click on the Highlight cell style option.

All the selected cells get highlighted by the Fill color (i.e., Yellow) you chose.

Method 4: Using Excel Conditional Formatting to Highlight Selected Cells (Row and Column)

To highlight the active cell’s row and column, we can use conditional formatting. In this case, we use formulas to highlight the active cell’s row and column together or individually.

Case 1: Both Row and Column in Same Color

To highlight both active cell’s rows and columns we use the OR function.

Step 1: Click on any cell (i.e., C12) you want both row and column to be highlighted.

Go to Home Tab > Select Conditional Formatting (in Style section)> Choose New Rule.

Step 2: New Formatting Rule window opens up. In the New Formatting Rule window, Choose Use a formula to determine which cell to format from Select a Rule Type option.

Paste the following formula under Edit the Rule Description box.

=OR(CELL("col")=COLUMN(),CELL("row")=ROW())

Here in the formula,

CELL(“col”)=COLUMN(); compares the selected cell’s column that is CELL(“col”) to the cell’s column to be highlighted that is COLUMN().

CELL(“row”)=ROW();compares the selected cell’s row that is CELL(“col”) to the cell’s row to be highlighted that is ROW().

We patch them with the OR function to highlight the cell’s row and column if either of the arguments is TRUE.

Follow Step 3 of Method 3 to choose the Fill color (i.e., Yellow).

Click OK.

You’ll see an outcome similar to the picture below.

Step 3: Again, Go to Conditional Formatting > Choose Manage Rules.

Step 4: Select the whole range (i.e., B3:H21) in the Applies to command box. Click on Apply.

It results in highlighting the active cell’s row and column depicted in the following image.

Case 2: Row and Column in Different Color

We Select Clear Rules from the Conditional Formatting we did in the previous case before demonstrating this case. Clear Rules formats all the previous formulas we apply to highlight the cells.

Step 1: Repeat Steps 1 to 4 of Case 1.

Replace the formula with two New Rules.

=CELL("row")=ROW()

=CELL("col")=COLUMN()

These two formulas declare the same argument as they did in Case 1.

Select two different Fill colors following Step 3 of Method 3.

Select the whole dataset to Applies to the dialog box.

Click on Apply.

The resultant picture will be similar to the picture below.

Case 3: Only Row or Column

You can also select any of the active cell’s rows or columns to highlight. In order to do so, you have to select only one Fill color for the row or column, selecting the other one’s Fill color No Color.

Step 1: Repeat Step 1 of Case 2.

Choose any of the Fill Color as No Color. In this case, we select the row formula to highlight the row with no color. It can be done vice versa. And the results also will be the opposite of the present instance.

Click on Apply.

The outcome will be similar to the following picture.

You can choose No Color as Fill Color for Column and the result will be the opposite.

Method 5: Using VBA Macro Code to Highlight Selected Cells

VBA Macro Code enables dynamic highlighting of selected cells with a couple of lines of code.

Step 1: Press ALT+F11 altogether. Microsoft Visual Basic window opens up.

Step 2: Double-click on Sheet3(VBAMacroCode) to bring up the Visual Basic Worksheet. Paste and save the following code in the Worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Worksheet.Cells.FormatConditions.Delete
Selection.FormatConditions.Add xlExpression, , "TRUE"
Selection.FormatConditions(1).Interior.Color = vbYellow
End Sub

The code deletes any previous cell formats and formats any selection, particularly in Sheet3. In the code, we chose Yellow as the highlighting color, you can choose yours.

Step 3: Back to the Excel Worksheet, select any cells or range of cells; you’ll see it simultaneously get highlighted with Yellow color.

Download Practice Workbook

Highlight Selected Cells in Excel

Conclusion

In this article, we use Excel’s features such as Define Name, Format Cells, Cell Styles, and Conditional formatting, as well as VBA Macro Code to highlight selected cells. Defined Name, Format Cells, and Cell Styles features highlight any selected cells with no dynamic operability. Conditional formatting highlights active cell’s rows and columns, offering one-dimensional usage. However, VBA Macro Code offers dynamic highlighting of selected cells as you move on from cell to cell or range to range. Hope the above discussed methods fulfil your quest. Comment if you have further queries or have something to add.

<< Go Back to Highlight Cell | Highlight in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!