*The tutorial explains the basics of Excel conditional formatting feature. You will learn how to create different formatting rules, how to do conditional formatting based on another cell, how to edit and copy your formatting rules in Excel 2007, 2010 2013, and 2016.*

**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 identify variances of cells' values with a quick glance.

At the same time, Conditional Formatting is often deemed as one of the most intricate and obscure Excel functions, especially by beginners. If you feel intimidated by this feature too, 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 short tutorial.

## The basics of Excel conditional formatting

The same as usual cell formats, you use conditional formatting in Excel to format your data in different ways by changing cells' fill color, font color and border styles. The difference is that conditional formatting is more flexible, it allows you to format only the data that meets certain criteria, or conditions.

You can apply conditional formatting to one or several cells, rows, columns or the entire table based on the cell contents or based on another cell's value. You do this by creating rules (conditions) where you define when and how the selected cells should be formatted.

### Where is conditional formatting in Excel?

For a start, let's see where you can find the conditional formatting feature in different Excel versions. And the good news is that in all modern versions of Excel, conditional formatting resides in the same location, on the **Home** tab > **Styles** group.

#### Conditional formatting in Excel 2007

#### Conditional formatting in Excel 2010

#### Conditional formatting in Excel 2013 and Excel 2016

Now that you know where the conditional formatting feature is located in Excel, let's move on and see what format options you have and how you can create your own rules.

## How to create Excel conditional formatting rules

To truly leverage the capabilities of conditional format in Excel, you have to learn how to create various rule types. This will help you make sense of whatever project you are currently working on.

Conditional formatting rules in Excel define 2 key things:

- What cells the conditional formatting should be applied to, and
- Which conditions should be met.

I will show you how to apply conditional formatting in Excel 2010 because this seems to be the most popular version these days. However, the options are essentially the same in Excel 2007, 2013 and 2016, so you won't have any problems with following no matter which version is installed on your computer.

- In your Excel spreadsheet, select the cells you want to format.
For this example, I've created a small table listing the monthly crude oil prices. What we want is to highlight every drop in price, i.e. all cells with negative numbers in the

*Change*column, so we select the cells C2:C9.

- Go to the
*Home*tab >*Styles*group and click**Conditional Formatting**. You will see a number of different formatting rules, including data bars, color scales and icon sets. - Since we need to apply conditional formatting only to the numbers less than 0, we choose
**Highlight Cells Rules > Less Than...**

Of course, you can go ahead with any other rule type that is more appropriate for your data, such as:

- Format values greater than, less than or equal to
- Highlight text containing specified words or characters
- Highlight duplicates
- Format specific dates

- Enter the value in box in the right-hand part of the window under "
*Format cells that are LESS THAN*", in our case we type 0. As soon as you have entered the value, Microsoft Excel will highlight the cells in the selected range that meet your condition. - Select the format you want from the drop-down list. You can choose one of the pre-defined formats or click
**Custom Format...**to set up your own formatting.

- In the
*Format Cells*window, switch between the**Font**,**Border**and**Fill**tabs to choose the font style, border style and background color, respectively. On the*Font*and*Fill*tabs, you will immediately see a preview of your custom format. - When done, click the
*OK*button at the bottom of the window.

**Tips:**

- If you want more background or font colors than the standard palette provides, click the
**More Colors...**button on the*Fill*or*Font*tab. - If you want to apply a
**gradient background color**, click the*Fill Effects button*on the*Fill*tab and choose the desired options. - Click
*OK*to close the "*Less Than*" window and check whether the conditional formatting is correctly applied to your data.

As you can see in the screenshot below, our newly created conditional formatting rule works right - it shades all the cells with a negative price change.

#### Creating an Excel conditional formatting rule from scratch

If none of the ready-to-use formatting rules meets your needs, you can create a new one from scratch.

- Select the cells to which you want to apply the conditional format and click
*Conditional Formatting > New Rule*.

- The
*New Formatting Rule*dialog opens and you select the needed rule type. For example, let's choose "**Format only cells that contain**" and opt to format the cell values**between 60 and 70**.

- Click the
**Format...**button and set up your formatting exactly as we did in the previous example. - Click
*OK*twice to close the open windows and your conditional formatting is done!

#### Excel conditional formatting based on cell's value

In both of the previous examples, we created the formatting rules by entering the numbers. However, in some cases it makes more sense to base your condition format on a certain cell's value. The advantage if this approach is that no matter how that cell's value changes in the future, your conditional formatting will adjust automatically and reflect the data change.

For instance, let's use the "Oil price" example again, but this time highlight all the prices in column B that are greater than February's price.

You create the rule in a similar fashion by selecting **Conditional formatting** > **Highlight Cells Rules > Greater Than...*** *But instead of typing a number in step 4, you select cell B6 by clicking the range selection icon as you usually do in Excel. As the result, the prices get formatted as you see in the screenshot below.

This is the simplest example of Excel conditional formatting based on another cell. Other, 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 in this article: How to change a cell color based another cell's value.

This is how you do conditional formatting in Excel. Hopefully, these very simple rules we have just created was helpful to understand the general approach.

## Apply several conditional formatting rules to one cell / table

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

For example, let's create 3 rules for the weather table that will shade temperatures higher than 60 °F in yellow, higher than 70 °F in orange, and higher than 80 °F in red.

You already know how to create Excel conditional format rules of this kind - by clicking *Conditional Formatting > Highlight Cells rules > Greater than*. However, for the rules to work correctly, you also need to set their priority in this way:

- Click
*Conditional Formatting > Manage Rules...*to bring up the*Rules Manager*. - Click the rule that needs to be applied first to select it, and move it to the top using the
**upward arrow**. Do the same for the second-in-priority rule. - Select the
**Stop If True**check box next to the first 2 rules because you do not want the other rules to be applied when the first condition is met.

### Using "Stop If True" in conditional formatting rules

We have already used the **Stop If True** option in the example above to stop processing other rules when the first condition is met. That usage is very obvious and straightforward. Now let's consider two more examples where the use of the *Stop If True* function** **is not so obvious but also very helpful.

#### Example 1. Show only some items of the icon set

Suppose, you have added the following icon set to your sales report.

It looks nice, but a bit inundated with graphics. So, our goal is to keep only the red down arrows to draw attention to the products performing below the average and get rid of all other icons. Let's see how you can do this:

- Create a new conditional formatting rule by clicking
*Conditional formatting > New Rule > Format only Cells that contain*. - Now you need to configure the rule in such a way that it gets applied only to the values greater than the average. You do this by using the
*=AVERAGE()*formula, as shown in the screenshot below.**Tip.**You can always select a range of cells in Excel using the standard range selection icon or type the range inside the brackets manually. If you opt for the latter, remember to use absolute cell references with the $ sign. - Click OK without setting any format.
- Click
*Conditional Formatting > Manage Rules...*and put a tick into the**Stop if True**check box next to the rule you have just created. And... see the result in the screenshot below : )

#### Example 2. Remove conditional formatting from empty cells

Suppose you created the "Between" rule to highlight cells' values between $0 and $1000, as you can see in the screenshot below. But the problem is that empty cells are also highlighted.

To fix this, you create one more rule of the "*Format only cells that contain*" type. In the *New Formatting rule* dialog, select **Blanks **from the drop-down list.

And again, you simply click *OK* without setting any format.

Finally, open the *Conditional Formatting Rule Manager* and select the **Stop if true** check box next to the "Blanks" rule.

The result is exactly as you would expect : )

## How to edit conditional formatting rules in Excel

If you've had a close look at the screenshot above, you probably noticed the **Edit Rule...** button there. So, if you want to change an existing formatting rule, proceed in this way:

- Select any cell to which the rule applies and click
*Conditional Formatting > Manage Rules...* - In the
*Conditional Manager**Rules Manager*dialog, click the rule you want to edit, and then click the**Edit Rule...**button.

- Make the required changes in the
*Edit Formatting Rule*window and click*OK*to save the edits.The

*Edit Formatting Rule*window looks very similar to the*New Formatting Rule*dialog you used when creating the rule, so you won't have any difficulties with it.**Tip.**If you don't see the rule you want to edit, select**This Worksheet**from the "*Show formatting rules for*" drop-down list to display the list of all rules in your worksheet.

## How to copy conditional formatting in Excel

If you want to apply the conditional format you have created earlier to other data on your worksheet, you won't need to create the rule from scratch. Simply use **Format Painter** to copy the existing conditional formatting to the new data set.

- Click any cell with the conditional formatting you want to copy.
- Click
**Home > Format Painter**. This will change the mouse pointer to a paintbrush.

**Tip.**You can double-click*Format Painter*if you want to paste the conditional formatting in several different ranges of cells. - To paste the conditional formatting, click on the first cell and drag the paintbrush down to the last cell in the range you want to format.

- When done, press
*Esc*to stop using the paintbrush.**Note.**If you've created the conditional formatting rule using a formula, you may need to adjust cell references in the formula after copying the conditional format.

## How to delete conditional formatting rules

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

- Open the
*Conditional Manager**Rules Manager*(as you remember, you open it via*Conditional Formatting > Manage Rules...*), select the rule and click the**Delete Rule**button. - Select the range of cells, click
*Conditional Formatting > Clear Rules*and choose one of the available options.

Now you have basic knowledge of Excel conditional format. In the next article, we will focus on more advanced features that will help you push conditional formatting in your spreadsheets far beyond its traditional uses.

And in the meantime, you may want to check out a few more examples of Excel conditional formatting:

How can I get the total in two cell's to reduce when I add an amount in another cell?

For example cell H7 is at 100 and cells M7 and N7 are at 20. When I make cell H7 105 how can I automatically get cells M7 and N7 to reduce to 15? The cells are in hours and minutes.

which formula we used in condition formation to coloring the amount is greater than 30 and less than 61

Hello,

You need to use the following formula:

=AND($A2>30,$A2<61)

Hi,

Could you tell me how to use conditional formatting to turn one cell yellow 5 minutes before to five minutes after the time "now()" listed in second or different cell?

How to I highlight a column cell if the minute value is within 15 minutes of another column cell?

Example:

C4 is 9/30/16 12:11

F4 is 9/30/16 12:23

C4 is only 11 minutes before F4 and needs to be highlighted.

Thanks

Hi

why my file cannot save the custom conditional formatting?

Hi I excel sheet if there are 10 employees I'd in column and in other excel sheet i want the employee I'd twice. Is there any formula for same

how to running value control via condition formatting a1-10then b1 100,c1 200 but my total is 300 i want a1 greater then b1 then highlight b1

IN EXCEL ONE COLUMN MATERIAL SEND DATE,NEAREST COLUMN DATE TODAY ,NEXT COLUMN COUNT DATE FROM 1 TO 2 COLUMN,NEXT COLUMN DAYS>180 MEANS INDICATES RED

DAYS<180 MEANS INDICATES GREEN

NEXT COLUMN ACTUAL DATE OF RECEIVING,NEXT COLUMN STATUS OPEN/CLOSED(IF MANUAL ENTER ACTUAL DATE OF RECEIVING MEANS)THAT AUTOMATICALLY COMES CLOSE THEN DATE FORMULA WILL NOT UPDATE FOR THAT PARICULAR ROW

I have at the top today date

I have around 30 rows where i have typed different dates. I want to highlight if the row date is less than today date with red font. Kindly help me how to do this.

Regards,

Guddappa nadiger

884,00

125,00

4,15

4,15

1,00

9,46

1,56

1,13

25,00

3,75

1,00

1,00

1,13

5,00

6,88

2,36

1,06

2,55

2,48

6,16

7,50

1,88

102,50

168,75

0,37

0,11

0,92

2,31

7,50

0,46

1,13

3,75

6,50

3,40

1,31

26,25

1,31

1,75

25,00

3,90

hi above value which is i want to sum but the value occurs always zero,please get me solution

Put these values column A

use below formula and drag down

=VALUE(SUBSTITUTE(A1,",",""))

then Use Sum function

I want a cell to turn red base on a response in a combo box. The formatting works when it is in relation to any other cell or even a drop down list, but it seems as if it is not recognizing the combo box.

I have created a sales sheet with description,price,quantity,and total with 5 different product that I sell. Now I want to create another, where when I sell any item the system will automatically calculate the total of so I can save. Thus POS but I will not print a receipt

I have some numbers,with seperate cells. if i select one digit, automatically

that shows how many same digits in that page. with Bold & Colored. ?

please

Hello experts!

I want a conditional formatting in following context:

1. I have 6 columns and more than 30 rows.

2. Column A contains dates in English & column B contains dates in local language.

3. Column C contains days as "Sun", "Mon"......

Here, I need a conditional formatting of cells from Column A through Column F if column C contains "Sat". This condition should apply even in blank cells where I need "cell fill color".

Problem: I can fill color in the cell containing "Sat" but I need the same in entire row from column A up to F.

Can somebody help me out?

Thank you

Hello Bhagirath,

You need to create a rule with a formula, here are the steps:

- Select the range with your data, e.g. A2:F100

- Click on "New Rule" under Conditional Formatting and select the last option: "Use a formula to determine which cells to format"

- Pick format for the rows and enter the following formula:

=$C2="Sat"

This blog post describes how to format entire rows based on values in certain cells:

https://www.ablebits.com/office-addins-blog/2014/06/10/excel-conditional-formatting-formulas/

Hello,

I have a worksheet of inventory items. The first column is the stock number the second is the name of the items. The third one is how many we have received. The other columns are items that went to a project, we have approximately 13 projects and a formula for the last two columns which have what has been issued and what is available. The problem arises when my boss wants to lock the third column C which has the number of items purchased that has been sent to us. I have tried using the page protection function, but this locks the whole page not just the items in column C. I need the rest of the page to function while locking a single column. Is that possible and if so how do I do it?

Hello Ray,

You can lock any range in your sheet, this feature is quite flexible in Excel. Please see detailed steps in this support article.

Hi,

I need to give me a reminder or change color of the vehicle ID colomn as soon as it reaches the oil change mileage. I have to develop a worksheet for oil change of fleet of vehicles. The oil change is based on current mileage to next mileage which is (Current Mileage + 7000). Would you help me out with this one.

THanks

Hello Afaq,

Could you please describe the structure of your table in more detail? Do you have columns with the IDs, current mileage, and mileage of the last time oil was changed? We'll do our best to assist you.

Hi there,

I have list of codes in tab 1 (approx 100). I want to format the colum A in tab 2 that if I type the code other than mentioned in tab 1 than it should highlight.

Will conditional format will work on this occasion?

Regards,,

Hi Sandeep,

Sure, you can use the following formula for your Conditional Formatting rule to highlight values that differ:

=AND($A2<>"",COUNTIF($A2, Sheet1!$A$2:$A$150)=0)

You can also compare the date with Duplicate Remover add-in to find unique values in your second sheet.

Hello, I have a large spreadsheet with columns of data (example, K 39,041.00, L 34,584,.25, M 26, N 39,470.04)and would like to highlight cells in both column K & N if column N is greater than column K. I would like to do the same thing (different color) if column L is equal to column K. Can I accomplish this through Conditional Formatting?

Thanks in advance,

Jeff

Hi,

I am working on a spreadsheet which is to show delays in projects. I am using conditional formatting formulas to change the colour of the cell directly beneath the planned week number.

Is there a way to copy this format so that the reference cells are automatically updated as they would be if a copying a normal formula?

Thanks

Steve

Hi

I have a column that I want to apply data bar conditional formatting to, however when I apply the formatting it doesn't appear. The data is calculated from other cells and contains formulas. Kindly assist

Regards

I want to implement alternating rows conditional formatting only if a field in Column A (A5:A500). Please help how I can achieve that? The formatting that I want to include is adding a border and filling the cell with a colour. Cheers.

Thank you Irina.

I want use to Conditional formatting

If A Column is "Sunday",

Then B Column Fill Red color. Is this possible

Is it possible to get copies of the workbooks you use in your conditional formatting articles so I can practice?

please send your mail id. i want to send a spread sheet to you. That's my worksheet for deviation statement for govt sector. i need your hepl to fixing the formula in this worksheet. thanking

I have a column with data in it with 3 rules set up. The rules are if the value is less than 80, highlight the cell in red, if the value is between 80 and 89, highlight in yellow and if the value is between 90 and 100, highlight the cell in green. Everything works as it should. I would like to know if it is possible to create another conditional format rule for all cells to the right of my column with values in it that will highlight the cells for each line of data to change color based on the data column?

I only want to input values in the one column all the way to the left. The rest of the columns to the right only change color based on that column of cells.

Hi Svetlana

I used your instructions for conditional formatting, which worked wonderfully, thank you! However, I'm now stumped as to how to copy these colours over, I will try to explain:

Used the formatting to fill colour in cells of varying "greater than" amounts relating to an average value which is based on a figure entered each week. So the names of people are down column A and each column after has their weekly score. Column AA has the average, which is coloured accordingly. I'd like to have the cell containing the name of each person to automatically fill with the same colour as the one containing their average score.

I am a complete novice at this, hope you can help :)

Good day. Please assist me as i have to insert an IF formula for an evaluation tool. I have an overall average calculated for the tool however i require an IF formula that states if a certain criteria = 0 then the overall evaluation average should =0.

Hy Svetlana

Today I Read Your excel formula where i am very pleasant because you solve many people problem i need some help

example

i create timing report

( Like )

Real In Time: Employ In Time (Remarks)

9:00 9:25 late

9:00 9:28 late

9:00 9:18 OK

9:00 9:22 late

i want formula which show automatically status if employ 20 mint late come

Hi team

i have a doubt regarding the average of values, for suppose if we have two trials TS1 and TS2, in which i have got TS1 is 0.08% and TS2 is 0.03%, the average we required is from the values which are >0.05% it means the final value from above is 0.08%. for this i have kept a command that =IF(AVERAGE(D14,F14)<0.05,"<0.05",AVERAGE(D14,F14)). for suppose the values for TS1 and TS2 both are <0.05% it means if TS1 is 0.03 and TS2 is 0.04 then the Average value to be displayed is <0.05. but iam not able to get it, plz anyone can help me out.....

hi

I need to be able to register in two cells the highest and lowest negative and positive results from a changing portfolio total in order not to have to manually monitor and record these myself. I am a complete novice when it comes to formatting cells so a simple abc approach would be very much appreciated.

Many thanks

Hi, David,

let's say that your totals are in the 7th row (A7:G7). And you have two other cells where you want to see the highest and lowest results. So, for the lowest one you put the next formula in the cell:

=MIN(A7:G7)

(where A7:G7 is your range of the results)

For the highest one, enter another formula in another cell:

=MAX(A7:G7)

Every time the results change, these formulas will adjust the highest and the lowest numbers.

If you need to find the lowest AND the highest for negative results, the lowest AND highest for positive ones, let us know what Excel version you're currently using.

Natalla

Many thanks for your advice, much appreciated.

I may not have explained the problem correctly. I have one cell with an ever-changing total, both post and neg, which I record in two other cells, one recording the highest positive and the other the highest negative. I would like to be able to record these automatically so need the formula for each cell to do that.

I am using Excell for Mac 2011 version 14.7.3

Apologies for any misunderstanding and thanks once again

Hi,

Can someone please help me. I need to do conditional formatting on values that are on different worksheets.Cell O5 in worksheet A if it's found in Cell I in worksheet B to be highlighted as whatever colour.

Many thanks

Hi,

i want to learn how to make alert(thru highlight) when the stocks was below safe level. i was able to find out how for 1 row but my problem was how to do it if i'm monitoring 1 thousand items wherein i don't have to do it 1 by 1. 2nd, is it possible to highlight the entire row?

example

item on hand safety stock

a1 10 15

a2 11 14

....

a1000 12 10

Hi, Edilberto,

if С column indicates safety stock and it should not (ideally) exceed the number of items on hand (column B) then the formula for conditional formatting rule is:

=$C1< $B1 and it applies to =$A:$C. It will also highlight the raw if C is empty. If you want something other than that, please, specify. Also, here is a great tutorial on how to highlight an entire raw.

hi

i have series of rows employee wise , with character p entered for present on applicable dates in a month , i want to highlight the column where p consecutively appears in a row 10th time

How to Get? - If amongst 4 cells if I put 1 in any of the cells, rest of the three cell should be showing 0. How to do that?

Which formula to use?

Hello!,

I am designing a hitmap using conditional formatting and I would like not to show the value in the cell. I have tried modifing the format of the cell by writting the comand ;;;; in the costum blog of the cell format... but does not work... any idea on how to "Hide" the cell value?

Thanks

I'm trying to make a timecard that calculates both regular and overtime hours. I want to make it where when I reach a total of 40 hours in regular time it stops entering data in the cells in the regular time column and any time over 40 hours will then start showing up in the cells in the overtime column.

I apologies if this doesn't make sense

Hello

I need help.

I want to see only one cell in which conditional formatting applied on the basis of value entered in the cell.That means if i enter 10% then 10% of the cell will coloured, if i enter 50% then 50% will be colored,if i will enter 100% then full cell will be coloured.(This is for only one cell).

Thanks in advance.

Hi

i have names lised in a column 1-15.

What i am trying to do is is, that if one of those same names appears in another cell in the same column, that the original name in the column 1-15 disappears and turns a different colour?

Is this possible through conditionl formatting?

Ray

im building 5 town houses, i owe the bank 2.5 million

when i have sell for 5 townhouses lets say 600- 700 thousand each

in excel i got

2.5m in cell B3

every time i sell a house i enter the price and it deducts from B3

lets say i have 300 thousand left to pay off showing in B3

i enter 400 thousand i want the B3 to be zero and what is leftover goes to a different cell

hope this makes sense

Hi Guys, Can someone help me please.

i need help with an example below.

i want in column x. 13/06/05 , 14/08/16, 25/07/17

in column y i want it to turn red if nothing is filled in a week after the dates in column x.

Can someone please help

Hello,

I've been trying to make this work. I want that every time I have a negative number in column L, the whole row where the number is change the font to red. I can make it work for just one row but when I try to implemented in the worksheet is when I got stuck. In the conditional formatting option I select the "Use a formula..." option. I type =$L:$L<0, But it doesn't work. when I just select a specific cell (i.e. =$L$145), it works but that's not what I am looking for. On the other part I just type the rows I want to be affected by this (=$A:$R,$T:$AD).

Any help regarding this matter, I really appreciate it.

Thank you!

I have a training spreadsheet with expiry dates. I want to use traffic lights to turn the cells red, amber or green as they approach the date of expiry compared to todays date, in months. eg due in 12 months or more green, due in 6 months or more amber and less than 6 months red. Is there an easy way to do this in Excel 2010 please. I have formatted the date as eg Jan 2017, Mar 2019 to make it easier. Thanks

How do I get excel to calculate the difference in dates in days?

Thank you

Kenny

Hi Kenny,

You can use the DATEDIF function with the "d" unit, as shown in this example:

How to calculate difference between 2 dates in days

R/MAM,

GOOD NOON,

PL NOTE THAT A1 CELL HAS A VALUE "X", I WANT TO HIGH LIGHT CELL B1 WHEN IT'S VALUE LESS THAN 10% OF CELL A1.

IN FORM OF MATHS

CELL B1 SHOULD BE HIGHLIGHT WHEN

A1 = < A1-(A1*0.1)

HOW CAN I DONE BY CONDITIONING FORMATTING FACILITY?

REGARDS,

MONIK

KINDLY IGNORE A1 =.

THANKS

Hello Friends!

I writing the figure 624 in a cell G6 and in cell H6 I writing =G6*1% in this case I got the correct answer, but I intend to get formula for multi figures in h6 like this =g6*1% with the new answer *10% and further with new answer *12% in just a single cell as H6. what I can do for the same. Guide me someone please.

M. Munir

Hello Experts..

I have a table in which I already inserted formula & conditional formatting to get the result.

Example:- In column O3 I have mentioned Expiry Dates & in column P3 I have mentioned =O3-TODAY() & inserted conditional formatting to know remaining days to get expired with Less than value using colour format. (Like, colour to be change as yellow if days remain less than 60 & Red if days remain less than 30) it is working.

But What I want is if the expiry date has not mention in column O3 then Column P3 should be blank without anything.

Sir/Mam

I have 2 question.

1:- When I will enter date in shhet2 A2 column then the out put will show in sheet2 B2 column. My B2 column formula is =If(isblank(A2),"Please enter Date",if(isnumber(A2),vlookup(A2,Sheet1!A:C,2,0),"Invalid Date")).

So I want, when will show "PLEASE ENTER DATE" in B2 then the font colour will be GREEN. When "INVALID DATE " will show then the font colour will be RED.And the result of vlookup font colour will show YELLOW.

2:- The second question is- I put the formula in B6 =if(A6="Sun","Ashok,Deepak",if(A6="Mon","Rajesh,Ramesh",if(A6="Tue","Prakash,Dillip",""))).

So I want when the name will show then the font colour will be blue.

so please tell me how to solve it by conditional formating.

How can we add value of two cell but show in one cell

For exmpl

A1 cell value is 10

B1 cell value is 20 total 30(a1+b1)

But these values calculated in b1 cell not another cell. Plz reply

Hi,

I'm sorry, but it's impossible to show the number AND the sum of numbers in a cell at the same time.

Hello Dear Experts..

I have a table in which I already inserted formula & conditional formatting to get the result.

Example:- In column O3 I have mentioned Expiry Dates & in column P3 I have mentioned =O3-TODAY() & inserted conditional formatting to know remaining days to get expired with Less than value using colour format. (Like, colour to be change as yellow if days remain less than 60 & Red if days remain less than 30) it is working.

But What I want is if the expiry date has not been mention in column O3 then Column P3 should be left blank without any text.

Awaiting for your response

Hello,

you can add IF function to your formula in P3:

=IF(ISBLANK(O3),"",O3-TODAY())

It will remain empty if there's nothing in O3, otherwise calculate the date.

Please read this article to learn why and when IF can be used.

Hope this is what you need.

Yes it's working, Thank you very much

Hello,

I'm trying to conditionally format cells ONLY if the cell does not contain a formula and the cells is >0. I have a data set that pulls data from different places and I want to be able to easily tell when something is hard coded as long as it's greater than zero.

Hello,

Perhaps someone might be able to help me. I'm an intermediate excel user but haven't extended much past simple formulas.

I am working on a project with a column dedicated to using 3-4 single letters to represent different words. Is there a way to conditionally format so a blank column would automatically change the font color given the letter imputed into the cell?

i.e. C=Card, B=Basket, K=Keep Brunch,F=Flowers, N=Nothing

C=orange

K=Blue

B=Purple

N=Red

Thanks so much!

Hi

Im trying to add a conditions into a excel sheet to highlight the cell in chosen colour.

EG

if figure is cell A is equal to or more than 25% of the figure in Cell B, then colour cell A Red, otherwise colour cell A Green.

are you able to help me with a simple formular. I am using excel 2016

thank you

I cant seem to work this out, I know its simple but my brain is fried.

I need to use conditional formatting to say Target met if there has been an increase of 5% in 2015 from 2014 and improvement needed if there has not been a 5% increase. Its the % that are confusing me. Thanks

2014

Strongly Agree

A2 0.93

A3 0.97

A4 0.92

A5 0.93

A6 0.95

A7 0.89

A8 0.82

2015

Strongly Agree

A13 0.91

A14 0.91

A15 0.9

A16 0.89

A17 0.88

A18 0.8

A19 0.75

Hi

I have table shown below

no low high b/s present

01 105 125 b 105

02 252 183 s 183

03 453 522 s 522

In need a formula for present column in such that if b/s column contain "b" then present column value should be low column otherwise if b/s column contain "s" then the value of present cell should be high . could you please give me the formula for the above case

Privet vsem!

Sorry for disturbing you, but I am a newbie to Excel fomatting and need some help. I have an excel sheet where I keep the profiles of people(name, last name, performance, ....). I do have multiple copies of the same person(let's call him Andrey Goncharov) on my sheet as I am copying it from another source (every week). So last week I marked Andrey's "Performance" column as a "Top Performer". When I copy (from a different source) again this week, if there is Andrey Goncharov, I want Excel to auto-fill the "Performance" column of a new copy as a "Top Performer", because I already have done before for that person. I hope I could explain my issue.

Thanks a lot for your help and support, you are a wonderful team :).

Ben

Hello, Ben,

I'm sorry, it's difficult to come up with any suggestion, since we don't know how your data is stored and there are too many peculiarities that can affect the way your task should be solved.

If you still require our assistance, feel free to send us your example workbook with the source information and the result you want to get to support@ablebits.com. Please include the link to this comment into the email.

Our technical specialist will take a look at your task and try to help.

Hi all,

My problem is probably very basic in comparison with all of yours but i am not an excel bizz and I couldn't find the resolution in the above comments or tutorial...

At work i have created a spreadsheet for all our new clients and all of the checks and ID they have top provide etc. before we can start working for them. When all of the checks have been passed and they have supplied sufficient information about themselves for our records the date of completion is put in the end column. How can I make this cell with the date in turn red after a year of the date? As this is when we have to request new up-to-date information from them... Any help would be greatly appreciated asap!

Hi,

I want to keep conditional formatting in dates for vehicles record. when i enter the current it should check last 6 month records. if it's there will change automatically.

Hello,

I am trying to create a training document. The document will contain several employees, each having a date they were certified, and then a date that they will be due to re-certify. I am trying to figure out the formulas that I would need to input in order to change the row either red=overdue, yellow=due within 90 days, and green=91days>. The formula that seems to be closest to what I need is Rule1=datedif($A2,Today(),"d")>=1:89,Rule2=datedif($A2,today(),"d")>=90:275, and Rule3=datedif($A2,today(),"d")>=276 but for some reason the rules are keeping all three colors from showing. could you please point me in the right direction and thank you for your time.

Hello, Ian,

Please try the following formulas:

Rule1=AND(DATEDIF($A2,TODAY(),"d")>=1,DATEDIF($A2,TODAY(),"d")<=89)

Rule2=AND(DATEDIF($A2,TODAY(),"d")>=90,DATEDIF($A2,TODAY(),"d")<=275)

Rule3=DATEDIF($A2,TODAY(),"d")>=276

Hope it will help you.

Hi,

I am trying to highlight number of cell with the number i keyin, what is the formula for this situation?

Thanks

Hi,

I am not sure if it has been mention above but I am stuck with a formula.

I want the following: If $X2 is not blank / empty and $W2 has a negative value (loss) then the entire row goes red. If $W2 has a positive value (revenue) then the entire row goes blue.

I have tried few things but I don't know. I have tried this for example =IF($W2<0,ISBLANK=$X2=FALSE)

Thanks in advance :-)

Hi -

I want to trigger a cell to change color depending on the % in another cell. I have been able to create rules for 0% in T8 to trigger U8 Grey, 80% T8 to trigger U8 orange, but when I add 100% T8 to trigger U8 Green it doesn't work and still triggers orange. Can you let me know what I am doing wrong?

Thanks for any and all your help!!!

Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

Please also don't forget to include the link to this comment into your email.

I'll look into your task and try to help.

Kindly anybody has Idea, I have one text cell in Excel, who to divide that text into tow cells ( I have 7000 full text cells with full text and I want to make the cells less high with transferring half the cell content to new cell in between the existing full cells) unfortunately this web has no facility to past a screen shot to attach to explain in an example what I want to do

Correction

Kindly anybody has Idea, I have one text cell in Excel, hwo to divide that text into two cells ( I have 7000 full text cells with full text and I want to make the cells less high with transferring half the cell content to new cell in between the existing full cells) unfortunately this web has no facility to past a screen shot to attach to explain in an example what I want to do, Thanks in adv. and happy new year

Hello,

I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

Sorry I can't assist you better.

Hello i have a work list sheet with multiple items on then i have a date type in two different columns i have a 90 day wjndow from the date to complete the work by the date i was trying to figure out how to have the celss turn red after the 90 is past

Hello,

I am trying to highlight cells in column C based on the second digit in a 10-digit code - I want each cell in that column highlighted one color if that digit is N, another color if that digit is not an N. I assume I will need two rules, and am trying to use the formulas =mid(c2,2,1)="N" and =mid(c2,2,1)"N". When I tested the formulas in a blank column the "true" and "false" come up correct, but when I put them into a Conditional Formatting rule, they are not highlighting the correct cells. Any suggestions?

how do you compare figures that are in the same raw. e. g show how the deposits made perform against each other.

How To Highlight Row If Cell Contains Text/Value/Blank In Excel

Thank you very much for these tutorials, they are so useful!!!!

Hey,

Can someone help me with the below one ?

A reports to B and B reports to C and c reports to D and so on hierarchy..

Now there are certain expectations wherein A reports directly to D .. in case I want to pull n represent this as per hierarchy how should I do it ?

Endly, let’s assume 5000 employees hierarchy is X and 3000 hierarchy is Y how should we add them up together

how can i add 2 cell the second cell will petch or getch the data of the first cell and after adding it the 1 cell will reset to zero and the second cell will stay its value

for example

1+0=1 then after the sum it will be like this

0+1=1 then i will input new no. to cell 1

2+1=3 the the the second cell will show the total and the 1cell will be reset to 0

I want to set the conditions of a cell to:

today's date - 180 days turn red

today's date - 135 days turn orange

today's date - 90 days turn yellow

today's date - 75 days turns green

Can anyone help?

Josh:

You've asked to apply four different conditional formats based on four different conditions, so you'll need four rules all applied to the same range of cells.

Select the range of cells to which you want to apply the rules.

Select Conditional Formatting, select Highlight Cell Rules, then More Rules, then select Use Formula to determine which cells to format.

Where the range is H48:H51, in the field that is displayed enter this formula:

=AND(H48>TODAY(),H48=(TODAY()+75))

Notice that the formula references the top left cell in the range H48:H51. Your range and top left cell is probably different, but this formula must reference the top left cell in the range.

Notice that this formula will apply the formatting to the date that is 75 days from today.

The H48>TODAY() ensures the date in H48 is not in the past.

You asked for TODAY()+ 180, etc. so the formula includes H48=(TODAY()+75), etc. So it is only formatting the cells that are equal to 180 days (or whatever number of days)from today. Not greater than the number of days from today. This allows you to put different formatting to each rule and specific future date.

Select format, then choose the fill color then OK and then apply and OK your way back to the dialogue window that displays the rules.

In that window you'll want to click Add New Rule so that you can enter a new rule which will have the same formula except that you'll change the "+" number to the different number of days and different formatting for each different rule.

Follow this same procedure until you have all four rules and their respective formatting the way you want them.

What are the different number formats?How will you change the format of the number?write the steps

Hi Maria,

We have a special tutorial on number formats. Please check it out: Custom number format in Excel

Please help.

So I’m not sure what to use or what to do. So I’m trying to make an inventory sheet, so right now b9,c9, is 10 where e9,f9 is 10 and h9,i9 is 10. So when i9 goes to “0” I want h9 to Recognise and talk to f9 and say hey I’m giving my value of 10 to i9 so you will need to talk to e9 and Your Your 10 from him. Do I make sense?

please help me ,

if i am type 20-2 in excle 2013 then excle take 43151 value , not take correct value so how to resolve this

Dinesh:

How is the cell you're entering the 20-2 formatted?

Try formatting it in another way.

Right click select Format Cells and then choose another option from that list. Maybe text.

Hello,

I have a total of 50 employees. On a separate sheet, I have the list of employees who filed for leaves on a specific day however I'd like to highlight only the first 10% of 50 (which is 5) since the allowed number of leaves per day is 5 only. The basis is the Transaction ID:

"Transaction

ID" Employee # Name Date and Time Rqstd

1 F025630 John Smith 4:30:03 PM

2 F025631 John Doe 7:32:03 PM

3 F025632 Jane Doe 12:30:03 AM

4 F025633 Jane Smith 1:30:03 AM

5 F025634 Tony Stark 1:35:03 AM

6 F025635 Steve Rogers 1:36:03 AM

7 F025636 Clint Barton 2:30:03 AM

8 F025637 Natasha Romanoff3:30:03 AM

9 F025638 Bruce Banner 6:30:03 AM

10 F025639 Thor Odinson 9:30:03 AM

How do I do that?