How to Change the Font Color Based on Cell Value in Excel

This post will guide you how to change the font color based on cell value in Excel. How do I color cell based on cell value using the conditional formatting feature in Excel.

1. Changing Font Color Based on Cell Value

Assuming that you have a list of data in range A1:C6, and you want to change the font color based values in A1:C4 cells, if the value is greater than 5, then changing the font color as red, otherwise, changing the font color as green. To changing the font color based on cell value, you need to do the following steps:

#1 select the range of cells which contain cell values, such as: A1:C6.

change font color based on cell value1

#2 go to HOME tab, click Conditional Formatting command under Styles group, and then click New Rule from the drop down men list. And the New Formatting Rule dialog will open.

change font color based on cell value2

#3 In New Formatting Rule dialog, select Format only cells that contain in the Select a Rule Type list box, chose Cell Value from the first list box, and select greater than from the second list, type number 5 into last text box under Format only cell with section.

change font color based on cell value3

#4 click Format button, and switch to Font tab in Format cells dialog, and choose one color as you need in Color list box. Click OK button.

change font color based on cell value4

#5 click Ok button. The Font color has been changed as red if the cell value is greater than 5 in the selected range of cells.

change font color based on cell value5

#6 repeat step 3, and create a new rule, select less than from the second list , and type number 5 into last text box under Format only cell with section. And click Format button, choose green color in Color list box. And click OK button.

change font color based on cell value6

#7  let’s see the last result:

change font color based on cell value7

2. Video: Changing Font color Based on Cell Value

This video will demonstrate you how to chagne Fond colore based on Cell value using Conditional formatting feature in Microsoft Excel 2013/2016/2019/365.

Highlight the Dates if its over a year

This post will guide you how to highlight the dates that are over 1 year in excel. How do I highlight the dates over a year old using conditional formatting feature in excel. How to use conditional formatting to highlight if the dates are over a year in excel. How to highlight cells color when the date is over a year old in excel.

1. Highlight the Date that Are over 1 Year using Conditional Formating

Assuming that you have a list of date in the range of cells B1:B5, and you would like to make it easy to determine all dates which are older than 1 year, then highlight it. You can use the Conditional formatting feature to highlight the dates over a year old, just do the following steps:

Step1: select the range of cells that contain the dates

highlight dates if its over year1

Step2: go to HOME tab, click Conditional Formatting command under Styles group, then select the New Rule… from the drop-down menu list. The New Formatting Rule dialog will appear.

highlight dates if its over year2

Step3: select Use a formula to determine which cells to format in the Select a Rule type list, and type the following formula =B1<=TODAY()-365 into the textbox of the Format values where this formula is true.

highlight dates if its over year3

Step4: click Format button, the Format Cells dialog will appear.

Step5: switch Fill tab, select one color that you want to highlight the dates. Click OK button.

highlight dates if its over year4

Step6: let’s see the result:

highlight dates if its over year5

You will see that all the dates over a year have been highlighted with a specified color in the range of cells B1:B5.

2. Highlight the Date that Are over 1 year with VBA Code

If you want to highlight the dates that are over a year old using VBA code in Excel, you can follow these steps:

Step1: Open the Excel file that contains the dates you want to highlight.

Step2: Press Alt + F11 to open the Visual Basic Editor.

Adding Comma Character at End of Cells vba1.png

Step3: In the Visual Basic Editor, go to Insert > Module to create a new module.

Adding Comma Character at End of Cells vba1.png

Step4: Copy and paste the following VBA code into the module:

Sub HighlightOverYearDates_ExcelHow()
    Dim myRange As Range
    Set myRange = Application.InputBox("Please select a range of cells", Type:=8)
    
    Dim cell As Range
    For Each cell In myRange
        If IsDate(cell) Then
            If DateDiff("yyyy", cell, Now()) >= 1 Then
                cell.Interior.ColorIndex = 6 ' Change the color index as per your preference
            End If
        End If
    Next cell
End Sub

Step5: Go to Developer > Macros (or press Alt + F8) to open the Macros dialog box. Select the HighlightOverYearDates_ExcelHow macro from the list and click Run.

Highlight the Dates if its over a year vba 2.png

Step6: Select the range of cells that contains the dates you want to highlight.

Highlight the Dates if its over a year vba 3.png

Step7: The dates that are over a year old will be highlighted in the color you specified.

Highlight the Dates if its over a year vba 4.png

3. Video: Highlight the Dates If its over a Year

This video will demonstrate how to highlight dates if they are over a year old in Excel using both conditional formatting and VBA code.

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 range based on another cell value in Excel or google sheets.

In Excel or Google sheets, it is relatively easy to apply conditional formatting based on the current value of a cell. However, suppose you run into a problem where you have to highlight or format a column based on the value of another column. You can use conditional formatting based on another column to solve such problems.

This article will describe in detail how to format another column based on another column’s value in a Microsoft Excel spreadsheet or Google Sheets.

EXAMPLE

The following is an example that can be used to demonstrate the application of conditional formatting functionality in an instance. Suppose you have a product sales table with a sales target and an actual sales value for each month, now let’s use conditional formatting to find the product names whose actual sales are higher than the sales target. Below is a screenshot of our product sales table.

Below we will introduce how to customize conditional formatting rules by formulas to format cells in one column based on the value of another column in Microsoft Excel Spreadsheet and Google sheets respectively.

Highlight Cells Based on Another Cell in Excel

If you want to highlight a column based on the value of another column, then you can create custom conditional rules in Conditional Formatting. The specific steps are as follows.

Step1: Select the column to which you want to apply the custom condition rule, for example: A3:A7.

Highlight Cells Based on Another Cell in Excel

If you want to apply conditional formatting, you must first select the cells. If you want to highlight the entire row after applying conditional formatting, you need to select all the data sets. But to highlight the cells of a column, you only need to select the cells of that column. If you want to highlight an entire row or column, then you need to select that column or row.

In the example in this article, you need to select the name column first, i.e. A3:A7

Step2: You need to open the Conditional Formatting panel and select the appropriate formatting rule as needed. In Ribbon, select HOME->Conditional Formatting -> New Rule, New Formatting Rule dialog will open.

Note: To apply conditional formatting to a column based on another column, we need to select the New Rule menu.

Highlight Cells Based on Another Cell in Excel

Step3:  From the list of rule types below, we can see the type of rule we need, that is, we want to determine the cells to be formatted by a formula. We need to select: Use a formula to determine which cells to format.

Highlight Cells Based on Another Cell in Excel

Note: As you can see from the image above, the New Formatting Rule dialog box has a number of rule types, such as: Format all cells based on their values, Format only cells that contain, Format only top or bottom ranked values, Format only values that are above or below average , Format only unique or duplicate values and Use a formula to determine which cells to format.

When the formatting formula returns a True value, the cell will be applied to the corresponding format, and if the formula returns False, then the formula will not be applied.

Step4: When you select the “Use a formula to determine which cells to format” rule type, the box named “Format values where this formula is true ” will appear below the “Select a Rule Type” box. Enter the following formula in the Format values where this formula is true text box:

=$C3>$B3

Note: The formula above compares the value of column C with the value of column B in the same row. If the value of column C is greater than the value of column B in the same row, then the cells in column A will be formatted.

Highlight Cells Based on Another Cell in Excel

Step5: Click the “Format” button to select a format for the cells that match the criteria. After setting your preferred formatting style, click the OK button.

Highlight Cells Based on Another Cell in Excel

Step6: You will see your selected formatting style in the Preview box of the New Formatting Rule window. After clicking OK, the selected formatting will be applied to the cells that meet the conditions.

Highlight Cells Based on Another Cell in Excel

Highlight Cells Based on Another Column in Google Sheets

The following is a conditional formatting function to highlight the cells in the name column that match the condition based on the column value in the Google Sheet spreadsheet. The steps are as follows.
Step1: In this case, select the product name column (excluding the title) A3:A7 and it will be highlighted in blue.

Highlight Cells Based on Another Column in Google Sheets

Step2: Click the “Format” menu and then click the “Conditional Formatting” menu.

Or right-click on the selected column -> select “View more cell actions” -> “ Conditional formatting “, the Conditional formatting rules dialog box will pop up.

Highlight Cells Based on Another Column in Google Sheets

Step3: Next we can select the desired format for the range or column. Select Single color Tab

Highlight Cells Based on Another Column in Google Sheets

Note: We can also select the range of cells by manually selecting them in the Conditional Formatting Rules window. For example, if we choose to conditionally format the first 100 cells of column A, we can specify the range to apply to as “A3: A103”.

Step4: Select “Custom formula is” from the “Format Cells if” drop-down list

Highlight Cells Based on Another Column in Google Sheets

Step5: In the Formula text box, enter the following formula.

=$C3>$B3
Highlight Cells Based on Another Column in Google Sheets

The formula here is the actual sales value corresponding to the product name cell is greater than the sales target value. The formula becomes “=$C3>$B3” because the actual sales value is in column C with the first cell row number of 3, and the sales target value is in column B with the first cell row number of 3.

The formula uses the greater than operator (>) to evaluate each cell in C3:C7 to the corresponding cell in B3:B7. When the formula returns “true”, the rule is triggered and the highlighting format is applied.

Step6: Select a style from the “Formatting style” drop-down list

Step7: Click the “Done” button

Highlight Cells Based on Another Column in Google Sheets

As you can see from the image above, you will see that the cell containing the name will be conditionally highlighted based on the value of another column.

Conditional Formatting Based On Another Column FAQ

How do you conditional formatting in Excel based on another column text?

  1. Select the cell to which you want to apply conditional formatting. Click the first cell in the range, then drag it to the last cell.
  2. Click Home > Conditional Formatting > Highlight Cell Rules > Included Text…
  3. Select the color formatting for the text, and then click OK.

How do you conditional format if one column is greater than another?

  1. Do one of the following to open the dialog box. choose Data > Conditional Formatting > Highlight Cell > Greater Than.

Can you use conditional formatting to compare two columns?

  1. If you want to compare two columns and highlight the matching data, you can use the duplicate function in conditional formatting.

How do you conditional format a range based on another range?

  1. Select all cells in the worksheet that you want to highlight and apply formatting rules to.
  2. Click Conditional Formatting.
  3. Select the Highlight Cells rule, and then select the rule that applies to your needs…
  4. Fill out the Less Than dialog box and select a formatting style from the drop-down menu.

Video: Conditional Formatting Based On Another Column in Excel/google sheets

Conclusion

This article provides you with a base method for conditional formatting of an entire column based on another column in Excel and Google sheets. Therefore, if you follow the steps described in this article, you will be able to apply conditional formatting to columns based on the conditions of other columns.

Sample files

Below are sample files in Microsoft Excel and google sheets that you can download for reference if you wish.

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 a single column, you can use the conditional formatting function to create formatting formulas.

This article will talk in detail about how to format a cell range based on the value of another cell in a Microsoft Excel spreadsheet or Google Sheets.

EXAMPLE

The following is an example that can be used to demonstrate how to apply conditional formatting functions in an instance. Suppose you have a table of student scores and you need to determine the names of students with scores greater than 85. Below is a screenshot of our sample student score table.

Conditional formatting based on another cell in Google Sheets Excel1

There are several ways to find and print out the names of students with grades greater than 85 in Excel or Google Sheet. You can use the IF function to do a score comparison and then print out the cell values that meet the criteria. Another way is to use the conditional formatting function to quickly highlight the names that meet the criteria.

Conditional format function introduction

You can easily highlight specific columns with the conditional formatting function, because the cells to be formatted are highlighted if they are the same as the cells to be evaluated or if they match a condition. That is, we can format the cell state of the column in which the same type of value is located based on the value of a cell as a condition.

To accomplish the above and simply highlight the status of the Score column, the following steps can be performed.

STEP 1: Select the Score column

Conditional formatting based on another cell in Google Sheets Excel1

STEP2: Click HOME Tab, in the Styles group, click Conditional Formatting command, click Highlight Cells Rules menu, and then click the cell rules in the secondary menu, such as: Greater Than, Less Than, Between, Equal To ,etc.

Conditional formatting based on another cell in Google Sheets Excel1

In this example, we select the Equal To cell rule. Then the Equal To dialog box will pop up.

Conditional formatting based on another cell in Google Sheets Excel1

Step3: We can enter 75 in the Format cells that are EQUAL TO: text box, select the corresponding highlighted state in the with drop-down list, and then click “OK“.

Conditional formatting based on another cell in Google Sheets Excel1

Step4: Excel will apply the selected format to the specific cell that meets the condition.

Conditional formatting based on another cell in Google Sheets Excel1

The method described above clearly does not meet our goal of highlighting the state of a specific column or other columns based on the value of a specific cell. That is, we need to format a specific cell or cell range in the Name column based on the value of cell E2.

When the built-in conditional rules of Excel or Google Sheets do not meet our goal, i.e., formatting other different cells based on a specific cell, then we need to use Excel or Google Sheets formulas to customize the formatting rules to meet our goal.

Below we will introduce how Microsoft Excel spreadsheet and Google Sheets respectively can customize conditional formatting rules via formulas to format cells in the Name column based on the value of cell E2.

Highlight Cells Based on Another Cell in Excel

If you want to highlight a cell range based on the value of another cell, then you can create custom conditional rules in Conditional Formatting. The specific steps are as follows.

Step1: Select the cell range where you want to apply the custom conditional rule, such as A2:B7

Step2: In the Ribbon area, select HOME->Conditional Formatting -> New Rule, the New Formatting Rule window will open.

Conditional formatting based on another cell in Google Sheets Excel1

Step3:  From below the list of rule types, we can see the type of rule we need, and that is we want to determine which cells to format by a formula. We need to select: Use a formula to determine which cells to format.

Conditional formatting based on another cell in Google Sheets Excel1

Note: As you can see from the image above, the New Formatting Rule dialog box has a number of rule types, such as, Format all cells based on their values, Format only cells that contain, Format only top or bottom ranked values, Format only values that are above or below average , Format only unique or duplicate values and Use a formula to determine which cells to format.

When the formatting formula is set to return a True value, the cell will apply the corresponding formatting, if the formula returns False, the formula will not be applied.

Step4: Enter the following formula in the Format values where this formula is true text box

=$B3>$E$2
Conditional formatting based on another cell in Google Sheets Excel1

Note: As you can see from the formula above, cell E2 needs to use absolute references to limit cell changes, you need to enter the $ sign in front of the row and column values.

Step5: Click the “Format” button to select a format for the cells that meet the conditions.  

Conditional formatting based on another cell in Google Sheets Excel1

Step6: When you click OK, the selected format is applied to the cells that meet the conditions.

Conditional formatting based on another cell in Google Sheets Excel1

Highlight Cells Based on Another Cell in Google Sheets

The following is a conditional formatting function in the Google Sheet spreadsheet to highlight the cells in the name column that match the condition based on the cell’s value. The steps are as follows.

Step1: Select the cell range A3:A7 in the name column

Conditional formatting based on another cell in Google Sheets Excel1

Step2: Click on the Format menu, and then click on the Conditional formatting menu

Conditional formatting based on another cell in Google Sheets Excel1

Step3: The Conditional formatting rules window will open.

Step4: Select Single color Tab

Conditional formatting based on another cell in Google Sheets Excel1

Step5: Select “Custom formula is” from the “Format Cells if” drop-down list

Step6: In the Formula text box, enter the following formula.

=$B3>$E$2

Step7: Select a style from the “Formatting style” drop-down list

Step8: Click on the “Done” button

As you can see from the image above, you will see that the cell containing the name will be highlighted based on the score condition in the adjacent cell.

Note: When our custom conditional formula returns True, the cells that meet the condition will be formatted, when it returns False, google sheets will not do anything.

Multiple conditional rules

If you want cells that meet more than one condition to apply formatting, then you can use the logical functions AND and OR, for example, you want to highlight cells or ranges with a score greater than 90 and the name carol. Then you can use the following formula to customize the conditional rules.

=AND($B3>$E$2,$A3="carol")

The effect after applying the format is as follows.

Conditional formatting based on another cell in Google Sheets Excel1

Conditional Formatting Based On Another Cell FAQ

How do you conditionally format a cell based on another cell?

1. Select the cell you want to format…

2. On the Home tab, in the Styles group, click Conditional Formatting > New Rule…

3. In the New Formatting Rule window, select Use Formulas to Determine Which Cells Need to be Formatted.

4. Enter the formula in the appropriate box.

5. Click the “Format… ” button to select your custom format.

Can I use an IF formula in conditional formatting?

The answer is both yes and no. Any conditional formatting parameter must produce a “true” or “false” result, i.e., your conditional formatting rule is an If/Then statement that says “If this condition is “true”, format the cell in this way”.

Can you use conditional formatting based on relative cell references?

This cell reference type is rarely used in Excel conditional formatting rules.

Can you drag conditional formatting?

Yes, Select the cell and apply the conditional formatting, referencing other cells in the row. Drag the corner of the row down to the bottom of the cells you want to apply the formatting 

Video: Conditional Formatting Based On Another Cell in Excel/google sheets

Conclusion

Once you learn the conditional formatting function in Excel or Google Sheets, you will easily format other cells or ranges based on specific cell values, which is a is a very useful method.

SAMPLE FILES

Below are sample files in Microsoft Excel and google sheets that you can download for reference if you wish.

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 need a convenient way to highlight all non-blank cells immediately. Actually, we can implement this via excel Conditional Formatting function, we can edit a new rule to filter all non-blank cells and then highlight them. We can also implement this via Go To Special function by checking on proper options. If you are familiar with VBA macro, you can also edit VBA code. This article will show you these three methods details step by step. Please read this article below and pick one of them to help you to solve your problem.

Precondition:

Prepare a table with some cells are blank.

Highlight All Non-Blank Cells 1

We can highlight all non-blank cells via below two methods.

Method 1: Highlight All Non-Blank Cells by Conditional Formatting


Step 1: Select the table displayed in above screenshot.

Step 2: Click Home in ribbon, click Conditional Formatting in Styles group.

Highlight All Non-Blank Cells 2

Step 3: Click the arrow on Conditional Formatting icon, select New Rule.

Highlight All Non-Blank Cells 3

Step 4: In ‘New Formatting Rule’ dialog ‘Select a Rule Type’ pane, select ‘Use a formula to determine which cells to format’ option.

Highlight All Non-Blank Cells 4

Step 5: Enter formula =NOT(ISBLANK(A1)) into ‘Format values where this formula is true.’.

Highlight All Non-Blank Cells 5

Step 6: Click Format button in Edit the Rule Description pane. On Format Cells, click Fill tab, select background color, then click OK to quit current dialog.

Highlight All Non-Blank Cells 6

Step 7: In Preview field, you can see that cell is highlighted with yellow. Click OK to quit editing.

Highlight All Non-Blank Cells 7

Verify that all non-blank cells are highlighted properly.

Highlight All Non-Blank Cells 8

Method 2: Highlight All Non-Blank Cells by Go To Special


Step 1: Select the table, click F5 to load Go To dialog. Click Special button.

Highlight All Non-Blank Cells 9

Step 2: On Go To Special dialog, check on Constants, then Numbers, Text, Logicals and Errors are activated by default. Click OK.

Highlight All Non-Blank Cells 10

Step 3: After step#2, all non-blank cells are selected.

Highlight All Non-Blank Cells 11

In Home ribbon, click Fill Color arrow in Font group. Select color, then non-blank cells are filled with this color.

Highlight All Non-Blank Cells 12

Method 3: Highlight All Non-Blank Cells by VBA Code


Step 1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic. You can also press Alt + F11 keys simultaneously to open it.

Step 2: In Microsoft Visual Basic for Applications window, enter below code:

 

Sub HighlightAllNonBlankCells()
    
    Dim myRange As Range
    Dim myCell As Range
    Dim nonEmptyCells As Range
    Set myRange = Application.ActiveSheet.UsedRange
    
    For Each myCell In myRange
        If Not (myCell.Value = "") Then
           If nonEmptyCells Is Nothing Then
              Set nonEmptyCells = myCell
           Else
              Set nonEmptyCells = Application.Union(nonEmptyCells, myCell)
           End If
        End If
    Next
    
    If Not (nonEmptyCells Is Nothing) Then
       nonEmptyCells.Select
    End If
    
End Sub

Step 3: Save code, quit Microsoft Visual Basic for Applications.

Step 4: Click Developer->Macros to run Macro.

Highlight All Non-Blank Cells 13

Step 5: Select ‘HighlightAllNonBlankCells’ and click Run.

Highlight All Non-Blank Cells 14

Verify that all non-blank cells are selected. Then you can follow step#3 in method#2 to fill highlight color.

Highlight All Non-Blank Cells 15

              

 

 

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.

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

Step1: First, select the range you want to do filter. In this case select A2 to A9.

Step2: In ribbon, click Home->Conditional Formatting under Styles.

Highlight Cells Based on Their First Letter 2

Step3: 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

Step4: In the pops up New Formatting Rule window, in Select a Rule Type panel, select Use a formula to determine which cells to format.

Step5: 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

Step6: 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

Step7: 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

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

Step9: Select A1 & B1 & C1, click Data->Filter. Then A & B & C columns are added filter.

Highlight Cells Based on Their First Letter 9

Step10: 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

Step11: Verify that only N type products rows are listed.

Highlight Cells Based on Their First Letter 11

2. Video: Highlight Cells Based on Their First Letter/Character by Conditional Format in Excel

This video will show you how to use conditional formatting in Excel to highlight cells based on their first letter or character.

How to Remove Conditional Formatting on Blank Cells in Excel

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 of data in range B1:B5, in which contain text values and blank cells, and have been highlighted with green color using Conditional Formatting . And you wish to remove conditional formatting for all blank cells in your worksheet. How to accomplish it. You just need to do the following steps:

Step1: select your range of cells that have been highlighted with red color using Conditional Formatting.

remove conditial formatting on blank cell1

Step2: go to Home tab, click Conditional Formatting command under Styles group. And select New Rule from the context menu list. And the New Formatting Rule dialog will appear.

remove conditial formatting on blank cell2

Step3: select Use a formula to determine which cells to format under Select a Rule Type list box in the New Formatting Rule dialog box, and type the following formula into Format values where this formula is true text box, and keep the Format as No Format Set. Click Ok button.

remove conditial formatting on blank cell3

Step4: go to Home tab, click Conditional Formatting command under Styles group. And select Manage Rules from the context menu list. And the Conditional Formatting Rules Manager dialog will appear.

remove conditial formatting on blank cell4

Step5: checked Stop If True check box for your newly created rule. And click on Ok button.

remove conditial formatting on blank cell5

Step6: you would notice that conditional formatting have been removed on the all blank cells in the selected range of cells.

remove conditial formatting on blank cell6

 

 

Conditional Formatting Rows by Groups in Excel

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 Groups


Assuming that you have a list of data in range A1:B5, in which contain text values, and you want to alter rows background color by groups, you can apply conditional formatting with a formula based on the ISODD function. Just do the following steps:

Step1: you need to add a helper column and add a conditional format rule that references the values in the helper column. select cell C1, and enter the number 1.

conditional format rows by group1

Step2: type the following formula into Cell C2 .

=IF(A2=A1,C1,C1+1)

conditional format rows by group2

Note: this example will group rows by column A. If you want to group rows by other columns, just replace Cell A2 and A1 with other cells in this formula.

 

Step3: Copy the formula in Cell C2 to the remaining cells in the range C3:C5.

 

Step4:  select your data range A1:C5, go to HOME tab, click Conditional Formatting command under Styles group, Click New Rule from the drop down menu list. And the New Formatting Rule dialog will open.

conditional format rows by group3

Step5: click Use a formula to determine which cells to format option in the New Formatting Rule dialog box, type the following formula in the Format values where this formulas is true text box.

=ISODD($C1)

conditional format rows by group4

Step6: click Format button, and the Format Cells dialog will open. Click Fill tab, select on color that you want to use. Click Ok button.

conditional format rows by group5

Step7:  click Ok button.

conditional format rows by group6

If you want to alter row color for Even rows, you just need to add another New Rule with a formula based on the ISEVEN($C1). so you just need to repeat the above steps.

=ISEVEN($C1)

conditional format rows by group7

conditional format rows by group8

 

How to Highlight Cell or Row If Date Is In Current Day/Week/Month in Excel

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 formatting in Excel 2013/2016.

Highlight Cell if Date is in Current Day/Week/Month


To highlight the cell of current day or the date is in the current week or month, you can do the following steps:

#1 select the range of cells that you want to highlight date.

highlight cell or row if date 1

#2 go to HOME tab, click Conditional Formatting command under Styles group, and click Highlight Cells Rules menu from the drop down menu list, then select A Date Occurring sub menu. And the A Date Occurring dialog will appear.

highlight cell or row if date 2

#3 if you want to highlight the current day, then select Today option from the first drop down list box in the A Date Occurring dialog box, then choose one color that you want to fill the cell in the second drop down list box. Click Ok button.

highlight cell or row if date 3

Note: if you want to highlight the cell if date is in the current week, just choose This Week option from the first drop down list box. And if you want to highlight the cell if date is in the current month, just choose This Month from the first drop down list box.

#4 you should notice that the current date has been highlighted.

highlight cell or row if date 4

Highlight Row if Date is in Current Day/Week/Month


If you want to highlight the row that If the date in that row is equal to the current day or is in the current week or month, you can do the following steps:

#1 select the entire rows of data that contain date values.

highlight cell or row if date 1

#2 go to HOME tab, click Conditional Formatting command under Styles group, Click New Rule from the drop down menu list. And the New Formatting Rule dialog will open.

highlight cell or row if date5

#3 click Use a formula to determine which cells to format option in the New Formatting Rule dialog box, type the following formula in the Format values where this formulas is true text box.

highlight cell or row if date6

To highlight current date, use the following formula:

=$A2=TODAY()

To highlight row if date is in the current week, use the following formula:

=TODAY()-WEEKDAY(TODAY(), 3)=$A2-WEEKDAY($A2, 3)

To highlight row if date is in the current month, use the following formula:

=TEXT($A2,”mmyy”)=TEXT(TODAY(),”mmyy”)

Note: the Cell A2 is the first cell of your selected range.

#4 click Format button, and the Format Cells dialog will open. Click Fill tab, select on color that you want to use. Click Ok button.

highlight cell or row if date7

#5 click Ok button. You should see that the entire rows which contain the current day have been highlighted.

highlight cell or row if date8

Related Functions


  • Excel WEEKDAY function
    The Excel WEEKDAY function returns a integer value representing the day fo the week for a given Excel date and the value is range from 1 to 7.The syntax of the WEEKDAY function is as below:=WEEKDAY (serial_number,[return_type])…
  • Excel TODAY function
    The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…
  • Excel Text function
    The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…

 

 

How to Highlight Cells Greater Than a Certain Length in Excel

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 Cells Based on Text Length


Assuming that you have a list of data in range A1:A6, in which contain text values. And you want to highlight All cells in red color if the cell length is more than 6 characters in it. How can I do it. You can use the Conditional Formatting function with the LEN function to achieve the result of Formatting Cells in selected range that containing more than 6 characters. Here are the steps:

#1 select the range that you want to highlight cells based on text length.

highlight cells based on cell lenght1

#2 go to HOME tab, click Conditional Formatting command under Styles group. And click New Rule from the popup menu list. And the New Formatting Rule dialog will open.

highlight cells based on cell lenght2

#3 choose Use a formula to determine which cells to format option under Select a Rule Type section in the New Formatting Rule dialog box. Then enter this formula =LEN(A1)>6 into the Format Values where this formula is true text box. Note: the Cell A1 is the first cell of your selected range.

highlight cells based on cell lenght3

#4 click Format button in the New Formatting Rule dialog box, and the Format Cells dialog will open. And switch to Fill tab, choose red color as you need in the Background Color list. Click OK button.

highlight cells based on cell lenght4

#5 click Ok button to apply those changes.

highlight cells based on cell lenght5

#6 you would notice that all cells in range A1:A6 that contain more than 6 characters have been highlighted in red color.

highlight cells based on cell lenght6

How to Highlight Dates Older Than 30 Days in Excel

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 if it’s older than x days in Excel.

Highlight Dates Older Than 30 Days


Assuming that you have a list of data in Range B1:B5, in which contain date values. And you need to format all date values if the dates are older than 30 days then highlighting the cells. You can use the Conditional Formatting feature to achieve the result of highlighting dates older than X days. Here are the steps:

#1 select the range that contain date values you want to highlight.

highlight dates older than 30 days1

#2 go to HOME tab, click Conditional Formatting command under Styles group. And select New Rule from the drop down menu list. And the New Formatting Rule dialog will open.

highlight dates older than 30 days2

#3 select Use a formula to determine which cells to format option under Select a Rule Type list box, and type the following formula into Format values where this formula is true text box.

=B1<TODAY()-30

highlight dates older than 30 days3

#4 click Format button in the New Formatting Rule dialog, and the Format Cells dialog will open. Click Fill tab in the Format Cells dialog, and choose one color as the background color to highlight cells. Click Ok button.

highlight dates older than 30 days4

#5 click Ok button. All date values are older than 30 would be highlighted in the selected range of cells.

highlight dates older than 30 days5

How to Select Cells That Meet Certain Criteria in Excel

This post will guide you how to select cells that meet certain criteria in Excel. How do I find and select cells based on specific types of data in your worksheet in Excel.

Select Cells That Meet one Criteria Using Go To Command


Assuming that you have a list of data in range A1:C5, and you want to select cells that contain specific types of data, such as: formulas, blanks, non-blanks, etc. How to do it. You can use the Go To command to quickly find and select all cells that meet your specific conditions.

For example, you need to select all blanks in the selected range of cell, just do the following steps:

#1 select the range of cells that you want to find for specific cells.

select cells that meet criteria1

#2 go to HOME tab, click Find & Select command under Editing group. And select Go To from the drop down menu list. And the Go To dialog will open.

select cells that meet criteria2

#3 click Special button in the Go to dialog, and the Go To Special dialog will open.

select cells that meet criteria3

#4 click Blanks option in the Go To Special dialog. Click Ok button.

select cells that meet criteria4

All of the blank cells have been selected in the selected range.

select cells that meet criteria5

Note: You can click one of the following options to select all cells that contain specific types of data:

Click To select
Comments Cells that contain comments.
Constants Cells that contain constants.
Formulas Cells that contain formulas.

Note: The check boxes below Formulas define the type of formula.

Blanks Blank cells.
Current region The current region, such as an entire list.
Current array An entire array if the active cell is contained in an array.
Objects Graphical objects, including charts and buttons, on the worksheet and in text boxes.
Row differences All cells that differ from the active cell in a selected row. There is always one active cell in a selection—whether this is a range, row, or column. By pressing the Enter or Tab key, you can change the location of the active cell, which by default is the first cell in a row.

If more than one row is selected, the comparison is done for each individual row of that selection, and the cell that is used in the comparison for each additional row is located in the same column as the active cell.

Column differences All cells that differ from the active cell in a selected column. There is always one active cell in a selection, whether this is a range, row, or column. By pressing the Enter or Tab key, you can change the location of the active cell—which by default is the first cell in a column.

When selecting more than one column, the comparison is done for each individual column of that selection. The cell that is used in the comparison for each additional column is located in the same row as the active cell.

Precedents Cells that are referenced by the formula in the active cell. Under Dependents, do either of the following:

  • Click Direct only to find only cells that are directly referenced by formulas.
  • Click All levels to find all cells that are directly or indirectly referenced by the cells in the selection.
Dependents Cells with formulas that refer to the active cell. Do either of the following:

  • Click Direct only to find only cells with formulas that refer directly to the active cell.
  • Click All levels to find all cells that directly or indirectly refer to the active cell.
Last cell The last cell on the worksheet that contains data or formatting.
Visible cells only Only cells that are visible in a range that crosses hidden rows or columns.
Conditional formats Only cells that have conditional formats applied. Under Data validation, do either of the following:

  • Click All to find all cells that have conditional formats applied.
  • Click Same to find cells that have the same conditional formats as the currently selected cell.
Data validation Only cells that have data validation rules applied. Do either of the following:

  • Click All to find all cells that have data validation applied.
  • Click Same to find cells that have the same data validation as the currently selected cell.

 

Find Cells That Meet one Criteria Using Conditional Formatting


You can also use the Conditional Formatting feature to find and select all cells that meet one certain criteria in your selected range.

For example, you need to select cells that the sale values are greater than or equal to 50 in your selected range. Let’s do the following steps:

#1 select the range of cells that you want to use (B2:B5).

select cells that meet criteria9

#2 go to HOME tab, click Conditional Formatting command under Styles group. And click New Rule from the drop down menu list. And the New Formatting Rule dialog will open.

select cells that meet criteria6

#3 select Format only cells that contain option under Select a Rule Type list box. Select greater than or equal to option from the second list box, and enter number 50 in the third box under Format only cells with section.

select cells that meet criteria7

#4 Click Format button in the New Formatting Rule dialog, switch to Fill tab in the Format Cells dialog, and select one color as the background color. Click OK button.

select cells that meet criteria8

#5 Click Ok button. The all of the Cells that are greater than or equal to number 50 have been find and highlighted with the color you set.

select cells that meet criteria10

 

How to Highlight The Highest and Lowest Value in Each Row or Column in Excel

This post will guide you how to highlight the highest value in each row or column in Excel. How do I highlight the lowest value in each row and column with conditional formatting function.

Highlight Highest Value in Each Row


Assuming that you have a list of data in range A1:D7, and you want to highlight all highest values in each row or highlight all lowest values in each row. How to achieve it. You need to use the conditional formatting feature to achieve the result. Just do the following steps:

#1 select the range of cells that you want to highlight the highest values in each row.

highlight highest value in row1

#2 go to HOME tab, click Conditional Formatting command under Styles group. And then select New Rule from the drop down menu list. And the New Formatting Rule dialog will appear.

highlight highest value in row2

#3 select Use a formula to determine which cells to format in the Select a Rule Type list box, and type the following formula in the Format values where this formula is true text box. And then click Format button.

=B2=MAX($B2:$D2)

highlight highest value in row3

#4 switch to Fill tab, select on background color as you need. Click Ok button.

highlight highest value in row4

#5 click Ok button. The highest value in each row has been highlighted.

highlight highest value in row5

Highlight Lowest Value in Each Row


If you want to highlight lowest value in each row in your table, you just need to repeat the above steps, just use the following formula instead of it.

=B2=MIN($B2:$D2)

highlight highest value in row10
highlight highest value in row11

Highlight Highest Value in Each Column


If you want to highlight highest value in one column, you can repeat the above steps, and then use the following formula:

=B2=MAX($B$2:B$7)

highlight highest value in row6
highlight highest value in row7

Highlight Lowest Value in Each Column


If you want to highlight highest value in one column, you can repeat the above steps, and then use the following formula:

=B2=MIN(B$2:B$7)

highlight highest value in row8
highlight highest value in row9

Video: Highlight Highest and Lowest Values in each Row or Column

Related Functions


  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
  • Excel MAX function
    The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…

How to Highlight Blank Cells in Excel

This post will guide you how to highlight blank cells in Excel. How do I use Conditional Formatting to highlight blank cells in Excel.

Highlight Blank Cells with Go To Special Feature


If you want to highlight all blank cells in your select range of cells, you need to select all blank cells firstly, then you can fill color in those selected blank cells. Just do the following steps:

#1 select the range of cells that contain blank cells to be highlighted.

highlight blank cells1

#2 go to HOME tab, click Find & Select command under Editing group, and then select Go To Special from the popup menu list. And the Go To Special dialog will open.

highlight blank cells2

 

#3 select Blanks radio button  , and click OK button. You will see that all blank cells will be selected in the selected range of cells.

highlight blank cells3

highlight blank cells4

#4 go to HOME tab, click Fill Color icon under Font group, and choose one color as you need. All blank cells will be highlighted.

highlight blank cells5

highlight blank cells6

Highlight Blank Cells with Conditional Formatting


You can also use the conditional formatting to highlight blank cells in your selected range of cells in Excel, just do the following steps:

#1 select the range of cells where you want to highlight all blank cells

 highlight blank cells1

#2 go to HOME tab, click Conditional Formatting command under styles group. And select New Rules from the popup menu list. And the New Formatting Rule dialog will open.

highlight blank cells7

#3 select Use a formula to determine which cells to format in the Select a Rule Type list box, and type the following formula into the Format values where this formula is true: text box.

=B1=””

highlight blank cells8

Note: the Cell B1 is the first cell of your selected range of cells.

#4 click Format button to open Format Cells dialog, switch to Fill tab, and then choose one color as you like to fill. Click Ok button.

highlight blank cells9

#5 click OK button. All blank cells in selected range of cells have been highlighted.

highlight blank cells10

Video: Highlight Blank Cells

 

How to Find and Highlight Duplicate Rows in Excel

This post will guide you how to find and highlight duplicate rows in a range of cells in Excel. How do I find duplicate rows in a range with a formula in Excel. How to find duplicate rows in two or three columns with Conditional Formatting feature in Excel.

Find Duplicate Rows Only


If you have a list of data in two or more columns, such as: A1:C4, and you want to find duplicate rows in those columns in Excel. How to achieve it. You can use a formula based on the IF function, the SUMPRODUCT function to achieve the result of finding all duplicate rows in a range of cells. If found, returns TRUE, otherwise, returns False. Like this:

=IF(SUMPRODUCT(($A$1:$A$4=A1)*1,($B$1:$B$4=B1)*1,($C$1:$C$4=C1)*1)>1,"TRUE","FALSE")

Type this formula into Cell D1, and press Enter key in your keyboard, and then drag the AutoFill Handle over to other cells to apply this formula.

find duplicate rows1

Find and Highlight Duplicate Rows


If you want to highlight duplicate rows, you need to use the conditional formatting feature to achieve the result in Excel. You just need to do the following steps:

#1 you need to create a helper column and concatenate values from all columns. Just add the below formula in Cell D1, and press Enter key, then drag the AutoFill Handle over to Cell D4 to apply the formula:

=A1&B1&C1

Or

=CONCATENATE(A1,B1,C1)

If you have a large number of columns, you can use the TEXTJOIN function to concatenate values for all columns. Like this:

=TEXTJOIN(“,”,TRUE,A1:X1)

find duplicate rows2

#2 select the range of Cells that you want to highlight duplicate rows including the range D1:D4 in Helper column, and go to HOME tab, click Conditional Formatting command under Styles group. And click New Rule from the drop down menu list. And the New Formatting Rule dialog will open.

find duplicate rows3-1

find duplicate rows3

#3 select Use a formula to determine which cells to format in Select a Rule Type list box, and type the following formula in text box of the Format values where this formula is true.

=COUNTIF($D$1:$D$4,$D1)>1

find duplicate rows4

D1:D4 is a range of cells in helper column.

#4 click Format command, and the Format Cells dialog will open. And switch to Fill tab, select one background color as you need, then click OK button.

find duplicate rows5

#5 click OK button. All duplicate rows are highlighted successfully.

find duplicate rows6

Video: Find and Highlight Duplicate Rows

Related Functions


  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel Concat function
    The excel CONCAT function combines 2 or more strings or ranges together.This is a new function in Excel 2016 and it replaces the CONCATENATE function.The syntax of the CONCAT function is as below:=CONCAT (text1,[text2],…)…
  • Excel TEXTJOIN function
    The Excel TEXTJOIN function joins two or more text strings together and separated by a delimiter. you can select an entire range of cell references to be combined in excel 2016.The syntax of the TEXTJOIN function is as below:= TEXTJOIN  (delimiter, ignore_empty,text1,[text2])…
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

 

 

Comparing Columns Using Conditional Formatting Icon Sets

This post will guide you how to compare the adjacent cells in the different columns using Conditional Formatting Icon Sets in Excel. How do I compare cells in the adjacent rows using Conditional Formatting Icon Sets in Excel. How to compare Columns or rows using Conditional Formatting Icon Sets to show increase or decrease status in your current worksheet. How to use the conditional formatting feature to show trend arrows when comparing the adjacent columns or rows.

Assuming that you have a list of data A1:B5, and you want to compare cells in Column A with Column B, if the value in Cells in Column B is greater than the value of cells in Column A, then showing an up arrow Icon in the Column C; If the value of cells in Column B is equal to the value in Column A, then showing an right arrow icon in column C; If the value of cells in Column B is less than the value in Column A, then showing an down arrow icon in Column C. How to achieve it.

Comparing Columns Using Conditional Formatting Icon Sets


To compare the adjacent columns using conditional formatting icon sets in excel, you need to do the following steps:

#1 type this formula into the formula box of the first cell in column C, and drag the AutoFill Handler over other cells to apply this formula.

=IF(A1>B1,0,IF(A1<B1,2,1))

comparing columns1

This formula will compare the adjacent cells in Column A and B.

#2 go to HOME tab, Click Conditional Formatting command under Styles group. and select New Rule from the popup menu list. The New Formatting Rule dialog box will appear.

comparing columns2

#3 click Format all cells based on their values option from the Select a Rule Type list box. and select Icon Sets value from the drop-down list box of Format Style. Select 3 Arrows icon from the drop-down list of Icon Style. Check Show Icon Only option, and then type the value 2 when the Icon is up arrow icon. and type the value 1 when icon is right icon. Click Ok button.

comparing columns3

#4 Let’s see the result.

comparing columns4

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….

 

 

 

Find Duplicate Values in Two Columns

This tutorial will show you how to find duplicate values in two given columns in Excel. Or How to compare two columns and find duplicate entries in Microsoft Excel worksheet. And you will learn two methods to compare columns so that the duplicate values can be highlighted or listed in a range of Cells.

Assuming that you need to compare two columns (Column A and Column B) to get the duplicate values in your worksheet, you can find duplicate values in two columns with Excel Formula, or Excel VBA Macro code.

Method 1: Find duplicate values in two columns with Excel Formula

To compare two given columns and find duplicate values in Excel, you can use a combination of the IF function, the ISERROR function, and the MATCH function to create an Excel formula. So you can use the following formula:

=IF(ISERROR(MATCH(A1,$B$1:$B$4,0))," ",A1)

Now you need to type this formula in Cell C1, press Enter key, drag AutoFill Handle down to Cell C2:C4, you will see all of the duplicated values are displayed in Column C.

find duplicate values in two columns1

Method 2: Find duplicate values in two columns with VBA Macro code

If you are familiar with the programming language and you can use a Visual Basic Macro to compare the value in two columns and then find duplicate values, just refer to the following steps:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

find duplicate cells in two columns111

Sub FindUplicatesinTwoColumns()
    Set Range1 = Application.Selection
    Set Range1 = Application.InputBox("Select the first range in one column:", "FindUplicatesinTwoColumns", Range1.Address, Type:=8)
    Set Range2 = Application.InputBox("Select the second range in another column:", "FindUplicatesinTwoColumns", Type:=8)

    For Each R1 In Range1
        xValue = R1.Value
        For Each R2 In Range2
            If xValue = R2.Value Then
                If R3 Is Nothing Then
                    Set R3 = R1
                Else
                    Set R3 = Application.Union(R3, R1)
                End If
            End If
        Next
    Next
    R3.Interior.ColorIndex=3

End Sub

5# back to the current worksheet, then run the above excel macro.

find duplicate values in two columns3

6# Select the first range A1:A4 in Column A, click OK button, then select the second range B1:B4 in Column B, click OK button.

find duplicate values in two columns4 find duplicate values in two columns5

7# let’s see the result:

find duplicate values in two columns6

Method 3: Find duplicate values in two columns with Conditional Formatting feature

You can use conditional formatting with on a formula based on the COUNTIF function and the AND function to find the duplicate values in two specified columns and then highlighted them. Just do it following:

1# Select the entire Column A via click on the Column Header and then the column A will be highlighted

find duplicate values in two columns7

2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.

find duplicate values in two columns8

3# select Use a formula to determine which cells to format as Rule Type in the New Formatting Rule window

4# Type the following formula in the Format values where this formula is true: box

=COUNTIf($B:$B, $A1)

5# click the Format… button, then the Format Cells window will appear.

6# in the “Format Cells” window, switch to the Fill tab, choose the background color, and then click OK button.

7#you will be back to the New Formatting Rule windows and you can check a preview of the formatting you have selected. Then click OK button.

find duplicate values in two columns9

Then the conditional formatting rule will be applied to all values in two columns and highlighted the duplicate values.

find duplicate values in two columns9


Related Functions

  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel AND function
    The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…)…

Related Posts

  • Highlight Rows
    You will learn that how to change the color of the entire rows if the value of cells in a specified column meets your conditions, such as, if the value of cells is equal to or greater than a certain number or text values, then excel should be highlight entire rows or change a row color as you need.…
  • Find Duplicate Rows
    If you want to check the entire row that duplicated or not, if True, then returns “duplicates” value, otherwise, returns “no duplicates”. You can create a formula based on the IF function and the SUMPRODUCT function..…
  • Highlight Duplicate Rows
    this post will talk that how to highlight entire rows that are duplicates in excel 2016, 2013 or lower version. Or how to change the background color of duplicate rows..…
  • Highlight duplicate values
    this post will teach you how to highlight duplicate values in the range of cells in excel. Normally, you may be need to identify duplicate values with a range of cells in Excel. And there is one of the fasted way that is using conditional formatting feature in Microsoft Excel……

Change background color of cells based on value

This tutorial will guide how to change the background color of cells based on specific value in excel. How to change the background color automatically when the cell value’s changes. You will learn that how to change the background color based on the certain value of cells with conditional formatting command in excel 2016, 2013 or other versions.

Change background color based on cell value

Assuming that you have a list of grade record in a range of cells in excel worksheet, and if you want to change the background color of cells based on cell values. And you also want to change the color dynamically to reflect the value changes.

You want to format the background color of cells as red based on value that is less than 60, and format color as blue if the cell value is between 60 and 80. Or greater than 80 to set the background color of cells as green.

There is a very useful excel command name as “conditional formatting” in MS excel, you can use it to format the cells based on its certain values, such as: background color or fond color changing, or others formats.

You just need to refer to the following steps:

Step 1# select the cells that you want to change the background color (you need only to select the cells that containing grade values).

change background color cell value1

Step 2# go to the Home Tab, then click Styles group-> “Conditional Formatting” command, then click on “New Rule…” menu.

change background color cell value2

Step 3# the “New Formatting Rule” window will appear.

change background color cell value3

Step 4# select “Format only cells that contain” under “Select a Rule Type:” selected box. Then you need to set the formatting rule under “Format only Cells with”, specify the conditions that you need. Such as: select “Cell Value” from the first drop down list, select “less than” from the second drop-down list and enter the specific cell value as 60.

change background color cell value4

Step 5# click the “Format…” button, then switch to the Fill Tab, select the color that you want to set, such as: red color, select OK button.

change background color cell value5

Step 6# back to the “New Formatting Rule” window, then click “OK” button. Let’s see the last result:

change background color cell value6

Then we can set the background color for other certain cell values, you just need to set the different format rules and repeat the above steps.

change background color cell value7

If you want to change the font color of cells based on value in excel, you just need to use the same ways above, and just need to switch to the Font Tab in step 5, and set the color that you want to specify.  Let’s see the below screenshot about changing font color:

change background color cell value8