If you enjoy working with numbers and charts, you likely know about Microsoft Excel. This software program is one of the most extensive for those individuals who have to build screenshots and other related documents.

Microsoft Excel has several advanced features that help you stay organized. Some of these tools let you better understand the relationships between two cells. You can input formulas with conditional formatting to see the context of your values.

If you want to show relationships between cells, you highlight them. This process lets you select the information you will manipulate. Learn more about how to highlight cells in Excel based on value in the step-by-step guide below.

Table of Contents

How to Highlight Cells in Excel Based on Value

This step-by-step guide will teach you how to highlight cells in Excel based on value. This process helps you keep your spreadsheets organized and formatted.

What is Conditional Formatting?

It is easy to learn how to do conditional formatting in Excel. This feature lets you highlight specific values or cells based on their identity. You will point them out in a group or spreadsheet.

The appearance of a group of cells will change because of their criteria. They have to meet the condition you put in your spreadsheet.

Use Conditional Formatting

You can learn how to highlight cells in Excel based on value using conditional formatting with the steps below.

  1. Choose the cells that you want to use for conditional formatting.
  2. Click on the “Home” tab.
  3. Press “Conditional Formatting.”
  4. Follow the instructions in the list below for the formatting you want to complete.

Highlight the Cells' Values

You can follow specific steps to highlight the values of the cells you highlight. There are two options you can choose after pressing “conditional formatting,” “highlight cells rules,” and “top/bottom rules.”

Highlight the Relationships of the Cells' Values

You can also use conditional formatting to show the relationships of the values of the cells. You can compare prices or other data entries. After clicking “conditional formatting,” press “Data Bars” and choose the fill color.

Highlight the Cells' Values Intensities and Relationships

You can also learn to perform Excel conditional formatting based on another cell multiple values. This process helps show the intensity of a specific dataset compared to another cell. Click on “Color Scales” after pressing “conditional formatting” to perform this process.

Highlight Cells to Show Groups of Values

Finally, you can perform Excel conditional formatting based on another cell multiple values to show groups of information. Assign specific thresholds that outline those cells that meet specific goals or data points. After pressing “conditional formatting,” choose “Icon sets” and the appropriate set.

Excel Highlight Cell if Value Greater than Another Cell

You can practice six methods to learn how to Excel highlight cell if value greater than another cell. Read about each in the information below.

Use the Greater Than Command Ribbon

The easiest way to learn how to highlight cells in Excel based on value is to use the “Greater Than” operator in the command ribbon. After you open a spreadsheet, follow the steps outlined below.

  1. Open a spreadsheet in Excel.
  2. Choose the cell you want to highlight. You can also choose a range of cells.
  3. Click on the “Home” tab in Excel.
  4. Press “Conditional Formatting.”
  5. Click on “Highlight Cells Rules” in the dropdown menu.
  6. Choose the first option, “Greater Than.”
  7. Wait for the pop-up window to appear.
  8. Look for the field labeled “Format Cells that are Greater Than.”
  9. Choose the cell you want to compare with the one you selected.
  10. Choose a fill and text color combination.
  11. Press “Ok.”
  12. Look for the cells highlighted with the format you chose.

This method only compares cells to a constant value. Your selected numbers have to remain the same.

Use the Greater Than (>) Operator

You can use the “Greater Than (>) Operator” to highlight any cell with a higher value than another. Again, you can Excel select rows based on cell value to group the highlighted information. Learn how to use this operator in the steps below.

  1. Choose the cell, row, column, or group you want to highlight.
  2. Click on the “Home” tab.
  3. Press “Conditional Formatting,” followed by “New Rule.”
  4. Wait for the dialogue box to appear.
  5. Look for the section labeled “Select a Rule Type.”
  6. Press “Use a Formula to Determine Which Cells to Format.”
  7. Look for the field labeled “Format Values Where this Formula is True.”
  8. Type “=C4>d4” without the quotation marks. Switch out the cell numbers with those you want to highlight.
  9. Press “Format.”
  10. Wait for the pop-up window to appear and choose a color in the “Fill” section.
  11. Press “Ok.”
  12. Click “Ok” again.

The highlighted cells will reflect those greater than the initial cells you selected.

Use the Greater Than (>=) Operator

The “Greater Than (>=) Operator” highlights any cells that are higher or equal to another. Again, it shows the relationship between any two values. You can use the below steps to use this function.

  1. Choose the cell or cells you want to highlight.
  2. Click the “Home” tab, followed by “Conditional Formatting.”
  3. Press “New Rule” in the dropdown menu.
  4. Wait for the pop-up window to show up.
  5. Click on “Select a Rule Type,” followed by “Use a Formula to Determine which Cells to Format.”
  6. Type “=C4>=E4” in the field labeled “Format Values Where this Formula is True.” Remove the quotation marks and switch out the cell numbers for your spreadsheet.
  7. Click on “Format.”
  8. Choose “Highlight,” followed by “Greater than Equal.”
  9. Follow the other formatting cues in the above method.
  10. Press “Ok.”

You will see highlighted cells that include information about any values that are greater than or equal to the given formula.

Use the Greater Than (>) Operator with the And Function

If you want to skip a blank cell, you can use the “Greater Than (>) Operator” with the “And” tool. You will only highlight the spaces that include relative values. Make sure you have blank cells in your document to begin the below steps.

  1. Choose the cell or group of cells you want to highlight. Do not include any blank spaces.
  2. Select the “Home” tab on your spreadsheet.
  3. Press “Conditional Formatting” in the toolbar.
  4. Choose “New Rule” in the dropdown menu and wait for the dialogue box to appear.
  5. Click “Select a Rule Type.”
  6. Press “Use a Formula to Determine which Cells to Format.”
  7. Look for the section labeled “Format Values Where this Formula is True” again.
  8. Type “=AND(D4>E4, $E4″”)” in the field. Remove the quotation marks at the beginning and end of the phrase. Replace the cell numbers with those in your document.
  9. Click on “Format” and wait for the pop-up window to appear.
  10. Follow the highlighting format options listed in previous methods.
  11. Press “Ok.”

You can use the “AND” tool to find those cells greater than the initial. It also locates those spaces that are blank. If the cell is not empty and is greater, Excel will highlight it.

Use the If Function to Highlight Cell

The “If” function is another valuable tool you can use as an Excel conditional formatting formula if cell contains text. Choose the space you want to begin with to start and read the below steps.

  1. Highlight the cell or range that you want to use for the formula.
  2. Click on the “Home” tab.
  3. Press “Conditional Formatting,” followed by “New Rule.”
  4. Wait for the pop-up window.
  5. Choose “Use a Formula to Determine which Cells to Format” under “Select a Rule Type.”
  6. Type “IF(C4>E4,C4,””)” in the “Format Values Where this Formula is True” field. Remove the quotation marks on either side of the parentheses. Again, you can replace the cell numbers with those in your spreadsheet.
  7. Click on “Format” to learn how to fill color in Excel cell using formula.
  8. Perform the edits listed in the first method.
  9. Press “Ok.”

Using the “If” function to learn how to highlight cells in Excel based on value helps you find the appropriate relationships. It will automatically highlight anything greater than the original cell.

Use the Average Function

Finally, you can use the “Average” tool to highlight values. First, select individual cells or use Excel select rows based on cell value. This latter method lets you choose a group of spaces.

The “Average” function lets you compare the mean of the values of your cells. It determines if the average in one group is higher than in another one. Use this function by following the steps below.

  1. Choose the cell that you want to start calculating with the average.
  2. Click on the “Home” tab.
  3. Select “Conditional Formatting” as mentioned in the above steps. Press “New Rule.”
  4. Again, wait for the dialogue box to appear.
  5. Choose “Use a Formula to Determine which Cells to Format” under the “Select a Rule Type” section.
  6. Type “=C4>AVERAGE(D4,E4)” in the section labeled “Format Values where this Formula is True” section. Change out the cell numbers for your spreadsheet and do not include the quotation marks.
  7. Click “Format” to highlight the cells that are greater than the initial average.
  8. Perform any other formatting edits.
  9. Press “Ok.”

How to Highlight Text in Excel

You can also learn how to use Excel conditional formatting formula if cell contains text instead of numbers.

  1. Open the Excel spreadsheet that you want to edit by using conditional formatting.
  2. Double-click the cell you want to highlight.
  3. Drag your mouse across the text you want to highlight.
  4. Wait for a menu to appear.
  5. Choose the icon that represents the font color.
  6. Select the text color you want to use.
  7. Click anywhere else on the spreadsheet to exit the editing process.

Highlight Text Using Cell Styles

You can also learn to highlight text in your Excel spreadsheet using the “Cell Styles” function. The steps required for this method are in the list below.

  1. Open the Excel spreadsheet with the text you want to highlight.
  2. Click on the cell you want to highlight.
  3. Press “Home” in the top menu, followed by “Cell Styles.”
  4. Hover your mouse over the colors to preview them.
  5. Click on one of the colors to make a change.
  6. Repeat the process for any other cells with text you need to highlight.

This process does not let you learn how to fill color in Excel cell using formula codes. You have to manually highlight the space every time.

How to Create a Custom Formatting Rule for Highlighting

Finally, you can learn how to create a custom formatting rule in Excel. These options allow you to quickly highlight cells in any spreadsheet.

  1. Open an Excel spreadsheet to start creating custom formatting.
  2. Select the range of cells you want to use for conditional formatting.
  3. Click on the “Home” tab.
  4. Select “Conditional Formatting.”
  5. Press “New Rule.”
  6. Choose one of the listed styles.
  7. Select the specific conditions you want to apply.
  8. Press “Ok.”

Highlight Cells in Excel Based on Value

This article helped you learn how to highlight cells in Excel based on value. You have to understand conditional formatting to use the information above.

You can use six methods to show the relationships between two cell values. You can take the average of these numbers or show if one is greater than another.

You can also highlight text inExcel. Change the font or fill color using “Color Style” options.

Finally, you can make custom rules for any conditional formatting formulas you want to use. These customizations let you easily highlight cells to show any relationship you can imagine.

The above article may contain affiliate links, which help support Clever Sequence

Latest Guides