Excel: Conditional Formatting

Excel: Conditional Formatting


You may already have some experience
using charts to visualize data in Excel. Conditional formatting offers another
way to visualize your data using options like color coding based on the cell’s value. In this example, I have the sales figures
for a group of different salespeople and I’d like to find out who’s meeting or
exceeding their monthly sales goal. Of course we could just look at the numbers, but it might be easier they were highlighted somehow and the good news is that we can actually do just that with a conditional formatting rule. First, select your data then go to Conditional Formatting on the
Home tab. Next, mouse over Highlight Cells Rules and, in this case, we’re going to use Greater Than. Now the sales goal for each person on
this list is $4,000 per month. If we type that number here, you can see
in the background that any cell with a value greater than 4,000 is being
formatted automatically. If you want you can even choose a
different formatting option just click OK when you’re done. As you can see, the formatted cells really stand out now so we can easily tell who’s met or exceeded their goal. It looks like August wasn’t a very good
month, for example, but September and October where much, much better. Next, let’s take a look at some of the
conditional formatting presets. We’re just going to re-select our data and
then go back to the Conditional Formatting command. You have three different types of
presets to choose from: data bars, color scales, and icon sets. To
learn more about each one mouse over these options on the menu. Data bars are kind of like bar graphs; the higher the value the longer the bar in the cell. Color scales, on the other change, will change the background color based on the cell’s value. Icon sets can be used to add different symbols, ratings, and indicators, and you have quite a few styles to
choose from depending on what you need I think this one’s a special easy to read. These icons help us see which items are the highest and also the lowest
when compared to one another. We can also see which ones are about average. Now you might have noticed that the
first rule we applied is still visible. That’s the one that’s changing the fill
color for cells above 4,000. That’s because Excel lets you have multiple conditional formatting rules at the same time, so you can highlight different things. To clear the current formatting rules,
just select your cells, go to Conditional Formatting and
mouse over the option that says Clear Rules. You can clear the rules for just the selected cells or for the entire spreadsheet. In this case, though, I’d like to remove
the greater than 4,000 rule while keeping the rest of the icons in place. We can do that by going to Manage
Rules instead. Here, you’ll find a list of all the rules
that are currently applied here cells. To remove one, just select the rule you want then click Delete Rule, and click OK! Conditional Formatting is a great tool for visualizing your worksheet, so practice using some of these rules on your own and try combining them to see what they can tell you about your data.

You May Also Like

About the Author: Oren Garnes

16 Comments

  1. sorry about my pic my friend and i like to change it while we do italian work but anyway amazing, thank you for making this vid this will definitely help in my work (and between you and me get better grades than the others) coughs multiple times yep ok so g'bye… also sub to my channel ill chaneg the pic back now!!

  2. How do you highlight a particular number or word in a specific color? I just want to highlight the number 01 in all my cells to orange. How do you do that? I tried it this way with conditional format and it makes the entire cell orange not the number.

  3. Hello, how can I color several random numbers from a series, like, i want all numbers 7 48 25 32 17 to be red. and then again, other numbers in other colors, blue for numbers 4,16,19 without having the need to set a new rule for each number, like 7 red, go again and set rule for 48, etc. Thx

  4. Very interesting video. I have tried to add rule in Conditional formatting, and using the excel online this feature does not show on the drop down list. Can anyone advise?  I would assume that this feature is not enabled on the online version.

  5. Hi, I have a rather difficult question: I got a file Excel with activation macro. I use many conditional formatting and I wish that the interval of application would remain unchanged. I think that the problem are the macros but I obtain no effect registering a macro and setting the conditional formatting. Do you have you any ideas? Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *