In this article, you will find two quick ways to change the background color of cells based on value in Excel 2016, 2013 and 2010. Also, you will learn how to use Excel formulas to change the color of blank cells or cells with formula errors.
Everyone knows that changing the background color of a single cell or a range of data in Excel is easy as clicking the "Fill color" 
button . But what if you want to change the background color of all cells with a certain value? Moreover, what if you want the background color to change automatically along with the cell value's changes? Further in this article you will find answers to these questions and learn a couple of useful tips that will help you choose the right method for each particular task.
How to change a cell's color based on value in Excel dynamically
The background color will change dependent on the cell's value.
Task: You have a table or range of data, and you want to change the background color of cells based on cell values. Also, you want the color to change dynamically reflecting the data changes.
Solution: You need to use Excel conditional formatting to highlight the values greater than X, less than Y or between X and Y.
Suppose you have a list of gasoline prices in different states and you want the prices greater than USD 3.7 to be of the color red and equal to or less than USD 3.45 to be of the color green.
Okay, here is what you do step-by-step:
- Select the table or range where you want to change the background color of cells. In this example, we've selected $B$2:$H$10 (the column names and the first column listing the state names are excluded from the selection).
- Navigate to the Home tab, Styles group, and choose Conditional Formatting > New Rule....
- In the New Formatting Rule dialog box, select "Format only cells that contain" under "Select a Rule Type" box in the upper part of the dialog box.
- In the lower part of the dialog box under "Format Only Cells with section", set the rule conditions. We choose to format only cells with a Cell Value - greater than - 3.7, as you can see in the screenshot below.
Then click the Format... button to choose what background color to apply when the above condition is met.
- In the Format Cells dialog box, switch to the Fill tab and select the color of your choice, the reddish color in our case, and click OK.
- Now you are back to the New Formatting Rule window and the preview of your format changes is displayed in the Preview box. If everything is Okay, click the OK button.
The result of your formatting will look similar to this:
Since we need to apply one more condition, i.e. change the background of cells with values equal to or less than 3.45 to the green color, click the New Rule button again and repeat steps 3 - 6 setting the required condition. Here is the Preview of our second conditional formatting rule:
When you are done, click the OK button. What you have now is a nicely formatted table that lets you see the highest and lowest gas prices across different states at a glance. Lucky they are in Texas :)
Tip: You can use the same method to change the font color based on the cell's value. To do this, simply switch to the Font tab in the Format Cells dialog box that we discussed in step 5 and choose your preferred font color.
How to permanently change a cell's color based on its current value
Once set, the background color will not change no matter how the cell's contents might change in the future.
Task: You want to color a cell based on its current value and wish the background color to remain the same even when the cell value's changes.
Solution: Find all cells with a certain value or values using Excel's Find All function or Select Special Cells add-in, and then change the format of found cells using the Format Cells feature.
This is one of those rare tasks that are not covered in Excel help files, forums and blogs and for which there is no straightforward solution. And this is understandable, because this task is not typical. And still, if you need to change the background color of cells statically i.e. once and forever unless you change it manually again, proceed with the following steps.
Find and select all cells that meet a certain condition
There may be several possible scenarios depending on what kind of values you are looking for.
If you need to color cells with a particular value, e.g. 50, 100 or 3.4, go to the Home tab, Editing group, and click Find Select > Find....
Enter the needed values and click the Find All button.
In our previous example, if we needed to find all gas prices between 3.7 and 3.799, we would specify the following search criteria:
Now select any of the found items in the lower part of the Find and Replace dialog window by clicking on it and then press Ctrl+A to select all found entries. After that click the Close button.
This is how you select all cells with a certain value(s) using the Find All function in Excel.
However, what we actually need is to find all gas prices higher than 3.7 and regrettably Excel's Find and Replace dialog does not allow for such things.
Luckily, there is another tool that can handle such complex conditions. The Select Special Cells add-in lets you find all values in a specified range, e.g. between -1 and 45, get the maximum / minimum value in a column, row or range, find cells by font color, fill color and much more.
You click the Select by Value button on the ribbon and then specify your search criteria on the add-in's pane, in our example we are looking for values greater than 3.7. Click the Select button and in a second you will have a result like this:
If you are interested to try the Select Special Cells add-in, you can download an evaluation version here.
Change the background color of selected cells using "Format Cells" dialog
Now that all cells with a specified value or values are selected (either by using Excel's Find and Replace or Select Special Cells add-in) what is left for you to do is force the background color of selected cells to change when a value changes.
Open the Format Cells dialog by pressing Ctrl+1 (you can also right click any of selected cells and choose "Format Cells…" from the pop-up menu, or go to Home tab > Cells group > Format > Format Cells…) and make all format changes you want. We will choose to change the background color in orange this time, just for a change :)
If you want to alter the background color only without any other format changes, then you can simply click the Fill color button and choose the color to your liking.
Here is the result of our format changes in Excel:
Unlike the previous technique with conditional formatting, the background color set in this way will never change again without your notice, no matter how the values change.
Change background color for special cells (blanks, with formula errors)
Like in the previous example, you can change the background color of special cells in two ways, dynamically and statically.
Use Excel formula to change background color of special cells
A cell's color will change automatically based on the cell's value.
This method provides a solution that you will most likely need in 99% of cases, i.e. the background color of cells will change according to the conditions you set.
We are going to use the gas prices table again as an example, but this time a couple of more states are included and some cells are empty. See how you can detect those blank cells and change their background color.
- On the Home tab, in the Styles group, click Conditional Formatting > New Rule... (see step 2 of How to dynamically change a cell color based on value for step-by-step guidance).
- In the "New Formatting Rule" dialog, select the option "Use a formula to determine which cells to format". Then enter one of the following formulas in the "Format values where this formula is true" field:
- =IsBlank()- to change the background color of blank cells.
- =IsError() - to change the background color of cells with formulas that return errors.
Since we are interested in changing the color of empty cells, enter the formula =IsBlank(), then place the cursor between parentheses and click the Collapse Dialog button
in the right-hand part of the window to select a range of cells, or you can type the range manually, e.g. =IsBlank(B2:H12).
- Click the Format... button and choose the needed background color on the Fill tab (for detailed instructions, see step 5 of "How to dynamically change a cell color based on value") and then click OK.
The preview of your conditional formatting rule will look similar to this:
- If you are happy with the color, click the OK button and you'll see the changes immediately applied to your table.
Change the background color of special cells statically
Once changed, the background color will remain the same, regardless of the cell values' changes.
If you want to change the color of blank cells or cells with formula errors permanently, follow this way.
- Select your table or a range and press F5 to open the "Go To" dialog, and then click the "Special…" button.
- In the "Go to Special" dialog box, check the Blanks radio button to select all empty cells.
If you want to highlight cells containing formulas with errors, choose Formulas > Errors. As you can see in the screenshot above, a handful of other options are available to you.
- And finally, change the background of selected cells, or make any other format customizations using the "Format Cells" dialog as described in Changing the background of selected cells.
Just remember that formatting changes made in this way will persist even if your blank cells get filled with data or formula errors are corrected. Of course, it's hard to imagine off the top of the head why someone may want to have it this way, may be just for historical purposes :)
How to get the most of Excel and make challenging tasks easy
As an active user of Microsoft Excel, you know that it has plenty of features. Some of them we know and love, others are a complete mystery for an average user and various blogs, including this one, are trying to shed at least some light on them. But! There are a few very common tasks that all of us have to perform daily and Excel simply does not provide any features or tools to automate them or make an inch easier.
For example, if you need to check 2 worksheets for duplicates or merge rows from single or different spreadsheets, it would take a bunch of arcane formulas or macros and still there is no guarantee you would get the accurate results.
That was the reason why a team of our best Excel developers designed and created 20+ add-ins that we call the Ultimate Suite for Excel. These smart tools handle the most grueling, painstaking and error-prone tasks in Excel and ensure quickly, neatly and flawless results. Below is a short list of just some of the tasks the add-ins can help you with:
- Remove duplicates and find unique values
- Merge tables and combine data from different sources
- Combine duplicate rows into one
- Merge cells, rows and columns
- Find and replacing in all data, in all workbooks
- Generate random numbers, passwords and custom lists
- And much, much more.
Just give these add-ins a try (a fully-functional trial version is here) and you will see that your usual Excel routines will take only a fraction of time they formerly did and your productivity will increase up to 50%, at the very least!
If you like any of these tools, be sure to make use of the coupon code that we provide especially for our blog readers: AB14-BlogSpo It will give you the 15% discount on the Ultimate Suite or any separate product.
*****
That's all for now. In my next article we will continue to explore this topic further and you will see how you can quickly change the background color of a row based on a cell value. Hope to see you on our blog next week!
Its very usefull for me
No i Can't understand
Very easy and Usefull, $nx...
thanks i found my problem solution.
Hi Dinesh and Hafiz,
Thank you for your comments! It's really nice to know that my article was helpful.
thanks a lot
i require a range of cells to turn color when the value of a non adjacent cell reaches a specific value, example;
cells b2,b3,b4,b5,b6 to turn red when cell f12 is greater than 20
Hi Gln,
Just select the needed cells (b2:b6) and create a new rule with this formula =$F$12>20.
hi , actually i have 100 cells out of which 10 contains value as 0,
and others are any number ...i have used some formulas , as a result of which 0 value has been entered in the cells,, i want to change the colour of cell containing
0 when its value changes to some new value other then 0, at the same time i do not want to change cell colour of my existing cells containing values other then 0..
pls help
Hi Sameer,
Select your 100 cells, press Ctrl+F, enter 0 into the "Find what" field and click the "Find all" button. Then switch to the list of found values and press Ctrl+A to select them all. After that create a conditional formatting rule by choosing "Format only cells that contain" (the rule will apply to the highlighted cells only). Under "Format only cells with" choose "Cell value", "not equal to", 0. Press the Format button to choose the fill color of your liking and you are done.
great helpfull
how do you do the above using drop-down menus
Hi Eric,
Sorry, I do not exactly understand your question. I do not know any way to change the background color in Excel other than by using conditional formatting.
it is shorting ....
Hi Svetlana,
Thank you for the post. I have a follow-up question. I have two columns and I want to compare the two values for row 1 in each column for dozens of rows. I want column A's cell to change color if it's value is less than the corresponding cell in Column B. For example if A1=4 and B1=5, then I want A1 to turn red and I want to do this for each cell in A1:A50 compared to each corresponding cell in B1:B50. Is there a way I can do this all at once instead of one cell at a time?
Thanks,
Matt
Hi Matt,
Of course, you can do this at once using the conditional formatting. Select cells A1:A50 and create a new rule (Conditional formatting > New rule > Use a formula to determine which cells to format). Enter this formula =$A1<$B1 and choose the red fill color. Done!
If you want to do the same in a table that has headers (data starts in row 2), then simply change A1 to A2 and B1 to B2 in the formula.
Hi Svetlana,
Thank you, but that only worked for the first cell (assuming I didn't do something wrong). How do I get it to compare A2 to B2 and C2 to C3, etc.? I'm used to using formulas where I could create the formula and then just copy the formula to all of the cells. Is there a similar way to do that with this conditional formatting?
Thanks again,
Matt
Hi Matt,
Yes, it works in a similar way with conditional formatting. Instead of copying the formula to other cells, you select the entire range that you want your formula to apply to, and use a mixed cell reference, i.e. an absolute column and relative row (e.g. $A1 and $B1).
I figured it out. Thanks for your help.
Hello Svetlana,
I'm having a trouble with conditional formatting, I've been looking for solutions several days but still can't figure out.
I use "between" rule to highlight cells. But is there any way that I can highlight cells with 0 and ignore blank cells? Because when I choose highlight between "0" to "46" (for example), blank cells are also highlighted.
I try to use 3-scale color, it can ignore blank cells but unfortunately my data will be much better if I use "between" rule to highlight.
Hope you can help figure this thing out! Thank you very much!!
Best regards!
Hi Thinh,
You can do this by creating a second rule of the "Format only cells that contain" type. In the 'New Formatting rule' dialog window, click the little arrow next to "Cell value" box and choose Blanks from the drop-down list. Then simply click OK without setting any format. Finally, click Conditional Formatting > Manage rule on the ribbon and select the option "Stop if true" next to the Blanks rule. Also, make sure it is first in the list, if not, move it to the top as explained here . Hope this helps : )
Thank you very very much!!! You are such a genius! I've been searching for days with no result! Your solution works exactly as I hope it will!
So much thank you for your help! You've just saved my thesis :)
Best regards!!
I'm glad to know it worked for you. Good luck with your thesis! : )
my problem out side of your presentation
Thanks I was able to find my answer / straight to the point
Hi,
I would just like to fill a cell with a color based on a %. Say fill a cell 50% with a color as a graphic representation of the amount. Is that possible in excel?
Thanks
Vinny
Hi Vinny,
Of course, you can do this using the conditional formatting. The easiest way is to select your table or a range of cells, click the Conditional formatting button > Highlight Cells rules > Equal to..., specify the percent you want to color, e.g. 50%, and choose a fill color from the drop down list (click Custom Format... if you want more colors).
Can this be done with a gradient as well? For example setting RED for 0% and GREEN for 100% so it does the colours by itself according to the result of the formula.
I'm using this and I would like the result of this cell to change its colour.
=COUNTIF(E4:E37, "SI")/34
Thank you.
I need some help here. I am trying to change the font of Cell A2 red based on B2 being blank. How can I work that out?
Hi,
Select the cells you want to highlight in Column A and create the conditional formatting rule based on this formula: =B2=""
And then choose the needed font color on the Font tab of the Format Cells dialog.
Hi,
For example A1 & B1 both cells values should be same. If it's not true, then C1 cell background need to be change! can you help me?
Hi Saran,
Just select all the cells in column C that you want to change the background color and then create a conditional formatting rule with this formula:
=($A1<>$B1)
Dear SC,
Thanks for your article know i know how can i use the conditional formatting for filling the color in an specific column, But could you please tell me how i can fill a color in a specific column based on the value of an another column
With your quoted example how you can color the name of states A1,A2,A3 based on the value of mentioned in the column B1,C1,D1
Hello Radhakrishnan,
You can color the names of states in column A based on values in columns B, C and D in this way:
- Select the cells you want to color (A2:A12 in our example)
- Create a conditional formatting rule based on formula, e.g. =AND($B2<3.5,$C2<3.5,$D2<3.5) It will color the states with gas prices less than 3.5 in columns B - D.
For more information, please check out my other article: How to change a cell's color based on a value of another cell. Hope this helps.
HI Svetlana,
Useful post and perfect answers :)
1 Query - i want to format cells in a column with colurs based on the vloopkup value from other sheet.
How can this be acheived?
Hi Adil,
Thank you for your kind words : )
You can use the Vlookup function in the formula, the conditional formatting rules support it. Here is an example:
=VLOOKUP($A2,Lookup!$A$2:$C$100,3)>20
Just keep in mind that Vlookup is a resource-consuming function and it may slow down your Excel if you have large tables.
thanks so much for this useful and well exmapled article, saved me hours on what i was about to do. you're awesome!
Thank you very much, Macca!
Hi Svetlana,
It's really helpfull article. I appriciate the way you described about the features of Excel.I am working on Excel services using O-data and I am stucked in one point.
Like in given example there are total 10 rows and you did formatting for that.
I want to add 4-5 extra rows in that same sheet.So is there any way that the cells having values greater than 3.7(given in your example) should come in reddish color and cells having values less than or equals tp 3.45(given in your example) should come in green color for extra rows automaticaly without again selecting the entire table.
Hope you can help figure this thing out!
Thanks
Hi Vishesh,
Usually Excel is smart enough to apply the existing conditional formatting rules to the newly added rows. If this does not happen, then you can either:
- When creating a rule, select some more empty rows below your data, say 100 - 200 rows, or
- Convert the range to table (Insert tab > Table). In this case, the existing rules will definitely be applied to all new rows you add to the table.
Hi Svetlana,
Amazingly good post.
I've one question as well.
A3 X
A4 X
A5 Y
A6 Z
A7 Z
A7 X
I'm trying to compare two cells for entire (Current and Preceding) and if they are not equal then fill with some color for the cell which differs from its previous one. I'm using Conditional formatting using a formula - =A2A3
Result is it fills color for A4 instead of A5 and A7 Instead of A8.
Hi Santosh,
Thank you! You simply need to use the cells' addresses in your formula in the reverse order. So, select the needed range, say A3:A8, and create a rule with this formula:
=A3<>A2
That works, Thank You :)
how to fill color by getting two values from two cells respectively.
For ex..
1 2 3 4 5 6 7 8 9
2 6 then from 2nd to 6th column have to fill color.
1 5 then from 1st to 5th column have to fill color.
can u pls help me?
Hi Charu,
I am sorry I do not exactly understand the task. Can you please send us your source data and the desired result at alexander.frolov@ablebits.com? We will try to figure this out.
Hi Svetlana, great, easy to understand instruction. I hope I haven't missed this, but I can't see how to color an entire row based on the value in one cell for a whole page of rows. For example - value in A2 is A so row is red, value B makes the row blue, etc. It will take a new rule for each value/color combo? Thanks, Justine
Hi, never mind previous request, I found the answer in another of your articles. Also very good, by the way.
Thank you for your feedback Justine! I am really glad to know you found the articles helpful.
How to change colour of entire column ( if > green &if < red)comparison to other column ?
Hello Rasendra,
This is a bit unusual to change the color of the entire column, most other users asked how to change the color of the entire row. Anyway, if this is your task, you can change the color of the whole column in this way:
- Select the entire column, say column B.
- Create 2 rules with these formulas: =$B$2<$A$2 and =$B$2>$A$2 (we are comparing values in row 2).
Make sure both formulas are applied to the entire column, say =$B:$B.
thanks for supporting
This has been so very helpful. Thank you for sharing your wealth of knowledge. I would love to know how to conditionally format a cell colour based on whether a different cell is NOT blank. I have perfected the blank value, how do I do the opposite?
I am also curious if it is possible to use the icon sets to show based on the value (or the blank versus containing data) of other cells. An example, a check list of items that spans a row, a check mark at the front of the row to show all columns have been filled in (contain data, any data) or an exclamation in the first cell to show that there are some cells that are blank, or an x if all cells in that row are blank. Is that even possible???
Thank you for your time
Hi Michelle,
You can create a rule with the following formula: =$B2="" It will color cells in the selected column(s) if a corresponding cell in Column B is not blank. Naturally, you will need to replace B with the column you need, and "2" with the row number where your data starts.
As for the icon sets, I described a solution exactly for this task in my latest article - How to use conditional formatting in Excel. You can go directly to the section How to apply an icon set based on other cells' values. Hopefully, this is what you are looking for.
Oh my gosh that icon set creativity is brilliant!!! Thanks Svetlana!!
Hi Svetlana,
I am struggling to figure out how to set a value range in my spreadsheet to correlate with specific colors when a value is input in the cells. For instance, I want the cell color to be blue when the value input into the cells is less or equal to 2.0, and if the value is 3.0 - 4.0 the cell will turn green, and if the value input into the cell is greater than or equal to 4.1 it will turn pink. Can you please help?
Thank you in advance for any assistance you can provide.
Julie
Hi Julie,
You can easily do this by creating 3 separate rules in this way:
- Select all the cells you want to color;
- Click Conditional Formatting > New Rule > Format only cells that contain;
- Select the needed option from the drop-down list (2nd box from the left) for each rule - "less or equal to", "between", and "greater than or equal to";
- Click the Format button and choose the format you want for each rule.
You can find the detailed guidance with screenshots here - Creating an Excel conditional formatting rule. Hopefully you will find the info helpful.
I am working with recertification dates of about 100 people. I have columns with recertification dates with an adjacent column that allows me to see if personnel are current or overdue. These cells change color based on that value. Their are several columns of data between the name of the indivual and their status. I would like to highlight names of individuals who are overdue. Is there a way to reference another cell so that when they go overdue it highlights their name and when they return current the highlighting goes away?
Hello Matt,
You can do this by creating a conditional formatting rule based on a formula. Select the column you want to highlight a create a rule using a formula similar to this: =$C2="Overdue" (where C is your status column and 2 is your 1st row with data). You can find more information about conditional formatting based on another cell here: Change a cell’s color based on another cell value.
Hi,
I want to change the cell colors in col A based on the values in another cells say Col C. Here, i have conditions that if the values in Col C are in the range <% then green, if + % then yellow otherwise Red..how to get it done!!
Hi!
You can select all cells with data in column A, not including the column header, and create 3 conditional formatting rules based on the formulas like these (assuming that row 2 is your first row with data).
Green: =$C2<50%
Yellow: =$C2>60%
Red: =AND($C2>=50%,$C2<=60%)
For more details, please check out this tutorial: How to change a cell’s color based on another cell.
Hai,
I have a trend monitoring datasheet . Each kind of trend refers to 1 - 100 numbers. i need to highlight the trend if it occurs more than 2 times.
Eg. Trend No.1 - BY Road, 2 -By shop, 3 -By School ... up to 100 numbers. If trend No.1 occurs two times, it should highlight. Can you please help me.
Hi Anil,
To be able to suggest a proper formula, I need to know more details. Is each trend a column or a row? Do you want to highlight certain cells or entire rows? Please send me a workbook with your sample data at support@ablebits.com and I will try to help.
How can you make a group of cells change their rules based on what you select from a drop down list??
I have a drop down list consisting of "Refrigerator" and "Freezer". I have coded values in "D10-D12" that need to change based on whether i have "freezer" or "refrigerator" selected.
Thanks!
Sorry, i meant color coded values
Hi Ron,
You can select a group of sells you want to color (e.g. D10:D12) and create 2 rules with different fill colors based on these formulas:
=$D10="Refrigerator" and =$D10="Freezer".
i want to color some cells based on different cell's condition. I've done it but the problem is when i close the document after saving and open again then i see that the condition has been erased. but why??
Hi Tanjin,
This is absolutely abnormal! Please make sure you save the workbook after creating the rule.
I am creating a task list and I would like to change the fill color of the row depending on to whom the task is assigned. I have created the drop-down list and used conditional formatting to assign colors to the individuals names. Now when I select a name from the drop-down list the fill color changes for that cell. How can I get the corresponding cells in the row to change to the same color?
Hello John,
You just need to apply the rule you created to the entire rows. Please see this tutorial for full details - How to change the row color based on a cell’s value in Excel.
Thank you for taking the time to assist me. It is much appreciated.
Please Please Help!!!
My cell C1 has to say "yes" if A1>100 and B1100 and B1>70.
please help how to make this formula ??? My head is broken by now :(
Hi Sergey,
Here you go :)
=IF(AND(A1>100, B1100>70, B1>70),"yes","")
very nice , thank you.
Hi,
Can I have a complete row highlighted in any color based on if there is any change (addition or deletion) of data from the exiting value?
Hi Rohit,
You can apply a conditional formatting rule to entire rows. Please see this tutorial for more details - How to change the row color in Excel based on a cell’s value. To be able to suggest a formula, I need more info about your data structure and the result you are looking for.
Thank you for your response, but i might not have explained it well enough.
I have 3 cells that need to change rules according to what i have selected from a drop down list. So, if i have "refrigerator" selected from the list, the 3 cells need to have one rule for that selection, and if i select "freezer" the same 3 cells need to have another set of rules. Not sure how to do it.
Thanks for you help though! :)
Hi Ron,
Regrettably, Excel conditional formatting rules cannot be changed depending on the value selected in the drop down list (this can be done using VBA only).
As an alternative, you can create several rules for the same range of cells, one per each value in the drop-down list, that will check the contents of your drop box and color the cells you want depending on the drop-down list's value and your additional conditions.
You're the best! Thank you doll
Dear Svetlana Cheusheva,
Can we color a cell based on result. In detail, I am using a formula (=min(various nos of cell in a particular row) to find the lowest figure , Like ( =MIN(W7,S7,O7,K7,like so on))and for example result is W7 cell and I want to automatically color the W7 cell. By conditional formatting it can be done by selecting entire cell range, but problem is I have to more than 40-50 row and I have to repeat the same rule for each row. Is there any help for me. Pls guide.
Hello Rameswar,
You can select all the rows and create the conditional formatting rule with the formula =A7=MIN($W7, $S7, $O7....)
Where A7 is the first (top-left) cell of your range.
But I'd recommend creating a helper column (hidden) and copy the following formula there: =MIN($W7, $S7, $O7....) And in your conditional formatting rule, use the formula =A7=$AB7, where A7 is the top-left column and AB is the helper column.
If you have a large data set, this rule will work much faster because Excel won't have to calculate =MIN($W7, $S7, $O7....) for each cell where the rule is applied.
Is there a way to format a column of numbers to highlight only when another column has an "X" in that row?
Hi Patricia,
Of course, there is a way. Simply select the column you want to highlight, not including the header and create a rule for it with this formula:
=A2="X"
Where A2 is the first cell with data in the other column, again not including the header row.
Thank you, this topic is helpful
Lets say I have a formula in a cell from another page in the workbook. The cell in the other work book is a drop down. Is there any way when that drop down is used/filled to make the cell on the second page change color to show its populated?
Hello Steph,
It looks like you need a special macro to fulfill this task. I am really sorry we can't help you with this.
hi the post is very helpful to me bcoz I have learnt a new thing but I got one question how do you do if you want to color the cell after entering the value maybe less than 3 to color yellow and greater than 3 or empty cell to remain with default color
i want to color selected cell based on cell color
i want to color some selected cells based on cell color
please let me how to do it
Thanks
In col. B2- 100. In col.A3-101,A4-102,A5-103. In col.C3-99,C4-98,C5-97.
when col.B2 there are 101 i want green color in A3.
when col.B2 there are 102 i want green color in A4.
when col.B2 there are 103 i want green color in A5
& Viseversa
when col.B2 there are 99 i want red color in C3
when col.B2 there are 98 i want red color in C4
when col.B2 there are 97 i want red color in C5
I am tracking training of people on my project. All of the training they take has to be retaken annually.
Based on the completion date in a cell I want to turn the cell GREEN if the number of days fall within 1 to 334 days from the completion date. I want to turn the cell YELLOW If the number of days fall within 335 to 364 days from the completion date. I want to turn the cell RED if the number of days is 365 or more from the completion date.
Thank you so much, it is very useful!
I also want to create legend after doing these way, how to create leagend?
Thank you indvance!
Hi,
I have applied conditional formatting to change the color of few cells to red in a row based on a value. After applying conditional formatting,if i copy and paste some data to these cells, the background color changes to white. Can you please help?
Hello Satish,
When you use the standard Paste command, the current rules are replaced with the ones applied to inserted cells.
You need to use the Paste Special option instead: right-click on the destination cells, select Paste Special from the context menu and choose the Values or Formulas radio button under Paste.
Hi Svetlana
I have a range of values in Row1 - say A1 to J1
I have another range of values in row2 - say A1 to J2.
I am comparing A1 and A2; then B1 with B2....... then J1 to J2.
Whenever value in row1 < row 2, the row2 cell should be changed to red color.
This is needed to be done across the entire row 2.
Kindly let me know the solution.
Hello Sridhar,
If you have to highlight each cell in row 2 that has a value greater than the value in row 1 in the same column, then select row 2 and create a rule with this formula:
=A2>A1
If you have to highlight the entire row 2 if at least 1 cell in row 1 has the value less than in row 2, you need a helper cell, e.g. A5, with the following array formula (remember to press CTRL+Shift+Enter instead of Enter to complete it):
=SUM(--(A1:J1<A2:J2))
After that, select row 2 and create a rule with this formula:
=$A$5>1
Thank you Svetlana!!
I am partially red/green colour blind and rely on the labels that appear when I use Fill on the Home ribbon (pause over a colour and the label appears). But those labels do not appear when I use Fill from the conditional formatting feature. How do I get them to appear when using conditional formatting? Also how do I get labels to appear in 'more colours'?
Hello Kevin,
I am very sorry, I don't know a way to make the labels to appear for conditional formatting colors.
Hello,
I'm trying to set up a sheet where if I enter something in A (will be a number) then B,C,D,E turns red until something is entered into each one.
Once a value, or word is entered in each of B:E they turn back to white.
Is this possible? It seems like trying to connect 2 formulas may be contradicting
Found the answer from you here:
https://www.ablebits.com/office-addins-blog/2014/06/10/excel-conditional-formatting-formulas/#blanks-nonblanks
Thank you so much, and welcome to the top of my bookmarks list =)
Thank you, Thomas! Much appreciated : )
Hi Svetlana,
I am very impressed with the creative solutions I have seen on this page.
I am hoping you can help me with a problem I have been struggling with for months.
I would like to copy the contents and format of a column of cells and paste them in another column using a formula.
All columns are formatted as type = General
Column A is a list of random IP addresses that are color formatted as green or red cells. Just say every other IP address is green and the others red.
Column C is also a list of IP addresses (a master list) with no color.
In column B, I want to take all the IP addresses in column A and line them up on the same row as the IP address in column C (and keep the color formatting from column A).
In column B to get the IP address from column A to line up with the IP addresses in column C, I used the formula:
=IF(ISNA(MATCH(C1,A:A,0)),"",INDEX(A:A,MATCH(C1,A:A,0)))
This works but column B does not have the color formatting as the cells in column A
Is there any way to keep that formatting when using a formula?
Here is the data (remove column B if you want to check the formual):
192.168.36.21 192.168.36.21 192.168.36.21
192.168.32.31 192.168.33.174
192.168.35.0 192.168.32.31 192.168.32.31
192.168.37.56 192.168.40.163
192.168.45.10 192.168.42.82
192.168.43.221 192.168.35.0 192.168.35.0
192.168.45.108 192.168.37.56 192.168.37.56
192.168.46.208 192.168.38.105
192.168.32.226 192.168.38.214
192.168.39.208 192.168.43.224
192.168.32.246 192.168.45.10 192.168.45.10
192.168.35.109 192.168.43.221 192.168.43.221
192.168.34.99 192.168.46.215
192.168.32.55 192.168.45.108 192.168.45.108
192.168.46.208 192.168.46.208
192.168.32.226 192.168.32.226
192.168.43.42
192.168.39.208 192.168.39.208
192.168.32.246 192.168.32.246
192.168.34.23
192.168.36.68
192.168.35.109 192.168.35.109
192.168.34.99 192.168.34.99
192.168.32.55 192.168.32.55
Maybe this is not the right approach and using some other way like Offset would be the way to go. Any input is welcome
Thank You for your valuable time,
Sven
Thank you very much
Svetlana,
Thank you for your post. It is very useful. You seem to be an expert in excel color and I wonder if I can ask you a question about Excel color. I inherited an excel file with a pale yellow/sand color. When I check the color index, it shows -4142. When I change the color of the cell, it shows the normal color. When I set as no fill, I get this color (-4142). When I print the sheet, the color displays similarly as a no filled cell. I wonder if you know how this -4142 "color" gets set in the first place?
Thank you in advance.
Hello Tina,
I am hesitant to give any advice without seeing the formatting. If you can send us your workbook at support@ablebits.com, we will hopefully be able to help.
Hi,
Is there a way to select a random cell in excel and have all the other cell with the same value highlight? So, once I select another cell, the originla highlight is lost and all the cells with values same as the new selected cell gets highlighted.
I need this for work to make my presentation look spot on and set me apart from the rest. If you could reply back, I would very much appreciate it.
Thank you!
Shweta
Can you use this to change a cells color based on a word in the cell instead of a value?
Thanks a lot
it was so helpful for me
thx for help XD
Hi Svetlana,
I am trying to set up a database for training records of employees,
using your rule above is it possible to set a cell to change green, yellow or red based on the training course expiry length.
For example Cell E13 is Green, if the date in cell F13 is 60 days away from the course expiry date set in G13 then Cell E13 turns yellow, if it passes the date then it turns red
Any help is much appreciated
Thanks
Craig
Hi Craig,
Try creating a rule for cell E13 with the following formulas:
Red: =$F13>$G13
Yellow: =AND($G13-$F13>0, $G13-$F13<=60)
Green: =$G13-$F13>=60
If you need to highlight more than 1 cell in column E, select them all before creating a rule and replace "13" in the above formulas with the number of the top row in the selection.
thanks it has been helpfull
I have Repeated elements
5347940
5347940
5347945
5347945
5348035
5348035
5348036
5348036
5348053
in this i want to color alternate numbers
Svetlana,
I have a slightly different question, though very related: I would like for cell A1 in Sheet 1 to be red if it is greater than A1 in Sheet 2, and green if it is less than A1 in Sheet 2. I would also like cell B1 in Sheet 1 to change color based on B1 in Sheet 2, and so on.
I can do this manually, using your instructions for "Format only cells that contain," but I have thousands of cells I would like to change in this way. It may be obvious that I am looking for trend changes, and normally I would use a line chart, but I want to know the exact numbers where the changes take place, and Excel charts don't seem to allow for that kind of detail.
Thank you for any help you can give.
Robert
Svetlana,
Nevermind, I figured it out, playing off your suggestion to question #59 (and the basic advice to use the conditional formatting button, of which I had been unaware before I found this post). I used "Use a formula to determine which cells to format," and typed in the formula:
=A1'Sheet 2'!A1
For the first formula I selected one color (though it looks like I could have done just about any formatting) and for the second I selected another.
If you highlight the whole area you need to follow this formatting, and make sure not to use dollar signs on any of the cell coordinates, the formatting should appear across your whole worksheet.
Thanks for getting me started!
Robert
It looks like I lost a few lines, including the second formula. The first one should have a "greater than" sign after the first "A1," and the second is identical to the first, except it has a "lesser than" sign:
=A1'Sheet 1'!A1
Hi there
I have a set of data in a table and all the numbers are different.
I'm sending this to someone else and if they wish to manually change the number to something else I want the cell to change colour.
Is there a way of doing this with conditional formatting?
Thanks for your help
with an using conditional formula, cell A1 results "passed" with colour letter after putting a date in Cell D1. Please...
hi,
i wanted to change the colour of a row based on a specific value of a cell. can i do so?
Hi, I have created a worksheet that produces division of an input number by the numbers 1 to 100 in individual cells. I am trying to turn change a cell colour for cells where the product of the division is an integer. I have checked and there does not appear to be a direct way to do this with the standard formulas. Could you help please.
Hi,
I am creating a spreadsheet and would like to have three collums with dates that change the color of each row.
colloum A - turns X color when a date is entered.
COlloum B - Turns X color when a date is entered.
Colloum C - Turns X color when a date is entered.
I would like a check mark to apear in a fourth colloum when the row has three dates entered, and a X when one or two dates are entered.
Thank you.
Hi Aaron,
You can create a rule for each column in this way: Conditional Formatting > New Rule > Fromat onl cells that contain and choose "No blanks" from the left-most drop down list.
Then, enter the following formula in the 4th column:
=IF(AND(A2<>"", B2<>"", C2<>""), 2, IF(AND(A2="", B2="", C2=""), "", 1))
Where A, B and C are your date columns.
Finally, create the following icon set rule for the 4th column:
Hi,
I need help in highlighting multiple cells till a certain value is reached:
Ex.
stock demands w01 demands w02 demands w03 demands w04
3000 500 1000 1200 1000
I need that Excels highlight first 3 postions because their sum is less than the stock.
Would someone help me pls?
Thanks
Thank You so much we solve over problem, before we don't know how to change background color..
Thank You so Much...
Hi Svetlana, you have many satisfied customers! I read the couple of hundred posts in your forums and did not quite see the issue I am having. I receive a weekly report and I need to compare 5 columns of data in this week's report vs. the same 5 columns in the prior week's report, and identify if any changes occurred in each row for 900 rows. I use a formula (below) where Column A looks at Column F, Column B looks at Column G, Column C looks at Column H, Column D looks at Column I, and Column E looks at Column J, for any changes (text or number). I put the formula in cell K2 and copy it down to K900. The formula in Column K tells me that some data has changed in the row if there was any change, but I have to visually look at all 10 columns in the row to see what cell(s) data changed. Is there a way to color the cells where the data has changed? Here is the formula I use to identify changes:
=IF(SUMPRODUCT((A2:A900=F2)*(B2:B900=G2)*(C2:C900=H2)*(D2:D900=I2)*(E2:E900=J2))>0,"","Changed")
I hope you can help! Thanks
Hello George,
Thanks for the detailed description.
If you simply need to compare 5 columns in one row, just select A2:E900 or F2:J900 (according to the part of the table you want to color) and create a conditional formatting rule using this formula:
=A2<>F2
If the group of 5 cells may be in different rows, then please send your sample workbook to support@ablebits.com. We will try to find the solution.
Hi Alexander,
I actually need to compare 10 columns in one row. I tried the formula you mentioned and variations of it using yellow shading but all it did was shade all the cells yellow. I will send a sample workbook to the email address you provided along with additional clarification of what I need. Thanks.
Hi Svetlana,
I want to know wheather top three selection, or bottom three selections can be displayedin three different colors of our choice
Thanking you
how do i create a formula where
if cell B1 = letter z or Z , and no other letter (remove blanks, then change cell color to green
thank you its help me....
dear Svetlana,
i have a question and hopefully you can help me. i have a file in which we have a consequence of numbers in range of 50 (...01-...50; ...51-..100 etc). please find example of how they look below.
140247951
140247952
140247953
140247954
140247955
140247956
140247957
140247958
140247959
140247960
140247961
140247962
140247963
140247964
140247965
140247966
140247967
140247968
140247969
140247970
is it possible to use condition formating to higlight let say 140247970 when i search it through find box (CTRL+F). these are barcodes that we have generated and we want to track them when they have been returned to me.
hopefully you can help me as i am not very good in these kind of formating.
thanks in advance :)
Hi Svetlana,
It is really helpful, Thank you
What a wonderfully helpful post, and active for so long!
Now, I have a question. I have a formula to calculate dates out: =WORKDAY(B1+(5-1),1,holidays) So, it will only return weekday results that don't fall on my defined holidays. Is there any way to make it so that when a date is "pushed forward" to a non-holiday weekday, that the cell would change color so you could see at a glance that the date had been pushed forward in that manner?
Thanks!
Dear Svetlana,
Congratulate for your blog, I am fully impressed.
I tried to find an answer and maybe you already had this question - if yes, sorry.
My question is: I have an excel sheet where costumers populate entries time by time based on general rules, so the entry is fixed. I have a mapping table about 20 countries. I would like to colour only these countries on my main sheet with conditional formatting or a rule-set but without an additional column where I use vlookup. I can set up 20 different rules with conditional formatting, but maybe you have a more professional idea as well.
Many thanks for your kind help in advance, Kate
Hi Kate,
This task is far from trivial :)
You can try creating a rule with the following formula:
=NOT(ISERROR(MATCH(A2,Sheet2!$A$2:$A$20,0)))
Where A2 is the first cell with data in your main table and A2:A20 is the list of countries in the mapping table.
Dear Svetlana,
You are a genius, it works!!! Thank you very much!
Kate
Hi Svetlana,
I noticed that you are a genius in excel and very helpful to others. I was wondering if you could help me out.
I have a table of numbers and need to fill each cell with a color based on if the previous number increased or decreased. So if 250 (A1) gets changed to 254 (A1), I want to fill in that cell with green because it increased. BUT if that same cell, A1 (254) gets changed to 253, I want the cell to fill in with red because of the decrease.
I am wondering if this is possible to format for each cell.
Thank you in advance,
Tommy
Hi Tommy,
I regret to tell you that it is not possible. Excel conditional formatting formulas can compare a value in a given cell with some other number, or a value in another cell, or a value returned by some other function. But it cannot respond to a value change in the same cell.
Ok, thank you for responding so fast! Is there any other way to fill in the cell based on an increase or decrease, that wouldn't have to be applied every day?
Hello Svetlana,
I was wondering if it is possible to get cells in a column to change colour based on whether a word occurs 2 or 3 times in that column. For example, I need to know is a maximum of 3 has been reached (typed into the workbook) so if any word occurs 2 times anywhere in the column I would like it to turn yellow, and if that same word occurs 3 times I would like it to turn red so that I know this item is exhausted. Is this possible?
Ex. If a given suburb, say "Sydney", has been entered by my sales reps into a column on a shared workbook 3 times I would like to to turn red so that I know it is completed, and if it has been entered 2 times I would like it to turn yellow so that I know it is almost completed.... complicated I know!
Typo in the above:
I need to know "IF" a maximum of 3 has been....
Hi Kristen,
Assuming that the values you want to highlight are in column A and row 2 is your 1st row with data (not including column headers), you can use the following formulas:
Highlight the 2nd occurrence: =COUNTIF($A$2:$A2,$A2)=2
Highlight the 3rd and all subsequent occurrences: =COUNTIF($A$2:$A2,$A2)>=3
what is the formula if the condition are
if the report delivered after 13 of every month than the cell shown red color.can u send me photo like a screen shot
Thanks
Best Regards
Inam ul haq
Thank you so much..really i got the solution
Hi Svetlana,
I have a workbook with multiple sheets of training events. Each sheet of of events makes up a job. All events must be current in order to be qualified for the job. I have conditional formatting set up now to have the training date entered in a cell be green if over 30 days from expiring, yellow if within 30 days and red if past do. I have a master sheet at the beginning of the workbook with a list of all of the employees. I want to list all of our jobs next to their names and have a cell for each job turn red if any of the date values from any of the job sheets turns red indicating something in their jobs qualifications is past do. Is this possible?
Hi
I am fully impressed. it's very useful to me thank u very much for simple logic's
thank you
It's very easy to know
Hi Svetlana,
IS this possible also to put some error message in the cell or in some other cell based upon conditional formatting. let say If cell of column A in a row has value less than 10 then cell of Column B will be highlighted as well as we need to put message that "column A is < 10" either in the same B cell or in different column cell in the same row?
Hi Swapnik,
You can display a message by entering an IF formula in cell B1 and then copying it down to other cells:
=IF(A1<10, "column A is < 10", "")
Hi Great help,
Just one thing how do I get five category and five colours the conditional formatting is only offering 3 .
Thanks so much...
Hi Svetlana,
Is it possible to change color of cell by using formula alone and not using any conditional formatting.
Regards
AMK
Hi!
Nope, formulas can perform various calculations and insert values based on the conditions you specify. To change cells' color you need either conditional formatting or VBA.
OK Thanks a lot and for your time.
I did google a lot but did not get any clue. You made it clear :)
Thanks dear!
Regards
AMK
Its very use full for me
thank you
Hi Svetlana,
This is very useful
I have one question,How to change the entire column color based on one cell value? eg if i have a text sunday in a cell, the entire column should be displayed in different color
Hi Hari,
You can select all the columns you want to highlight and create a rule with the following formula:
=countif(A:A,"Sunday")>0
Where A is the left-most column.
Hi
I am completely lost when it come to VBA and I need help.
I have a folder where I save a lot of reports with the same excel format and then I have a master file where I copy and paste all the data from all the folders in. So I have tried a lot of things but cant get the master file to automatically copy the data from all the workbooks.I think where it becomes complex is the workbooks have drop down lists and some color formats in as well in some columns. So what I would like to do is the master file copy all data and past special from the other workbooks and if possible delete them after closing. Please I need help.
Can you please attached a filee as reference because i can not follow up with all the comments?
Hi Svetlana,
Thanks for all this info.
I would like to change the colour of an entire row not just a cell, based on contents of a cell in that row. ie if the specified cell contains a specific word, the entire rows fill can be changed.
Thanks
Walter
Hi Walter,
To highlight entire rows, you simply apply the same rule to several columns instead of one. You can find the full details in the following article:
https://www.ablebits.com/office-addins-blog/2013/10/29/excel-change-row-background-color/
Hi Svetlana,
I have requirement where in i want to color the cell automatically when i have added some value to other cell. how can i do that?
like i want to color column X automatically when i will add some value to column Z
Hi Svetlana,
I have requirement where in i want to color the cell automatically when i have added some value to other cell. how can i do that?
like i want to color column X automatically when i will add some value to column Z.
Hi...Svetlana Cheusheva
Thanks for all information.
This is very useful to me.
Again thanks a lot.
i want this type of date& day format but i can't create this,please help me:-
Month---March-15
Dispatch Date Day
01 Sunday
02 Monday
03 Tuesday
04 Wednesday
05 Thursday
06 Friday
07 Saturday
08 Sunday
09 Monday
10 Tuesday
11 Wednesday
12 Thursday
13 Friday
14 Saturday
15 Sunday
the formula contain B2(for select the month & year)also contain the formula A5 & B2(i.e contain result 1,2,3,4,5 etc.etc.)
and day contain formula is on table format(i.e cloum A & B,that's result day sunday,monday etc.)
Svetlana
I can't figure out how to make a cell color change when A1 is 10% greater that A2. I would like A2 to change its color
Hi Greg,
You can create a rule with the percent change formula:
=($A$1-$A$2)/$A$2>10%
I have a requirement whereby I want Cell Q21 to turn one of two colours based on the result that appears in Cell Q22.
So if the result in Cell Q22 is equal to or greater than the value in Cell E20 I want Cell Q21 to turn "green"
But if the result in Cell Q22 is less than the value in Cell E20 I want the Cell Q21 to turn "red"
Can someone please advise a formula that can achieve this.
Thank you.
Hello Perry,
You can create 2 rules for Q21 with the following formulas:
Green: =$Q$22>=$E$20
Red: =$Q$22<$E$20
Hi Svetlana,
I have the following data...
A1=20
A2=12
A3=9
then
B1=19
B2=13
B3=8
I want that when the values in column A are smaller than the adjacent values in bolumn B, the figures in column A change colour. In this case, A1 and A3 should turn into a different colour.
Thank you before hand for your help.
I have the conditional formatting set for dates in one column, now how do I get that row to highlight based on that date.
tia!
Just apply the rule to several columns that you want to have highlighted.
It was very helpful....thank you
I need my cells to change color when a specific date arrives. Like 90 days before an expiration it changes to yellow and then 30 days prior it changes to red. Please help!
Hi Kristy,
You can create the following rules:
Yellow: =AND($A1-TODAY()>30, $A1-TODAY()<90)
Red: =AND($A1-TODAY()>=0, $A1-TODAY()<=30)
For more info, please see How to conditionally format dates and time in Excel.
Let say, we have standard output for various items in one column B2:B10. Daily production numbers for similar items are inserted in other columns C2:D10. For control purposes, daily production number below standard output should be formatted red in font in range C2:D10. How to get it done with Conditional formatting? Please advise.
Thanks...Aamir
IN STD Mon Tues
1 200 190 200
2 100 110 105
3 250 240 210
4 150 160 100
5 275 274 250
6 300 290 310
7 350 360 340
8 700 690 710
9 250 255 250
Hi Svetlana,
I have Jan to Dec Sale for Year for 4 separate products and for 4 region.
I have put vlookup to find sale sale for one product with one region.
But it needs to be colored In Data sheet.
Hi, will you please help me,
i want to find cell using find & select feature in excel with color, Format changing is not working with my excel,
if you have any other way please help me.
Thank's
Hi Svetlana,
i have highlited a column with 3 conditions and i got all the column filled with 3 different colrs as i needed. Now, i need to calculate no. of cells in each color for all 3 colors. can you help me?
please.
thanks.
Hi Varun,
Check out the following VB script to count and some conditionally formatted cells:
https://www.ablebits.com/office-addins-blog/2013/12/12/count-sort-by-color-excel/#count-conditional-formatting-color
what if i want to change the color based on the value of other cell?
Suppose that if value of F3 cell is greater than E3 cell then F3 cell should fill with red colour. How can i apply formula for that?
Vishwan,
Create a rule for F3 with the formula =$F3>$E3
Hi Svetlana,
I just have one problem hope you could suggest some steps.
We export an excel file from web application we made which is working fine. Now we created an Excel template that we use to fill data. But I am stuck with one problem. When I export the file from web app I have no problem with styling, but when I use template I am unable to set background color for blank cells that are generated in between data. The data generated and is not having a fixed size of rows. Can we create a formula which will detect blank cells and follow the same rules we give for cells with data until the last row of data discovered and from there treat blank as blank.
Thanks for this helpful article.
Is there a way to create conditional formatting that would change the color of a cell depending on the number of dates entered into the cell? For instance, I have a teacher that keeps up with her students' milestones by entering a date into the cell each time the student completes a milestone. If one or two dates are entered, she wants the cell to be yellow. When the third date is entered, she wants the cell to change to green. This would allow her to see the date in which a student reached a milestone and when they are completed the milestone. Any help would be appreciated! I'm completely stuck.
I have one data sheet how can auto find particular record then find the record change the color of cell find the records.
Thanks
Hi Svetlana,
I want to change the text of a Cell by changing color of another Cell. Like: If I change the color of A1 to green, then A2 should be written as Correct.
Kindly help me with a function.
Hi Svetlana,
Can you help me in this,
I have two columns
Column A - Numbers
Column B - Some Remarks to be updated manually.
If column A1>0 & B1 has some text B1 should not change its bg color
If column A1>0 & B1 is blank then B1 should change its bg color to Yellow automatically
Is it possible??
Thanking you in advance.
Awaiting for your reply....
Hello,
Here is the solution.
1. go to cell B1
2. click on Conditional Formatting
3. select Manage Rules
4. click New Rule
5. select Use a formula to determine which cells to format
6. in the box of "Format values where this formula is true:" type
=and(a1>0,b1="")
7. click on format select fill and select the color you want
8. click ok again ok and again ok
its done.
enjoy.
Thank you very much Deepak,
It is working :)
Useful information.
Thanks
Svetlana,
Thank you for the article. However, I am still trying to figure out a formatting issue. How can I automatically format multiple cells to a certain color if they have multiple values that are less than or greater than a certain value. For e.g., I am trying to format different values with different quota goals listed in multiple cells. Would I need to format each cell individually? I'd appreciate your feedback.
Hello Sushant,
No need to format each cell individually. You can select the range where you want to apply the conditional formatting and apply it.
Hi All,
The numbers "Age" column should be filled in with colors against the status mentioned against each of them.
How can we do this using conditional formatting.
Say for example cell containing "3" should be filled with Orange color.
Age Status
3 Orange
4 Green
10 Red
Hello Dinesh
Here is your solution step by step
let me assume cell A2=3, A3=4 and A4=10
1. Go to cell B2
2. Click on Conditional Formatting
3. Select Manage Rules
4. Click New Rule
5. Select Use a formula to determine which cells to format
6. In the box of "Format values where this formula is true:" type
=A2=3
7. Click on format select fill and select the color you want
8. Click ok again ok and again ok
9. Go to Cell B3 and repeat step 2 to step 5
10. In the box of "Format values where this formula is true:" type =A3=4
11. Repeat step 7 and step 8
12. Go to Cell B4 and repeat step 2 to step 5
13. In the box of "Format values where this formula is true:" type =A4=10
14. Repeat step 7 and step 8
its done.
enjoy.
Can I format a single cell or set of cells to highlight if another single cell or set of cells has any value? In other words, I have 5 cells that will contain data. If cells further down change from blank to any value (could be numeric or alpha or combo) I want the 5 cells with data to then be highlighted. Is that possible??
thanks!!!
superb .....its work.
thanks.
Hi Svetlana,
What I am trying to achieve is a reporting tool.
I want to have default values and when they change, then have the cell display as red.
So the scenario is A1 = Yes, B1 = No
When the user changes either one, I'd want it to change colour. Is this done through conditional formatting?
Hi Will,
I think you can create 2 rules with the following simple formulas:
For column A: =$A1<>"yes"
For column B: =$B1<>"no"
Hi,
I want the following thing
If B2 is equal to D2 then the cells become Green
If B2 is equal to C2 then the cells become Red
Can you please tell me
This is great. You have explained it so easily. Thanks
Hi All,
I need help in excell my question is if i change a color in one cell of sheet
automatically it should change in another cell of another sheet.
Example:I have 6 sheets with same information.
In "Sheet1 Jan sales" "Sheet2 FEBsales".If I change color "Yellow" For "Sheet1 Jan sales"in A6 cell "YEs" it should same refelect in "Sheet2 FEBsales" in A6 cell "Yes" as Yellow.
Please send me with temp let with clear explanation.
Waiting for solution for my question
regards,
Lakshmi
Thnx 4 the HELP......
Hello. I am not 100% whether it has already been answered, so I'm sorry if the question is being repeated.
I want to be able to change a cell (i.e. someone's name to a different colour) if they have responded to me. Basically if they have not response cell is blank, I want the font colour of the persons name to be Red, and if they have responded and the response cell has some data in it, I want their name to become Green. How would I do this?
I hope that's clear enough? If not I can provide a clearer question.
Thankyou! :)
I want to change a range of cells based on the values of each equivalent adjacent one. For example.
A1 0
A2 1
A3 1
I want to change A1 only if its equivalent adjacent is 0, same rule for A2 and A3, get it?
I want to apply this for a range of cells, because my spreadsheet is really huge and apply this conditional formatting for each cell is really painful.
Thanks!
Thanks very much for your helpful article.
Hello Svetlana - Thank you so much for offering your time to all of us...
I want to change the background color of an entire row to the color associated with the results of the conditional formatting of a cell in the row.
Thanks!
Hello Neil,
Just select the entire rows before creating a rule, or you can edit an existing rule and make sure it applies to all the columns you want to highlight.
You can find full details with screenshots and examples here:
https://www.ablebits.com/office-addins-blog/2013/10/29/excel-change-row-background-color/
In cell m145 I have formula =sum (a1:a5,b3:b7,c4:c9,etc...)
I can not figure out how to click on m145 and command it to change all the cells in the formula to turn a certain color.
Help please.
Its very usefully for me
Hi Svetlana
Thank you for sharing knowledge it is more useful and easy for me by following your steps.
Thanks alot
But the thing I want is instead of values if i keep letter or characters for which i want to change the color of whole row or colum
i got it thank you soo much
I have three cells. If Cell A1 is blank AND cell A2 is also blank then cell A3 should have value of 1.00 with light grey color and if cell A1 is not blank AND cell A2 is not blank then the cell value of A3 should be 1.00 in black colour. How can I do this in Excel?
Useful Article.Thanks...
i want whem i write the world ( done ) the raw will be automatically green .... how can i do that
Hi,
i have one set of numbers in one cell ex: 32 48 52 55 57 59, now i want to color only 32 in this array
please let me know how to do......
i want whan type BUY than cell become Green and whan type SELL than cell become Red pls help on that
I would like to highlight cells which end in a certain number. Cells ending w/ 0,1,2 colored yellow; 3,4,5 green and so on
Hello, Shawn,
Please see the picture below:
Hope this helps.
Many Thanks Easy to understand.
This help me. Thank you
Thanks a lot for adding the drops of knowledge in my excel skills,
I respect as like as Teacher,
DEAR SIR,
I AM USING ONE IF CONDITION FORMULA, WHICH RESULT IS PASS OR FAIL.
I NEED THE RESULT WITH COLOR. EXAMPLE.PASS WITH GREEN BACKGROUND AND FAIL WITH RED COLoUR
i want this cell number 1 to 100 but 15 is grater then color green and other red color then use formula excel sheet
Thanks,
It is really useful for us ...
Thanks again
This article is super helpful. I'm trying to do something based off of your writings and...it's not working. I wonder if I'm going about things completely wrong.
I have a list of student levels on a worksheet from one semester, I have made a duplicate for the next semester, the new values are entered in semester 2 - and want anybody who has moved up to level 4 (Levels run 0 Bad to 4 Achieved) to be highlighted green.
I.e. If you have moved up to level 4 between semesters, you are colored green.
I tried conditional format in the second semester
=AND(Semester1!$A$1:$D$29<4,Semester2!$A$1:$D$29=4)
Format: Fill Green
I don't know if it's because the data is on two sheets (I could put it on one) or if it's the way I'm using the range. Or that I'm being too basic and should just color in the cells myself!
Sorry if I have gotten all this completely wrong. It's nice to find someone on the interweb who makes sense!
Stephanie
Hello, Stephanie,
To help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.
Hi Svetlana,
Actually i am in "sheet1 cell a1" and I want this cell colour to be changed based on the value of "sheet3 cell n12". Can you please help me out. Thanks
Thanks I got just now...
I want chart details for I use two column plan and actual, plan > actual green colour ,and plan < red colour , how to form methed
I want line charts details ,with green colour line shows if its upward and red colour line shows if its Downward ,one line in one chart
I would like to conditionally format cells that contains a date, based on values in different cells. My cell contains a date (indicates due date) and the gradient bar behind it would indicate percentage complete in color (stored in a different cell). Can you provide some help please?
This is awesome, thank you.
I am wondering: I have a group of cells in which I have numbers, and I am usin the MIN command to find the lowest number between these cells. I would like to have a cell next to the one displaying the lowest number then display a block of text and a cell color based on which of these cells has the lowest number, so I can basically show a "winner" of which number is the lowest in the data set. Is this possible?
So essentially it looks like:
1 2 3
Thank you!
I have 2 column's one for start date and the other end date - and next coloum gives the numbers of days between the start date and end date ..
if i say
Column E -- start date
Column F -- End Date
Column G -- no of days
If i want to get the cells in Coloumn G coloured for all the row which donot have a end date ..... how can I format it .
Am prepare timeline chart for project. i need to highlight sundays dates.how to color Sundays by conditional format
This Article is very helpful and it contains many solutions for excel.
Thank you very much.
I need to change the colour of cell as follows
A B C
1 38 40
2 38 42
3 38 35
If B1 is greater than or equal to A1 the colour of C1 should be green otherwise it should be red. Pls help
Hi Everybody,
I need your help.I wanna to fill colour only numeric in excel.
Number is 10000 between 20000.This numbers are fill colour after choice.
Thus, If it's not ok.
Give me another one method, and how to write macro.
Please explain me!
clear n precise...very very useful
Thanks a ton
Finally some easy to follow steps. I needs this time to time and I'm often struggling to achiev what I wanted. This one was awesome ;-)
Dear sevetlana,
Tell me can i put sell value according to name like 'open'and 'closed'.
and if yes then how.
Ajay
I have two coloum in excel one order qnt and obe export qnt and I want after full qnt export automatic course change of qnt.
I have B1 through B400 that I need each cell to change based on cell A1 through A400. I do not want to format each cell manually. Copy and past has all the B cells referance A1. What am I doing wrong?
Hey,
I want excel to change the color of cell in one sheet when value of it's reference cell has been changed in another sheet.
e.g.
sheet 2 contains values referred from sheet 1
and sheet 2 is completely protected.
Then any value in particular cell modified in Sheet 1 shall displayed with another background color in protected Sheet 2
Please let me know, if its possible.
Thanks.
thanks alot, for the information
thanks a lot
Hey ,
Can some one help to achieve this task :
I need to change color of A1 cell based upon following criteria .
if Cell A2 to A10 value is = Y
then
color of A1 cell will be Red
Else
color of A1 cell will be Green
How to add 4rth condition as i want to make 4 conditions for a cell to change color
Hi Svetlana,
Thanks for wondering excel formatting techniques. I would like to know about colour change in excel formatting, When a cell colour is already coloured by conditional formatting, when task changes, i want the colour of that cell to be changed. Could you please give some ideas to that.
thks in advance
rahul
Hi
Could you please advise? How cell will be auto highlighted if we will do any amendment.
i have raw data and values are there but in order to make a report i need to change some values. But after that report i want original data. So if there will be auto highlighted function then i can see the cell easily and put the original value back.
Looking for your urgent reply.
Thank you
Why not keep a master copy of your document and then work only on a copy, so that changes to the copy don't make change to the master document?
Thank you so much... It helped me a lot. I got a great applause from my team. thank you... :) :) :)
I want color in cell according to value as like tank level indication
Hi SC,
This article is really helpful.
I just want to know
Is it Possible to change the color of the text in this way?
Hello Ali,
Of course, it's possible. When setting the format, switch to the Font tab instead of the Fill tab, and select the font color you want.
Dear Svetlana cheusheva
I need an formula for changing the background color in Excel sheet if the value changes then the color of background automatically change using excel formula .Kindly revert if any any information required
Dear Svetlana cheusheva
I need an formula for changing the background color in Excel sheet if the value changes then the color of background automatically change using excel formula .Kindly revert if any information required
Congratz. This is very helpful.
I want to change the color of a cell on one tab and have it automatically change the color of a cell on a different tab. How can I do this? Both cells will have the same text, but be in a different place. I know how to copy data from one sheet to another. But I don't know how to copy the cell color too.
Great, Great,Great..
Very useful article.
i got much help from here.
Thanks alot..
Hi Svetlana Cheusheva,
How the color of the cell will be changed automatically to the same color of a cell already changed via conditional formatting?
Thank you very much.....................
Is it possible to fill up text for the value of cell?? Means if the value of cell is 100, it format text as "Below average". Is it possible?
hi,
Need Your help..
I have problem of high light lowest value from diff.Columns in single row
e.g. i want to do find lowest value from e6:h6 and then highlight with red colur & bold it.
If the Cell A1 is Colour than its - 1
If the cell A1 is Blank than its - 2
Practical.
thank you.
Hi,
Is there a way to format the cells to change from one colour to another depending on a word added to the cell. I want to add "anyword" meaning any word added to the cell will change the colour of the cell.
Thanks,
Hi,
yet, I don't example for :
easy coloring backgroud of cells for chosen values in cells
For example in the row I have values 0 - 100 - in 101 columns
I chose values : e.g. 37, 56, 100 and I want these cells
with chosen backgroud color. For example green.
Thank you for help
Don't why below scenario did not post...seeking auto solution
B3 is red if less than B2
B3 is green if greater the B2
B4 is red if less than B3
B4 is green if grater than B3
ect...
Hi,
I've a array of data (600 rows X 300 columns) want to compare 2 rows of data with conditional formatting and change color. The problem I facing is I can't able to write formula for each cell. If u know any special formula which can be used for entire array, kindly us know.
For Eg, Compare A1 & A2 if A2 is less than A1, change color. Similar need to do for A3&A4, A5&A6, B1&B2, B3&B4, B5&B6, C1&C2, C3&C4, etc...
Hi use this site as I am just more than a novice with Excel but this helps me set what appear to be impossible tasks with ease, all explained in laymans terms.
Many Thanks
Malcolm
thanks i problem solved
How to change the color of States column (column A) automatically according to the color of conditional format applied in other column (Column B)?
hi.. Please tell me if i have a huge data and there is some coloured cell so how can i copied or filtered only those cell which are coloured.
Greetings,
I want to highlight a row in the following manner:
1. Columns C, D, and E will have an X entered in them and they are for good, not good, partially good.
2. When I put an X in the good column for that row, I would like the row from columns A through J to highlight Green, Not good = Red, partially good = yellow. If there is no X in either 3 of these columns then the row is blue. If all 3 have an X then it should also turn Red.
Is there any possible way this could be done through excel?
Thanks in advance.
Hi Svetlana Cheusheva,
Thank you so much,
your article is really helpful.
I have problem that,
How to add "Ablebits" option?