4 Excel Conditional Formatting Examples for Beginners

Conditional formatting in Excel allows you to quickly and automatically make sense of your data. Without manually scanning rows and columns of figures or text, conditional formatting enables you to easily highlight cells that meet your criteria.

Let’s look at some beginner-level conditional formatting Excel examples:

 

EXAMPLE 1: Using "Greater than"

Let’s say your spreadsheet tracks customer purchases. You want to send a special holiday gift to all customers who spent over $10K.

You could, of course, manually scan the data for these records. But if your customer list is big, this could take a lot of time. Instead, set up a “rule.”

• Highlight the column with the spend data (by simply clicking on the letter at the top of the column).
• On the “home” ribbon (menu) at the top of your screen, click the down arrow on “Conditional Formatting.”
• Choose “highlight cell rules” and select “Greater than…” In the box that pops up, enter 10,000.
• Next choose the color you want your over-$10K customers to show up in. You can choose one of the default colors or get fancy with “custom format.” If you choose the fancy route, another box will pop up with all your choices. Go to the “color:” box and choose something from the existing options. Or, you can go crazy with “more colors,” which, if selected, will pop up a color wheel that lets you customize by simply tapping on the position on the wheel that suits you.

 

Learn more and get your questions answered when you register for our live, online workshop: Getting the Most From Microsoft Excel. 

 

EXAMPLE 2: Finding figures "Between" two numbers (with two conditional formatting rules)

Now, let’s complicate this a tiny bit. Let’s say you want your customers who spent between $5K and $9,999 to also get a gift — maybe something smaller. So, now you’ll have two rules for one column of data.

• Again, highlight the column of spend data and select “Conditional Formatting.”
• Choose “highlight cell rules” and select “Between…”
• This time enter two amounts and a color, then OK. Now you should have your over-$10K customers in one color and your $5K-$9999 customers in another.

EXAMPLE 3: Applying multiple conditional formatting rules at once

There are other ways to do this. Imagine you’ve got multiple levels (rather than just two). Set each rule one at a time…customers > $10K, customers > $9.5K, customers > $9, etc.…each segment with its own color. So, let’s try that now.

• Again, highlight your column and go to the dropdown on “conditional formatting.” First click, “clear rules.”
• Now begin setting your new rules by choosing “highlight cell rules” and “greater than….” Your first rule will be > $5,000 with a color; set another rule for > $5,500 and a color; set another rule for > $6,000, and so on up to > $10,000.
• Because these rules overlap (people over $10K are also over $6K), you’ll need to put your rules in descending order either as you create them or by choosing “manage rules” under the “conditional formatting” dropdown. Once selected, all the rules that apply to that column will now show up. You’ll want to reorder them from largest to smallest and then click apply. Now you should see many colors.

EXAMPLE 4: Highlight the entire line in your spreadsheet using conditional formatting

You can also use conditional formatting that checks the value in a particular cell and then formats an entire row. So rather than just highlight the cell with the data, the entire line (including the customer’s name in this case) will be highlighted.

• To do this, select all the cells you want to check (not just the one column), and go to “conditional formatting.” (This selection should not include any headers you may have.)
• Choose “new rule” and then click “use a formula to determine which cells to format.”
• Enter your formula using the absolute reference to the cell you want to consider. (Absolute value means that you’re able to consider the actual number in that cell rather than any formula or calculation used to get that number.) Absolute value is assigned by adding a $ sign in front of the cell location.
• In this example, let’s assume your spend numbers are in column F and the first customer shows up on line 2, your calculation would be =$F2>10,000.
• Then press format, choose a color, click OK and you’ll go back to the original dialog box, where you’ll click OK again and now the highlighting should extend across the entire spreadsheet.

Explore Excel conditional formatting. There are many, many ways to set rules for both text and numbers that help you “see” your data more easily, without having to calculate or scan manually.

---