assignment on conditional formatting in excel

Using Conditional Formatting in Excel (The Ultimate Guide + Examples)

Conditional Formatting is one of the most simple yet powerful features in Excel Spreadsheets.

As the name suggests, you can use conditional formatting in Excel when you want to highlight cells that meet a specified condition.

It gives you the ability to quickly add a visual analysis layer over your data set. You can create heat maps , show increasing/decreasing icons, Harvey bubbles , and a lot more using conditional formatting in Excel.

This Tutorial Covers:

Using Conditional Formatting in Excel (Examples)

In this tutorial, I’ll show you seven amazing examples of using conditional formatting in Excel:

1. Quickly Identify Duplicates

Conditional formatting in Excel can be used to identify duplicates in a dataset.

Here is how you can do this:

This would instantly highlight all the cells that have a duplicate in the selected data set. Your dataset can be in a single column, multiple columns, or in a non-contiguous range of cells.

2. Highlight Cells with Value Greater/Less than a Number

You can use conditional formatting in Excel to quickly highlight cells that contain values greater/less than a specified value. For example, highlighting all cells with sales value less than 100 million, or highlighting cells with marks less than the passing threshold.

Here are the steps to do this:

• Select the entire dataset.

The same process can be followed to highlight cells with a value less than a specified values.

3. Highlighting Top/Bottom 10 (or 10%)

Conditional formatting in Excel can quickly identify top 10 items or top 10% from a data set. This could be helpful in situations where you want to quickly see the top candidates by scores or top deal values in the sales data.

Similarly, you can also quickly identify the bottom 10 items or bottom 10% in a dataset.

This would instantly highlight the top 10 items in the selected dataset. Note that this works only for cells that have a numeric value in it.

Also, if you have less than 10 cells in the dataset, and you select the options to highlight Top 10 items/Bottom 10 Items, then all the cells would get highlighted.

Here are some examples of how the conditional formatting would work:

4. Highlighting Errors/Blanks

If you work with a lot of numerical data and calculations in Excel, you’d know the importance of identifying and treating cells that have errors or are blank. If these cells are used in further calculations, it could lead to erroneous results.

Conditional Formatting in Excel can help you quickly identify and highlight cells that have errors or are blank.

Suppose we have a dataset as shown below:

This data set has a blank cell (A4) and errors (A5 and A6).

Here are steps to highlight the cells that are empty or have errors in it:

• Select the dataset in which you want to highlight blank cells and cells with errors.

This would instantly highlight all the cells that are either blank or have errors in it.

5. Creating Heat Maps

A heat map is a visual representation of data where the color represents the value in a cell. For example, you can create a heat map where a cell with the highest value is colored green and there is a shift towards red color as the value decreases. 

Something as shown below:

The above data set has values between 1 and 100. Cells are highlighted based on the value in it. 100 gets the green color, 1 gets the red color.

Here are the steps to create heat maps using conditional formatting in Excel.

• Select the data set.

As soon as you click on the heatmap icon, it would apply the formatting to the dataset. There are multiple color gradients that you can choose from. If you are not satisfied with the existing color options, you can select more rules and specify the color that you want.

Note: In a similar way, you can also apply Data Bard and Icon sets.

6. Highlight Every Other Row/Column

You may want to highlight alternate rows to increase the readability of the data.

These are called the zebra lines and could be especially helpful if you are printing the data.

Now there are two ways to create these zebra lines. The fastest way is to convert your tabular data into an Excel Table. It automatically applied a color to alternate rows. You can read more about it here .

Another way is using conditional formatting.

Suppose you have a dataset as shown below:

Here are the steps to highlight alternate rows using conditional formatting in Excel.

• Select the dataset. In the example above, select A2:C13 (which excludes the header). If you want to include the header as well, then select the entire data set.

• Enter the following formula in the field in the ‘Edit the Rule Description’ section: =ISODD(ROW())

That’s it! The alternate rows in the data set will get highlighted.

You can use the same technique in many cases. All you need to do is use the relevant formula in the conditional formatting. Here are some examples:

• Highlight alternate even rows:  =ISEVEN( ROW ())
• Highlight alternate add rows:  =ISODD(ROW())
• Highlight every 3rd row:  = MOD (ROW(),3)=0

7. Search and Highlight Data using Conditional Formatting

This one is a bit advanced use of conditional formatting. It would make you look like an Excel rockstar.

Suppose you have a dataset as shown below, with Products Name, Sales Rep, and Geography. The idea is to type a string in cell C2, and if it matches with the data in any cell(s), then that should get highlighted. Something as shown below:

Here are the steps to create this Search and Highlight functionality:

• Select the dataset.

That’s it! Now when you enter anything in cell C2 and hit enter, it will highlight all the matching cells.

How does this work?

The formula used in conditional formatting evaluates all the cells in the dataset. Let’s say you enter Japan in cell C2. Now Excel would evaluate the formula for each cell.

The formula would return TRUE for a cell when two conditions are met:

• Cell C2 is not empty.
• The content of cell C2 exactly matches the content of the cell in the dataset.

Hence, all the cells that contain the text Japan get highlighted.

You can use the same logic, to create variations such as:

• Highlight the entire row instead of a cell .
• Highlight even when there is a partial match .
• Highlight the cells/rows as you type (dynamic) [You are going to love this trick :)].

How to Remove Conditional Formatting in Excel

Once applied, conditional formatting remains in place unless you remove it manually. As a best practice, keep the conditional formatting applied only to those cells where you need it.

Since it’s volatile , it may lead to a slow Excel workbook.

To remove conditional formatting:

• Select the cells from which you want to remove conditional formatting.

Important things to know about Conditional Formatting in Excel

• Conditional formatting in volatile. It can lead to a slow workbook. Use it only when needed.
• When you copy paste cells that contain conditional formatting , conditional formatting also gets copied.
• If you apply multiple rules on the same set of cells, all rules remain active. In the case of any overlap, the rule applied last is given preference. You can, however, change the order by changing the order from the Manage Rules dialogue box.

You May Also Like the Following Excel Tutorials:

• The Right Way to Apply Conditional Formatting in Pivot Table
• Find and Remove Duplicates in Excel
• Conditional Formatting Not Working in Excel
• The Ultimate Guide to Using Conditional Formatting in Google Sheets
• How to Highlight Weekend Dates in Excel?

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

33 thoughts on “Using Conditional Formatting in Excel (The Ultimate Guide + Examples)”

how can i highlight an approaching date i.e. I want a blank cell to change colour as the target date approaches today’s date

Thanks, good content!

Is there a way to make conditional formatting stable when loading data into Excel through PowerQuery? The issue is that I can add or remove custom columns to loaded data and conditional formatting stays fine, but if I reorder/add/remove columns to source data in PowerQuery, the conditional formatting formulas apply to the unintended column.

In other words, if formula paints red with formula “=AND($P13″China”;$O13″”), applying to “Country” ($P) and “Sales” ($O), when adding/removing/reordering columns in PowerQuery and reload data, coloring applies to i.e. “Product” (now in $P) and “Population” (now in $O), instead of “Country” (now in $Q) and “Sales” now in $B), breaking the whole thing.

I tried =AND(MyTable[Country]”China”;MyTable[Sales]””) but the conditional formatting formula does not seem to accept such a thing as formula-based columns.

learn a lot

I want to protect an excel file with a password which have any type of vba coding

Here there, I need some help with conditional formatting;

what’s the Formula Rule I can use to change color of an entire column if there is less than a specific numbers of charters in that column? For example, I need an eye catcher or attention grabber (change color of column or change to bold) when I have the letter “D” less than 6 or more than 6 times in a column?

Thanks in advance for your help,

How do I turn a conditionally formatted list into a seperate list that only contains the highlighted cells?

Can anyone help me to solve a small doubt.?

I need cell D5 – D17 to be highlighted in different colors if a specific value comes in the cell F5-F17.

HEY DUDE….IM NOT GETTING SEARCH &HIGHLIGHT DATA USING COND.FORM

Thanks Sumit

Hello – I have comma delimited data (A, A A, B, C, D D, etc) in, say, cells K3 through K99. I want to format another corresponding cell, say H5, or the entire row based on how many such delimited items are in K5. Example, assign red to H5 if K5 has 10 items, assign green if K5 has 5 items, etc. Could anyone help with VB code to do this in excel please?

Is there a way to overlap conditional formatting for a field? Like, if I have a row that displays a color for a range of dates for a project task in a gannt chart, but when I put in a % complete, a range of those rows might highlight over (or replace) the original color indicated by the start and end date.

Love what you did here. I took it one step further and made it toggle Ascending vs. Descending based on what was currently selected. Kind of a hack job in the end, but couldn’t have done it without your help. Thanks!

I want to highlight based on whether a value increased or decreased from the week prior. (less than last week = green, greater than last week = red)

Very good lessons

hii I would like to apply formatting for my task list. the idea is to highlight the cells red with pending task and highlight the cells containing done with green colour. is it possible to do in excel

Yes – You can have a look at the templates I share here. it does exactly what you need. https://trumpexcel.com/excel-to-do-list-template-download/

Hello, what i’ve done on the search example AND($C$2″”,ISNUMBER(SEARCH($C$2),B5)) Like this i have a search on partial words.

The examples are very useful. I want to apply conditional formatting to a range depending on a value within that range. I have several range of that type and want to copy paste that condtional formartting to rest all of the individual ranges. Plss plss help me out.

You can copy and paste conditional formatting as well. Just copy the cells which have the conditional formatting and use paste special options to paste format only.

This is really very useful

Very useful and well explained with examples.

thank u ,really great and simple discussion but what the meaning of Nth Row

Thanks for commenting Mariana.. Nth could be any number. So if you want to highlight every 5th row, you can use the formula =MOD(ROW(),5)=1 in conditional formatting.

Number 6 can be done easier: =isodd(row())

Thanks for sharing.. If one wants to highlight ODD or EVEN rows, then ISODD and ISEVEN functions are better. Else we can you the MOD function

This is really helpful, I really liked this post.

Thanks for commenting Deepak.. Glad you liked it 🙂

Love the 7th one!

Thanks for commenting Greg.. Glad you liked it 🙂

6th is very useful..

Thanks for commenting.. Glad you liked it 🙂

Thank you sir, it is excellent training on excel. Can you send me Gantt chart preparation video.

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

FREE EXCEL E-BOOK

• Ablebits blog
• Conditional Formatting

Excel Conditional Formatting tutorial with examples

The tutorial explains all main features of Excel conditional formatting with examples. You will learn how to do conditional formatting in any version of Excel, efficiently use preset rules or create new ones, edit, copy and clear formatting.

Excel conditional formatting is a really powerful feature when it comes to applying different formats to data that meets certain conditions. It can help you highlight the most important information in your spreadsheets and spot variances of cell values with a quick glance.

Many users, especially beginners, find it intricate and obscure. If you feel intimidated and uncomfortable with this feature, please don't! In fact, conditional formatting in Excel is very straightforward and easy to use, and you will make sure of this in just 5 minutes when you have finished reading this tutorial :)

What is conditional formatting in Excel?

Excel Conditional Formatting is used to apply certain formatting to data that meets one or more conditions. Just like usual cell formatting, it lets you highlight and differentiate your data in various ways by changing cells' fill color, font color, border styles, etc. The difference is that it is more flexible and dynamic - when the data changes, conditional formats get updated automatically to reflect the changes.

Where is conditional formatting in Excel?

Now that you know where to find conditional formatting in Excel, let's move on and see how you can leverage it in your daily work to make more sense of the project you are currently working on.

How to use conditional formatting in Excel

To truly leverage the capabilities of conditional format, you need to learn how to utilize various rule types. The good news is that whatever rule you are going to apply, it defines the two key things:

• What cells are covered by the rule.
• What condition should be met.

So, here's how you use Excel conditional formatting:

• In your spreadsheet, select the cells you want to format.
• On the Home tab, in the Styles group, click Conditional Formatting .

In a similar manner, you can use any other rule type that is more appropriate for your data, such as:

• Greater than or equal to
• Between two values
• Text that contains specific words or characters
• Date occurring in a certain range
• Duplicate values
• Top/bottom N numbers

How to use a preset rule with custom formatting

If none of the predefined formats suits you, you can choose any other colors for cells' background, font or borders. Here's how:

• If you want more colors than the standard palette provides, click the More Colors… button on the Fill or Font tab.
• If you wish to apply a gradient background color , click the Fill Effects button on the Fill tab and choose the desired options.

How to create a new conditional formatting rule

If none of the preset rules meets your needs, you can create a new one from scratch. To get it done, follow these steps:

• Click the Format… button, and then choose the Fill or/and Font color you want.

Excel conditional formatting based on another cell

In the previous examples, we highlighted cells based on "hardcoded" values. However, in some cases it makes more sense to base your condition on a value in another cell. The advantage of this approach is that irrespective of how the cell value changes in future, your formatting will adjust automatically to respond to the change.

As an example, let's highlight prices in column B that are greater than the threshold price in cell D2. To accomplish this, the steps are:

• Click Conditional formatting > Highlight Cells Rules > Greater Than…
• In the dialog box that pops up, place the cursor in the text box on the left (or click the Collapse Dialog icon), and select cell D2.
• When done, click OK .

That is the simplest case of conditional formatting based on another cell. More complex scenarios may require the use of formulas. And you can find several examples of such formulas along with the step-by-step instructions here:

• Excel conditional formatting formulas based on another cell
• How to change the row color based on a cell's value
• Video: Conditional formatting formulas based on another cell

Apply multiple conditional formatting rules to same cells

When using conditional formats in Excel, you are not limited to only one rule per cell. You can apply as many rules as your business logic requires.

For example, you can create 3 rules to highlight prices higher than $105 in red, higher than $100 in orange, and higher than $99 in yellow. For the rules to work correctly, you need to arrange them in the right order . If the "greater than 99" rule is placed first, then only the yellow formatting will be applied because the other two rules won't have a chance to be triggered - obviously, any number that is higher than 100 or 105 is also higher than 99 :)

To re-arrange the rules, this is what you need to do:

• Select any cell in your dataset covered by the rules.
• Open the Rules Manager by clicking Conditional Formatting > Manage Rules…
• Click the rule that needs to be applied first, and then use the upward arrow to move it to top. Do the same for the second-in-priority rule.
• Select the Stop If True check box next to all but the last rule because you do not want the subsequent rules to be applied when the prior condition is met.

What is Stop if True in Excel conditional formatting?

The Stop If True option in conditional formatting prevents Excel from processing other rules when a condition in the current rule is met. In other words, if two or more rules are set for the same cell and Stop if True is enabled for the first rule, the subsequent rules are disregarded after the first rule is activated.

In the example above, we have already used this option to ignore subsequent rules when the first-in-priority rule applies. That usage is quite evident. And here are another couple of examples where the use of the Stop If True function is not so obvious but extremely helpful:

• How to show only some items of the icon set
• Exclude blank cells from conditional formatting

How to edit Excel conditional formatting rules

To make some changes to an existing rule, proceed in this way:

• Select any cell to which the rule applies and click Conditional Formatting > Manage Rules…

Tip. If you don't see the rule you want to edit, then select This Worksheet from the Show formatting rules for drop-down list at the top of the Rules Manager dialog box. This will display the list of all the rules in your worksheet.

How to copy Excel conditional formatting

To apply a conditional format you've created earlier to other data, you won't need to re-create a similar rule from scratch. Simply use Format Painter to copy the existing conditional formatting rule(s) to another data set. Here's how:

• Click any cell with the formatting you want to copy.

Tip. To copy the formatting to multiple non-contiguous cells or ranges, double-click Format Painter .

• When done, press Esc to stop using the paintbrush.
• Select any cell in your new dataset, open the Rules Manager and check the copied rule(s).

Note. If the copied conditional formatting uses a formula, you may need to adjust cell references in the formula after copying the rule.

How to delete conditional formatting rules

I've saved the easiest part for last :) To delete a rule, you can either:

This is how you do conditional formatting in Excel. Hopefully, these very simple rules we created were helpful to get a grasp of the basics. Below, you can find a few more tutorials that can help you understand the inner mechanics and expand conditional formatting in your spreadsheets far beyond its traditional uses.

Practice workbook for download

You may also be interested in.

• How to conditionally format dates in Excel - how to apply Excel conditional formatting to dates using built-in rules and formulas.
• Change background color based on cell value - two quick ways to change the background color of cells based on their values.
• Alternate row colors in Excel - how to shade every other or every N row using table styles and conditional formatting.
• How to sum and count by color in Excel - how to sum and count colored cells with custom functions and VBA. The solutions work both for cells colored manually and with conditional formatting.
• How to highlight duplicates in Excel - how to use conditional formatting to highlight duplicate values and rows.
• Cell references in Excel conditional formatting - how to correctly use relative and absolute cell references in formula-based conditional formatting rules.
• Excel conditional formatting for blank cells - Everything you need to know about conditional format for empty cells in Excel.
• Quick Analysis formatting - how to apply conditional formatting for a selected range with a couple of clicks.
• Auto-format GROUPBY and PIVOTBY results - how to enhance the output of Excel's GROUPBY and PIVOTBY formulas with conditional formatting.

Table of contents

312 comments

I have a monthly dashboard with Plan vs Actual in Horizontal rows, and I am using a conditional formatting red or green based on Plan vs actual, but when I copy the format to the posterior cells, it keeps the original (plan) as reference.

How can I do a conditional formatting for each cell?

Thanks, Alex

Hi Alex! Pay attention to the following paragraph of the article above: Copy conditional formatting. If this is not what you wanted, please let me know and I will try to help you.

Sir, at present I am facing these two problems. Please help me.

1) I want to know one thing that when we are writing the formulas in the cell then those formulas are visible or going in the formula bar. I want to write that formula or formulas by VBA to solve the problem. How is it possible? If you give a detailed guide, it will be very helpful. For example, I am doing Date of Retirement calculation with EOMONTH or I am writing a specific formula in a cell to calculate Present Age. I want to run these formulas with VBA. How to do? That means, I will write date of birth in one cell and present age in another cell and Retirement Date will be written in another cell simultaneously/automatically.

2) In those cells that cannot be duplicate entries, how can it be solved by conditional formatting? Means, I wrote the ID code in a cell, in the next case when entering that ID, a massage will show that this ID has been entered. It would be very helpful to know in detail how it can be solved.

Hi! Your first question can be solved without VBA with the help of this guide: How to calculate age in Excel from birthday . The answer to your second question can be found here: Data validation to allow only unique entries and disallow duplicates .

I am trying to make a conditional format based on a range of numbers.

For example, if the cell has a value outside of 15-20%, say with a range of 10-14% I want it to show red but if it falls into the range, such as 16-18% I want it to display green. Is it possible to do this based off of a range value in the cell or does it have to be a single number in the cell for the format to work?

Also the value I would be putting in the cells would be 10-14% and 16-18% in the above examples.

Hi! For each color, create a separate conditional formatting rule as described in the article above. To do this, write a single number in the cell. 16-18% is text. You can find useful information in this article: Change background color based on cell value .

I have twelve rules that format some cells based on conditions of some other cell which works just fine. When I copy the formatting to a new sheet in the same workbook, the rule copied adds the sheet name of the source. The find and replace formulas does not edit conditional rule formulae. I have to manually delete the 'sheetname'! from each of the 12 copied formulae.

I'm pretty sure it's not possible, but maybe you guys know a way around; Is there any way to do partial formatting of a cell, that's the result of a formula?

I have a cell that's the output of a formula, which is a text string, and I want to underline the first word/first 10 characters of that string.

Hi! You cannot format part of a cell using conditional formatting.

Is there a way to highlight all cells that are not formula (ie. hardcoded) to show where formulas in a range have been written over?

Hi! We have a tool that can solve your task in a couple of clicks - Select by Value & Color tool . Use Select Special Cells - Select cells containing formulas option. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.

Hello, I'm trying to determine how I can conditionally format a range of months based off of the Effective and expiration month dates, as well as the Start month.

On the first example, the Start Month is Jan, that would be the first month cell to highlight, followed by the duration of months (differenc between Jan 2023 and March 2024 (14 months).

In the next example, Start month is May, followed by a duration of 16 months ending in Sept (aligned with the Sept expiration date).

Effective date will always be a prior date to the Expiration Date; duration can be anywhere from 6 months to 29 months; Start month will either be same month as effective date or the following month.

Appreciate you taking the time to review!

EffectiveDate ExpirationDate StartMonth Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul 1/1/2023 3/1/2024 Jan (JAN through March cells would be highlighted) 4/1/2023 7/15/2024 May (MAY through July cells would be highlighted)

Hi! You can find the examples and detailed instructions here: Excel conditional formatting for dates & time: formulas and rules .

I've already applied CF to a row on selected cells, this is based on a drop down "Yes" in one cell. This then changes the selected cells to change colour, however I want to expand the cell ranges from the CF Rule but I cannot work out how to do this. I've tried copy/Special paste and the paint brush and this just copies the text, colour of the already CF cells.

Please help :-)

Hi! To expand the range of conditional formatting cells, use rule editing as recommended in the article above.

I've tried this method:

Click the first cell out of cell range which has the format Double click the paint brush as it has multiple columns, which are not in order Highlight the cells I want to include in the format, but it will then just colour code the entire range, this is without selecting the drop down option on row 2 to have that format..

I have told you how to do things correctly. Also note that copying the format changes the references in the conditional formatting formula. Read more: Relative and absolute cell references in Excel conditional formatting .

Hi, I am trying to highlight all the zero values in a row after a certain number

Example I have: 100 in A1 is the total which is made of

25 (A2), 0 (A3), 20, 15, 0, 40, 0, 0 (A9)

So I want to highlight the 0 values after 40 in cell A7. The numbers something don't add exactly to 100, so I must use some formula which say highlight if value is zero.

Thanks in advance

Hi! In the conditional formatting formula, you must specify the exact cell by which the conditional formatting is done. In your case this is not possible.

I am wanting to figure out how to duplicate conditional formatting down 15 rows in a column. An example of the first row will be =$L$7>$D$7 (turns L7 red). Is there a way to drag this down to row 8, to auto create the same conditional formatting - =$L$8>$D$8 (turns L8 red) without having to create the conditional formatting manually for cell in the column?

Hi! Apply the formula =L1>D1 to your desired range of cells in column L as described in the article above.

I tried this multiple times and multiple ways. It never created another rule and all of the cells I copied to relied on the original cell's rule to determine the color. Ultimately I had to manually create a rule for each cell/row.

I am trying to highlight the whole row based on the first word I insert, for example:

Region 1 = red, Region 2 = blue;

So, when I type region 1, the column and its row will become red.

Do you have a formula that I could use to do this? or do you have the guideline to do this in conditional formatting rules manager?

Hi! Maybe this article will be helpful: Change the row color based on cell value .

Hello! I am trying to highlight cells based on if another cell has a date entered in it (cell in column "I" will need to change color once the corresponding cells in columns L, M, and N (each need to change individually). I do this to have an easy visual que that a sub-task has been completed. Cells in columns L, M, and N will be given dates once reports are issued/tasks completed - cells in L will get dates first, then M, and then N. Each row is a different item, and once the cells from L, M, and N are given dates, I need that row's "I" cell to change color. I was doing it by the NOT ISBLANK function, but then it would multiply/separate out when you open up rules, so adding a new one was difficult. I started out with 3 rules, and then they altered themselves and ended up showing up as twenty some, or more, different rules.

So I guess two questions - do you have a formula that I could use to do this? And how do you keep rules from multiplying/modifying themselves?

Hi! If I understand your task correctly, try the following conditional formatting formula:

=AND(ISNUMBER(L1:N1))

THanks for your well described help on this topic. Can the output be text rather than colour? i.e. if something needs action it would display action rather than red?

Hi! Conditional formatting can only change the format of a cell, not its value. To change the value, use Excel formulas.

I am wanting to create a conditional formatting rule that highlights cells D8 and E8 if any cell between F8 and V8 have a value of 1-99. Is this possible?

Thanks in advance! I’ve tried lots of things but can’t get it to work.

Hi! Please try the following conditional formatting formula:

=SUM((F8:V8>0)*(F8:V8<100))>0

I appreciate the help. I'm not sure if you can help further. But when I have applied these rules and reviewed the section you suggested the formulas are highlighting date 8/1/23 yellow and 7/15/23 & 7/17/23 red even though they have not yet passed.

I think you didn't read this article very carefully. If the date is less than today, =$B2

So, I am trying to set up a conditional formatting function to highlight cells greater than 130 in each row. However, once the first cell to reach 130 in the row is highlighted, I do not want the following numbers to be highlighted (since the numbers just increase).

I tried using the in-between function but that would give me a range greater than 30 for some rows and simply is not working for the spread I am using.

Is there a specific function for this in conditional formatting?

Hi! If I understand you correctly, to select only the first value greater than 130, select row 2 and create a conditional formatting rule:

=COUNTIF($A$2:A2,">130")=1

You can learn more about COUNTIF in this article: Excel COUNTIF function examples . Hope this is what you need.

Trying to find a conditional format solution to a stock available v continuous daily demand for a production week As you may guess starting stock in the first day of the week maybe 100, but on each day of the week the stock that is to be consumed each day will run out on a particular day, question how can I highlight what day the stock will run out on , as I will have multiple rows of parts to apply this too, the idea is to plan production based upon run out day Can you help I was thinking each cell will need some kind of subtraction referencing the row stock level

Hi! To calculate how many days' supply is enough, divide the beginning supply by the daily consumption. It is not possible to give more detailed advice without seeing your data.

Hello! I find that in some files the conditional formatting "disappears" from one use to the next. I set it, it's working fine. And when I go back to the file another time, I have to set it again. In other files it's still there. I cannot work out why one would be different from the other. Is it to do with other settings in the individual files? Thanks, Manuela

i want the conditional formatting i created for a cell to be copied down to the next. but i want the continuation of the references as well. example the first cell's reference is $G$1, the next cell should be $G$2 and so on. i can do this one by one. but is there a faster way to do it. or a shortcut or maybe a command for multiple cells like dragging down. because when i drag down. the reference cell is the same.

Hello! If you want a conditional formatting formula to apply to a range of cells, don't use an absolute reference . You need a relative reference. I recommend reading this guide: Excel formulas for conditional formatting based on another cell value .

Can you use conditional formatting to change background colour depending on the case of individual letter (CHAR) been LOWER or UPPER? i.e. 'c' or 'C'

Hi! For uppercase letters, this formula will return TRUE

=AND(CODE(LEFT(A2,1))<91,CODE(LEFT(A2,1))>64)

I hope it’ll be helpful. If something is still unclear, please feel free to ask.

I prefer this: =EXACT(LEFT(A2,1),UPPER(LEFT(A2,1)))

Sir in m column 3 text Refund, Failed & Succes

now if text Success than a b c row is highted in colour

Hello! To check if the text "Succes" is in the cell, use the formula

=IF(ISNUMBER(SEARCH("Succes",A1,1)),TRUE,FALSE)

You can learn more about SEARCH function in Excel in this article on our blog.

Post a comment

The Complete Guide to Conditional Formatting in Excel

- Written by Puneet

This tutorial gives a complete view of how to use conditional formatting and what options are available. It also gives you a list of examples you can learn to use in the real world while working with data.

What is Conditional Formatting?

Conditional Formatting is an option to apply to a cell or a range with a condition. When you use it, it tests a condition first, and if that condition is TRUE, then only it will apply the Formatting. For example, you want to use red for all the cells in a range with an error or all the blank cells.

You can use conditional Formatting in two ways:

• Pre-Defined Rules
• Custom Rule with a Formula

In this tutorial, we will learn about both.

Simple Example to Understand Conditional Formatting

Let’s take an example to understand conditional Formatting. In the example below, you have name data here and must highlight the cells with an “Aryan” in the name.

Let’s understand this: Here, we have a condition test for the cells with the text “Aryan” in it. And only the cells where you have this text are highlighted.

Note: Conditional Formatting is one of the most useful tools to help you quickly analyze data in real-time in Excel.

Available Pre-Defined Conditional Formatting Rules

Five major options have pre-defined rules for you to use in conditional formatting. With pre-defined options, there comes the pre-defined formatting to apply.

1. Highlight Cells Rule

With the highlight cells rule, you can apply conditional formatting to the cells by checking their value. If you want to highlight a cell where a number is less than 1500, you can use the less than option from the list.

Here you have options to deal with all the kind of data, like, text, dates, and numbers.

2. Top/Bottom Rules

With the Top and Bottom options, you can analyze your data quickly. There are options to highlight the top 10 or bottom 10 values and values below or above average.

Even further, you can customize these options per your needs; let’s say, if you want to highlight the top 20 values instead of the 10, you can do that.

3. Data Bars

Data bars are tiny in-cell charts that can help you create a data bar based on the values in the selected cells. It considers the highest and lowest value and inserts data bars based on them in all the selected cells.

Tip: When you change the column’s width, the data bar adjusts its width according to that.

4. Color Scales

Color Scales help you highlight the cells based on your values in the range. There are two or three-color scales that you can use. It has 12 pre-defined color scales which you can use.

These color scales help you quickly identify data in no time.

5. Icon Sets

With Icon sets, you can apply the icon to a cell based on its values compared to those in the range. For example, if a value is higher in the range, it will get an upwards arrow; if it’s lower, it will be a downward arrow.

And as you can see, you have several icons sets to use.

Note: These pre-defined rules will be useful in over 80% of situations. And if you want to go further, you can create a custom rule.

Create a Custom New Rule

And you can also create custom rules if you want. When you click on the “New Rule”, it opens a dialog box where you can choose to create a custom rule to apply conditional Formatting.

In this dialog box, you can select any options we discussed above and apply them customized. And in this dialog box, you have one more option which allows you to apply a rule with a custom formula. You can learn more from these detailed tutorials to use conditional Formatting in a specific way:

• Apply Conditional Formatting on Blank Cells
• Compare Two Columns using Conditional Formatting
• Apply Conditional Formatting Based on Another Cell
• Apply Conditional Formatting Based on Another Column
• Copy Conditional Formatting from Range to Another
• Apply Conditional Formatting to an Entire Column
• Highlight Rows using Conditional Formatting
• Apply Multiple Conditions in Conditional Formatting
• Conditional Formatting Not Working
• Highlight IF a Cell Contains a Specific Text with Conditional Formatting in Excel
• Apply Conditional Formatting Based on a Date
• Applying Color Scales using Conditional Formatting
• How to Add Data Bars in Excel
• Find and Highlight Duplicates in Excel using Conditional Formatting
• Stop IF True in Conditional Formatting
• Remove Conditional Formatting in Excel

Conditional Formatting is a formatting, and you can remove it from a cell or a range of cells without removing the values.

Or you can also use the keyboard shortcut to Alt > E > A > F to clear all the formatting from the selected cell or the range.

Managing Rules

This option helps you manage all the conditional formatting rules in a workbook or selection. With Rules Manager, you can:

• Edit or Delete a Rule.
• Create a Duplicate or a New Rule.
• Change the Applied to Range.
• And can also change the order of the rules.

Stop IF True

When you check the stop is a true option for a rule, it will stop applying further rules on the cell when a condition is true on a cell.

Using Cell Formatting in Conditional Formatting

Conditional Formatting allows you to apply formatting to a cell using the format cells options. You get all the formatting options that you have in it.

• Number formatting
• Font Formatting
• Cell Formatting

Identify All the Cells where Conditional Formatting is Applied

When you open the go to special option from Home > Find and Select > Go To Special.

In the Go To Special dialog box , select the conditional Formatting, and then click OK.

And the moment you click OK, it selects the cells where the conditional formatting is applied.

Few Points to Consider Before using Conditional Formatting.

• Excessive conditional formatting in the workbook can make it a heavy and slow Excel file.
• While using formulas with conditional formatting, you must take care of the cell reference.
• If you share an Excel workbook with someone else, the conditional formatting stays intact.

• Get started with computers
• Learn Microsoft Office
• Apply for a job
• Improve my work skills
• Design nice-looking docs
• Getting Started
• Smartphones & Tablets
• Typing Tutorial
• Online Learning
• Basic Internet Skills
• Online Safety
• Social Media
• Zoom Basics
• Google Docs
• Google Sheets
• Career Planning
• Resume Writing
• Cover Letters
• Job Search and Networking
• Business Communication
• Entrepreneurship 101
• Careers without College
• Job Hunt for Today
• 3D Printing
• Freelancing 101
• Personal Finance
• Sharing Economy
• Decision-Making
• Graphic Design
• Photography
• Image Editing
• Learning WordPress
• Language Learning
• Critical Thinking
• For Educators
• Translations
• Staff Picks
• English expand_more expand_less

Excel 2016  - Conditional Formatting

Excel 2016  -, conditional formatting, excel 2016 conditional formatting.

Excel 2016: Conditional Formatting

Lesson 24: conditional formatting.

/en/excel2016/charts/content/

Introduction

Let's say you have a worksheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw information. Similar to charts and sparklines, conditional formatting provides another way to visualize data and make worksheets easier to understand.

Optional: Download our practice workbook .

Watch the video below to learn more about conditional formatting in Excel.

Understanding conditional formatting

Conditional formatting allows you to automatically apply formatting—such as colors , icons , and data bars —to one or more cells based on the cell value . To do this, you'll need to create a conditional formatting rule . For example, a conditional formatting rule might be: If the value is less than $2000, color the cell red . By applying this rule, you'd be able to quickly see which cells contain values less than $2000.

To create a conditional formatting rule:

In our example, we have a worksheet containing sales data, and we'd like to see which salespeople are meeting their monthly sales goals. The sales goal is $4000 per month, so we'll create a conditional formatting rule for any cells containing a value higher than 4000.

• From the Home tab, click the Conditional Formatting command. A drop-down menu will appear.

• A dialog box will appear. Enter the desired value(s) into the blank field. In our example, we'll enter 4000 as our value.

You can apply multiple conditional formatting rules to a cell range or worksheet, allowing you to visualize different trends and patterns in your data.

Conditional formatting presets

Excel has several predefined styles—or presets —you can use to quickly apply conditional formatting to your data. They are grouped into three categories:

To use preset conditional formatting:

• Click the Conditional Formatting command. A drop-down menu will appear.

Removing conditional formatting

To remove conditional formatting:.

Click Manage Rules to edit or delete individual rules. This is especially useful if you've applied multiple rules to a worksheet.

• Open our practice workbook .
• Click the Challenge worksheet tab in the bottom-left of the workbook.
• Select cells B3:J17 .
• Let's say you're the teacher and want to easily see all of the grades that are below passing. Apply Conditional Formatting so it Highlights Cells containing values Less Than 70 with a light red fill .
• Now you want to see how the grades compare to each other. Under the Conditional Formatting tab, select the Icon Set called 3 Symbols (Circled) . Hint : The names of the icon sets will appear when you hover over them.

• Using the Manage Rules feature, remove the light red fill , but keep the icon set .

/en/excel2016/track-changes-and-comments/content/

Use conditional formatting to highlight information in Excel

Conditional formatting can help make patterns and trends in your data more apparent. To use it, you create rules that determine the format of cells based on their values, such as the following monthly temperature data with cell colors tied to cell values. 

You can apply conditional formatting to a range of cells (either a selection or a named range), an Excel table, and in Excel for Windows, even a PivotTable report.

Apply conditional formatting in a PivotTable report

Conditional formatting typically works the same way in a range of cells, an Excel table, or a PivotTable report. However, conditional formatting in a PivotTable report has some extra considerations:

There are some conditional formats that don't work with fields in the Values area of a PivotTable report. For example, you can't format such fields based on whether they contain unique or duplicate values. These restrictions are mentioned in the remaining sections of this article, where applicable.

If you change the layout of the PivotTable report by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained as long as the fields in the underlying data are not removed.

The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined by all the visible children (the next lower level in a hierarchy) of a parent (the next higher level in a hierarchy) on rows for one or more columns, or columns for one or more rows.

Note:  In the data hierarchy, children do not inherit conditional formatting from the parent, and the parent does not inherit conditional formatting from the children.

There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field, and by value field.

The default method of scoping fields in the Values area is by selection. You can change the scoping method to the corresponding field or value field by using the Apply formatting rule to option button, the New Formatting Rule dialog box, or the Edit Formatting Rule dialog box.

Use Quick Analysis to apply conditional formatting in Excel

Select the data that you want to conditionally format. The Quick Analysis button appears on the lower-right corner of the selection.

In the pop-up that appears, on the Formatting tab, move your mouse over the different options to see a Live Preview on your data, and then click on the formatting option you want.

The formatting options that appear in the Formatting tab depend on the data you have selected. If your selection contains only text, then the available options are Text , Duplicate , Unique , Equal To , and Clear . When the selection contains only numbers, or both text and numbers, then the options are Data Bars , Colors , Icon Sets , Greater , Top 10% , and Clear .

Live preview will only render for those formatting options that can be used on your data. For example, if your selected cells don't contain matching data and you select Duplicate , the live preview will not work.

If the Text that Contains dialog box appears, enter the formatting option you want to apply and click OK .

If you'd like to watch a video that shows how to use Quick Analysis to apply conditional formatting, see Video: Use conditional formatting .

Download a sample workbook

You can download a sample workbook that contains different examples of applying conditional formatting, both with standard rules such as top and bottom, duplicates, Data Bars, Icon Sets and Color Scales, as well as manually creating rules of your own.

Download: Conditional formatting examples in Excel

Format cells by using a two-color scale

Color scales are visual guides that help you understand data distribution and variation. A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and yellow color scale, as shown below, you can specify that higher value cells have a more green color and lower value cells have a more yellow color.

Tip:  You can sort cells that have this format by their color - just use the context menu.

Tip:  If any cells in the selection contain a formula that returns an error, the conditional formatting is not applied to those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value other than an error value.

Quick formatting

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, click the arrow next to Conditional Formatting , and then click Color Scales .

Select a two-color scale.

Hover over the color scale icons to see which icon is a two-color scale. The top color represents higher values, and the bottom color represents lower values.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Formatting Options button that appears next to a PivotTable field that has conditional formatting applied.

Advanced formatting

On the Home tab, in the Styles group, click the arrow next to Conditional Formatting , and then click Manage Rules . The Conditional Formatting Rules Manager dialog box appears.

Do one of the following:

To add a completely new conditional format, click New Rule . The New Formatting Rule dialog box appears.

To add a new conditional format based on one that is already listed, select the rule, then click Duplicate Rule . The duplicate rule appears in the dialog box. Select the duplicate, then select Edit Rule . The Edit Formatting Rule dialog box appears. 

To change a conditional format, do the following:

Make sure that the appropriate worksheet, table, or PivotTable report is selected in the Show formatting rules for list box.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet, and then by selecting Expand Dialog .

Select the rule, and then click Edit rule . The Edit Formatting Rule dialog box appears.

Under Apply Rule To , to optionally change the scope for fields in the Values area of a PivotTable report by:

Selection: Click Selected cells .

All cells for a Value label: Click All cells showing <Value label> values .

All cells for a Value label, excluding subtotals and the grand total: Click All cells showing <Value label> values for <Row Label> .

Under Select a Rule Type , click Format all cells based on their values (default).

Under Edit the Rule Description , in the Format Style list box, select 2-Color Scale .

To select a type in the Type box for Minimum and Maximum , do one of the following:

Format lowest and highest values:     Select Lowest Value and Highest Value .

In this case, you do not enter a Minimum and Maximum Value .

Format a number, date, or time value:     Select Number and then enter a Minimum and Maximum Value .

Format a percentage Percent:     Enter a Minimum and Maximum Value .

Valid values are from 0 (zero) to 100. Don't enter a percent sign.

Use a percentage when you want to visualize all values proportionally because the distribution of values is proportional.

Format a percentile:     Select Percentile and then enter a Minimum and Maximum Value . Valid percentiles are from 0 (zero) to 100.

Use a percentile when you want to visualize a group of high values (such as the top 20thpercentile) in one color grade proportion and low values (such as the bottom 20th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

Format a formula result:     Select Formula and then enter values for Minimum and Maximum .

The formula must return a number, date, or time value.

Start the formula with an equal sign (=).

Invalid formulas result in no formatting being applied.

It's a good idea to test the formula to make sure that it doesn't return an error value.

Make sure that the Minimum value is less than the Maximum value.

You can choose a different type for the Minimum and Maximum . For example, you can choose a number for Minimum a percentage for Maximum .

To choose a Minimum and Maximum color scale, click Color for each, and then select a color.

If you want to choose additional colors or create a custom color, click More Colors . The color scale you select is shown in the Preview box.

Format cells by using a three-color scale in Excel

Color scales are visual guides that help you understand data distribution and variation. A three-color scale helps you compare a range of cells by using a gradation of three colors. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color.

Select a three-color scale. The top color represents higher values, the center color represents middle values, and the bottom color represents lower values.

Hover over the color scale icons to see which icon is a three-color scale.

To add a new conditional format, click New Rule . The New Formatting Rule dialog box appears.

To add a new conditional format based on one that is already listed, select the rule, then click Duplicate Rule . The duplicate rule is copied and appears in the dialog box. Select the duplicate, then select Edit Rule . The Edit Formatting Rule dialog box appears. 

Selection:     Click Just these cells .

Corresponding field:     Click All <value field> cells with the same fields .

Value field:     Click All <value field> cells .

Under Select a Rule Type , click Format all cells based on their values .

Under Edit the Rule Description , in the Format Style list box, select 3-Color Scale .

Select a type for Minimum , Midpoint , and Maximum . Do one of the following:

Format lowest and highest values:     Select a Midpoint .

In this case, you do not enter a Lowest and Highest Value .

Format a number, date, or time value:     Select Number and then enter a value for Minimum , Midpoint , and Maximum .

Format a percentage:     Select Percent and then enter a value for Minimum , Midpoint , and Maximum . Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.

Use a percentage when you want to visualize all values proportionally, because using a percentage ensures that the distribution of values is proportional.

Format a percentile:     Select Percentile and then enter a value for Minimum , Midpoint , and Maximum .

Valid percentiles are from 0 (zero) to 100.

Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) in one color grade proportion and low values (such as the bottom 20th percentile) in another color grade proportion, because they represent extreme values that might skew the visualization of your data.

Format a formula result:     Select Formula and then enter a value for Minimum , Midpoint , and Maximum .

The formula must return a number, date, or time value. Start the formula with an equal sign ( = ). Invalid formulas result in no formatting being applied. It's a good idea to test the formula to make sure that it doesn't return an error value.

You can set minimum, midpoint, and maximum values for the range of cells. Make sure that the value in Minimum is less than the value in Midpoint , which in turn is less than the value in Maximum .

You can choose a different type for Minimum , Midpoint , and Maximum . For example, you can choose a Minimum number, Midpoint percentile, and Maximum percent.

In many cases, the default Midpoint value of 50 percent works best, but you can adjust this to fit unique requirements.

To choose a Minimum , Midpoint , and Maximum color scale, click Color for each, and then select a color.

To choose additional colors or create a custom color, click More Colors .

The color scale you select is shown in the Preview box.

Format cells by using data bars

A data bar helps you see the value of a cell relative to other cells. The length of the data bar represents the value in the cell. A longer bar represents a higher value, and a shorter bar represents a lower value. Data bars are useful in spotting higher and lower numbers, especially with large amounts of data, such as top selling and bottom selling toys in a holiday sales report.

The example shown here uses data bars to highlight dramatic positive and negative values. You can format data bars so that the data bar starts in the middle of the cell, and stretches to the left for negative values.

Tip:  If any cells in the range contain a formula that returns an error, the conditional formatting is not applied to those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value (such as 0 or "N/A") instead of an error value.

On the Home tab, in the Style group, click the arrow next to Conditional Formatting , click Data Bars , and then select a data bar icon.

You can change the method of scoping for fields in the Values area of a PivotTable report by using the Apply formatting rule to option button.

To add a conditional format, click New Rule . The New Formatting Rule dialog box appears.

Under Edit the Rule Description , in the Format Style list box, select Data Bar .

Select a Minimum and Maximum Type . Do one of the following:

Format lowest and highest values:     Select Lowest Value and Highest Value .

In this case, you do not enter a value for Minimum and Maximum .

Format a number, date, or time value:     Select Number and then enter a Minimum and Maximum Value .

Format a percentage:     Select Percent and then enter a value for Minimum and Maximum .

Valid values are from 0 (zero) to 100. Do not enter a percent (%) sign.

Format a percentile     Select Percentile and then enter a value for Minimum and Maximum .

Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) in one data bar proportion and low values (such as the bottom 20th percentile) in another data bar proportion, because they represent extreme values that might skew the visualization of your data.

Format a formula result      Select Formula , and then enter a value for Minimum and Maximum .

The formula has to return a number, date, or time value.

Start the formula with an equal sign ( = ).

You can choose a different type for Minimum and Maximum . For example, you can choose a Minimum number and a Maximum percent.

To choose a Minimum and Maximum color scale, click Bar Color .

If you want to choose additional colors or create a custom color, click More Colors . The bar color you select is shown in the Preview box.

To show only the data bar and not the value in the cell, select Show Bar Only .

To apply a solid border to data bars, select Solid Border in the Border list box and choose a color for the border.

To choose between a solid bar and a gradiated bar, choose Solid Fill or Gradient Fill in the Fill list box.

To format negative bars, click Negative Value and Axis and then, in the Negative Value and Axis Settings dialog box, choose options for the negative bar fill and border colors. You can choose position settings and a color for the axis. When you are finished selecting options, click OK .

You can change the direction of bars by choosing a setting in the Bar Direction list box. This is set to Context by default, but you can choose between a left-to-right and a right-to-left direction, depending on how you want to present your data.

Format cells by using an icon set

Use an icon set to annotate and classify data into three to five categories separated by a threshold value. Each icon represents a range of values. For example, in the 3 Arrows icon set, the green up arrow represents higher values, the yellow sideways arrow represents middle values, and the red down arrow represents lower values.

Tip:  You can sort cells that have this format by their icon - just use the context menu.

The example shown here works with several examples of conditional formatting icon sets.

You can choose to show icons only for cells that meet a condition; for example, displaying a warning icon for those cells that fall below a critical value and no icons for those that exceed it. To do this, you hide icons by selecting No Cell Icon from the icon drop-down list next to the icon when you are setting conditions. You can also create your own combination of icon sets; for example, a green "symbol" check mark, a yellow "traffic light", and a red "flag."

Tip:  If any cells in the selection contain a formula that returns an error, the conditional formatting is not applied to those cells. To ensure that the conditional formatting is applied to those cells, use an IS or IFERROR function to return a value (such as 0 or "N/A") instead of an error value.

Select the cells that you want to conditionally format.

On the Home tab, in the Style group, click the arrow next to Conditional Formatting , click Icon Set , and then select an icon set.

Under Edit the Rule Description , in the Format Style list box, select Icon Set .

Select an icon set. The default is 3 Traffic Lights (Unrimmed) . The number of icons and the default comparison operators and threshold values for each icon can vary for each icon set.

You can adjust the comparison operators and threshold values. The default range of values for each icon are equal in size, but you can adjust these to fit your unique requirements. Make sure that the thresholds are in a logical sequence of highest to lowest from top to bottom.

Format a number, date, or time value:     Select Number .

Format a percentage:     Select Percent .

Format a percentile:     Select Percentile . Valid percentiles are from 0 (zero) to 100.

Use a percentile when you want to visualize a group of high values (such as the top 20th percentile) using a particular icon and low values (such as the bottom 20th percentile) using another icon, because they represent extreme values that might skew the visualization of your data.

Format a formula result:     Select Formula , and then enter a formula in each Value box.

To make the first icon represent lower values and the last icon represent higher values, select Reverse Icon Order .

To show only the icon and not the value in the cell, select Show Icon Only .

You may need to adjust the column width to accommodate the icon.

The size of the icon shown depends on the font size that is used in that cell. As the size of the font is increased, the size of the icon increases proportionally.

Format cells that contain text, number, or date or time values

To more easily find specific cells, you can format them by using a comparison operator. For example, in an inventory worksheet sorted by categories, you could highlight products with fewer than 10 items on hand in yellow. Or, in a retail store summary worksheet, you might identify all stores with profits greater than 10%, sales volumes less than $100,000, and region equal to "SouthEast."

The examples shown here work with examples of built-in conditional formatting criteria, such as Greater Than, and Top %. This formats cities with a population greater than 2,000,000 with a green background and average high temperatures in the top 30% with orange.

Note:  You cannot conditionally format fields in the Values area of a PivotTable report by text or by date, only by number.

On the Home tab, in the Style group, click the arrow next to Conditional Formatting , and then click Highlight Cells Rules .

Select the command you want, such as Between , Equal To Text that Contains , or A Date Occurring .

Enter the values you want to use, and then select a format.

If you'd like to watch videos of these techniques, see Video: Conditionally format text and Video: Conditionally format dates .

On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules. The Conditional Formatting Rules Manager dialog box appears.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet or on other worksheets, and then by selecting Expand Dialog .

Under Select a Rule Type , click Format only cells that contain .

Under Edit the Rule Description , in the Format only cells with list box, do one of the following:

Format by number, date, or time:     Select Cell Value , select a comparison operator, and then enter a number, date, or time.

For example, select Between and then enter 100 and 200 , or select Equal to and then enter 1/1/2009 .

You can also enter a formula that returns a number, date, or time value.

If you enter a formula, start it with an equal sign (=).

Format by text:     Select Specific Text , choosing a comparison operator, and then enter text.

For example, select Contains and then enter Silver , or select Starting with and then enter Tri .

Quotes are included in the search string, and you may use wildcard characters. The maximum length of a string is 255 characters.

You can also enter a formula that returns text.

To see a video of this technique, see Video: Conditionally format text .

Format by date:     Select Dates Occurring and then select a date comparison.

For example, select Yesterday or Next week .

To see a video of this technique, see Video: Conditionally format dates .

Format cells with blanks or no blanks:     Select Blanks or No Blanks .

A blank value is a cell that contains no data and is different from a cell that contains one or more spaces (spaces are considered as text).

Format cells with error or no error values:     Select Errors or No Errors .

Error values include: #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!, and #NULL!.

To specify a format, click Format . The Format Cells dialog box appears.

Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK .

You can choose more than one format. The formats you select are shown in the Preview box.

Format only top or bottom ranked values

You can find the highest and lowest values in a range of cells that are based on a cutoff value you specify. For example, you can find the top 5 selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department .

On the Home tab, in the Style group, click the arrow next to Conditional Formatting , and then click Top/Bottom Rules .

Select the command you want, such as Top 10 items or Bottom 10 % .

Under Apply Rule To , to optionally change the scope fields in the Values area of a PivotTable report by:

Under Select a Rule Type , click Format only top or bottom ranked values .

Under Edit the Rule Description , in the Format values that rank in the list box, select Top or Bottom .

To specify a top or bottom number, enter a number and then clear the % of the selected range box. Valid values are 1 to 1000.

To specify a top or bottom percentage, enter a number and then select the % of the selected range box. Valid values are 1 to 100.

Optionally, change how the format is applied for fields in the Values area of a PivotTable report that are scoped by corresponding field.

By default, the conditional format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

A column and its parent row field, by selecting each Column group .

A row and its parent column field, by selecting each Row group .

Format only values that are above or below average

You can find values above or below an average or standard deviation in a range of cells. For example, you can find the above average performers in an annual performance review or you can locate manufactured materials that fall below two standard deviations in a quality rating.

Select the command you want, such as Above Average or Below Average .

Under Select a Rule Type , click Format only values that are above or below average .

Under Edit the Rule Description , in the Format values that are list box, do one of the following:

To format cells that are above or below the average for all of the cells in the range, select Above or Below .

To format cells that are above or below one, two, or three standard deviations for all of the cells in the range, select a standard deviation.

By default, the conditionally format is based on all visible values. However when you scope by corresponding field, instead of using all visible values, you can apply the conditional format for each combination of:

Click Format to display the Format Cells dialog box.

You can choose more than one format. The formats you select are displayed in the Preview box.

Format only unique or duplicate values

Note:  You can't conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.

Tip:  When using the "Duplicate Values" formatting rule, if the "Office authoring languages and proofing" setting is set to a language that uses Double-byte character set (DBCS) encoding, such as Japanese, halfwidth and fullwidth characters are treated the same. If you need to differentiate between them, you can use the "Use a formula to determine where this formula is true" rule type with a COUNTIF formula. For example, to identify duplicate values in the range A2:A400, apply the formula "=COUNTIF($A$2:$A$400,A2)> 1" for conditional formatting.

In the example shown here, conditional formatting is used on the Instructor column to find instructors that are teaching more than one class (duplicate instructor names are highlighted in a pale red color). Grade values that are found just once in the Grade column (unique values) are highlighted in a green color.

Select Duplicate Values .

Make sure that the appropriate worksheet or table is selected in the Show formatting rules for list box.

Under Select a Rule Type , click Format only unique or duplicate values .

Under Edit the Rule Description , in the Format all list box, select unique or duplicate .

Set up your own conditional formatting rule

If none of the above options is what you’re looking for, you can create your own conditional formatting rule in a few simple steps. 

Notes:  If there's already a rule defined that you just want to work a bit differently, duplicate the rule and edit it.

Select Home > Conditional Formatting > Manage Rules , then in the Conditional Formatting Rule Manager dialog, select a listed rule and then select Duplicate Rule . The duplicate rule then appears in the list.

Select the duplicate rule, then select Edit Rule .

Select the cells that you want to format.

On the Home tab, click Conditional Formatting > New Rule .

Create your rule and specify its format options, then click OK .

If you don't see the options that you want, you can use a formula to determine which cells to format - see the next section for steps).

Use a formula to determine which cells to format

If you don't see the exact options you need when you create your own conditional formatting rule, you can use a logical formula to specify the formatting criteria. For example, you may want to compare values in a selection to a result returned by a function or evaluate data in cells outside the selected range, which can be in another worksheet in the same workbook. Your formula must return True or False (1 or 0), but you can use conditional logic to string together a set of corresponding conditional formats, such as different colors for each of a small set of text values (for example, product category names).

Note:  You can enter cell references in a formula by selecting cells directly on a worksheet or other worksheets. Selecting cells on the worksheet inserts absolute cell references. If you want Excel to adjust the references for each cell in the selected range, use relative cell references. For more information, see Create or change a cell reference and Switch between relative, absolute, and mixed references .

Tip:  If any cells contain a formula that returns an error, conditional formatting is not applied to those cells. To address this, use IS functions or an IFERROR function in your formula to return a value that you specify (such as 0, or "N/A") instead of an error value.

On the Home tab, in the Styles group, click the arrow next to Conditional Formatting , and then click Manage Rules .

The Conditional Formatting Rules Manager dialog box appears.

Optionally, change the range of cells by clicking Collapse Dialog in the Applies to box to temporarily hide the dialog box, by selecting the new range of cells on the worksheet or other worksheets, and then by clicking Expand Dialog .

Under Apply Rule To , to optionally change the scope for fields in the Values area of a PivotTable report, do the following:

To scope by selection:     Click Selected cells .

To scope by corresponding field:     Click All cells showing <Values field> values .

To scope by Value field:     Click All cells showing <Values field> for <Row> .

Under Select a Rule Type , click Use a formula to determine which cells to format .

Under Edit the Rule Description , in the Format values where this formula is true list box, enter a formula. You have to start the formula with an equal sign (=), and the formula must return a logical value of TRUE (1) or FALSE (0).

Select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK . You can choose more than one format. The formats you select are shown in the Preview box.

Example 1: Use two conditional formats with criteria that uses AND and OR tests     

The following example shows the use of two conditional formatting rules. If the first rule doesn't apply, the second rule applies.

First rule: a home buyer has budgeted up to $75,000 as a down payment and $1,500 per month as a mortgage payment. If both the down payment and the monthly payments fit these requirements, cells B4 and B5 are formatted green.

Second rule: if either the down payment or the monthly payment doesn't meet the buyer's budget, B4 and B5 are formatted red. Change some values, such as the APR, the loan term, the down payment, and the purchase amount to see what happens with the conditionally formatted cells.

Example 2: Shade every other row by using the MOD and ROW functions     

A conditional format applied to every cell in this worksheet shades every other row in the range of cells with a blue cell color. You can select all cells in a worksheet by clicking the square above row 1 and to the left of column A. The MOD function returns a remainder after a number (the first argument) is divided by divisor (the second argument). The ROW function returns the current row number. When you divide the current row number by 2, you always get either a 0 remainder for an even number or a 1 remainder for an odd number. Because 0 is FALSE and 1 is TRUE, every odd numbered row is formatted. The rule uses this formula: =MOD(ROW(),2)=1.

The following video shows you the basics of using formulas with conditional formatting.

Copy and paste conditional formatting

If you want to apply an existing formatting style to new or other data on your worksheet, you can use Format Painter to copy the conditional formatting to that data.

Click the cell that has the conditional formatting that you want to copy.

Click Home > Format Painter .

The pointer changes to a paintbrush.

Tip:  You can double-click Format Painter if you want to keep using the paintbrush to paste the conditional formatting in other cells.

To paste the conditional formatting, drag the paintbrush across the cells or ranges of cells you want to format.

To stop using the paintbrush, press Esc.

Note:  If you’ve used a formula in the rule that applies the conditional formatting, you might have to adjust any cell references in the formula after pasting the conditional format. For more information, see Switch between relative, absolute, and mixed references .

Find cells that have conditional formatting

If your worksheet contains conditional formatting, you can quickly locate the cells so that you can copy, change, or delete the conditional formats. Use the Go To Special command to find only cells with a specific conditional format, or to find all cells that have conditional formats.

Find all cells that have a conditional format

Click any cell that does not have a conditional format.

On the Home tab, in the Editing group, click the arrow next to Find & Select , and then click Conditional Formatting .

Find only cells that have the same conditional format

Click any cell that has the conditional format that you want to find.

On the Home tab, in the Editing group, click the arrow next to Find & Select , and then click Go To Special .

Click Conditional formats .

Click Same under Data validation .

Manage conditional formatting rules

When you use conditional formatting, you set up rules that Excel uses to determine when to apply the conditional formatting. To manage these rules, you should understand the order in which these rules are evaluated, what happens when two or more rules conflict, how copying and pasting can affect rule evaluation, how to change the order in which rules are evaluated, and when to stop rule evaluation.

Learn about conditional formatting rule precedence

You create, edit, delete, and view all conditional formatting rules in the workbook by using the Conditional Formatting Rules Manager dialog box. (On the Home tab, click Conditional Formatting , and then click Manage Rules .)

When two or more conditional formatting rules apply, these rules are evaluated in order of precedence (top to bottom) by how they are listed in this dialog box.

Here's an example that has expiration dates for ID badges. We want to mark badges that expire within 60 days but are not yet expired with a yellow background color, and expired badges with a red background color.

In this example, cells with employee ID numbers who have certification dates due to expire within 60 days are formatted in yellow, and ID numbers of employees with an expired certification are formatted in red. The rules are shown in the following image.

The first rule (which, if True, sets cell background color to red) tests a date value in column B against the current date (obtained by using the TODAY function in a formula). Assign the formula to the first data value in column B, which is B2. The formula for this rule is =B2<TODAY () . This formula tests the cells in column B (cells B2:B15). If the formula for any cell in column B evaluates to True, its corresponding cell in column A (for example, A5 corresponds to B5, A11 corresponds to B11), is then formatted with a red background color. After all the cells specified under Applies to are evaluated with this first rule, the second rule is tested. This formula checks if values in the B column are less than 60 days from the current date (for example, suppose today’s date is 8/11/2010). The cell in B4, 10/4/2010, is less than 60 days from today, so it evaluates as True, and is formatted with a yellow background color. The formula for this rule is =B2<TODAY()+60 . Any cell that was first formatted red by the highest rule in the list is left alone.

A rule higher in the list has greater precedence than a rule lower in the list. By default, new rules are always added to the top of the list and therefore have a higher precedence, so you'll want to keep an eye on their order. You can change the order of precedence by using the Move Up and Move Down arrows in the dialog box.

What happens when more than one conditional formatting rule evaluates to True

Sometimes you have more than one conditional formatting rule that evaluates to True. Here's how rules are applied, first when rules don't conflict, and then when they do conflict:

When rules don't conflict      For example, if one rule formats a cell with a bold font and another rule formats the same cell with a red color, the cell is formatted with both a bold font and a red color. Because there is no conflict between the two formats, both rules are applied.

When rules conflict      For example, one rule sets a cell font color to red and another rule sets a cell font color to green. Because the two rules are in conflict, only one can apply. The rule that is applied is the one that is higher in precedence (higher in the list in the dialog box).

How pasting, filling, and the Format Painter affect conditional formatting rules

While editing your worksheet, you may copy and paste cell values that have conditional formats, fill a range of cells with conditional formats, or use the Format Painter. These operations can affect conditional formatting rule precedence in the following way: a new conditional formatting rule based on the source cells is created for the destination cells.

If you copy and paste cell values that have conditional formats to a worksheet opened in another instance of Excel (another Excel.exe process running at the same time on the computer), no conditional formatting rule is created in the other instance and the format is not copied to that instance.

What happens when a conditional format and a manual format conflict

If a conditional formatting rule evaluates as True, it takes precedence over any existing manual format for the same selection. This means that if they conflict, the conditional formatting applies and the manual format does not. If you delete the conditional formatting rule, the manual formatting for the range of cells remains.

Manual formatting is not listed in the Conditional Formatting Rules Manager dialog box nor is it used to determine precedence.

Controlling when rule evaluation stops by using the Stop If True check box

For backwards compatibility, select the Stop If True check box in the Manage Rules dialog box to simulate how conditional formatting might appear in those earlier versions of Excel that do not support more than three conditional formatting rules or multiple rules applied to the same range.

For example, if you have more than three conditional formatting rules for a range of cells, and are working with an earlier version of Excel, that version of Excel:

Evaluates only the first three rules.

Applies the first rule in precedence that is True.

Ignores rules lower in precedence if they are True.

The following table summarizes each possible condition for the first three rules:

You can select or clear the Stop If True check box to change the default behavior:

To evaluate only the first rule, select the Stop If True check box for the first rule.

To evaluate only the first and second rules, select the Stop If True check box for the second rule.

You can't select or clear the Stop If True check box if the rule formats by using a data bar, color scale, or icon set.

If you'd like to watch a video showing how to manage conditional formatting rules, see Video: Manage conditional formatting .

Edit the order in which conditional formatting rules are evaluated

The order in which conditional formatting rules are evaluated - their precedence - also reflects their relative importance: the higher a rule is on the list of conditional formatting rules, the more important it is. This means that in cases where two conditional formatting rules conflict with each other, the rule that is higher on the list is applied and the rule that is lower on the list is not applied.

The conditional formatting rules for the current selection are displayed, including the rule type, the format, the range of cells the rule applies to, and the Stop If True setting.

If you don't see the rule that you want, in the Show formatting rules for list box, make sure that the right range of cells, worksheet, table, or PivotTable report is selected.

Select a rule. Only one rule can be selected at a time.

Optionally, to stop rule evaluation at a specific rule, select the Stop If True check box.

Clear conditional formatting

Clear conditional formatting on a worksheet     

On the Home tab, click Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet .

Follow these steps if you have conditional formatting in a worksheet, and you need to remove it.

For an entire worksheet

In a range of cells

Select the cells that contain the conditional formatting.

Notes:  Quick Analysis Lens will not be visible if:

All of the cells in the selected range are empty, or

There is an entry only in the upper-left cell of the selected range, with all of the other cells in the range being empty.

Click Clear Format .

Find and remove the same conditional formats throughout a worksheet

Click on a cell that has the conditional format that you want to remove throughout the worksheet.

On the Home tab, click the arrow next to Find & Select , and then click Go To Special .

Click Same under Data validation . to select all of the cells that contain the same conditional formatting rules.

On the Home tab, click Conditional Formatting > Clear Rules > Clear Rules from Selected Cells .

Tip:  The following sections use examples so you can follow along in Excel for the web. To start, download the Conditional Formatting Examples workbook and save it to OneDrive. Then, open OneDrive in a web browser and select the downloaded file.

Add a new Conditional Formatting rule

Select the cells you want to format, then select Home > Styles > Conditional Formatting > New Rule . You can also open the Conditional Formatting pane and create a new rule without first selecting a range of cells.

Verify or adjust the cells in  Apply to range .

Choose a Rule Type  and adjust the options to meet your needs. 

When finished, select Done and the rule will be applied to your range.

Edit a Conditional Formatting rule

Select a cell which has a conditional format you want to change. Or you can select Home > Styles > Conditional Formatting > Manage Rules to open the Conditional Formatting task pane and select an existing rule.

Hover over the rule and select Edit  by clicking the   pencil icon. This opens the task pane for rule editing. 

Modify the rule settings and click Done to apply the changes.

Manage Conditional Formatting rules

The Conditional Formatting task pane provides everything you need for creating, editing, and deleting rules. Use Manage Rules to open the task pane and work with all the Conditional Formatting rules in a selection or a sheet.

In an open workbook, select Home > Styles > Conditional Formatting > Manage Rules .

The  Conditional Formatting  task pane opens and displays the rules, scoped to your current selection. 

From here, you can: 

Choose a different scope on the Manage Rules in menu - for example, choosing this sheet tells Excel to look for all rules on the current sheet.

Add a rule by selecting New Rule (the plus sign).

Delete all rules in scope by selecting Delete All Rules (the garbage can).

You can use a formula to determine how Excel evaluates and formats a cell.  Open the Conditional Formatting pane and select an existing rule or create a new rule.

In the Rule Type dropdown, select Formula .

Enter the formula in the box. You can use any formula that returns a logical value of TRUE (1) or FALSE (0), but you can use AND and OR to combine a set of logical checks.

You can clear conditional formatting in selected cells or the entire worksheet.

To clear conditional formatting in selected cells, select the cells in the worksheet. Then Select  Home > Styles > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells .

To clear conditional formatting in the entire worksheet, select  Home > Styles > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet .

To delete conditional formatting rules, select  Home > Styles > Conditional Formatting >Manage Rules and use the delete (garbage can) on a specific rule or the Delete all rules button.

Format cells by using a color scale

Color scales are visual guides which help you understand data distribution and variation. Excel offers both two-color scales and three-color scales. 

A two-color scale helps you compare a range of cells by using a gradation of two colors. The shade of the color represents higher or lower values. For example, in a green and yellow color scale, you can specify that higher value cells be more green and lower value cells have a more yellow.

A three-color scale helps you compare a range of cells by using a gradation of three colors. The shade of the color represents higher, middle, or lower values. For example, in a green, yellow, and red color scale, you can specify that higher value cells have a green color, middle value cells have a yellow color, and lower value cells have a red color.

Tip:  You can sort cells that have one of these formats by their color - just use the context menu.

Select the cells that you want to conditionally format using color scales.

Click Home > Styles > Conditional Formatting > Color Scales and select a color scale.

Select  Home > Styles > Conditional Formatting > Data Bars and select a style. 

Select  Home > Styles > Conditional Formatting > Icon Sets and choose an icon set.

Format only cells that contain text, number, or date/time values

This option lets you highlight specific cell values within a range of cells based on their specific contents. This can be especially useful when working with data sorted using a different range.

For example, in an inventory worksheet sorted by categories, you could highlight the names of products where you have fewer than 10 items in stock so it's easy to see which products need restocking without resorting the data. 

Select  Home > Styles > Conditional Formatting > Highlight Cell Rules .

Select the comparison, such as Between , Equal To, Text That Contains , or A Date Occurring .

You can highlight the highest and lowest values in a range of cells which are based on a specified cutoff value.

Some examples of this would include highlighting the top five selling products in a regional report, the bottom 15% products in a customer survey, or the top 25 salaries in a department.

Select  Home > Styles > Conditional Formatting > Top/Bottom Rules .

Enter the values you want to use, and select a format (fill, text, or border color).

You can highlight values above or below an average or standard deviation in a range of cells.

For example, you can find the above-average performers in an annual performance review, or locate manufactured materials that fall below two standard deviations in a quality rating.

Select the option you want, such as Above Average or Below Average .

Select  Home > Styles > Conditional Formatting > Highlight Cell Rules > Duplicate Values .

If you want to apply an existing formatting style to other cells on your worksheet, use Format Painter to copy the conditional formatting to that data.

Click the cell that has the conditional formatting you want to copy.

The pointer will change to a paintbrush.

Drag the paintbrush across the cells or range of cells you want formatted.

Note:  If you’ve used a formula in the rule that applies the conditional formatting, you might have to adjust relative and absolute references in the formula after pasting the conditional format. For more information, see Switch between relative, absolute, and mixed references .

Note:  You can't use conditional formatting on external references to another workbook.

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Conditional formatting compatibility issues

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

How to Use Conditional Formatting to Highlight Text in Excel

Conditional formatting in Excel is formatting that changes with the cell content.

That means you don’t have to change it when the cell content changes.

You use conditional formatting to make it easier for the user to identify key points of interest.

That could be:

• All cells that contain the name of a certain employee
• All rows regarding a certain order number

Or a lot of other things.

Follow this step-by-step guide and learn to highlight text with conditional formatting in Excel.

And a bit more😉

Download the workbook I use in this guide if you want to tag along .

Table of Contents

Highlight text with conditional formatting

Highlight numbers with conditional formatting.

• Highlight entire rows based on a formula
• Editing and removing conditional formatting

Frequently Asked Questions

The reason for highlighting text with conditional formatting is to draw the user’s attention to those cells – so they find relevant information faster🏃

You apply conditional formatting with what is called conditional formatting rules .

It’s really easy to insert one of those.

1. Select all the cells where the text you want to highlight can be.

In the sample data, I want to identify all L compatible adapters. So, I select all cells in column B (from cell B2 and down).

2. In the middle of the Home tab, click ‘Conditional Formatting’.

3. Hover your cursor over ‘Highlight Cells Rules’ and select ‘Text that Contains’.

4. In the dialog box that appears, write the text you want to highlight, in the left field.

As you type it, you can see the conditional formatting applied instantly.

The formatting being applied is the standard conditional formatting format.

5. To change the format, click the drop-down arrow in the right field and select another of the formatting presets.

6. Or you can create your own format by clicking ‘Custom format’.

7. After clicking ‘Custom Format’ the ‘Format Cells’ dialog box appears.

This is the same as the normal ‘Format Cells’ dialog box you’re using when you apply normal formatting in a spreadsheet.

Use whatever formatting style you want for your conditional formatting rule.

And that’s it!

Now, all cells containing the text “L compatible” are highlighted with the chosen formatting.

Pretty cool, right?💪

Because conditional formatting is dynamic, it changes when the data changes. So if the content of a cell changes from whatever to “L compatible” that cell gets highlighted too.

Sometimes you’re not reading all your data in your sheets, you’re just looking for specific numbers.

That can be a tedious process, especially if you have a large data set.

Let’s dive into our inventory list again🤿

If the quantity in stock of an item reaches below 100 it’s critical that you find out, so you can buy more units of that particular item.

Let’s make a simple conditional formatting rule to highlight cells.

1. Select the cells in the quantity column (cells C2 and down).

With those selected cells, we need to apply a ‘Highlight Cells’ conditional formatting rule.

2. In the Home tab of the ribbon, click ‘Conditional Formatting’.

3. Hold your cursor over ‘Highlight Cells Rules’ and click ‘Less than’.

This conditional formatting rule allows you to apply conditional formatting if the value of a cell is less than what you’re defining in a few seconds.

4. In the left field, write the number the cell value should be less than to apply conditional formatting.

As you type, you can see the format of this conditional formatting rule being applied to the selected cells.

Right now, it’s just the standard format: Light Red Fill with Dark Red Text.

5. In the right field, click the drop-down button if you want to change to another formatting preset.

6. Or you can click ‘Custom Format’ to control the formatting style 100%.

And that’s it.

Watch all the values below 100 being highlighted automatically. Beautiful, isn’t it?💕

This conditional formatting rule can be modified to other situations if you take a step back.

For instance, you can turn it upside down and highlight cells that are above 100.

Or cells with values between 50 and 150.

The possibilities are almost endless.

Highlighting entire rows based on a formula

Entire rows look nice when they are formatted in a certain way. Instead of random colored cells everywhere.

So, let me show you how to color an entire row with a conditional formatting rule if the value in column C is less than 100👀

1. Select the entire data set. The selected cells indicate how much of each row is highlighted by the conditional formatting rule.

2. Click ‘Conditional Formatting’ on the Home tab and click ‘New Rule.’

3. In the dialog box that appears, select ‘Use a formula to determine which cells to format’ from the ‘Rule Type’ menu.

4. Right below, enter this formula:

=$C1<100

So, if the value in column C drops below 100, the conditional formatting rule triggers.

You can’t refer to entire columns, so you need to refer to C1. NOT C2, although it seems more logical⚙️

If your data starts in row 3, 5, 10, or whatever other row, the reference still goes to C1 (or whatever column you’re using).

5. Now chose your formatting style from the ‘Custom Format’ button.

Or settle for the standard preset.

And behold!

All the cells in your data rows are now formatted, and not just in 1 column like they typically are with conditional formatting.

Editing and removing conditional formatting rules

The layout and use of a workbook are rarely static, so the conditional formatting that you apply to your sheet may have to be edited at some point.

For instance, the previous highlight cells rules we set… Maybe it’s time to change them.

Luckily that’s fairly easy.

You can edit any conditional formatting rule from the conditional formatting rules manager.

Edit the conditional formatting rule and formatting style

1. Click ‘Conditional Formatting’ from the Home tab and select ‘Manage Rules’.

After you click ‘Manage Rules…’ you’ll see a ‘Conditional Formatting Rules Manager’ dialog box.

Change the following 3 points as you see them on in the manager.

2. Set ‘Show formatting rules for:’ to ‘This Worksheet’ to show all conditional formatting rules for the entire sheet.

3. Find the rule you want to edit (there might be several on the list).

4. Click the ‘Edit Rule’ button.

Another dialog box appears. From here you can change anything about the rule.

Below we’ll go through the different options shown in the screenshot above.

Circle 1) Here you can change the ‘Rule Type’ . If you don’t want to apply conditional formatting when “something” happens in a cell, you can change it to apply on all cells and fill them with color based on their value compared to the average of the data.

Circle 2) If you keep the ‘Rule Type’ you can change the “trigger” of the formatting. The trigger depends entirely on which type of conditional formatting rule you choose in circle 1. In this case, you can change the custom formula.

Circle 3) From this little (familiar) box you can change the formatting that you applied in the first place. If you don’t want your cells to turn yellow when it drops below cell value: 100, you can have them turn purple instead.

As you see there are more than enough options.

But wait – there’s more!

Edit the conditional formatting rules range

All of the above is very nice, but if you accidentally selected the wrong area when you made the conditional formatting rule – then it doesn’t help to change the ‘Rule Type’ or whether the cells should be red or blue.

And if you spent 5 minutes entering a unique formula to “trigger” the formatting, then it sucks to delete it and do it all over.

To change the range of cells that the conditional formatting applies to, you don’t need to go to the ‘Edit Rule’ box above.

You just need to click in the ‘Applies to’ column at the rule you want to change in the ‘Conditional Formatting Rules Manager’ box.

If you can’t remember the range that the rule should apply to, click the tiny red arrow to select the range manually.

That’s how you edit a conditional formatting rule✏️

So if you screw one up?

You don’t have to delete it – simply just edit it.

Delete a conditional formatting rule

Sometimes you see a workbook with too many conditional formatting rules.

This can create immense confusion and on top of that, it can make your workbook slow .

In this case, you need to remove conditional formatting from the spreadsheet. Maybe even all of it!

Click the ‘Conditional Formatting’ button on the ‘Home’ tab and hover your mouse over ‘Clear Rules’.

Then click ‘Clear Rules from Entire Sheet’.

That was pretty easy. But it’s not always clever to clear all conditional formatting rules from a sheet!

Sometimes some rules should still apply.

To select specifically which conditional formatting rules are to be removed, click ‘Manage Rules…’ instead.

Here you’ll find the conditional formatting rules manager dialog box that you can use to clear rules, in addition to editing rules.

To delete specific conditional formatting rules:

• Click the drop-down arrow that initially says “Current Selection”. Change it to “This Worksheet”.
• Select the conditional formatting rule you don’t want anymore.
• Press ‘Delete Rule’ to actually clear rules.

Remember to click OK when you’re done.

And that’s how you actually clear rules✏️

That’s it – Now what?

That’s how to highlight cells with conditional formatting.

Like all the cells that contain a specific word or a number.

Heck, you even learned how to remove conditional formatting, create a new rule from a formula, and much more.

Conditional formatting is a part of Excel that can think for itself. Meaning it makes its own decisions. It “reads” the cell and reacts on the content.

There are several other parts of Excel that react to the content of cells – just like conditional formatting does.

And you most definitely want to learn those parts.

So, I’ve made a free course teaching you 2 of the best conditional functions Excel has to offer: IF and SUMIF.

Also, you learn VLOOKUP and pivot tables.

Click here to enroll in my free 30-minute course and I’ll send it to your inbox right away.

Other resources

There are several other ways of using conditional formatting.

Instead of highlighting specific cells, you can visualize high/low values in an entire data set in a few seconds with data bars , icon sets , and color scales .

Another cool feature of conditional formatting is that you can highlight duplicate values .

Or you can use this neat conditional formatting tricks to highlight every other row .

Oh, and here’s the best way to remove normal formatting , in case you want to apply new formatting.

If you’re looking for a specific answer to a specific question, please see the FAQ section below. Maybe you’ll find what you need😊

What is conditional formatting in Excel?

This is very different from normal formatting which is static and does not change unless you make the change yourself.

For example, you can make a cell change color when its content drops below 100.

Where is conditional formatting in Excel?

Select your data and click the conditional formatting button to use any of the conditional formatting rules.

Can I copy conditional formatting in Excel?

Select the cells with conditional formatting and from the Home tab, click the ‘ Format painter ‘ button.

Then select the cells where you want to paste conditional formatting.

Written by Kasper Langmann

Hi, I'm  Kasper Langmann 👋

I'm the co-founder of Spreadsheeto, a certified Microsoft Office Specialist, and a Microsoft MVP.

With over 10 years of experience, I’ve taught Excel to millions of people worldwide.

I spent over 21 hours researching and writing this tutorial.

Last updated on August 30th, 2024.

Before you go, sign up for my free Excel course (+100,000 students) ->

• Dashboard templates
• Data Charts
• Analyses & Reports
• Master Class

Conditional formatting training in Excel with examples

Conditional formatting is a convenient tool for data analysis and visual representation of results. Knowing how to use this tool will save you a lot of time and effort. A fleet glance at the document will be enough to obtain the necessary information.

Conditional formatting of dates in Excel

Select the range containing the dates.

Apply «Highlight Cells Rules»-«A Date Occurring» to it.

In the newly-opened window, you can seen the list of available conditions (rules):

Select the suitable one (for instance, for the last 7 days) and click OK.

The red fill color highlights the cells containing the dates within the past week (the date when this article was written is March 3, 2017).

Conditional formatting in Excel using formulas

If the standard rules are not sufficient for the task, the user can apply a formula. The capabilities of this instrument are limitless, so virtually any formula can be used. Let's view a simple variant.

We have a column containing numbers. We need the cells with even numbers to be highlighted with a color. We will use the formula: =MOD(A1,2)

Select the range containing the numbers and open the «Highlight Cells Rules» menu. Select «New Rules». Click «Use a formula to determine which cells to format». Fill in the box as follows:

Click Ok to close the window and view the result.

Conditional formatting of the row by a cell's value

The task is to highlight the row containing a cell with a certain value.

The exemplary table:

We need to highlight in red the information on the projects in progress («Underway»). For the completed projects' data («Completed»), the green fill in color should be applied.

Select the range containing the table values A2:D12. Click «Highlight Cells Rules»-«New Rule». Choose a formula as the type of condition. We will use the function: =IF().

The order of filling in the formatting conditions for «Completed projects».

Note: links to a row are absolute; links to a cell are mixed (only the column is fixed).

Likewise, set the formatting rules for the projects in progress.

In «Rules Manager», the conditions appear as follows:

The obtained result:

When the formatting parameters are set for the entire range, the condition will be fulfilled as soon as the cells are filled in. For example, let's complete Caroline project dated January 28 by replacing «Underway» with «Completed».

The highlight has changed automatically. It would have taken you a while to achieve this result using the standard Excel tools.

• Excel Formula Examples
• Create table
• Excel Functions
• Formulas and ranges
• Sort and filter
• Charts in Excel
• Pivot Tables
• Printing sheets
• Databases and XML
• Excel features
• Settings and Options
• Excel Classes
• Download Examples

Privacy Policy Contact

How To Use Conditional Formatting in Excel

Conditional Formatting is one of the most requested features of Excel. It brings your attention to what matters on a spreadsheet, making your analysis and reports are more compelling.

With Conditional Formatting, data analysts can format cells or display icons depending on the cell value’s performance against a rule.

In this tutorial, you’ll learn how to apply Conditional Formatting rules to text and number values. I’ll also talk you through using the data bars and icon sets features of Conditional Formatting.

You can learn more Excel analysis skills with our free 5-day data short course .

Here’s what we’ll cover:

• Format based on the text value
• Conditional Formatting with number values
• Manage Conditional Formatting rules
• Using data bars to measure performance
• Conditional Formatting for KPI performance

Use this Excel workbook to practise.

1. Format based on the text value

For our first example of setting up a Conditional Formatting rule, we have a list of tasks and their current status. The status is either “Not Started,” “In Progress,” or “Complete.”

We would like to automatically format the cells when the task status is set to “Complete.”

1. Select the range C3:C7.

2. Click Home > Conditional Formatting > Highlight Cells Rules > Equal To .

3. Enter “Complete” into the box provided and specify the format you want to apply. Click OK .

In this example, I have opted for a green fill color.

When a cell value is changed, the format is automatically applied or removed.

2. Conditional Formatting with number values

Let’s now look at an example of setting up Conditional Formatting rules for number values.

In this example, we have a list of product sales and we will create two rules. One rule to format values that are greater than or equal to 2500 in green, and a second rule to format values between 1500 and 2499 in orange.

1. Select the range C3:C8.

2. Click Home > Conditional Formatting > Highlight Cells Rules .

3. There is not an option for greater than or equal to, so click More Rules .

4. Select “greater than or equal to” from the list and enter 2500 for the value. Click Format and choose the format you want to apply.

The first rule is applied. Two values meet this criteria and are formatted.

You can create and apply as many Conditional Formatting rules to a range as you need, although you probably do not want more than three or four.

5. Select range C3:C8, and click Home > Conditional Formatting > Highlight Cells Rules > Between .

6. Type 1500 in the first box, type 2499 in the second box, and then select the format you want to apply.

Both rules are now applied to the range.

3. Manage Conditional Formatting rules

Excel makes it very easy to find, edit and delete your existing Conditional Rules.

1. Click any cell within the formatted range.

2. Click Home > Conditional Formatting > Manage Rules .

There is a lot of information and functionality in the Conditional Formatting Rules Manager window. Let’s explore the different elements of the window.

• The “Show formatting rules for” list defaults to “Current Selection.” We can see both rules because we clicked a cell within the formatted range.

This list can be changed to view all the rules for a specific sheet. This is very useful for finding the Conditional Formatting rules on a sheet.

• The rules are listed with columns showing the rule, format, and range that it applies to.
• There are buttons to create, edit, delete and duplicate rules.
• The two up/down arrows next to the buttons are used to change the order of the rules. If more than one rule is True, both formats are applied with the rule highest in the list applied last.

4. Using data bars to measure performance

Data bars are very useful for comparing values against each other and also measuring progress.

In this example, we have some sales values and want a visual indicator to their performance.

1. Select range C3:C8.

2. Click Home > Conditional Formatting > Data Bars .

3. There is the selection from six different gradients and six different solid fill colors. Click More Rules to see more options.

4. There are a few options in here to customize your data bar. I have opted for a lighter blue than the colors offered by default. This is because I want to see the number behind the color. Click Ok .

The data bar is applied to the selected range.

The bar covers the entire cell for the maximum value. This is how the automatic axis works that is applied by default.

You can edit this to specify custom minimum and maximum values for the bar axis.

5. Conditional Formatting for KPI performance

The icon sets feature of Conditional Formatting offers a variety of different icons such as up/down arrows, stars and traffic lights. These icons can be applied when a rule is met and offer a nice alternative font and cell formatting.

In this example, we will display an icon in cell B3 if the value is greater than or equal to 45%.

1. Select cell B3.

2. Click Home > Conditional Formatting > New Rule .

3. Click the “Format Style” list arrow and select Icon Sets .

4. Click the “Icon Style”   list arrow and choose the icons you want to apply.

5. Change both “Type”options to Number , enter “0.45” for both values and change the second icon to No Cell Icon . Click Ok .

The rule is applied to the cell. A green tick mark is shown if the value is 45% or higher, otherwise the red cross is shown.

Final thoughts

I hope this has been a nice introduction to Conditional Formatting in Excel. There’s much more it can do, especially when you use formulas for the rule.

If you’re getting to grips with Excel in your quest to become a data analyst, you can try out CareerFoundry’s free data analytics short course , which is just a taster for their month-long Introduction to Data Analytics online course and full Data Analytics Program .

Keen to learn more Excel functions? Check out the following:

• Convert text to numbers in Excel (4 methods)
• How to use the COUNTIFS function in Excel
• How to use the INDIRECT function in Excel

Conditional formatting based on another column

To apply conditional formatting based on a value in another column, you can create a rule based on a simple formula. In the example shown, the formula used to apply conditional formatting to the range D5:D14 is:

This highlights values in D5:D14 that are greater than C5:C14. Note that both references are mixed in order to lock the column but allow the row to change.

Generic formula

Explanation .

In this example, a conditional formatting rule highlights cells in the range D5:D14 when the value is greater than corresponding values in C5:C14. The formula used to create the rule is:

The rule is applied to the entire range D5:G14. The formula uses the greater than operator (>) to evaluate each cell in D5:D14 against the corresponding cell in C5:C14. When the formula returns TRUE, the rule is triggered and the highlighting is applied.

Mixed references

The mixed references used in this formula ($D5, $C5) make this rule portable. You could use the same formula to highlight cells in B5:B14 instead of D5:D14, or even to highlight entire rows based on the same logic.

Related formulas

• Conditional formatting based on another cell

• Highlight values between

• Highlight values greater than

• Highlight cells that contain

• Highlight entire rows

Related videos

• How to apply conditional formatting with a formula

• Conditional formatting based on a different cell

• How to build a search box with conditional formatting

• How to highlight rows with conditional formatting

• How to create a mixed reference

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

• Conditional formatting with formulas
• Test conditional formatting with dummy formulas
• Cool things you can do with conditional formatting
• Core Formula
• Conditional Formatting

Get Training

Quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

Using Conditional Formatting with OR Criteria in Excel

Conditional Formatting in Excel provides a great way to highlight certain cells that may be more important than others.

When combined with the OR criterion, you get added flexibility to your formatting condition options.

In this tutorial, we will see how to use conditional formatting, specifically with the OR criteria.

Table of Contents

What is Conditional Formatting in Excel?

Conditional formatting allows you to apply particular formatting to only those cells that satisfy the given criteria.

Most often it is to apply color-based formatting to highlight, emphasize, or differentiate among data and information stored in a spreadsheet.

The great thing about conditional formatting is that it lets you specify the condition or criteria for formatting in a multitude of ways. You can use it to format:

• Cells that contain duplicate or unique values
• Cells that contain a particular value
• Cells that contain a particular range of values
• Cells that return true for a particular formula

…and more.

In this tutorial, we will be applying conditional formatting using a formula. This means that Excel will decide which cells to format based on the result of a formula.

What are Logical Functions in Excel?

Logical functions like OR, AND and NOT let you carry out more than one comparison, or test multiple conditions . The function returns a logical value based on the set of conditions, which can be either TRUE or FALSE.

The OR function returns a TRUE if any of the conditions operated on is TRUE. So, you can have an OR function with the following syntax:

where condition1 , condition2 , etc. are conditions with comparison operators like =, <, <=, >, or <=. The OR function will return TRUE even if one of the conditions is true.

Example to Explain How to Use Conditional Formatting with OR Criteria

Now let us see how to combine the two concepts discussed – Conditional Formatting with OR Criteria. To explain this, we will use a sample problem.

Using Conditional Formatting with OR Criteria

When you include an OR function in a conditional formatting rule, you can highlight cells in the table that satisfy at least one of the conditions defined in the OR function.

Let us say you have a set of Employee Names , Departments , and Sales values as shown in the screenshot below:

If you want to highlight all the rows where an employee is from department B or has Sales of more than $5000, you can use Conditional Formatting with OR criteria as follows:

• Select all the cells where you want to apply cell formatting. In our case, we select the range A2:C7.

• This will open the ‘ New Formatting Rule ’ dialog box.

• This will open the ‘Format Cells’ dialog box from where you can apply whatever formatting you want to apply to the selected rows .

You can apply this method with as many conditions and as many types of conditions as you need to. You can also follow the same technique with other comparison functions like AND and NOT.

In this tutorial, I showed you step by step how to use conditional formatting with OR criteria to highlight data in a table if at least one of the given conditions is met.

I hope you can use the method explained in this tutorial for your own data and applications, to get your required formatting results.

Other Excel tutorials you may also like:

• How to Find Duplicates in Excel (Conditional Formatting/ Count If/ Filter)
• How to Highlight Every Other Row in Excel (Conditional Formatting & VBA)
• Highlight Cell If Value Exists in Another Column in Excel
• How to Hide Rows based on Cell Value in Excel (2 Easy Methods)
• How to Copy Formatting In Excel (4 Easy Ways)

Steve Scott

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

See all How-To Articles

Conditional Formatting – Multiple Conditions (And) – Excel & Google Sheets

Mel Jenkins

Reviewed by

Laura Tsitlidze

This tutorial will demonstrate how to highlight cells if multiple conditions are met using Conditional Formatting in Excel and Google Sheets.

Conditional Formatting With Multiple Conditions

To highlight cells according to multiple conditions being met, you can use the IF and AND Functions within a conditional formatting rule .

• Select the range you want to apply formatting to.
• In the Ribbon , select Home > Conditional Formatting > New Rule .

• Select Use a formula to determine which cells to format , and enter the following formula:
• Click on the Format button.

• Set a format. For example, an orange fill color.

• Click OK , then OK again to return to the Conditional Formatting Rules Manager .

• Click Apply to apply the formatting to your selected range and then click Close .

Every cell in the range selected that has a value greater than 5 and less than 10 will have its background color changed to orange.

Multiple Conditions in Google Sheets

The process to highlight cells based on the value contained in that cell in Google Sheets is similar to the process in Excel.

• Highlight the cells you wish to format, and then click on Format > Conditional Formatting .

• The Apply to Range section will already be filled in.

• From the Format Rules section, select Custom Formula .

• Select the fill style for the cells that meet the criteria.

• Click Done to apply the rule.

AI Formula Generator

Try for Free

• Excel Help Bot
• Formula Generator
• Formula Explainer
• VBA Code Generator
• VBA Code Commenter
• Excel Template Generator

Practice And Learn Excel Online For Free

Welcome to Excel Practice Online!

Now you can practice Excel everywhere! You can even practice on your mobile phone!

Every function and tool has an explanation followed by an online excel exercise which can be solved within the page itself, no need to download anything – All thanks to the amazing powers of Excel Online!

The tutorials are sorted from beginner level to advanced level. If you like this site please share it with your friends! 🙂

Tip for mobile phone users – tap twice on the cell you want to edit in order to edit it.

• Free Excel Courses and Resources
• Excel Self-Assessment Tool
• Free Excel Online Exercises
• Excel Basics – Zero to Hero
• Excel Tests
• Top 10 formulas and functions in Excel
• Vlookup – Tutorial with Example and Exercise Sheet
• Pivot Tables Tutorial
• Excel Shortcuts – Windows and Mac
• HOT! – Excel Mortgage Calculator – Calculate your mortgage payments and get the payment schedule for the entire period of the loan – Step-by-step tutorial on how to build a Mortgage Calculator in Excel.
• New! Excel Online Cheat Sheet – A Quick Guide To Excel’s Web Version
• Can’t find what you’re looking for? Suggest a tutorial here!

• Excel Basics – Start here if you are new to Excel! Learn how Excel works, how to perform basic calculations, and how to use cell references to save time and increase efficiency!
• Addition (Plus)
• Subtraction (Minus)
• Multiplication
• Excel Shortcuts for Windows – Master Excel Shortcuts to save time and increase efficiency!
• Excel Shortcuts for Mac – Learn how to make the most of Excel on your Mac!

Formulas/Functions

• SUM function – Sum multiple values in Excel
• MAX – find the maximum value in a range
• MIN – find the minimum value in a range
• COUNT – Count numeric values in a range
• COUNTA – Count numeric and textual values
• AVERAGE – Calculate average of a range
• Filtering in Excel – Learn how to filter your data using Excel’s Filter Tool
• Excel Sort – Learn how to sort your data in Excel.
• Flash Fill – Excel’s hidden gem for auto-completing data based on a pattern
• Remove Duplicates – Remove duplicate values in a single column or multiple columns!

Intermediate

Conditional.

• IF function – check if a condition is met
• NESTED IF – Multiple if conditions
• Conditional Formatting – Format Excel Cells based on criteria
• COUNTIF – Count cells in range which meet a certain criteria
• SUMIF – Sum range based on criteria
• AVERAGEIF – Calculate the average of a range based on criteria
• SUMIFS – Sum cells using multiple criteria
• COUNTIFS – Count cells using multiple criteria
• MAXIFS – Find maximum value in a range based on criteria
• MINIFS – Find minimum value in a range based on criteria
• AND/OR – Check if multiple criteria are met (Works great when combined with an IF function!)
• ISBLANK – Check if a cell is blank or not

• VLOOKUP – lookup value and return corresponding value from a table
• HLOOKUP – lookup value and return corresponding value from a table
• Hot!!! XLOOKUP – Excel’s next generation lookup function which combines the best features from VLOOKUP, INDEX MATCH, HLOOKUP and IFERROR/IFNA

Pivot tables

• Pivot Table – Quickly Analyze and Summarize your data using Excel’s most powerful tool!

Text Formulas

• LEFT, MID, RIGHT – Basic Text Functions
• HOT! – TEXTBEFORE & TEXTAFTER – Extract text before or after a delimiter using Excel’s brand new powerful functions!
• HOT! – TEXTSPLIT – Split your text into multiple cells using this super powerful new function!
• TEXTJOIN – Easily combine multiple cells using delimiter
• CONCAT – Combine range of cells without delimiter
• CONCATENATE – Combine two cells or more into one cell
• LEN – Find the length of a cell
• FIND – Find the position of a text within another text (Case-sensitive)
• SEARCH – Find the position of a text within another text (Case-insensitive)
• SUBSTITUTE – Replace text with another text in a cell/expression
• TRIM – Remove extra spaces from the text
• LOWER, UPPER, PROPER – Convert text to lowercase, uppercase and proper case
• VALUE – Convert data stored as text into values
• TEXT – Convert and format numbers into text
• Text to Columns – Quickly split a column into multiple columns using a delimiter. Bonus – Quickly change date formats or convert text to numbers!
• FORMULATEXT – display a formula in another cell as text

Date functions

• DAY, MONTH, YEAR – Extract day, month and year from a date in Excel
• DATE – Create a date from individual values
• WEEKDAY – Return the number of the day of the week
• EOMONTH – Return the date of the last day of the month based on a specific date

Index & Match lookup

• INDEX – Retrieve cell in nth position in a range
• MATCH – Find position of value in a range
• INDEX MATCH – Just like VLOOKUP, only better.

Other advanced tools

• SUMPRODUCT – Sum the products of Excel ranges
• Excel Wildcards – Advanced searching and matching in Excel
• Advanced Filter – Filter by multiple criteria in the same column, or even in different columns!

Power Query

• Combine data from multiple Excel workbooks using Power Query
• Column from Examples tool – Learn the secret to mastering Power Query without any prior knowledge!
• Unpivot columns easily using Power Query

Secret Excel Functions

This section covers Excel functions that are not available in most of Excel’s versions. These functions will unlock a new set of capabilities such as fining only unique values, sorting, and filtering – the tutorials below will help you with mastering Excel’s new functions!

• UNIQUE – Extract unique values from a range
• SORT Function – Sort range dynamically
• SORTBY – Sort range dynamically by using another range
• FILTER Function – Filter range by specific criteria
• RANDARRAY – Create an array of random numbers 
• SEQUENCE – Create a range of sequential values
• LET – Assign values and calculations to names to improve your formula’s ease of use, readability, and performance!
• HOT! – LAMBDA – The mother of all functions that will help you create amazing and powerful custom functions for your own need! 
• VSTACK – Vertically stack arrays/ranges in Excel
• HSTACK – Horizontally stack arrays/ranges in Excel
• CHOOSEROWS – Return specific rows from a range or array
• CHOOSECOLS – Return specific columns from a range or array
• TOROW – Convert a range/array into a single row
• TOCOL – Convert a range/array into a single column

Financial Functions

Learn how to use Excel to make financial calculations!

• Excel Financial Calculator – quickly calculate PV, FV, PMT, NPV, IRR
• PMT – Calculate the periodic payment amount of a loan, mortgage, or another financial instrument
• PPMT & IPMT – Find the Principal and Interest portion of a certain payment
• PV – Find the Present Value of a loan, mortgage, or any other financial instrument 

Excel Macros – VBA (Visual Basic for Applications)

• Start here – How to run your first VBA Macro in Excel without knowing VBA? 

Excel Data Sheets for Practice

Want to do some freestyle practice? Create your own Excel playground with our blank excel Worksheet!

• Excel-Online Blank Worksheet
• Excel Practice Data

How to Calculate in Excel – Excel-Online Calculators

• How to Calculate GPA in Excel
• How to Calculate BMI in Excel
• How to Calculate Density in Excel
• How to Calculate Weighted Average in Excel

Lists in Excel (Download Link Available)

• List of Numbers in Excel –  Generate sequential or custom patterns of numbers for your Excel projects.
• ABC List in Excel –  Easily create alphabetical lists from A-Z for indexing, sorting, and categorization.
• List of U.S. States   in Excel –  Access a comprehensive list of all 50 U.S. states.
• Days of the Week in Excel –  A quick list of the days of the week, essential for scheduling and planning.
• Months of the Year in Excel –  Download a list of the 12 months of the year for time-based analyses.
• List of Colors in Excel –  Explore a list of standard color names for data visualization and UI design.
• Country List in Excel –  A global list of countries for international datasets or geographic studies.
• Holiday List in Excel –  A curated list of standard holidays for planning and project management.
• List of Currencies : A list of world currencies for financial modeling or international business.

Terms and Conditions - Privacy Policy

Home » Tutorials » Conditional Formatting based on another cell

Conditional Formatting based on another cell

Learn how to use conditional formatting based on another cell and create a rule that depends on another cell value in Excel.

Conditional formatting enables us to determine highlights based on the value of a referred cell. We can modify the values arbitrarily in the referred cell. If the value changes, then the highlights of the given range (icons, shapes, custom formatting, etc.) will also dynamically change.

In the example, you want to highlight all the dates in column N when they are more than 15 days newer than the date I entered in cell M2. Select the Updated column (range N2:N24) in the sample file.

Steps to use conditional formatting based on another cell

• Click Conditional Formatting on the Home tab of the ribbon
• Click New Rule. A popup window will appear.
• Choose ‘Use a formula to determine which cells to format’.
• Enter the formula =$M2>$N$1+15
• Click the Format button and select your formatting style.

Explanation: In this expression, Excel evaluates values in column ‘M.’ M2 references the first cell in the selected range. You can apply this conditional formatting rule for this range. Use the ‘$’ symbol for M2. Press F4 twice ($N$1) to apply absolute reference because N1 is an absolute value. The conditional formatting rule always uses this cell. What does the second part of this formula ‘>$N$1+15’ mean? Every cell more than 15 days after the date in cell N1 is appropriate to our criteria!

Result: Excel will highlight cells where the date difference is more than 15 days.

How to highlight cells based on another cell?

To highlight cells based on a value in another cell, use this conditional formatting formula to the range C4:G11 is: =C4>=$K$4. Thus, in this example, you want to highlight cells in the range C4:G11 when they are greater than the value entered in cell K4.

How to highlight a column based on another column

To use conditional formatting based on a value in another column, create a rule using a formula for range D5:D14: =$F4>$E4. Excel highlights values in column F that are greater than in column E.

Additional resources :

• Stop if True rule
• Multiple conditions

Conditional Formatting with Formula in Excel (21 Examples)

How to Use Formula with Excel Conditional Formatting

In the following image, we have a very simple dataset. With Excel conditional formatting with formula, we will highlight the values that are greater than 3.

• Select the range of cells.
• Go to Home,  click on the Conditional Formatting drop-down, then select New Rule from the drop-down menu.

• The New Formatting Rule dialog box appears.
• Select Use a formula to determine which cells to format
• In the Format values where this formula is true: field, we input this formula: =B3>3

• Click on the Format command. Choose a Fill Color and click OK.

• Click OK in the dialog box.

The range on the spreadsheet was B3:B6 . It will now look like this.

Using Conditional Formatting with Formula in Excel: 21 Examples

Example 1 – format text values.

Let’s consider this dataset containing both numeric and string values in it.

• Select all the cells in the dataset excluding headers.
• Go to the Home tab on your ribbon.
• Select Conditional Formatting from the Styles group section.
• Select New Rule from the drop-down menu.

• In the Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type .
• Insert the following formula in Format values where this formula is true .

=ISTEXT(B5)

• Select your preferred format type.

• Click on OK .

Example 2 – Highlight Cells That Are Equal to Another Cell

We are going to format the values that match the cell value of E5 .

• Insert the following formula in the Format values where this formula is true .

Example 3 – Conditional Formatting in Excel Based on Another Cell

We will format a dataset based on whether it is larger or smaller than another cell’s value.

=C5>$E$5

• You can repeat the same process for the lower values and end up with a dataset like this.

Read More: How to Do Conditional Formatting Based on Another Cell in Excel

Example 4 – Conditional Formatting Using the IF Formula in Excel

We’ll determine whether the product turned a profit and highlight the cell.

• Select cell E5 and insert the following formula.

=IF(D5>C5,"Profit","Loss")

• Click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.

=E5="Profit"

Example 5 – Highlight Cells Using Multiple Conditions

Let’s go back to the first dataset. We are going to highlight all the cells that are either 5, 6, or contain the text “cat”.

• In the Edit Formatting Rule box, select the Use a formula to determine which cells to format option under Select a Rule Type .

=OR(B5=5,B5=6,B5="cat")

Read More: Applying Conditional Formatting for Multiple Conditions in Excel

Example 6 – Highlight Duplicate Rows

We are going to highlight cells in the whole rows where the whole row matches with another one.

We can see the third and sixth row fully match.

• Select cell F5 and use the following formula.

=CONCATENATE(B5,C5,D5,E5)

• Press Enter .

• Select the cell again and click and drag the fill handle icon down to fill up the rest of the cells with the formula for their references.

• Select the range B5:B10 .

=COUNTIF($F$5:$F$10,$F5)>1

• Right-click on the column header of F and select Hide from the context menu.

Here’s the sheet.

Example 7 – Highlight Cells Containing Formulas

Let’s take a look at a dataset that contains formulas to complete.

=ISFORMULA(B5)

Read More: How to Format Cell Based on Formula in Excel

Example 8 – Highlight Sales from a Particular Region

We are going to highlight sales from a dataset that belongs to a particular region.

=$D5="Arizona"

Example 9 – Highlight Column Differences

We can also highlight rows that have different columns than their adjacent ones.

=$B5<>$C5

Read More: How to Compare Two Columns Using Conditional Formatting in Excel

Example 10 – Using a Formula to Highlight Missing Values

We will use a formula to highlight missing values in conditional formatting in Excel.

=COUNTIF($D$5:$D$10,$B5)=0

Example 11 – Creating a Simple Search Box to Highlight Cells

We will put a value in cell E4, and Excel will highlight the value in the range, all with the conditional formatting with the formula method.

=ISNUMBER(SEARCH($E$4,B5))

The texts containing the word game will be marked.

If we change the value in cell E4 , the highlights will change.

Example 12 – Highlight Values That Are Lower Than Average

Let’s revisit one of the datasets from before.

=E5<AVERAGE($E$5:$E$10)

Example 13 – Highlight Values That Are Greater Than Average

=E5>AVERAGE($E$5:$E$10)

Example 14 – Find the Nearest Value Including the Exact Match

We are going to find the value that is closest to the one in cell C13 . If there is the same value in the dataset, it will highlight that cell instead of the closest value.

• We need to find the smallest difference from this value in that set of data. For that, select cell C14 and insert the following formula.

=MIN(ABS(B5:D11-(C13)))

=OR(B5=$C$13-$C$14,B5=$C$13+$C$14)

Example 15 – Find the Top 3 Values

Let’s go back to the random set of data.

=B5>=LARGE($B$5:$D$11,3)

Example 16 – Find the Bottom 3 Values

=B5<=SMALL($B$5:$D$11,3)

Example 17 – Show the Temperature with a Color Scale

We have two columns because we will use a formula to change the color based on the input in cell E5 . There is a blank row at the start of the dataset. We will change the color scheme of temperature values based on the current temperature.

• Select cell C6 and insert the following formula in it.

=IF(B6=$E$5,"",IF(AND(B6<$E$5,$E$5<B5),"",B6))

• Select the cell again and click and drag the fill handle icon to replicate the formula for the rest of the cells in the column.

• Go to Conditional Formatting from the Styles group of the Home  section.
• Hover over Color Scales .
• Select your preferred color scale.

• The temperature scale for conditional formatting based on the formula for current temperature will now be complete.

• If we change the value of the current temperature in cell E5 , the temperature scale will change accordingly.

Example 18 – Highlight Alternate Rows with Conditional Formatting

We will highlight alternate rows with a color.

=INT(MOD(ROW(),2))

Example 19 – Highlight Cells with Error

Consider this simple dataset.

=ISERROR(B5)

Example 20 – Create a Checklist with Conditional Formatting

We will make a checklist beside a set of data. With the options checked, the original data will change its format.

• Go to the Developer tab on your ribbon.
• Select Insert from the Controls group section.

• Select the Check box (Form Control) from the drop-down menu.
• Place the box in its appropriate place.

• Right-click on the box and select Format Control from the context menu.

• Go to the Control tab on the box and select cell C5 as its linked cell.

• Remove the alt text and place the checkbox in the middle of the cell.

• Repeat the process for all of the boxes.

• Once checked, there will be a TRUE/FALSE value on the cells depending on it.

• Use a white font color to make them invisible.

• Select the task range you want to format.

• Select your preferred format type. We chose a strikethrough font.

Example 21 – Highlight Weekends in a Week

We will use the OR and WEEKDAY functions to create this formula.

=OR(WEEKDAY($B5)=1,WEEKDAY($B5)=7)

Download the Practice Workbook

Conditional Formatting with Formula.xlsx

Conditional Formatting with Formula in Excel: Knowledge Hub

• Format Cell Based on Formula
• Use Conditional Formatting Based on VLOOKUP
• Apply Conditional Formatting with INDEX-MATCH
• Excel Conditional Formatting Formula with IF
• Apply Conditional Formatting Formula in Excel If Cell Contains Text

<< Go Back to Conditional Formatting | Learn Excel

What is ExcelDemy?

Tags: Excel Conditional Formatting

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF