Conditional Formatting in Excel 2013 HI, I am trying to find a way of applying similar conditional formatting rules to multiple cells rather than having to do it individually. Aug 18, 2016 - Formulas let you access the full power of Excel to apply conditional formatting with exactly the logic you need. This article shows how to.
Adding conditional formatting to a cell in Excel allows you to apply different formatting options, such as color, when the data in that cell meets the conditions that you have set.
Instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Mac, Excel for Office 365, and Excel Online.
How to Use Conditional Formatting
To make conditional formatting easier, Excel supports pre-set options that cover commonly used situations, such as:
- Dates
- Duplicate data
In the case of dates, the pre-set options simplify the process of checking your data for dates close to the current date such as yesterday, tomorrow, last week, or next month.
If you want to check for dates that fall outside of the listed options, however, customize the conditional formatting by adding your own formula using one or more of Excel's date functions.
of 05
Check for Dates 30, 60, and 90 Days Past Due
Customize conditional formatting using formulas by setting a new rule that Excel follows when evaluating the data in a cell.
Excel applies conditional formatting in top-to-bottom order as they appear in the Conditional Formatting Rules Manager dialog box.
Even though several rules may apply to some cells, the first rule that meets the condition is applied to the cells.
This demo uses the current date, 40 days before the current date, 70 days before the current date, and 100 days before the current date to generate the results.
of 05
Check for Dates 30 Days Past Due
In a blank Excel worksheet, highlight cells C1 to C4 to select them. This is the range to which the conditional formatting rules will be applied.
- Select Home > Conditional Formatting > New Rule to open the New Formatting Rule dialog box.
- Choose Use a formula to determine which cells to format.
- In the Format values where this formula is true text box, enter the formula:
This formula checks to see if the dates in cells C1 to C4 are more than 30 days past. - Select Format to open the Format Cells dialog box.
- Select the Fill tab to see the background fill color options.
- Select a background fill color.
- Select the Font tab to see font format options.
- Set the font color.
- Select OK twice to close the dialog boxes and return to the worksheet.
- The background color of cells C1 to C4 changes to the fill color chosen, even though there are no data in the cells.
of 05
Add a Rule for Dates More Than 60 days Past Due
Rather than repeat all the steps above to add the next two rules, use the Manage Rules option to add the additional rules all at once.
- Highlight cells C1 to C4, if necessary.
- Select Home > Conditional Formatting > Manage Rules to open the Conditional Formatting Rules Manager dialog box.
- Select New Rule.
- Select Use a formula to determine which cells to format.
- In the the Format values where this formula is true text box, enter the formula:
This formula checks to see if the dates in cells C1 to C4 are greater than 60 days past. - Select Format to open the Format Cells dialog box.
- Select the Fill tab to see the background fill color options.
- Select a background fill color.
- Select OK twice to close the dialog box and return to the Conditional Formatting Rules Manager dialog box.
of 05
Add a Rule for Dates More Than 90 days Past Due
- Highlight cells C1 to C4, if necessary.
- Select Home > Conditional Formatting > Manage Rules to open the Conditional Formatting Rules Manager dialog box.
- Select New Rule.
- Select Use a formula to determine which cells to format.
- In the Format values where this formula is true, enter the formula:
- This formula checks to see if the dates in cells C1 to C4 are greater than 90 days past.
- Select Format to open the Format Cells dialog box.
- Select the Fill tab to see the background fill color options.
- Select a background fill color.
- Select OK twice to close the dialog box and return to the Conditional Formatting Rules Manager dialog box.
- Select OK to close this dialog box and return to the worksheet.
The background color of cells C1 to C4 changes to the last fill color chosen.
of 05
Test the Conditional Formatting Rules
Test the conditional formatting rules in cells C1 to C4 by entering the following dates:
- Enter the current date in cell C1. The cell changes to the default white background with black text since none of the conditional formatting rules apply.
- Enter the following formula in cell C2:This formula determines which date occurs 40 days before the current date. The cell is filled with the color you selected for the conditional formatting rule for dates more than 30 days past due.
- Enter the following formula in cell C3:
This formula determines which date occurs 70 days before the current date. The cell is filled with the color you selected for the conditional formatting rule for dates more than 60 days past due. - Enter the following formula in cell C4:
This formula determines which date occurs 100 days before the current date. The cell color changes to the color you selected for the conditional formatting rule for dates more than 90 days past due.
This tutorial on using conditional formatting to highlight dates is brought to us by MVP Frédéric Le Guen, with special acknowledgment and thanks to Ken Puls for assistance with translation from French to English.
Date functions in Excel make it is possible to perform date calculations, like addition or subtraction, resulting in automated or semi-automated worksheets. The NOW function, which calculates values based on the current date and time, is a great example of this.
Taking this functionality a step further, when you mix date functions with conditional formatting, you can create spreadsheets that display date alerts automatically when a deadline is near or differentiates between types of days, like weekends and weekdays.
The basics of conditional formatting for dates
To find conditional formatting for dates, go to
Home > Conditional Formatting > Highlight Cell Rules > A Date Occuring.
You can select the following date options, ranging from yesterday to next month:
These 10 date options generate rules based on the current date. If you need to create rules for other dates (e.g., greater than a month from the current date), you can create your own new rule.
Below are step-by-step instructions for a few of my favorite conditional formats for dates.
Highlighting weekends
When you design an automated calendar you don’t need to color the weekends yourself. With the conditional formatting tool, you can automatically change the colors of weekends by basing the format on the WEEKDAY function. Assume that you have the date table–a calendar without conditional formatting:
To change the color of the weekends, open the menu Conditional Formatting > New Rule
In the next dialog box, select the menu Use a formula to determine which cell to format.
In the text box Format values where this formula is true, enter the following WEEKDAY formula to determine whether the cell is a Saturday (6) or Sunday (7):
=WEEKDAY(B$5,2)>5
The parameter 2 means Saturday = 6 and Sunday = 7. This parameter is very useful to test for weekends.
Note: In this case, you must lock the reference of the row so that the conditional format will work correctly in the other cells in this table.
Then, customize the format of your condition by clicking on the Format button and you choose a fill color (orange in this example).
Click OK, then open Conditional Formatting> Manage Rules
Select This Worksheet to see theworksheet rules instead of thedefault selection. In Applies to, change the range that corresponds to your initial selection when creating your rules to extend it to the whole column.
Now you will see a different color for the weekends. Note: This example shows the result in the Excel Web App.
Highlighting holidays
To enrich the previous workbook, you could also color-code holidays. To do that, you need a column with the holidays you’d like to highlight in your workbook (but not necessarily in the same sheet). In our example, we have US public holidays in column AH (as related to the year in the cell B2).
Again, open the menu Conditional Formatting > New Rule. In this case, we use the formula COUNTIF in order to count if the number of public holidays in the current month is greater than 1.
=COUNTIF($AH$4:$AH$16,B$5)>1
Then, in the dialog box Manage Rules, select the range B4:AF11. If you want to highlight the holidays over the weekends, you move the public holiday rule to the top of the list.
This example in the Excel Web App below shows the result. Change the value of the month and the year to see how the calendar has a different format.
Highlighting delays
In case we want to change the color of cells based on our approach on a date again, we will use conditional formatting to make it work for us.
In the following example, we show:
- yellow dates between 1 and 2 months
- orange dates between 2 and 3 months
- purple dates more than 3 months
We then construct three rules conditional formatting using formula DATEDIF . Respectively for the three cases the following formulas:
=DATEDIF($B2,$E$2,”m”)>0
=DATEDIF($B2,$E$2,”m”)>1
=DATEDIF($B2,$E$2,”m”)>2
In the Excel Web App below, try changing some dates to experiment with the result.
Color scales
Rather than choose a different color set for each period in our timeframe, we will work with the option of color scales to color our cells.
First, go into a new column (column E), calculate the difference in number of days in a year again with the DATEDIF formula and the parameter “yd”.
=DATEDIF($D2,TODAY(),”yd”)
Then choose the menu Conditional Formatting> New Rule option Format all cells based on their value and choose the following options:
- Scale = 3 colors
- Minimum = 0 red
- Midpoint = 10 yellow
- Maximum = 30 white
The result is a gradient color scale with nuances from white to red through yellow. The closer to 0, the more red it will have, the closer to 10 the more yellow, and the closer to 30 the more white. In the Excel Web App below, try changing some dates to experiment with the result.
--Frédéric Le Guen