Looking at a wad of numbers or a litany of texts, you cannot immediately interpret the relation between the values. Lowest prices, highest sales, changing trends, a specific text string, too much information.
Data can be filtered or sorted but how can you sort everything if you want one column showing descending values and the other showing ascending values? Here's an idea, keep everything where it is and just spotlight the items of interest with some highlighting.
Our quickest understanding of data is very graphic and the best overview will also come through a play of colors and graphics. We’re starting to sound more and more like we’re hinting at Conditional Formatting and yes we are! Don’t know what Conditional Formatting is? Scroll ahead!
What is Conditional Formatting
Conditional Formatting is a feature in Excel that changes the display of cells whose values meet the Conditional Formatting rule’s criterion. Conditional Formatting makes it very easy to make note of important data whether numerical or non-numerical.
This feature uses graphic means such as highlighting, icons, and data bars to visually cast attention on the required group of values. By doing that, Conditional Formatting makes reviewing and visual analysis of data a very quick task without rearranging the data.
How Does Conditional Formatting Work?
Conditional Formatting applies to cells on the worksheet. At its very basic, you need to select the cells and then apply one of the Conditional Formatting rules. You can choose from the preset options or create your own rule (we will tell you how to do both).
The basic concept is that if a cell meets the Conditional Formatting condition, it results in TRUE and will be reformatted. If it results in FALSE, it won’t be reformatted.
The ease of Conditional Formatting is not only due to the visual impact, but also the automatic selection of the target data. Let’s say e.g., you want the sales figures over $1,000 highlighted below:
If this was a manual task, you would select the cells and then use Fill Color to flag the cells. With Conditional Formatting, you do not have to worry about selecting the right cells.
If you select all the data cells from column C, Conditional Formatting will do the job of flagging the right cells according to the set rule. Therefore, select all the cells with the sales figures:
Now where is this Conditional Formatting feature we’ve been talking about? In the Home tab, you will find the Conditional Formatting icon in the Styles group.
Conditional Formatting has highlighted the cells from the dataset that had sales of over $1,000:
Want to see how that’s done? Highlighting is not the only thing this feature does so you won’t get this formatting by just a click on the icon. Conditional Formatting has a bunch of options in its menu:
We’ll talk about creating different types of rules to conditionally format cells ahead in this tutorial.
Notes:
Conditional Formatting deals with numbers, dates, time, and text strings and while some rules won't work for text, they all work for numbers, dates, and time (excluding the Text that Contains rule).
Conditional Formatting is dynamic. This means that it will adjust its result according to changes within the data. E.g. if a value that was previously not highlighted with Conditional Formatting is changed and meets the criterion of the applied rule, it will then be highlighted.
If you intend to make additional entries to the data and want Conditional Formatting to accommodate them, you can try leaving the references in the formula free if it doesn’t hinder the results of the Conditional Formatting rule(s).
E.g. enter B3 instead of $B$3. Another option is to force Conditional Formatting to include more rows/columns by using the Format Painter parallelly to the new row/column and then adding the data. Otherwise, you can simply extend the range in the Rules Manager.
Any Conditional Formatting action can be undone with Undo or Ctrl + Z including adding a new rule, deleting a rule, applying a rule, and editing a rule.
Cell Formatting You Can/Cannot Change
The format of a cell comprises several elements e.g., font size, font color, color fill, border, row height, etc. and there is only so much that Conditional Formatting can do. Some elements of the cell format can be changed by Conditional Formatting while others can’t. To keep your expectations real, let’s classify the elements of cell formats into what Conditional Formatting can and cannot change.
The Format Cells dialog box is what you will get when you click on the (Custom) Format button while dealing with a formatting rule. We can open Format Cells through Conditional Formatting and see what the feature will allow. Good idea. Let’s go through the Format Cells dialog box tab by tab, beginning with the Number tab:
All Number formats are applicable through Conditional Formatting including Custom Number Format.
The next tab is the Font tab:
In the Font tab, the elements of the font in the cell that we will be able to change with Conditional Formatting are font style (bold, italic, etc.), color, underline, and effects (strikethrough, superscript, etc.).
The rest of the elements that are grayed out are not accessible and cannot be changed using the Conditional Formatting feature. These are font family and size.
Moving on to the Border tab:
Border styles are changeable from the available options. The Border color can also be altered.
If you’re familiar with Borders in Excel, you’ll know that the Border styles in Conditional Formatting are limited. To be more specific, the double border and thicker borders are missing. So, the deduction is that border thickness cannot be changed with Conditional Formatting.
Let’s see what we can do fill-wise in the Fill tab:
All the Fill options can be edited in Conditional Formatting.
All in all, other than some font and border elements, all others can be changed through Conditional Formatting. Comparing this window to the regular Format Cells dialog box, we notice we have a couple of tabs missing; the Alignment and Protection tabs.
Conditional Formatting does not give access to these tabs which implies that the feature cannot change the text alignment or the protection of a cell via the feature.
Note:
Conditional Formatting is only a formatting feature and does not affect the value of a cell.
Conditional Formatting Options
Let’s refer back to the Conditional Formatting menu:
We’re going to tackle each item in this menu so…
Let’s get formatting! Conditionally though…
Highlight Cells Rules
One of the most commonly used features of Conditional Formatting is Highlight Cells. Selected cells will be highlighted according to a chosen cell highlighting rule. There are a few interesting preset rules available in this category e.g., highlighting a particular value, a cell that contains the given text, etc. Let’s go over all the options so you know how to apply them to your Excel work.
Greater Than…
The Greater Than rule highlights numerical values greater than the user-provided value. This option can be used to cast focus on high-performing products, the highest tier in an age group, amounts over a borderline value, etc. If that sounds like what you’re targeting, you can use the steps below to highlight cells with numerical values greater than the supplied value.
As an example, we’re taking some sales data categorized by product and state. For now, we want to find out which product is giving us the highest sales and from which region. Here’s how we do it:
- Select the relevant cells with the numeric values from where you want the highlighted cells.
- Select the Home tab’s Conditional Formatting icon in the Styles Point to Highlight Cells Rules and select the Greater Than rule.
- This rule will lead to a dialog box where you can set the numerical value and format intended for the highlighted cell. Gauging the selected cells, Excel will already provide you with an example value in the dialog box.
- In the Greater Than dialog box, adjust the numerical value in the text box. *(Read note below). The numbers that are greater than this number will be highlighted in the dataset. E.g. in our case, we want sales greater than $600 to be highlighted.
- You can also choose another format for the highlighted cells from the drop-down list in the dialog box.
- When done setting the number and format, click on the OK button of the dialog box.
The dialog box will close and the cells with sales greater than $600 will be highlighted in the chosen format:
Notes:
* The number $600 can also be added as a cell reference by clicking on the applicable cell with the blinking cursor in the text box of the dialog box. This would be the easier way if the value you want to set is a decimal.
By default, the highlight format for the cells will be light red fill with dark red text. This can quickly be changed to another ready format from the drop-down menu in the dialog box which also carries an option of a custom format. You’ll see how to set a custom highlight format in a while.
Less Than…
Let’s squeeze in the opposite of what we did just now in the same example. With the same concept, Conditional Formatting also gives the option of highlighting cells with values less than a provided number.
After applying this formatting rule in our case example, we’ll be able to see the top and flop products together on the same sheet, distinguished by a different format. See the steps and find out what that would look like:
- Select the data cells and go to the Conditional Formatting icon in the Home From the menu, head to Highlight Cells Rules and now select the Less Than option.
- Now you will have a Less Than dialog box, much like the one we saw earlier.
- Enter the number in the text field. Conditional Formatting will use this number to determine which cells should be highlighted. For our case, we are going with $150 so that values under 150 will be flagged.
- Change the format if you wish. We are aiming to highlight the bottom values so the default red format is good with us.
- Then hit the OK
From the selected cells, the products with sales lower than $150 are highlighted in the default red format:
Since we’ve used the Less Than rule with the Greater Than rule, we get a quick view of the hot and not products. Notice how we're halfway to making a heat map?
Now that we mention it, Conditional Formatting is already ahead of us and allows us to create heat maps in Excel. How? You’ll see later. Hint: Color Scales.
Between…
The Between option highlights numerical values between 2 given numbers. This can be particularly helpful e.g., color coding an age group of interest. We’ll take a simple example and show you how it works.
In our simple example case, 10 people take part in a 5-question quiz to win a prize. A point is awarded for each question so we’d like to see how many participants scored averagely, say between 2 and 4. This is how to use Conditional Formatting to highlight values between 2 numbers inclusive:
- Select the number cells and open the Conditional Formatting menu from the Home Select Highlight Cells Rules > Between.
- A dialog box will appear for you to set the range of values and the highlighting format.
- Enter the numbers between which you want values highlighted in the dataset.
- This range works inclusively and the top and bottom values will also be highlighted.
- Choose a format for the highlighted cells and then click on the OK
We have set the values between 2 and 4 to be highlighted with a yellow fill:
Equal To…
But more importantly, wouldn’t you want to find out the winners? Of course you would. That would require highlighting the cells with a score of 5. Conditional Formatting makes highlighting cells carrying a specific value a task done in seconds.
Using the Equal To option in Highlight Cells Rules, we have mentioned the steps to highlight cells of a single value:
- Select the cells with the numbers.
- Go to Home tab > Conditional Formatting icon > Highlight Cells Rules > Equal To.
- In the text box in the Equal To dialog box, enter the number you want highlighted in the dataset and select the format for those highlighted cells.
- When you press the OK button, the entered number will be highlighted in the set format:
Text that Contains…
By now it looks like Highlight Cells Rules only likes working with numbers but that’s not the case. Have you ever had difficulty finding a part of text from a text string because it was not categorized appropriately?
We’ve been there too. If the target is to locate those cells or split the text, you can use Ctrl + F and other options to divide the text but if you want the text string found and the cell containing the text highlighted, nothing does it better than Conditional Formatting.
Follow the steps relating to the example ahead to find and highlight cells that contain the provided text with Conditional Formatting:
In this example, we have a list of fragrances with their brand names both mentioned together in column B. If we want to see which perfumes are made by a particular brand, taking e.g. Chanel, we will start by:
- Select the cells containing the data that you want to check.
- In the Conditional Formatting menu (Home tab > Style group), select Highlight Cells Rules and then the Text that Contains
- In the text field add the string of text you want to find in the data cells.
- To choose your format for the highlights, click on Custom Format from the drop-down menu.
- We’ve set a custom format with an orange color fill.
- Click OK on both windows to close them and apply Conditional Formatting to the cells in the custom format.
As per the set format and text value entered in the dialog box, the cells containing that text value will be highlighted:
A Date Occurring…
Another type of number value Conditional Formatting runs with is dates. With the A Date Occurring option, dates that range from the previous month to the next month can be highlighted.
For specific dates, you’ll have to resort to other Conditional Formatting rules since this rule does the job of highlighting dates relative to the current date e.g. dates from last week, yesterday, next month, etc.
Would be easier with an example? We think so too. Our example shows the order dates and scheduled delivery dates of a range of products. Take the current date as 25th of July. We want to highlight the dates from the list that are scheduled to be delivered the next day i.e. on the 26th of July.
With the following steps, you can use the A Date Occurring rule to highlight the date cells relative to the date option selected.
- Select the cells with the dates.
- Our target is to highlight the delivery date cells so we’ll select the date cells from column D.
- From the Home tab, select the Conditional Formatting button, Highlight Cells Rules, A Date Occurring.
- In the dialog box, open the first drop-down menu and select the required option.
- In our example, we want the dates for the next day highlighted and hence, we will go with the ‘Tomorrow’ option.
- Use the next drop-down menu to change the highlighted cells format.
The cells with the date 26th July 2022 in column D will be highlighted according to the selected format:
Note: The plus point of using A Date Occurring instead of forming a rule to highlight the 26th specifically is that this option will work relatively; with the changing date, the dates for the next day will automatically be highlighted e.g., on the 26th, 27th will be highlighted.
Duplicate Values…
When dealing with large data, there is a possibility of doubly-made entries and duplicate values. In most cases, you would want to avoid this and a good place to start would be to highlight them. Let’s see how to highlight repeat values with Conditional Formatting using an example.
In this example, we have used the RANDBETWEEN function to return 10 random numbers from 1 to 20. On its own, RANDBETWEEN doesn’t return unique numbers and there will be duplicates. Let’s check our data for duplicates with Conditional Formatting’s Duplicate Values option.
- Select the range you want to be searched for repetitions.
- From the Styles group in the Home tab, go to the Conditional Formatting’s Highlight Cells Rules menu and select Duplicate Values.
- In the dialog box, the default selection is “Duplicate” *(read note below) so you only need to change the default format if you want to.
- Select the OK command to close the dialog box and apply the Conditional Formatting for duplicate values:
* There is also an option to highlight unique values as shown in the example shot.
Top/Bottom Rules
We can now move on to the second item in the Conditional Formatting menu. The Top/Bottom Rules help the user format the top or bottom number values. In this set of rules, we have options for highlighting the top/bottom items, top/bottom x%, and the above/below average items.
Keep in mind that Top/Bottom Rules do not work on non-number values so text values, you’re out for now.
Also, we have a very interesting finding. The top/bottom percentage rule is a quick way to highlight the required percentile! If you’ve worked with percentiles before, you’ll find this particular feature rather handy.
Let’s start exploring the preset options in the Top/Bottom Rules one by one.
Top 10 Items…
Say you have a long list of expenses (don't we all) and want to point out the top 10 items in the list that are taking up the most bucks. The top 5 priciest products, the top 15 songs from a database with the most hits; this is what you can use the Top 10 Items option for.
Another instance where you can use the Top 10 Items rule is the example ahead. We have the test marks out of 50 in a class of 10 students. Since our data consists of 10 students, we obviously won’t be searching for the top “10”.
Gladly, we can change this number. Let’s aim to find the top 3 scores on this test with Conditional Formatting’s Top 10 Items rule:
- Select the cells from the data containing the numbers, dates, or time.
- Now open the Conditional Formatting menu and go to Top/Bottom Rules and then Top 10 Items.
- The Top 10 Items dialog box has the number 10 suggested by default.
- Change the number ‘10’ according to your requirement using the spin button or by editing the value in the text box.
- At this point, you can also change the highlight format through the drop-down menu.
- To apply the format, close the dialog box with the OK
The top 3 items in the list are highlighted. In our case example, one of the marks has a repeat value i.e. 39 which is why you see 4 highlighted cells below:
The next rule in line is Top 10 % but we’ll bump up Bottom 10 Items because doesn’t that make a better succession to Top 10 Items?
Bottom 10 Items…
The Bottom 10 Items rule highlights, well yes, the bottom items. It works opposite of the Top 10 Items rule. Why would we want to bring the bottom items to notice? This could be for detecting the lowest performance products, months of lowest sales in a report, lowest scores, etc.
Taking the example from the section above, we will now study the 3 lowest scores. Looking at the range of bottom scores will help us ascertain if it’s just the lowest score that’s pulling the percentile down or if more students have scored similarly.
Let’s go ahead with using Conditional Formatting’s Bottom 10 Items rule to highlight the bottom 3 items in our case example:
- In the dataset, select the range with the numbers.
- From the Top/Bottom Rules in Conditional Formatting, select the Bottom 10 Items
- Change the number in the dialog box in line with the number of bottom items you want highlighted.
- Set the format of choice for the highlighted cells.
- When done, use the OK button to close the dialog box and highlight the relevant cells.
Here you can see that the bottom 3 items are highlighted in the chosen format:
The bottom 3 marks are 31, 33, and 34 showing that the lowest score is not the only reason for the lower percentiles since two other students out of 10 have scored likewise.
Top 10 %…
This is a very interesting rule. The Top 10 % rule works out the percentage from the numbers in the selected cells and highlights the cells with the numbers that fall in the top 10% (percentage can be adjusted).
With Excel putting in so much effort, isn’t life that much easier? Let’s find out if it is so! To highlight the top x% with Conditional Formatting use these steps:
- Begin with selecting the data cells with the numbers.
- Go to Home tab > Styles group > Conditional Formatting button > Top/Bottom Rules > Top 10 %.
- In the text field of the dialog box, you can change the percentage number which by default is set at 10.
- We’ll leave it at 10 since we are also aiming to calculate the top 10%. To calculate e.g. the bottom 25%, change this number to 25 in the dialog box.
- Use the drop-down menu in the dialog box to change the format of cells that are to be highlighted.
- Click on the OK button when done.
The marks falling in the top 10% of the selected cells will be highlighted:
Bottom 10 %…
After the Top 10 % follows the Bottom 10 %. The antithesis of what we just saw earlier, the Bottom 10 % rule takes the numbers in the selected cells to highlight the values that fall in the lower 10% of the numbers.
Carrying forward the previous example, let’s test how this rule works to highlight the bottom 10% with the top 10% already highlighted.
- Select the cells from the dataset and go to the Conditional Formatting options to select the Bottom 10 % rule in the Top/Bottom Rules.
- Set the format for highlighting and the number for the percentage in the Bottom 10% dialog box.
- When the OK command is pressed, the bottom 10% of the values will be highlighted.
Percentage or Percentile?
Let’s analyze what is happening here now that we’ve got the top and bottom 10% highlighted. And for discussion’s sake, we’re only talking about 10% here (though this can be any other percentage). If you think about regular percentage, 10% of 50 marks should be 5 marks so the top 10% (i.e. 90%) should be 45 marks.
In the Top 10 % rule, Conditional Formatting determines the top 10% only from the numbers selected. That means, this rule is unconcerned with the actual percentage because it is taking the lowest and highest values in the selection as 0% and 100%. Does that sound familiar? That is also the concept of percentile.
This finding is in line with percentile computations. If you look at the example shot above, we have calculated the 10th and 90th percentiles as 31.2 and 37.5 with the PERCENTILE.INC function. Compare this to the cells highlighted by Conditional Formatting.
For the top 10%, we have 31 marks highlighted (falls under 31.2 marks) and for the bottom 10%, 48 marks have been highlighted (going over 47.5 marks).
On this deduction, we can conclude that the Top and Bottom 10 % rules work as percentiles and can be a quick visual percentile map! Fascinating!
Above Average…
This section will further confirm the percentile theory. The Above Average rule in the Top/Bottom Rules highlights all the number values in the selection that are above the average of those numbers i.e. above 50% or the median.
Our previous example case has the median calculated using the PERCENTILE.INC function. Therefore, we’ll show you the steps to highlight the cells that are above the average of those numbers using Conditional Formatting’s Above Average rule.
- After selecting the cells with the numbers, click on the Conditional Formatting icon to access the Above Average option from the Top/Bottom Rules.
- You will be led to the Above Average dialog box:
- There is only one setting in this dialog box and i.e. the format of the highlighted cells.
- This rule only focuses on the average or 50% since the percentages can be adjusted in the Top and Bottom 10% rules.
- Hit OK when done and find one half of the values above average highlighted in the set format:
Below Average…
And for the other half of the average, this rule highlights the values from the cell selection that are below the average of those numbers.
Let’s piece this together with the case example from above and highlight the values below the average with Conditional Formatting’s Below Average rule with the steps mentioned ahead:
- Select the data with the numbers from where you want to calculate the average.
- Apply the Below Average rule by selecting Home tab, Conditional Formatting icon, Top/Bottom Rules, Below Average
- Set the preferred format for the highlighted values in the dialog box and click on the OK
The values below average in the selected cells will be highlighted in the set format:
Calculated as per the PERCENTILE.INC function, 50% of these numbers are above 37.5 marks, and 50% fall below. Evaluating the results of the highlights, we can see that Conditional Formatting tallies with the percentile system.
Data Bars
While numbers are crucial for calculations and analysis, the best type of data is one that is easily perceptible with distinguished high and low points. These two Conditional Formatting features (namely Data Bars and Color Scales) fulfill the convenience of giving the data a visual representation.
First comes Data Bars. Data Bars won’t be a very alien concept if you’ve seen bar charts. It’s like a personal bar for each cell.
Data Bars underlays a single bar in the selected cell. For a selected range, a bar will be added to each cell in line with and under the cell’s number value. The value of the cell determines and is directly proportional to the length of its Data Bar; small number – small bar.
In case you would ever want to add Data Bars to your dataset, we suggest these steps using Conditional Formatting in a simple example:
- Select the cells you want to add the Data Bars
- In the Home tab, head to the Styles group to access the Conditional Formatting Hover the cursor on Data Bars to reveal the menu. Choose the Data Bar style from the menu from Gradient Fill or Solid Fill. The color can also be chosen or you can create your own style from More Rules.
The Data Bars will be entered in the chosen style:
If you want to go for customized Data Bars, click on More Rules in the Data Bars menu.
In the Type fields, you have various options to change the type of Data Bar e.g., the one we just showed you was simply according to the numerical value. You can change this to percentile, percentage, etc. instead of numerical value.
These are fairly detailed options including changes for the bar direction (left to right, right to left) and Negative Value and Axis settings for when the data includes negative numbers.
Note: Data Bars and Icon Sets will also not affect the cell value as Conditional Formatting is only a formatting feature.
Color Scales
Remember we spoke of heat maps earlier? Now is a good time to come back to that. Excel can speedily ready a heat map for your dataset with Color Scales in Conditional Formatting. Think of it as color coding for the numbers in your data e.g., on a scale of red to green, the smallest number is red and the largest number is green. If you’re the one who had every color highlighter for studies or work, you’ll love this!
The perk of a Color Scale is that it can tell the position of any number in the range by the shade of the cell’s color. We’ll put Color Scales into action now with a very black and white example of product and state-wise categorized sales data.
- Select the relevant range for adding the Color Scale.
- Select Conditional Formatting from the Ribbon in the Home Hover the mouse pointer on Color Scales to open up some preset options.
Select the favored Color Scale or you can use the More Rules option to form a custom 2 or 3-Color Scale where you can also specify the minimum, mid (if any), and maximum values for the color points.
When the Color Scale is selected, it will be immediately applied to the selected range.
See how that works to give an instantaneous overview of the values? Maybe some of your highlighters have already made their way to the garbage.
Icon Sets
This is another very cool feature called Icon Sets that shows in what range a cell’s value lies according to its icon added by Conditional Formatting. Icon Sets applies a set of icons to the cells in selection. The Icon Sets represent the lowest, highest, and multiple mid (can be excluded) values that are editable.
Let’s apply Icon Sets in an example of quality assurance scores for 10 products. See what to do below to use Icon Sets in Conditional Formatting.
- Select the number cells for applying Icon Sets.
- Go to the Home tab > Conditional Formatting icon and point to the Icon Sets
- If you want to apply Icon Sets with the default settings* click on the relevant Icon Set from the menu. To change the settings according to which the icons will be added, click on the More Rules option in the Icon Sets menu.
- You will see a New Formatting Rule dialog box:
- Now make the changes as per your preference. These are the changes made for our case example:
- Pick the style from the Icon Style drop-down menu.
- Change the Type from Percentage to Number so that the Icons are entered according to the number of the cell, not the percentage of the number against all the selected cells.
- Set the numbers in the Value fields as the range for each icon. E.g. for values 12 and higher, a green check mark will be returned. Values between 8 and 11 will be assigned a yellow exclamation point and for lower numbers, there will be a red cross mark.
- If you want the numbers overwritten, click the Show Icon Only checkbox.
- Click on the OK button after the changes are made.
The Icon Set has been entered with the values in the selected cells in accordance with the changed settings:
* When applied, Icon Sets will enter icons to the selected cells in their default settings. The icons are set at equal percentage intervals e.g., a 3-Icon Set is applied at 33 percent intervals, 4-Icon Set at 25 percent intervals and 5-Icon Set at 20 percent intervals.
Tip: If you want the values and icons in separate columns, paste the values in another column, apply the Icon Set and overwrite the values by tick-marking the Show Icon Only checkbox in the settings.
Change Conditional Formatting Rule
To change a rule in Conditional Formatting, you need access to the Conditional Formatting Rules Manager. The Rule Manager enlists all the Conditional Formatting rules and allows changing, deleting, creating, and stacking rules according to preference of application.
You would want to change a rule in the case that an incorrect rule has been formed or some element of the rule needs editing and the original rule is not required anymore e.g., instead of the top 10 items, we want to highlight the top 3 items and don’t need to keep the top 10 items highlighted.
The original rule will not be overwritten if a new rule is formed.
See how to change a Conditional Formatting Rule using the steps written below:
- Select the range to which the rule applies.
- If you’re unclear about the range, you can select just a single cell or make a rough estimate of the range and the rules applied to the selected cell/range will be shown in the Rules Manager. Otherwise, you can select the entire sheet from the active worksheet or even from the Rules Manager.
- To open the Rules Manager, open the Conditional Formatting options from the Home tab and select Manage Rules.
- The Conditional Formatting Rules Manager lists the created rules for the cells selected on the worksheet. You can change this in the Show formatting rules for drop-down menu at the top in the Rules Manager.
- Select the rule you want to edit and click on the Edit Rule Alternatively, you can double-click a rule to edit it.
- Use the Edit Formatting Rule window to change the rule description and/or rule type.
- When you press the OK command, the edited rule will show in the Rules Manager.
- Press the OK button to apply the edited Conditional Formatting Rule.
As a result of the rule being changed from top 3 to top 2, only the top 2 numbers are highlighted in the example case above.
Delete Conditional Formatting Rule
Conditional Formatting Rules are deleted from the Conditional Formatting Rule Manager.
To completely remove a rule from the worksheet, you will have to delete the rule. It may also be easier to create a new rule instead of editing an old one in which case, you will have to delete the old rule. Deleting a rule is reversible by undoing the action so if you accidentally lose a rule, you can get it back.
Check these steps on how to delete a Conditional Formatting Rule:
- Select a cell in the range of the rule that you want to delete.
- Go to Home tab > Style group > Conditional Formatting > Manage Rules.
- This option opens the Conditional Formatting Rule Manager.
- Click on the rule and hit the Delete Rule
- The rule will be deleted without any confirmation. It's not really a problem, if you delete the wrong rule, hit the Cancel command. If you’ve already pressed the OK button, you can hit Undo; the rule will be back.
- After deleting the rule, select the OK
The rule will be deleted and the formatting relevant to that rule will be cleared:
Deleting all Rules from Selection
One last thing left in the Conditional Formatting menu. While it’s easy to delete whichever rule from the Rules Manager, it can be tiresome carefully looking at absolutely referred ranges to see which rule applies where especially if you aim to delete the rules only from a specific range without deleting others.
To clear rules from a specific range, select the cells and go to Conditional Formatting icon > Clear Rules > Clear Rules from Selected Cells.
If you want to delete all the rules from the worksheet, you can go for Clear Rules from Entire Sheet instead of deleting the rules one by one from the Rules Manager. The other two options become available if there is an Excel Table or PivotTable present on the worksheet.
New Rule
New rules will not overwrite rules, they will be stacked (newest first unless the order is changed in the Rules Manager if any results are overlapping.
While more favorably you can create a new rule from Conditional Formatting’s New Rule option, a few options are also available in the Quick Analysis tool. The icon appears in the bottom-right corner when you select more than one data cell:
Click on this icon to see the options.
This is the little cousin of Conditional Formatting with 5 rules and an option to clear formatting from the selected area. Conditional Formatting in the Quick Analysis tool will add new rules to the selected range; it won’t overwrite rules.
New Formatting Rule Dialog Box
This dialog box will come into play with 3 actions; creating a new rule, editing a rule, and when you select More Rules in the Conditional Formatting menu. Most of the items in Rule Type are pretty much an extension of the options in the Conditional Formatting menu and some of them you will see in the sections later. Let’s quickly go over the Rule Types and what they contain.
Format all cells based on their values
This rule type has detailed options regarding Color Scales, Data Bars, and Icon Sets.
Format only cells that contain
This one’s intriguing. This rule type has varied options for formatting cells based on a specified value (number and text), date, blanks, no blanks, errors, and error-free cells.
Format only top or bottom ranked values
Here we can select top or bottom items or percentages to format the cells accordingly.
Format only values that are above or below average
This type is for widening the Above and Below Average rule in Conditional Formatting. We can format the cells with the average inclusive or exclusive and can also see some standard deviation options (hello math and stats).
Format only unique or duplicate values
Simply includes duplicate and unique values formatting.
Use a formula to determine which cells to format
If none of the preset rules and rule types suit your Excel action, you can get the needful done by entering a formula here.
Common Use Cases of Conditional Formatting
Now that we’ve covered everything contained in the Conditional Formatting menu, we can move on to scenarios that would commonly use Conditional Formatting. Some have already been discussed above. Let’s see what else Conditional Formatting can do.
Use Conditional Formatting to Highlight Alternate Rows
Highlighting alternate rows is nothing big for Conditional Formatting. And while this can also be done using a preset format in Excel Tables, you might not want to convert your range to a Table.
There are quite a few formulas that can be used with Conditional Formatting to highlight every other row. We’ll go with the one used in our example case below because it’s easy to tweak for your range and requirement. Let’s see the formula and how to apply it using Conditional Formatting to highlight alternate rows:
- On the worksheet, select the range where you want the alternately highlighted rows.
- Click on the Conditional formatting button in the Home tab and choose New Rule.
- There are 3 steps you need to take in the New Formatting Rule dialog box.
- In the Select a Rule Type section, choose Use a formula to determine which cells to format.
- In the given text box, enter this formula to highlight every other row:
=INT(MOD(ROW(),2))Explanation below*
- Hit the Format button to set the format for the highlighted rows.
- One more step, select the OK
- That’s alternate rows highlighted for you with Conditional Formatting:
- The ROW function will take the number of the row the formula is entered in. Starting from the first row, in our example case, the number will be 3. The MOD function will return the remainder after 3 is divided by the supplied number 2. We are left with the number 1. The INT function is there to round the remainder down to an integer. Left with 1, the formula highlights the first row.
This formula can highlight even rows for you. So you can use this formula instead:
=INT(MOD(ROW()+1,2))With the added 1, the formula will highlight the rows starting from the second row (the remainder 1 added to the extra 1 in this formula equals 2) in the range:
Use Conditional Formatting to Find & Highlight Blank Cell Rows
There are two aspects to highlighting blank cells in Excel. The first is only highlighting the blank cells. The second is highlighting the entire row or column in the dataset containing the blank cell.
Highlighting blank cells with Conditional Formatting doesn’t require any formula. There’s an in-built setting to highlight blank cells in the New Formatting Rule dialog box.
To enable it, select the area containing the blank cells and go to Conditional Formatting menu > New Rule. In the dialog box, select Format only cells that contain as the rule type and Blanks in the drop-down menu. Set the format.
This setting will highlight blank cells in the selected range. We had selected D6:D15.
To highlight the full row (let’s say from B7 to D7 when D7 is found blank), you’ll have to include a formula in the works so we can control highlighting the full row of the table. Here’s what you need to do to highlight the full row containing the blank cells with Conditional Formatting:
- Select the range in the dataset containing the blank cells. You need to select the area that you would want to be highlighted. In our case, the blank cells are among D6:D15 but we want the full row highlighted so we have to select the B6:D15.
- Create a new rule by selecting New Rule in the Conditional Formatting
- A New Formatting Rule dialog box will open. Make these changes in the dialog box:
- Click on the last option in Select a Rule Type field.
- Set the desired format for highlighting the rows.
- Enter this formula in the text box:
=COUNTBLANK($D6)The reason we are using the COUNTBLANK function instead of ISBLANK is that our cells appear blank but contain a formula returning an empty text string. These will not be counted as blank by ISBLANK but COUNTBLANK will do the job for us.
Also note that the formula contains $D6 as the reference. This is the trick that highlights the full row in the selection. If the reference was D$6, the full column will be highlighted. For $D$6 as the reference, the mix of the two will be highlighted. Since we only want to highlight the full row of the selection, we’ve kept the row free with $D6.
- Hit the OK button when done.
Conditional Formatting will highlight, from the cell selection, the entire row with the empty cells:
Use Conditional Formatting to Highlight Overdue Dates
If you have a date-wise schedule for tasks and want to view the overdue tasks, Conditional Formatting makes that possible by highlighting the overdue date cells.
This can be done in 3 ways. Using the Less Than Highlighting Rule and entering the date. The dates before the given date will be returned.
But it won’t be possible to use the TODAY function in this method and neither will you be able to highlight the full row. The second method for conditionally formatting overdue dates involves the steps as follows:
- Select the date cells and create a new rule from the Conditional Formatting options from the Ribbon.
- Choose the second rule type in the dialog box.
- Select Cell Value and less than in the first and second fields of the Rule Description.
- Enter the formula mentioned ahead in the text box:
=TODAY()- Set your preferred format for highlighted overdue dates.
- Press the OK command to create the new rule and highlight the overdue dates in the designated format:
The third way is to use the formula rule to add a complete formula that can be edited to highlight complete rows or columns instead of cells. No different from how we highlighted blank rows in the previous section.
Use Conditional Formatting to Compare Two Columns
When comparing columns for the the same or unique items, Conditional Formatting doesn’t leave much to work on our part as it can highlight duplicates or unique items in two (or more) lists. This will quickly give you an idea of common or even mismatched items especially if you are comparing columns linearly (line by line).
Earlier we showed you how to highlight duplicate numbers in a single column. Now let’s see how Conditional Formatting works to compare words in two columns. We’ll also switch it up to find unique values instead. Follow the steps below to compare two columns by highlighting unique values using Conditional Formatting:
- Select the data from the two columns that you want to compare.
- Select the Conditional Formatting icon > Highlight Cells Rules > Duplicate Values.
- Select Unique from the first drop-down menu in the Duplicate Values dialog box.
- Assign a format for the highlighted cells.
- Next, click on the OK
Return to the worksheet to find the unique values highlighted:
Use Conditional Formatting to Create Search and Format Box
This has to be the best of Excel tricks with Conditional Formatting and we’ve saved it for now! If whatever you enter in a particular cell will be searched on the sheet and highlighted, wouldn’t that be so cool to learn? We’re in the affirmative. There are a couple of ways to do this. One is to directly assign a formula to the data range. The steps to do this are right here:
- Assign a free cell on the worksheet as the search box.
- You can also label it. Highlight it in the same color that the searched and highlighted cells from the data would be. *(refer below)
- Select the range where the search will be targeted.
- For our case, we’ve selected B3 to B12.
- Make a new rule by selecting New Rule in the Conditional Formatting options in the Home
- In the dialog box, select Use a formula to determine which cells to format as the Rule Type.
- In the provided field, enter this formula:
=SEARCH($F$2,B3)- This formula uses F2 as the search box and the target to be searched has been entered as B3 which will apply to the whole data range in column B. If you want to enter more columns as your search target, you can add the references using the & operator. E.g. if we were to include column C, we would refer B3&C3 or B3:C12 in the formula.
- Hit OK on the dialog box and you’re ready to go!
Now when you return to the worksheet, all your data range will be highlighted. Enter your search item in the search box and see the relevant cell(s) highlighted!
You can also try searching numbers:
The second method is to use a preset rule subject to highlight as per the search in F2. See how to do that with these steps:
- Select the data range for the search.
- Go to Conditional Formatting > Highlight Cells Rules > Text that Contains.
- In the given field, instead of entering any text, select the cell that you want to use as the search box.
You end up with the same search and format box!
*This will technically not have any impact on the search and format; it’s more like a reminder that the searched value will be highlighted this way (and let’s admit, when you see it working like that, it does look like Excel witchcraft).
Note: Search is not case-sensitive.
This is not where it ends. Likewise, you can enter the search box cell reference with other types of Conditional Formatting Rules too. And why stop at one? You can have multiple search boxes, each bound to a different rule e.g. two search boxes with upper and lower amounts where the upper one is used to highlight greater than quantities and the lower one for less than.
Use custom formats when you don’t want search boxes on the face of the sheet or when Conditional Formatting rules aren’t sufficient for your objective. Such custom formulas can be used for many tasks such as the ones we’ve seen in this tutorial. We’ll list a few examples with formulas that are to be used on the range containing the data:
Highlight Rows Meeting two Separate Conditions
To highlight e.g. those aged under 40 (column B) who took longer than 10 hours (column C) to complete a test:
=AND($B3<40,$C3>10)
Highlight Linear Differences
For highlighting linear differences between two lists:
=$B3<>$C3
Checked Box Strikes Text Through
For striking out tasks (with Strikethrough Font Effects) in a checklist when a checkbox is marked:
=IF(C3=TRUE,TRUE,FALSE)The prerequisites for this Conditional Formatting rule are that you insert the checkboxes using Form Controls in the Developer tab and link each box to the cell it is in by right-clicking the checkbox, selecting Format Control, and linking the checkbox.
Highlight Cell when Target is Achieved
To highlight a cell when a total is met:
=IF(COUNTA($D$3:$D$20)>=18,TRUE,FALSE)Enter this formula as a Conditional Formatting rule in the target cell F20. The trick is to enter the text “Daily target achieved!" in white font in F20 so that when the condition is met and the cell is highlighted in a dark color, the white text becomes visible.
Tip: If you find creating such formulas confusing or intimidating, you can apply the basic part of the formula in any free cell and apply Conditional Formatting in reference to that cell e.g. you apply the following formula in cell F19.
=COUNTA(D3:D25)This will count the occupied cells for you (22 in this case). And then use this formula =$F$19>=18 in Conditional Formatting with F20 selected. Since 22 is greater than 18, F20 will be highlighted by Conditional Formatting which means that you will still achieve the same results.
We’re certain we’ve covered all the major and minor elements regarding Conditional Formatting in Excel as well as where and how it can be applied to achieve various results. Once you get around to using this feature, you’ll find it indispensable to quickly spot trends and anomalies and analyze data without changing its position.
Alright, we’re pretty sure you know that by now so about you try giving your worksheets a Conditional Formatting spin?
About Mehwish Javaid