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.
#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.
#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.
#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.
#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.
#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.
#7 let’s see the last result:
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.
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
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.
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.
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.
Step6: let’s see the result:
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.
Step3: In the Visual Basic Editor, go to Insert > Module to create a new module.
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
Note: you can modify the ColorIndex property as per your preference. The code above sets the color to yellow.
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.
Step6: Select the range of cells that contains the dates you want to highlight.
Step7: The dates that are over a year old will be highlighted in the color you specified.
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.
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.
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->ConditionalFormatting -> NewRule, 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.
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.
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.
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.
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 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.
Step2: Click the “Format” menu and then click the “ConditionalFormatting” menu.
Or right-click on the selected column -> select “View more cell actions” -> “ Conditional formatting “, the Conditional formatting rules dialog box will pop up.
Step3: Next we can select the desired format for the range or column. Select Single color Tab
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 “Customformulais” from the “FormatCellsif” drop-down list
Step5: In the Formula text box, enter the following formula.
=$C3>$B3
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
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?
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.
Click Home > Conditional Formatting > Highlight Cell Rules > Included Text…
Select the color formatting for the text, and then click OK.
How do you conditional format if one column is greater than another?
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?
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?
Select all cells in the worksheet that you want to highlight and apply formatting rules to.
Click Conditional Formatting.
Select the Highlight Cells rule, and then select the rule that applies to your needs…
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.
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.
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
STEP2: Click HOME Tab, in the Styles group, click Conditional Formatting command, click HighlightCellsRules menu, and then click the cell rules in the secondary menu, such as: Greater Than, Less Than, Between, Equal To ,etc.
In this example, we select the Equal To cell rule. Then the Equal To dialog box will pop up.
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“.
Step4: Excel will apply the selected format to the specific cell that meets the condition.
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.
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.
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
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.
Step6: When you click OK, the selected format is applied to the cells that meet the conditions.
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
Step2: Click on the Format menu, and then click on the Conditional formatting menu
Step3: The Conditionalformattingrules window will open.
Step4: Select Singlecolor Tab
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 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.
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.
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.
Step 3: Click the arrow on Conditional Formatting icon, select New Rule.
Step 4: In ‘New Formatting Rule’ dialog ‘Select a Rule Type’ pane, select ‘Use a formula to determine which cells to format’ option.
Step 5: Enter formula =NOT(ISBLANK(A1)) into ‘Format values where this formula is true.’.
Step 6: Click Format button in Edit theRule Description pane. On Format Cells, click Fill tab, select background color, then click OK to quit current dialog.
Step 7: In Preview field, you can see that cell is highlighted with yellow. Click OK to quit editing.
Verify that all non-blank cells are highlighted properly.
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.
Step 2: On Go To Special dialog, check on Constants, then Numbers, Text, Logicals and Errors are activated by default. Click OK.
Step 3: After step#2, all non-blank cells are selected.
In Home ribbon, click Fill Color arrow in Font group. Select color, then non-blank cells are filled with this color.
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.
Step 5: Select ‘HighlightAllNonBlankCells’ and click Run.
Verify that all non-blank cells are selected. Then you can follow step#3 in method#2 to fill highlight color.
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.
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.
Step3: Click the arrow button on Conditional Formatting icon to load all sub menus, select New Rule in it.
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”.
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.
Step7: Returns to New Formatting Rule window, see the Preview. Verify that cell is filled with light blue background. Then click OK.
Step8: Verify that in Serial Number column, cells with N type are highlighted properly.
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.
Step10: Click filter arrow in A column, select Filter by Color, then select background light blue in Filter by Cell Color.
Step11: Verify that only N type products rows are listed.
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.
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.
Step2: go to Home tab, click ConditionalFormatting command under Styles group. And select New Rule from the context menu list. And the New Formatting Rule dialog will appear.
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.
Step4: go to Home tab, click Conditional Formatting command under Styles group. And select Manage Rules from the context menu list. And the ConditionalFormatting Rules Manager dialog will appear.
Step5: checked Stop If True check box for your newly created rule. And click on Ok button.
Step6: you would notice that conditional formatting have been removed on the all blank cells in the selected range of cells.
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.
Step2: type the following formula into Cell C2 .
=IF(A2=A1,C1,C1+1)
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 ConditionalFormatting command under Styles group, Click New Rule from the drop down menu list. And the New Formatting Rule dialog will open.
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)
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.
Step7: click Ok button.
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.
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.
#2 go to HOME tab, click ConditionalFormatting 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.
#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.
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 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.
#2 go to HOME tab, click ConditionalFormatting command under Styles group, Click New Rule from the drop down menu list. And the New Formatting Rule dialog will open.
#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.
To highlight current date, use the following formula:
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.
#5 click Ok button. You should see that the entire rows which contain the current day have been highlighted.
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)…
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.
#2 go to HOME tab, click ConditionalFormatting command under Styles group. And click NewRule from the popup menu list. And the NewFormattingRule dialog will open.
#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.
#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.
#5 click Ok button to apply those changes.
#6 you would notice that all cells in range A1:A6 that contain more than 6 characters have been highlighted in red color.
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.
#2 go to HOME tab, click Conditional Formatting command under Styles group. And select NewRule from the drop down menu list. And the NewFormattingRule dialog will open.
#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
#4 click Format button in the New Formatting Rule dialog, and the FormatCells 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.
#5 click Ok button. All date values are older than 30 would be highlighted in the selected range of cells.
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.
#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.
#3 click Special button in the Goto dialog, and the GoToSpecial dialog will open.
#4 click Blanks option in the Go To Special dialog. Click Ok button.
All of the blank cells have been selected in the selected range.
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).
#2 go to HOME tab, click ConditionalFormatting command under Styles group. And click NewRule from the drop down menu list. And the NewFormattingRule dialog will open.
#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 Formatonlycellswith section.
#4 Click Format button in the NewFormattingRule dialog, switch to Fill tab in the FormatCells dialog, and select one color as the background color. Click OK button.
#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.
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.
#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.
#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.
#4 switch to Fill tab, select on background color as you need. Click Ok button.
#5 click Ok button. The highest value in each row has been highlighted.
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.
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])…
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.
#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.
#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.
#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 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
#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.
#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=””
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.
#5 click OK button. All blank cells in selected range of cells have been highlighted.
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.
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:
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 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:
#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.
#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.
#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.
#5 click OK button. All duplicate rows are highlighted successfully.
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],…)…
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)…
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.
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.
#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.
#4 Let’s see the result.
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])….
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:
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.
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.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
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.
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.
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
2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.
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
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……
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).
Step 2# go to the Home Tab, then click Styles group-> “Conditional Formatting” command, then click on “New Rule…” menu.
Step 3# the “New Formatting Rule” window will appear.
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.
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.
Step 6# back to the “New Formatting Rule” window, then click “OK” button. Let’s see the last result:
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.
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:
