Conditional Formatting: 15 Easy Tips To Make Your Data Beautiful

Conditional Formatting in Excel:
15 Easy Tips To Make Your Data Beautiful

Conditional formatting takes the layout and design options for your Excel sheet to the next level.

Not only does it make your spreadsheet look awesome, it also enables you to make sense of your data and spot important cues in the blink of an eye.

We’re about to show you exactly how in 15 step-by-step tips to conditional formatting.

Download Sample File

Throughout these 15 tips to conditional formatting, we’ll be using a sample file.

This sample file has been created specifically for this guide, so we highly urge you to download it and try out the things we show you!

Download Our Free Sample File

Tip 1: Skip to important data with highlighted cells

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

That can be a pretty tedious process – especially if you have a large set of data.

Example:

Let’s say you have an inventory list, with multiple items and their corresponding quantity in stock.

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

If you’re unaware of conditional formatting, you might place the tip of your finger on your screen and start moving it downwards to see if there are any numbers in that column that is below 100.

This is not a very effective method in a dataset with 50+ rows (and the risk of human error is high).

Instead, let conditional formatting do the job!

After this tip, the only thing you need to do is click a few times with your mouse. You can even follow along as we go with this sample file, which I strongly recommend you do.

Download Our Free Sample File

The first thing you need to do is to select your quantity column. In our case, it’s range C4:C26.

In the ‘Home’ tab of the ribbon (that’s geek for the menu in the top with all the buttons) click ‘Conditional Formatting’.

Then hold your pointer over the option ‘Highlight cells rules’ and click ‘Less than…’

After this, you’ll see a pop-up box with some very simple options.

In the left box, you enter the value that the cell content must be less than in order to “trigger” the conditional formatting.

So if you enter 100, something will happen when the value is less than 100. What that “something” is we’ll define in the box to the right.

As you see the standard option is “Light Red Fill with Dark Red Text”.

If you just click OK now, all the quantities that are below 100 will be formatted this way.

If you want to change that (and you probably will) click the drop-down arrow next to the right box and select one of the other presets.

If you’re not happy with the preset formatting provided by Excel, you can make your own.

Click ‘Custom Format…’ and pick your favorite cell formatting for the cells that are below 100.

That’s it!

Now you can easily identify specific value intervals in your data – in this case, 0 to 99.

This technique can be applied to many other situations, and perhaps a situation where you need to identify cells that have certain text in it.

Read the next tip to learn how…

Tip 2: Highlight cells that contain text

In tip 1 you learned how to highlight values in your sheet that are below 100.

You can use the same method to pinpoint exactly where a certain string of text is.

So if we’re looking for all the M compatible adapters (in the sample file) then we don’t have to use our eyes (very much). Instead, we can let conditional formatting do all the hard work and identify the results easily ourselves.

Select some data with text. In the sample file, we’ll select the range from B4 to B26. So basically all our “Item” names.

Then click the ‘Conditional Formatting’ button on the ‘Home’ tab, hover your mouse over ‘Hightlight Cells Rules’ and click ‘Text that Contains…’.

Then we’ll see a pop-up box very similar to the one we saw in the previous tip.

Basically, you just enter the text that you want the formatting to apply to.

If you write “M compatible” in the left field, then all cells that contain the text “M compatible” will be formatted as you chose in the right box.

So a cell containing more text than you write in the left field will “trigger” the conditional formatting if some of the text is “M compatible”.

Remember that you can also apply a custom format if you don’t think the presets suits your style.

Tip 3: Editing a conditional formatting rule

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

Luckily that’s fairly easy.

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

This manager is accessed from the ‘Conditional Formatting’ button on the ‘Home’ tab.

After you click ‘Manage Rules…’ you’ll see a pop-up box.

  1. “Show formatting rules for:” is set to “Current Selection” by default. Change that to “This Worksheet”.
  2. Find the rule that you want to change in the list.
  3. Press the “Edit Rule…” button.

Another pop-up box appears. From here you can change anything about the rule.

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

  1. Here you can change the ‘Rule Type’. If you don’t want the conditional formatting to apply when “something” happens in a cell, you can change it to apply on all cells and filling them with color based on their value compared to the average of the data. You can also make the rule more advanced by basing it on a formula.
  2. If you keep the ‘Rule Type’ you can change the “trigger” of the formatting. Here you edit your rule so it no longer “triggers” on a cell containing specific text, by choosing “Blanks” or “Cell Value”. This means that the formatting applies when a cell is empty or, for example, holds a value greater than, or less than, 100.
  3. If you keep the ‘Specific text’ you can decide here whether the cell should contain, not contain, begin with or end with the text that you enter in the field to the right.
  4. Here you enter your new text.
  5. From this little (familiar) box you can change the formatting that you applied in the first place. If you don’t want your cells to turn green when it includes the text “L compatible” (or something else), you can have it turn yellow instead.

As you see there’re enough options, but wait – there’s more!

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

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

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

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

Click the formula and change the area. E.g. the current area B4:B26 could be changed to B2:B100.

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

That’s how you edit a conditional formatting rule.

So if you screw one up?

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

Tip 4: Delete a conditional formatting rule

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

This can create immense confusion and on top of that, it can make your workbook slow. In this case, you need to delete some (if not all) of the rules in the sheet.

Removing all conditional formatting rules in a sheet is very straightforward.

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

Then click ‘Clear Rules from Entire Sheet’.

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

Sometimes some rules should still apply.

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

Here you’ll find a pop-up box that will help you greatly in administrating your conditional formatting rules (what other uses this box has we will get into later).

For now, we’ll use it to delete a specific conditional formatting rule.

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

Close the pop-up and apply the deletion by clicking “OK”.

And that is, very simply, how you delete conditional formatting rules in Excel.

Tip 5: Finding the bottom 8 items of your data.

If you’re an inventory manager and your main supplier are closing for restoration for 3 months, you’ll have to refill your stock now before it’s too late. It may be bad timing because you aren’t low on any items, but you have to order some anyway.

You need to pick 8 items that you have the least amount of in stock – and it has to be done in 15 minutes (before office hours ends). How can you make it in time?

In Excel, there are several ways to tell you the 8 lowest items in your inventory, but if you have less than 500 different items in your inventory, I’d chose the fastest way possible: Conditional formatting.

Click ‘Conditional Formatting’ in the ‘Home’ tab. Hover your mouse over Top/Bottom Rules and click ‘Bottom 10 Items…’

Another pop-up appears:

  1. In the field to the left, you can click the tiny arrows to change the number of items from the bottom. If you want to look at the 20 items that are lowest in your inventory, then set the number to 20. The preset is 10, so we change this to 8.
  2. In the field to the right, you can change which formatting that is applied to the 8 bottom items. Click the drop-down arrow to choose between the different presets.

Tip: If you want to use your own formatting style, then click the arrow and click ‘Custom format…” and create it.

That’s how you make a conditional formatting rule for the bottom items in your data.

You can easily create similar rules that apply to the “Top 10” (or whatever number you want) of your data, by using the exact same method as this.

Simply just click ‘Top 10 Items…’ instead of ‘Bottom 10 Items…’.

Tip 6: Using data bars to give immediate overview of highs and lows

When dealing with larger datasets getting the perfect overview of your data becomes difficult.

If you’re not looking for specific values or text in your data, then data bars are perfect for showing you the top and bottom values, performers etc. of your data.

Data Bars is simply a bar in your cells.

The length of the bar is based on the value of the cell compared to the rest of the selected cells. So a short bar means the value is low compared to the rest of the cells and a long bar means the value is high compared to the rest of the cells.

Here’s how to do it: Select the data that you want visualized with the data bars.

In this case, we want to visualize the quantity in stock in column C – so we select range C4:C26.

Click the ‘Conditional Formatting’ button in the ‘Home’ tab of the ribbon. Hover your mouse over ‘Data Bars’. Here you’ll see different formatting options of the data bars.

The formatting options are divided into “Gradient Fill” and “Solid Fill”.

  • Gradient Fill means that the color of each bar fades from the color you chose to a lighter version of that color.
  • Solid Fill simply means that the entire bar is the same exact color. What you choose here is entirely a matter of taste.

I’ve chosen the upper middle color of the “Gradient Fill” and as you can see it gives an instant overview of the highs and lows of the items in stock.

Data bars is a very quick and effective tool to use in larger datasets, though not as “gimmicky” as its sibling ‘Icon sets’. (LINK)

Tip 7: Color Scales

Although a very similar variant to “Data Bars”, “Color Scales” are too a very effective tool for giving the reader an immediate overview of the data in Excel.

The difference between “Color Scales” and “Data Bars” is just the way it’s presented.

Using “Color Scales” is basically a way to assign colors to all the value-filled cells of your choice.

  • One color is assigned to the cell with the lowest value
  • Another color is assigned to the cell with the highest value.
  • All cells and values in between are assigned colors that are a blend of those 2 colors.

You can also choose to set your color scales up with 3 colors, so the value in the middle of the data is assigned this 3rd color.

Let’s get to it!

Start with selecting the data that you want the “Color Scales” applied to.

This time, we’ll select the ‘Purchase prices’ in column E (range E4:E26).

Then click the ‘Conditional Formatting’ button on the ‘Home’ tab. Hover your mouse over ‘Color Scales’ and hover your mouse over the different options in the next box.

When you hover the mouse over the different options you can see the colors change in the selected range (E4:E26). Simply pick the setting you like the most and click it.

Now you’ve applied ‘Color Scales’ to your data.

Color Scales provides the user with a nice overview of essential values in the data AND it looks pretty darn good too, doesn’t it?

Tip 8: Icon Sets

Icon Sets are Excel’s go-to-tool if you’re looking for gimmicky effects that actually has a purpose.

Watch our video below and learn how to use Excel’s funniest formatting tool.

Tip 9: Find duplicate values

Conditional formatting can also be used to identify duplicate values in a data set.

There’s a difference between ‘Removing Duplicates’ (accessed from the ‘Data’ tab) and identifying duplicate values through conditional formatting.

The ‘Remove duplicate’ button simple removes the duplicate values without letting you know which ones you removed.

This means it can do the job for you very quickly and save you a lot of time, but sometimes you need to be more careful with your data and supervise which entries that are removed.

Doing this manually is a slow and tedious process – let’s use conditional formatting instead!

Here’s how to do it:

Select the data that you want to find duplicate values in. It doesn’t have to be numbers, it can also be duplicates of text (e.i. product names, employees, URL’s).

In this case, I’ll look for duplicates in the ‘Item name’ column (range B4:B26) because I’m suspicious that I’ve accidentally entered the same item several times.

Click the ‘Conditional Formatting’ button on the ‘Home’ tab. Then hover your mouse over ‘Highlight Cells Rules’ and click ‘Duplicate Values…’

In the next pop-up box that shows up, choose a formatting style and click ‘OK’.

It doesn’t really matter how the duplicates are formatted since you’re probably going to remove either the cells or the conditional formatting rule again.

Scroll down to the end of the sample data. Now you’ll see 2 red cells with the same text inside. Enter cell B24 and change the ‘C compatible’ to ‘V compatible’.

Tip 10: Hiding zero values

One of the things I use conditional formatting to the most is hiding values that are zero.

This can be done in several ways. One of the common ways is to change the Excel settings so all zero values are hidden.

This “solution” however creates new challenges:

  • If you send the file to someone else who hasn’t changed their Excel settings, they will see the zero values.
  • You can’t decide if you only want to hide the zeros in certain parts of your workbook or sheets. It’s all or nothing.

So let’s do it in a smarter way!

With conditional formatting you can hide “zero values”, send it to someone else and they won’t be able to see the zero values either.

Additionally, you can select specific ranges of your data to be hidden if the values are zero and other places you can choose to see the zero values.

It’s fairly easy:

Select the area where you want to hide the zero values.

I usually choose an entire worksheet, so I just select the entire sheet by clicking a cell and pressing CTRL + A (after that, I press the shortcut once again, since it usually just selects my data first, and not the entire sheet).

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

Click ‘Equal To…’.

Now you see a new box with 2 fields.

In the field to the left, you enter 0.

In the field to the right, you click the arrow next to the field and choose ‘Custom Format…’

(Above process is shown in the animation below)

In this box, you go to the ‘Font’ pane and change the font color from automatic/black to white (or whatever your cell color is).

Now all zeros disappear from the sheet, because the font color of all zeros are changed to match the cell color.

Tip 11: Make a rule dependent on the content of another cell

In some situations, it’s best not to have the conditional formatting rule ‘set in stone’.

What I mean by this is that if you highlight all cells that are above a certain value, it could be clever if that specific value was not typed into the rule, but entered in a cell that the rule is referring to.

That means that you can change the value without having to edit the rule – just by entering another value in a cell.

I’ll give you an example:
I want all the dates in column G to be highlighted when they are more than 60 days older than the date I enter in cell E1 (which is a merged cell).

Select the ‘Last updated’ column (range G4:G26) in the sample file.

Click ‘Conditional Formatting’ on the ‘Home’ tab of the ribbon.

Click ‘New Rule’ and then

1: Select ‘Use a formula to determine which cells to format’.

2: Then enter the formula =G4<$E$1-60

3: Pick the formatting you want for the cells that meets the criteria.

4: Press ‘OK’.

Let me elaborate on above formula:

1)
This first part [ =G4 ] is a reference to the first cell in the selected range where we want to apply this conditional formatting rule. Excel understands that this is not an absolute reference (no $’s).

2)
This is an absolute reference to cell E1 (remember the $’s) which means that it should always look at this specific cell.

3)
The whole expression [ <$E$1-60 ] means that every cell that is more than 60 days before the date in cell E1 is meeting our criteria and should be formatted in a certain way. If you want the rule to apply to cells that are more than 100 days older than what’s in cell E1, then type 100 here instead of 60.

Now you see that most of the cells are formatted the way we chose earlier (I chose a red font). These cells are more than 60 days before the date in cell E1 (14th of October 2015).

When you change the date in E1, then you change the entire formatting rule.

Try to change E1 to 6/14/2015 and see if that changes the result… 😉

Tip 12: Highlighting an entire row based on a value of a cell

Highlighting entire rows with conditional formatting has grown very popular.

This is because it’s much easier to recognize important data when the whole row is highlighted compared to just a single cell. A single cell is hard to see if there are a lot of columns in your data.

Watch the video below and learn how to use this powerful conditional formatting technique.

Tip 13: Highlighting an entire row if it’s within a range of dates

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

In this tip, you’ll learn how to color an entire row with conditional formatting if a date in the row (column G) is between two dates.

First we need to establish these two dates. I’d say August 1st and November 1st. I’m going to enter that in cell G1 and G2. Like this:

Now select your entire data from A4 to G26.

After that click ‘Conditional Formatting’ on the ‘Home’ tab of the ribbon. Then click ‘New Rule’.

Then a pop-up box appears with some different options.

1: Select ‘Use a formula to determine which cells to format’ from the Rule Type menu.

2: Enter the formula:

=AND($G4>$G$1,$G4<$G$2)

This formula tells Excel that we have 2 criteria.

  • The first is that the date in column G (any row) must be after 8/1/2015.
  • The second is that the date in column G (any row) must be before 11/1/2015. This is combined in the formula by the AND function.

3: Now chose your formatting style.

4: Click ‘OK’.

Now we have colored rows that are between the dates in cells G1 and G2.

If you want to change the date interval, simply type some new dates into these cells.

Tip 14: Avoid confusion by knowing the hierarchy of conditional formatting rules

When using several conditional formatting rules at the same time (in the same sheet), even on the same range of cells, trouble can arise.

Think about these two rules.

Rule 1)
All entries updated more than 100 days before the date in E1 must have their entire row colored yellow.

Rule 2)
All quantities below 200 must be colored red.

Both rules are important. One applies to an entire row, the other just to one particular column (B).

Let’s take a look at it.

Click on ‘Sheet3’ in the sample file.

Keeping the 2 rules in mind, you quickly notice that somethings’ not right.

Rule 2 clearly states that all cells in column C (Qty.) should be red if they are below 200. But they’re not red, they’re yellow because of the other conditional formatting rule.

Let’s take a look at the Conditional Formatting Rules Manager.

Click ‘Conditional Formatting’ on the ‘Home’ tab of the ribbon. Then click ‘Manage Rules’.

You can see that the rule that makes the cells red is in the bottom of the two rules.

Now if we change the order of the rules, the cells will actually be colored red instead of yellow.

Let’s try this:

  1. Change the first field from ‘Current Selection’ to ‘This Worksheet’ in the dropdown menu.
  2. Select the first rule.
  3. Click the ‘move down’ arrow.
  4. Click ‘OK’.

Now it looks different.

That’s because the rule that makes all cells in column C red if they are below 200 is in the top of the Conditional Formatting Rules Manager window.

The red color “wins” over the yellow color in this case.

If you wanted the cell to be yellow, leave the setup as it were.

Remember: The most important rules go on top and the order descends according to importance.

Tip 15: Highlighting columns based on a value of a cell

When going through large sets of data, the chance of you missing a specific cell increases as the data increases.

Coloring the specific important cells may be the solution in some cases, but in other cases where the data sets are huge, we need a more visual solution.

The solution?

We change the entire column to be colored if one cell in it meets a certain criterion.

Click on ‘Sheet2’ of the sample file. Here you’ll see a time-log for an employee – David Mosek. He has 4 different assignments to do on his job. Some he does every day. Others every now and then.

We want to make this time-log a bit fancier and more user-friendly.

What we want: Whenever a day’s work is logged, we want the cells in the specific column to turn white to match the background.

Select the range where we want the conditional formatting rule to apply. That’s range B4:AF9.

Click ‘Conditional Formatting’ in the ‘Home’ tab of the ribbon. Then click ‘New Rule’.

Select ‘Use a formula to determine which cells to format’ and then enter the formula

=ISNUMBER(B$9).

Click ‘Format’.

  1. Go to the ‘Fill’ pane.
  2. Click ‘No Color’.
  3. Press ‘OK’.

And click ‘OK’ again.

Instantly you’ll see that column B through G (rows 4 through 9) are no longer grayed out.

Try to enter a value in cell H6 and see the gray disappear.

CLICK HERE to try our free Excel training.

2017-02-28T08:45:51+00:00

Send this to a friend