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

 

Related Posts

Conditional formatting based on another column in Google Sheets or Excel

In Microsoft Excel Spreadsheet or google sheets, conditional formatting is used to highlight any cell based on a predefined condition and the value of those cells. In the previous article, we described how to format a specific column or cell ...

Conditional Formatting based on Another Cell in Google Sheets/Excel

In Microsoft Excel Spreadsheet or google sheets, when you want to format a specified cell or cell range based on the value of a different cell, for example, formatting the first row based on the value of a cell in ...

How to Save Only One Single Worksheet in Workbook in Excel

Sometimes you may want to save only one worksheet in one workbook, actually there are two ways to implement this. The first one, you can remove the others from current workbook, you must confirm that these worksheets are useless before ...

How to Create Filter on Same Column but Multiple Worksheets Simultaneously in Excel

In daily work we may have multiple worksheets with the same template in excel. So, if we create a filter on column A on worksheet1, in most time we need to create filter on column A for the other worksheets ...

How to Highlight All Non-Blank Cells in Excel

Suppose we have a table with some blank cells, if we want to highlight all non-blank cells how can we do? Though we can press ctrl and pick each non-blank cell one by one, this way is very bothersome. We ...

Sidebar