Excel: Guide to highlighting cells based on the value

Here in this blog, we will address how you can highlight cells based on value. Conditional formatting allows you to easily highlight certain numbers or make specific cells stand out. Based on a condition, this alters the look of a cell range (or criteria). Conditional formatting can be used to highlight cells that contain values that fulfill a specific requirement. Alternatively, you may format a whole-cell range and change the precise format as the value of each cell changes.

Follow through for more information on how you can highlight cells based on value.

Step by step process – How to highlight cells based on value.

  1. Firstly, open your Excel document.
  2. Now select the range of cells that you want to apply the value highlight.
  3. Next click on “Conditional formatting”.
  4. Multiple options to choose from but well use the color scale for this example.
  5. For this hover over “Highlight Cell Rules”, then click on “Text that Contains”.
  6. In the style select either the two or three-color scale, depending on how refined you want your values to show.
  7. Nice and easy now assign a color to the lowest and highest value. If you have a three-color option you can also choose the median color.
  8. Select “OK”.
  9. To edit simply click on “Conditional formatting”.
  10. Click on “Manage Rules”.
  11. Now click on “Edit Rule”.
  12. Edit the “Applies to” section to refine what area requires highlighting.

Pretty much a solid example of conditional formatting, you only need to assign the rule once and it will be spread across the entire selection. The Excel algorithm finds the optimal gradient to apply to your selection between the colors you have chosen. That’s not all, you can apply this rule to values of a specific range, for instance, the lowest few values and the highest few values are both marked in different colors. This can be helpful if you want to find the most and least expensive products in your spreadsheet catalog. 

When and where to apply the value format to cells in Excel

Follow the table below for more information on where and where you can use different formatting features based on the criteria and information you have. There is plenty to learn so continue reading through to learn more on this particular subject.

Highlight valueWhat to do
Specific cell values. Use this method rule if you’re seeking specific value criteria.Select Highlight Cells Rules or Top/Bottom Rules by pointing to them and clicking the relevant option.
The connection between the values in a cell range. Uses a color scale in which the intensity of the cell’s color reflects the value’s placement at the top or bottom of the range. Sales distributions between regions are one example.Point to Color Scales and then click the desired scale.
A cell range contains three to five sets of values, each with its own threshold. For example, you might use a set of three symbols to emphasize cells with sales of less than a specific value group. Alternatively, you may assign a 5-point rating system to autos and use a set of five icons.Navigate to Icon Sets and then select a set.
The connection between the values in a cell range. Extends a strip of color across the cell. Price or population comparisons in the largest cities are two examples.Point to Data Bars and then select the desired file.

Highlight Rows Using Text Criteria

The dataset of records here shows an inventory for a list of BBQs a company sells, they want to highlight all the ones that have inventory marked as zero. So they know what to exclude from offers and keep a more detailed visual record of what needs to be made out of stock.

Step by step process – Highlight Rows Using Text Criteria

  • Firstly, highlight the sheet rows and columns consisting of the dataset in our example between A1 and D11.
  • Now click on “Home”.
  • After that click on “Conditional formatting”.
  • Select “New Rules.”
  • Click on “Use a formula to determine which cells to format” in the “New Formatting Rule” dialogue box.
  • Enter the following formula in the formula field: (=$C2=0).
  • Select the ‘Format’ option.
  • Set the color in which you wish the row to be highlighted in the dialogue box that appears.
  • Finally, click on “OK”.

That’s it, all the inventory records shown as zero will not be highlighted for recording purposes, I stated earlier there is a reason why you select the whole table and not just the inventory column. This is because the whole row will get highlighted when you select the entire table making the referencing process easier, you can of course select only the relevant rows or columns if you require that.

Step by step process – How to highlight cells based on value

  • Firstly, open your Excel document.
  • Now select the range of cells that you want to apply the value highlight.
  • Next click on “Conditional formatting”.
  • Multiple options to choose from but well use the color scale for this example.
  • For this hover over “Highlight Cell Rules”, then click on “Text that Contains”.
  • In the style select either the two or three-color scale, depending on how refined you want your values to show.
  • Nice and easy now assign a color to the lowest and highest value. If you have a three-color option you can also choose the median color.
  • Select “OK”.
  • To edit simply click on “Conditional formatting”.
  • Click on “Manage Rules”.
  • Now click on “Edit Rule”.
  • Edit the “Applies to” section to refine what area requires highlighting.

That’s it for this Blog thank you for taking time out to read our content, please feel free to email our team about how it went if you followed the steps or if you need more help with the questions we answered in this Blog.

Saajid Gangat

Saajid Gangat has been a researcher and content writer at Business Tech Planet since 2021. Saajid is a tech-savvy writer with expertise in web and graphic design and has extensive knowledge of Microsoft 365, Adobe, Shopify, WordPress, Wix, Squarespace, and more! You can connect with Saajid on Linkedin.

Recent Posts