I wanted to show one other thing helpful thing that Excel can do when you need to highlight data called conditional formatting. (Note: this tutorial is image heavy!)
Conditional formatting basically allows you to use colors, font styles, and borders to highlight cells that meet certain conditions. Unlike IF and other Excel functions, conditional formatting is found as a pull-down menu item in Excel rather than a function that you can type into a cell.
With the same salesman example I’ve been using in the previous tutorials on using the IF function, let’s look at how this would work. First, here’s the raw data, unformatted:
Before we go further, if you’d like to work through the examples yourself, here’s the raw data. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A1 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.
Bonus |
12% |
|
Minimum amount |
100000 |
|
Maximum amount |
200000 |
|
|
Sales |
Bonus |
Salesman A |
87925 |
No bonus |
Salesman B |
100000 |
12000 |
Salesman C |
145000 |
17400 |
Salesman D |
200750 |
No bonus |
Salesman E |
178650 |
21438 |
Salesman F |
99555 |
No bonus |
Salesman G |
147000 |
17640 |
Salesman H |
213450 |
No bonus |
Salesman I |
122680 |
14721.6 |
Salesman J |
92500 |
No bonus |
Ok, now back to the tutorial.
Suppose you wanted to highlight all the sales that were above the minimum amount of $100K green, and any that were below the minimum amount red. How would you do that?
Read the rest of this entry »