How do you use conditional formatting to highlight information?
 Show
Format > Conditional Format > Examples Excel conditional formatting formula examples. Highlight expiry dates, colour if duplicate, COUNTIF, high or low values, hide or show values. 20+ examples Highlight Dates 📆The following examples use conditional formatting formulas to highlight dates in a list. 1 -- Highlight Upcoming Expiry Dates 2 -- Highlight Expired Dates 3 -- Highlight Weekend Dates 4 -- Separate Dates With Top Border Lines 1) Highlight Upcoming Expiry DatesYou can use Excel conditional formatting to highlight payments that are due in the next thirty days. In this example, Due dates are entered in column A.
To set up the conditional formatting for upcoming expiry dates, follow these steps
2) Highlight Expired DatesYou can use Excel conditional formatting to highlight policies with dates that have expired. In this example, due dates are entered in cells B2:B7.
To set up the conditional formatting for past expiry dates, follow these steps
3) Highlight Weekend DatesTo highlight the weekend dates in a list, you can use conditional formatting. The WEEKDAY function returns a number for each day of the week, so you could adjust this formula to highlight other days of the week. You can see the steps in this video, and there are written steps below the video Highlight Weekend DatesIn this example, the Excel file has a list of product sales for the first half of July. We’d like to highlight the dates and sales numbers that fall on a weekend. Conditional formatting will colour the cells in columns A, B and C light green, for rows with a weekend date (Saturday or Sunday). To set up the conditional formatting for weekend dates, follow these steps
The weekend rows are highlighted in light green fill colour 4) Separate Dates With LinesIf you’re working with a list of tasks or orders, sorted by date, use conditional formatting to separate the dates with a border line. You can see the steps in this video. Format Duplicates 🔁The following examples show how to use conditional formatting formulas to work with duplicates in a list. Highlight duplicate values, to spot potential problems, or hide duplicate headings, to make a list easier to read. 1 -- Highlight Duplicates in Column 2 -- Highlight Duplicate Records in a List 3 -- Hide Duplicate Headings in List 1) Highlight Duplicates in ColumnUse Excel conditional formatting to highlight values that are duplicate entries in a specific column, or in a range of cells (multiple rows and columns). To highlight all the duplicate values, follow these steps:
On the worksheet, duplicate numbers in column A are highlighted with light red fill colour. 2) Highlight Duplicate Records in a ListTo highlight the duplicate records in a list, you can use conditional formatting. Add a formula in one column first, to string all the data together. You can see the steps in this video, and the written instructions are below the video. Highlight Duplicate Records in a ListYou can use Excel conditional formatting to highlight duplicate records in a list. First, you'll set upe a formula to combine all the fields into one column, then test that column for duplicates. Combine Date with FormulaFirst, create a formula to combine the data:
Highlight Duplicate RecordsNext, follow these steps, to add the conditional formatting which will format values that are duplicates:
COUNTIF Formula Notes1) Occurrences: The COUNTIF function counts the occurrences of each row's combined text, starting from row 2, and down to the formula's row. If there is more than one occurence, the row is highlighted. 2) Character Limit: The COUNTIF function only works for 255 characters or fewer. For longer strings use the following formula:
3) First Instance: If you only want to highlight the duplicate records, and not the first instance of a duplicated record, use the following conditional formatting formula:
3) Hide Duplicate ValuesUse Excel conditional formatting to hide duplicate headings on a worksheet, to make a list easier to read. This video shows you the steps, and the written instructions are below the video. Hide Duplicate ValuesIn a table, each row should have all data entered, to enable sorting and filtering. However, you can use Excel conditional formatting to hide the duplicate values, and make the list easier to read. In this example, when the table is sorted by Region, the second (and subsequent) occurrences of each region name will have white font colour. You can see the text if you select the cells. Follow these steps to hide the duplicates, and you can see the steps in the video above.
Highlight Numbers in List 🌡The following examples show different ways to highlight numbers with conditional formatting rule. 1 -- Highlight Top or Bottom Values 2 -- Highlight Lottery Numbers 3 -- Highlight Weather Data 4 -- Show Temperatures With Color Scale 1) Highlight Top or Bottom ValuesIn these two examples, see how to highlight the:
Both examples use the same list -- the months of the year, and the quantity sold each month. This video shows the steps, and the written instructions are below the video. Top 3 ValuesIn this example, we'll highlight the 3 highest numbers in the list of monthly sales. The conditional formatting rule will use the LARGE function, which has 2 arguments:
The rule will compare each number in the selected cells (C2:C13), to see if it is greater than or equal to that nth number
Follow these steps to apply the conditional formatting:
Bottom X ValuesIn this example, we'll highlight the lowest numbers in the list of monthly sales. Instead of typing a specific number in the formula, we'll set up a cell on the worksheet, where that number can be entered. Then, the conditional formatting formula will refer to that cell. First, set up the number cell:
TIP: Later, you could type a zero, to temporarily remove any highlighting To highlight the bottom values, the conditional formatting rule will use the SMALL function, which has 2 arguments:
The rule will compare each number in the list, to see if it is less than or equal to that nth number
Next, follow these steps to apply the conditional formatting:
To test the conditional formatting:
2) Highlight Lottery NumbersYou can use Excel conditional formatting to highlight the ticket numbers that have been drawn in a lottery. Winning numbers are entered on the sheet, and those numbers are highlighted in the list of purchased tickets. Written instructions are below the video, and you can download the sample file to follow along with the video. Highlight Lottery NumbersYou can use Excel conditional formatting to highlight the ticket numbers that have been drawn in a lottery, or the tickets that have 3 or more winning numbers. In this example the ticket numbers are in cells C6:H8, and the drawn numbers are entered in cells C3:H3. If a ticket cell's value is found in the cells with drawn numbers, the ticket number cell will be highlighted in green. To highlight the winning numbers:
To highlight the winning tickets:
3) Highlight Weather DataIn this conditional formatting example, temperatures and weather descriptions are highlighted in a weather log. You can download the sample data below. In the weather log table,
In another worksheet, there are 2 named Excel tables - one for weather types, and one for weather descriptions. There are also 3 named ranges, shown in the screen shot below The named ranges are used as the source for data validation drop down lists, like this list with weather descriptions. The named ranges are also used in the INDEX/MATCH formulas in the conditional formatting rules. This screen shot shows the formula in a worksheet cells, where it was used for testing the formula, before creating the rule. Note: A blank cell or cells with text, will not be affected by the color scale formatting. Here are the 5 rules, listed in the Rules Manager. You can download the Conditional Formatting for Weather Data sample data below. 4) Show Temperatures With a Color ScaleTo see the steps for using a color scale on a temperature cell, please watch this short video. The written instructions are below the video. Show Temperatures With a Color ScaleTo show hot temperatures in a red cell, and cold temperatures in a blue cell, you can use Excel's conditional formatting color scale. This feature is available in Excel 2007 and later versions.
Show or Hide Data 👁️The following examples show how to hide data, or show it, based on conditional formatting formula rules. 1 -- Hide Cell Contents When Printing 2 --Hide Errors 3 --Hide Follow-Up Questions 1) Hide Cell Contents When PrintingIn the following example, use Excel conditional formatting to hide cells when printing. In this example, the contents of cells B2:F4 are changed to white font, if cell H1 contains an x. To print with the cell contents hidden, type an x in cell H1. To display the cell contents, delete the x in cell H1.
2) Hide ErrorsYou can use Excel conditional formatting to check for error values in your spreadsheet, and change the font colour to match the cell colour. In this example, if column A contains a zero, the #DIV/0! error value is displayed in column C.
3) Hide Follow-Up QuestionsIn this example, there is a short questionnaire, and some of the questions have a follow-up item. The follow-up question might appear, based on the first answer. At first, only the main questions are visible. The follow-up items are in white font with white fill. For example, "Do you have dependents?"
To set this up:
For step-by-step video and written instructions, see my Hidden Questions blog post. The sample file is in the download section, below. Highlight for Cell Content 📝The following examples show how to highlight cells based on thier content. 1 -- Colour Cells Based on 2 Conditions 2 -- Highlight Cells With Formulas 3 -- Highlight Items in a List 4 -- Cross Off Completed Items 1) Colour Cells Based on 2 ConditionsUse Excel conditional formatting to colour cells if 2 conditions are met. In this example, a country code is entered in cell B2. If the code "US" is entered, cells that contain "United States" are coloured red. Enter the ConditionsYou could enter the conditions in the conditional formatting formula, but if you enter them in worksheet cells it's easier to see the conditions, and change them, if necessary. In this example, the conditions are on the same sheet as the data entry cells, but you can store them on a different sheet. You could also name the cells, and use those names in the conditional formatting formula To set up the conditions:
Add the Country Code CellNext, set up the cell where a country code can be entered:
Add Conditional FormattingNext, add conditional formatting to country cells in the data range. The formula is explained below.
If US is entered in cell B2, and a cell in D5:D14 contains "United States", it is coloured red. How It WorksThe conditional formatting formula is: =AND($B$2=$E$2,D5=$F$2) The AND function checks the 2 conditions:
Some notes about the cell references in the formula:
2) Highlight Cells With FormulasUse Excel conditional formatting to highlight cells that contain a formula. In this example, there are values in cells A2:B8, and totals in cells C2:C8 and in A9:C9
3) Highlight Items From Criteria ListUse Excel conditional formatting to highlight cells that contain values from a different list on the worksheet, such as a criteria list with valid two-digit codes.. In this screen shot, a list in column C has 3 code: AA, BB and CC. In column A, cells with those codes are changed to green color, thanks to a conditional formatting rule.
4) Cross Off Completed ItemsIf you have a list of the tasks that you have to work on, use conditional formatting to cross off completed items. In this example, completed tasks are marked with an X in the "Done" column. There is a conditional formatting on the list, to cross off completed items, and change the font to light grey. That makes it easier to focus on the tasks that still need to be finished. To set up this conditional formatting, follow these steps:
After you set up the conditional formatting rule, the item will be crossed off, if you type anything in the "Done" column. This example is on the Strikethrough sheet in sample file #1. Shade Rows ⬒The examples below show how you can use Excel conditional formatting to shade alternating rows on the worksheet, or groups of rows. 1 -- Shade Every Other Row 2 -- Shade Bands of Rows 3 -- Shade Bands by Group 4 -- Shade Alternating Filtered Rows Shade Every Other RowIn the screen show below, the entire row is shaded green, for odd-numbered rows. This alternate shading can make it easier to read across a wide row of data.
Shade Bands of RowsYou can use Excel conditional formatting to shade bands of rows on the worksheet. In this example, 3 rows are shaded light grey, and 3 are left with no shading. In the MOD function, the total number of rows in the set of banded rows (6) is entered.
Shade Bands by GroupYou can use Excel conditional formatting to shade bands by group. In this example, the sales rows for the dates are in alternating colours - blue and no fill. This technique was adapted from Chip Pearson's site. Tip: Another way to separate the groups is with a top border above the first date in each group. First, to prepare the table for shade bands by group, follow these steps:
Next, follow these steps to add the conditional formatting:
Shade Alternating Filtered RowsYou can use Excel conditional formatting to shade alternating rows in a filtered list.
Show Icons, Shapes or ColoursThe following examples show how to create coloured shapes, or your own icon set, or show a specific colour in a cell. 1 -- Create Coloured Shapes 2 -- Create Coloured Icons 3 -- Show Selected Colour 1) Create Coloured Shapes ●You can use Excel conditional formatting and the Wingding font to create coloured shapes in a cell. In this example, coloured shapes will appear in cells C3:C7, depending on the value in the adjacent cell in column B. If the value is less than 10, a red circle will appear, if the value is greater than 30, a green square will appear. Otherwise, a yellow diamond will appear.
2) Create Coloured IconsIn Excel 2007 and later, you can use icon sets to highlight the results in a group of cells. In Excel 2010 and later, you can customize these sets, but can't change the color of the icons.
Tip: Another option is to use Conditional Formatting Data Bars, that are like miniature charts in a range of cells. First, set up the lookup table in cells G3:I5
Next, create the icons in column C:
3) Show Selected Colour in Next CellSelect a colour name from a drop down list, and the next cell fills with the selected colour.
Watch this video to see the steps for creating this worksheet, and the written instructions are on the Show List and Colors page. Rules Manager Tips ✅After you set up Conditional Formatting rules in Microsoft Excel, you might want to review the rules, or do some troubleshooting. To see the Conditional Formatting rules in the active worksheet, follow these steps:
Each rule is listed, and shows its formula, format, range it applies to, and check box for "Stop if True". Quickly Check the FormulasOnly a small part of each formula is visible, and you can't show more. Unfortunately,
However, you don't have to click the Edit Rule button, to see a full formula.
After viewing the formula, if you need to edit it, click the Edit Rule button, and make your changes. Extra Conditional Formatting RulesWhen you check the Conditional Formatting Rules Manager, you might see a problem with new rules that have been created automatically. There might be a few rules duplicated, or you might see hundreds of extra rules! You can see how to clean up those extra rules, with manual steps, or with a macro, on the Fix Conditional Formatting Extra Rules page. Download Sample Files ⬇️1 -- Conditional Formatting Examples: Download the sample Excel Conditional Formatting file , with most of the examples from this page. The zipped Excel file is in xlsx format, and does not contain any macros. 2 --Hidden Data Warning: This sample file shows warnings, if rows or columns are hidden. Formula checks for hidden rows, and conditional formatting marks hidden column. Zipped file does not contain any macros. 3 -- Conditional Formatting for Weather Data: This sample file uses color scale for temperatures, and 4 formula rules for weather conditions - Sun, Cloud, Rain and Snow. The zipped file is in xlsx format, and does not contain macros 4 -- Hidden Questions: Main questions are visible, and conditional formatting hides the follow-up questions. The zipped file is in xlsx format, and does not contain macros More TutorialsConditional Formatting Intro Conditional Format Overview Fix Conditional Formatting Extra Rules Conditional Formatting Multiple Cells Conditional Formatting - Currency Conditional Formatting Documentation Conditional Formatting Data Bars How do you use conditional formatting in Excel?Apply conditional formatting based on text in a cell. Select the cells you want to apply conditional formatting to. Click the first cell in the range, and then drag to the last cell.. Click HOME > Conditional Formatting > Highlight Cells Rules > Text that Contains. ... . Select the color format for the text, and click OK.. What is conditional formatting and how is it used?Conditional formatting is a feature of many spreadsheet applications that allows you to apply special formatting to cells that meet certain criteria. It is most often used to highlight, emphasize, or differentiate among data and information stored in a spreadsheet.
How do you highlight cells based on a condition?On the Home tab, click Conditional Formatting, point to Highlight Cells Rules, and then click Text that Contains. In the box next to containing, type the text that you want to highlight, and then click OK.
|
