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

How to Delete Entire Rows if Blank Cell Exists in Excel
How to Delete Entire Rows if Blank Cell Exists in Excel12

Sometimes we need to delete the entire row which contains blank cell from a table. Though we can delete them by right click on row index and just ‘Delete’ them manually, if they are not adjacent or the table is ...

How to Save Only One Single Worksheet in Workbook in Excel
How to Save Only One Single Worksheet in Workbook 7

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
Create Filter on Same Column but Multiple Worksheets 10

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 Sum All Digits in A Cell in Excel
Sum All Digits in A Cell 6

If we enter a number 1234 in a single cell and we want to get the sum of all digits like 1+2+3+4=10 in another cell, how can we do? As there is no function like SUM to sum all digits ...

How to Highlight All Non-Blank Cells in Excel
Highlight All Non-Blank Cells 15

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 ...

How to Remove Conditional Formatting on Blank Cells in Excel
remove conditial formatting on blank cell6

This post will guide you how to remove conditional formatting if cell is blank in Excel. How do I skip conditional formatting for blank cells in Excel 2013/2016. Remove Conditional Formatting on Blank Cells Assuming that you have a list ...

Conditional Formatting Rows by Groups in Excel
conditional format rows by group8

This post will guide you how to apply conditional formatting for alternate row color based on groups in your worksheet in Excel. How do I alter row color by groups using conditioanl formatting in Excel 2013/2016. Alternating Rows Color by ...

How to Highlight Cell or Row If Date Is In Current Day/Week/Month in Excel
highlight cell or row if date8

This post will guide you how to highlight cell if date is the current day or is in the current week or month in Excel. How do I highlight row if date is in current week or month with conditional ...

How to Highlight Cells Greater Than a Certain Length in Excel
highlight cells based on cell lenght6

This post will guide you how to highlight a cell that contains more than a certain length using Conditional Formatting function in Excel. How do I highlight cells greater than or less than a certain text length in Excel. Highlight ...

How to Highlight Dates Older Than 30 Days in Excel
highlight dates older than 30 days5

This post will guide you how to highlight dates older than 30 days in Excel. How do I use conditional formatting to highlight if date is greater than 30 days old in Excel. How to highlight a row or cell ...

Sidebar