9 Examples to Highlight Cells Based on Value in Excel

Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. There are frequent examples where you need to highlight cells based on values in Excel. Using various ways, you can highlight them based on requirements as per requirement. This article will help you with nine ways to highlight cells in excel based on value. It is applicable for multiple cells in Excel and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.

📁 Download Excel File

Download the Excel file below.

Learn to Highlight Cells Based on Value in Excel with These 9 Examples

The article is going to introduce nine approaches in Excel to highlight cells in excel based on value. The methods will help you understand the uses elaborately. The following discussion will cover the diverse uses of the Conditional Formatting option in Excel. Therefore, users will get a walkthrough of these solutions. A dataset will be modified in the following methods below to help you understand.

You can highlight cells based on a value in excel. One of the ways is using the Greater Than a Specific Value option from the  Conditional Formatting.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select the range D6:D10.
• Secondly, choose the Greater Than menu from the Highlight Cells Rules under the group of Styles. You will find it in the Conditional Formatting option under the Home tab.

• Thirdly, you should get a Greater Than dialog box.
• Fourthly, write “835” in the Greater Than box like the following image.
• Fifthly, hit the OK button.

• Sixthly and finally, you will get three cells that have a greater value than 835.

There are a lot of types of datasets that require top values. Here, you will see the method to highlight cells in Excel based on the top three values.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the range C6:C10.
• Now, choose the Top 10 Items menu from the Top/Bottom Rules under the group of Styles. You will find it in the Conditional Formatting option under the Home tab.

• Next, you should get a Top 10 Items dialog box.
• Here, select 3 in the TOP box like the following image.
• Then, hit the OK button.

• Finally, you will get three cells that have the top 3 product values.

There are cases while analyzing a data set where you find duplicate values. You might want to highlight these duplicate values individually, apart from the unique values.

⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, select the range D6:D10.
• Next, choose the Duplicate Values menu from the Highlight Cells Rules under the group of Styles. You will find it in the Conditional Formatting option under the Home tab.

• Now, you should get a Duplicate Values dialog box.
• Here, select the Duplicate option from the Contain drop-down box like the following image.
• Then, hit the OK button.

• Therefore, you will get two cells that have duplicate values.

If you want to find out texts with specific letters, you can try to highlight cells in Excel based on text value.

⬇️⬇️ STEPS ⬇️⬇️

• To begin with, select the range B6:B10.
• Now, choose the Text that Contains menu from the Highlight Cells Rules under the group of Styles. You will find it in the Conditional Formatting option under the Home tab.

• After that, you should get a Text That Contains dialog box.
• Here, type the specific letter (“i” for the image) in the Contain the Text box like the following image.
• Then, hit the OK button.

• Moreover, you will get two cells that have an “i” text value within them.

Blank cells are quite common in the Excel dataset. You can mark out these blank cells if you can highlight cells in Excel based on value.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select the range B6:D10.
• Secondly, choose the New Rule menu from the group of Styles. You will find it in the Conditional Formatting option under the Home tab.

• Thirdly, you should get a New Formatting Rule dialog box.
• Fourthly, select the Rule Type as a formula like the following image.
• Fifthly, enter the following formula into it using COUNTIF function.

• Sixthly, click on the Format button and format the cells as you wish.
• Seventhly hit the OK button.

• Finally, you will get a row that has blank cell(s) within them.

Amidst analysis in Excel, you can use multiple criteria to highlight cells based on value.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the range B6:D10.
• Then, choose the New Rule menu from the group of Styles. You will find it in the Conditional Formatting option under the Home tab.

• Now, you should get a New Formatting Rule dialog box.
• After that, select the Rule Type as a formula like the following image.
• Next, enter the following formula into it using AND function.

• Then, click on the Format button and format the cells as you wish.
• Now, hit the OK button.

• Finally, you will get a row that fulfills both criteria.

There might be cases when you want to use different colors to highlight cells based on a different value. This method will help you with that process.

⬇️⬇️ STEPS ⬇️⬇️

• In the beginning, select the range B6:D10.
• Now, choose the New Rule menu from the group of Styles. You will find it in the Conditional Formatting option under the Home tab.

• Now, you should get a New Formatting Rule dialog box.
• After that, select the Rule Type as a formula like the following image.
• Next, enter the following formula into it.

• Then, click on the Format button and format the cells as you wish.
• Now, hit the OK button.

• Here, you can see the rows that have higher sales than 800 due to highlighting.
• Again, select the range B6:D10.
• Now, choose the New Rule menu from the group of Styles. You will find it in the Conditional Formatting option under the Home tab.

• Then, you should get a New Formatting Rule dialog box.
• After that, select the Rule Type as a formula like the following image.
• Next, enter the following formula into it.

• Then, click on the Format button and format the cells as you wish. You should choose a different format this time.
• Now, hit the OK button.

• In the end, you will get a row that has higher sales than 860 in one color. On the other hand, you will get more than 800, but less than 860 sales rows in another color formatting.

In Excel, it is difficult to connect cells by condition. Now, you can highlight cells based on the value of another cell in Excel.

⬇️⬇️ STEPS ⬇️⬇️

• To begin with, select the range B6:D10.
• Now, choose the New Rule menu from the group of Styles. You will find it in the Conditional Formatting option under the Home tab.

• Then, you should get a New Formatting Rule dialog box.
• After that, select the Rule Type as a formula like the following image.
• Next, enter the following formula into it.

• Then, click on the Format button and format the cells as you wish.
• Now, hit the OK button.

• Finally, you will get two cells that fulfill both criteria.

You may have used a Drop-Down List in Excel. This method helps you to highlight an entire row depending on the value you are choosing in the drop-down list.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select any options from the drop-down list.

• Secondly, select the range B6:D10.
• Thirdly, choose the New Rule menu from the group of Styles. You will find it in the Conditional Formatting option under the Home tab.

• Then, you should get a New Formatting Rule dialog box.
• After that, select the Rule Type as a formula like the following image.
• Next, enter the following formula into it.

• Then, click on the Format button and format the cells as you wish.
• Now, hit the OK button.

• Therefore, you will get the row of “Charles” as per the choice from the Drop-Down List.

📄 Important Notes

🖊️  You should modify your formulas according to your dataset.

🖊️  Make sure you check the “Applies To” range in the cases of the New Rule option.

📝 Takeaways from This Article

The article lets the readers understand the variety of options available to highlight cells based on the value in Excel.

📌  Primarily, you can highlight cells based on a value in excel. One of the ways is using the Greater Than a Specific Value option from the  Conditional Formatting.

📌  Also, you can mark out top-numbered values using the Top 10 Items.

📌  You can highlight the Duplicate Values as well. It will help you distinguish between duplicates and unique ones.

📌  Again, you can use the Text that Contains option to get texts with specific letters.

📌  You can mark out these blank cells if you can highlight cells in Excel based on value. For that, you need to apply a New Rule.

📌  Here, you can also highlight based on multiple criteria. You only need to add another New Rule for the advantage.

📌  Also you can try different formats or colors to highlight cells in Excel based on a value.

📌  Additionally, you can also connect another cell condition to a reference cell. It will require the New Rule option as well.

📌  Finally, you can automatically get your record to highlight itself using only a Drop-Down List and a New Rule.

Conclusion

To highlight cells based on the value in Excel, nine methods are helpful. You can use the Conditional Formatting option in Excel to help you with it. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.