How to Highlight Cells Based on Their First Letter/Character by Conditional Format in Excel

Sometimes we want to highlight some cells based on certain criteria, for example highlight all cells which the first letter is X, this requirement is quite commonly used in searching. Actually, to implement this function, you can highlight these cells by conditional format in excel. This function can help you quickly locate and highlight the cells match your criteria. This article will do a simple introduction about how to highlight cells with the same first letter in their contents by conditional format.

Highlight Cells Based on First Letter/Character by Conditional Format


We prepare a list of products for example. They have different serial number. See example below.

Highlight Cells Based on Their First Letter 1

And if we want to check the sales for N or M type of products, we can highlight all cells based on N or M serial number in A column. Now let’s started to learn how can we highlight them by conditional format.

Step 1: First, select the range you want to do filter. In this case select A2 to A9.

Step 2: In ribbon, click Home->Conditional Formatting under Styles.

Highlight Cells Based on Their First Letter 2

Step 3: Click the arrow button on Conditional Formatting icon to load all sub menus, select New Rule in it.

Highlight Cells Based on Their First Letter 3

Step 4: In the pops up New Formatting Rule window, in Select a Rule Type panel, select Use a formula to determine which cells to format.

Step 5: In Edit the Rule Description panel, in Format values where this formula is true textbox, enter the formula =LEFT(A2,1)=”N”. Notice that if you want to highlight cells with the first number is M, you can change the formula like =LEFT(A2,1)=”M”.

Highlight Cells Based on Their First Letter 5

Step 6: After entering the formula, click Format button to specify you cell format for highlighted cells. You can design the format by your demands. In this case, we just mark them with light blue background in cells. In Format Cells, click Fill, select Background Color, then click OK.

Highlight Cells Based on Their First Letter 6

Step 7: Returns to New Formatting Rule window, see the Preview. Verify that cell is filled with light blue background. Then click OK.

Highlight Cells Based on Their First Letter 7

Step 8: Verify that in Serial Number column, cells with N type are highlighted properly.

Highlight Cells Based on Their First Letter 8

If you want to only show the rows with N type products, you can follow below steps to filter data.

Step 9: Select A1 & B1 & C1, click Data->Filter. Then A & B & C columns are added filter.

Highlight Cells Based on Their First Letter 9

Step 10: Click filter arrow in A column, select Filter by Color, then select background light blue in Filter by Cell Color.

Highlight Cells Based on Their First Letter 10

Step 11: Verify that only N type products rows are listed.

Highlight Cells Based on Their First Letter 11

 

You might also like:

Sidebar