On this article, you’ll discover two fast methods to alter the background shade of cells based mostly on worth in Excel 2016, 2013 and 2010. Additionally, you’ll discover ways to use Excel formulation to alter the colour of clean cells or cells with components errors.
Everybody is aware of that altering the background shade of a single cell or a variety of knowledge in Excel is simple as clicking the “Fill shade”
You are watching: how to change color based on value in excel
- Change the background shade of cells based mostly on worth (dynamically) – The background shade will change robotically when the cell worth modifications.
- Change a cell’s shade based mostly on its present worth (statically) – As soon as set, the background shade is not going to change irrespective of how the cell’s worth modifications.
- Change shade of particular cells (blanks / with errors / with formulation)
The way to change a cell’s shade based mostly on worth in Excel dynamically
The background shade will change depending on the cell’s worth.
Job: You will have a desk or vary of knowledge, and also you need to change the background shade of cells based mostly on cell values. Additionally, you need the colour to alter dynamically reflecting the information modifications.
Answer: That you must use Excel conditional formatting to spotlight the values better than X, lower than Y or between X and Y.
Suppose you’ve gotten a listing of gasoline costs in numerous states and also you need the costs better than USD 3.7 to be of the colour pink and equal to or lower than USD 3.45 to be of the colour inexperienced.
Okay, here’s what you do step-by-step:
- Choose the desk or vary the place you need to change the background shade of cells. On this instance, we have chosen $B$2:$H$10 (the column names and the primary column itemizing the state names are excluded from the choice).
- Navigate to the Dwelling tab, Kinds group, and select Conditional Formatting > New Rule….
- Within the New Formatting Rule dialog field, choose “Format solely cells that comprise” below “Choose a Rule Sort” field within the higher a part of the dialog field.
- Within the decrease a part of the dialog field below “Format Solely Cells with part”, set the rule circumstances. We select to format solely cells with a Cell Worth – better than – 3.7, as you may see within the screenshot beneath. Then click on the Format… button to decide on what background shade to use when the above situation is met.
- Within the Format Cells dialog field, swap to the Fill tab and choose the colour of your alternative, the reddish shade in our case, and click on OK.
- Now you’re again to the New Formatting Rule window and the preview of your format modifications is displayed within the Preview field. If every little thing is Okay, click on the OK button. The results of your formatting will look much like this:
Since we have to apply yet another situation, i.e. change the background of cells with values equal to or lower than 3.45 to the inexperienced shade, click on the New Rule button once more and repeat steps 3 – 6 setting the required situation. Right here is the Preview of our second conditional formatting rule:
If you end up achieved, click on the OK button. What you’ve gotten now could be a properly formatted desk that permits you to see the very best and lowest gasoline costs throughout totally different states at a look. Fortunate they’re in Texas 🙂
The way to completely change a cell’s shade based mostly on its present worth
As soon as set, the background shade is not going to change irrespective of how the cell’s contents may change sooner or later.
Job: You need to shade a cell based mostly on its present worth and need the background shade to stay the identical even when the cell worth’s modifications.
Answer: Discover all cells with a sure worth or values utilizing Excel’s Discover All operate or Choose Particular Cells add-in, after which change the format of discovered cells utilizing the Format Cells characteristic.
That is a type of uncommon duties that aren’t coated in Excel assist recordsdata, boards and blogs and for which there isn’t any easy resolution. And that is comprehensible, as a result of this activity is just not typical. And nonetheless, if you want to change the background shade of cells statically i.e. as soon as and perpetually except you modify it manually once more, proceed with the next steps.
Discover and choose all cells that meet a sure situation
There could also be a number of doable eventualities relying on what sort of values you’re searching for.
If you want to shade cells with a selected worth, e.g. 50, 100 or 3.4, go to the Dwelling tab, Enhancing group, and click on Discover Choose > Discover….
Enter the wanted values and click on the Discover All button.
In our earlier instance, if we would have liked to search out all gasoline costs between 3.7 and three.799, we might specify the next search standards:
Now choose any of the discovered gadgets within the decrease a part of the Discover and Exchange dialog window by clicking on it after which press Ctrl + A to pick out all discovered entries. After that click on the Shut button.
That is how you choose all cells with a sure worth(s) utilizing the Discover All operate in Excel.
Nevertheless, what we really want is to search out all gasoline costs larger than 3.7 and regrettably Excel’s Discover and Exchange dialog doesn’t permit for such issues.
Fortunately, there may be one other instrument that may deal with such advanced circumstances. The Choose Particular Cells add-in enables you to discover all values in a specified vary, e.g. between -1 and 45, get the utmost / minimal worth in a column, row or vary, discover cells by font shade, fill shade and far more.
You click on the Choose by Worth button on the ribbon after which specify your search standards on the add-in’s pane, in our instance we’re searching for values better than 3.7. Click on the Choose button and in a second you should have a end result like this:
If you’re to strive the Choose Particular Cells add-in, you may obtain an analysis model right here.
Change the background shade of chosen cells utilizing “Format Cells” dialog
Now that each one cells with a specified worth or values are chosen (both through the use of Excel’s Discover and Exchange or Choose Particular Cells add-in) what’s left so that you can do is power the background shade of chosen cells to alter when a worth modifications.
Open the Format Cells dialog by urgent Ctrl + 1 (you too can proper click on any of chosen cells and select “Format Cells…” from the pop-up menu, or go to Dwelling tab > Cells group > Format > Format Cells…) and make all format modifications you need. We are going to select to alter the background shade in orange this time, only for a change 🙂
If you wish to alter the background shade solely with out another format modifications, then you may merely click on the Fill shade button and select the colour to your liking.
Right here is the results of our format modifications in Excel:
Do you know: Welcome to f.lux | Photoshop Services
In contrast to the earlier method with conditional formatting, the background shade set on this means won’t ever change once more with out your discover, irrespective of how the values change.
Change background shade for particular cells (blanks, with components errors)
Like within the earlier instance, you may change the background shade of particular cells in two methods, dynamically and statically.
Use Excel components to alter background shade of particular cells
A cell’s shade will change robotically based mostly on the cell’s worth.
This methodology offers an answer that you’ll more than likely want in 99% of circumstances, i.e. the background shade of cells will change in response to the circumstances you set.
We’re going to use the gasoline costs desk once more for example, however this time a few extra states are included and a few cells are empty. See how one can detect these clean cells and alter their background shade.
- On the Dwelling tab, within the Kinds group, click on Conditional Formatting > New Rule… (see step 2 of The way to dynamically change a cell shade based mostly on worth for step-by-step steerage).
- Within the “New Formatting Rule” dialog, choose the choice “Use a components to find out which cells to format”. Then enter one of many following formulation within the “Format values the place this components is true” area:
- =IsBlank()– to alter the background shade of clean cells.
- =IsError() – to alter the background shade of cells with formulation that return errors.
Since we’re interested by altering the colour of empty cells, enter the components =IsBlank(), then place the cursor between parentheses and click on the Collapse Dialog button within the right-hand a part of the window to pick out a variety of cells, or you may sort the vary manually, e.g. =IsBlank(B2:H12).
- Click on the Format… button and select the wanted background shade on the Fill tab (for detailed directions, see step 5 of “The way to dynamically change a cell shade based mostly on worth”) after which click on OK.
The preview of your conditional formatting rule will look much like this:
- If you’re pleased with the colour, click on the OK button and you will see the modifications instantly utilized to your desk.
Change the background shade of particular cells statically
As soon as modified, the background shade will stay the identical, whatever the cell values’ modifications.
If you wish to change the colour of clean cells or cells with components errors completely, comply with this manner.
- Choose your desk or a variety and press F5 to open the “Go To” dialog, after which click on the “Particular…” button.
- Within the “Go to Particular” dialog field, test the Blanks radio button to choose all empty cells. If you wish to spotlight cells containing formulation with errors, select Formulation > Errors. As you may see within the screenshot above, a handful of different choices can be found to you.
- And at last, change the background of chosen cells, or make another format customizations utilizing the “Format Cells” dialog as described in Altering the background of chosen cells.
Simply keep in mind that formatting modifications made on this means will persist even when your clean cells get full of information or components errors are corrected. After all, it is laborious to think about off the highest of the top why somebody might need to have it this manner, could also be only for historic functions 🙂
The way to get most of Excel and make difficult duties straightforward
As an energetic person of Microsoft Excel, you understand that it has loads of options. A few of them we all know and love, others are a whole thriller for a median person and varied blogs, together with this one, are attempting to shed at the very least some mild on them. However! There are a number of quite common duties that each one of us must carry out day by day and Excel merely doesn’t present any options or instruments to automate them or make an inch simpler.
For instance, if you want to test 2 worksheets for duplicates or merge rows from single or totally different spreadsheets, it will take a bunch of arcane formulation or macros and nonetheless there isn’t any assure you’ll get the correct outcomes.
That was the explanation why a crew of our greatest Excel builders designed and created 60+ add-ins that we name the Final Suite for Excel. These good instruments deal with probably the most grueling, painstaking and error-prone duties in Excel and guarantee rapidly, neatly and flawless outcomes. Under is a brief checklist of simply a number of the duties the add-ins may also help you with:
- Take away duplicates and discover distinctive values
- Merge tables and mix information from totally different sources
- Mix duplicate rows into one
- Merge cells, rows and columns
- Discover and changing in all information, in all workbooks
- Generate random numbers, passwords and customized lists
- And far, far more.
Simply strive these add-ins and you will note that your Excel productiveness will enhance as much as 50%, on the very least!
That is all for now. In my subsequent article we’ll proceed to discover this matter additional and you will note how one can rapidly change the background shade of a row based mostly on a cell worth. Hope to see you on our weblog subsequent week!
You may additionally be interested by
- Excel conditional formatting based mostly on formulation
- Icon Units, Knowledge Bars and Coloration Scales
- Excel conditional formatting for dates
- The way to alternate row shading in Excel
- The way to depend and sum Excel cells by shade
- Video: Excel conditional formatting based mostly on one other cell worth