Applying basic conditional formatting

Thanks to the many predefined scenarios that Excel offers, you can apply some basic conditional formatting with a few clicks of the mouse. To get a first taste of what you can do, click the Conditional Formatting button found on the Home tab of the Ribbon, as shown in Figure 5-1.

As you can see, Excel has five categories of predefined scenarios: Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets.

Take a moment to review what you can do by using each category of predefined scenario.

Using the Highlight Cells Rules

The formatting scenarios under the Highlight Cells Rules category, shown in Figure 5-2, allow you to highlight those cells whose values meet a specific condition.

The thing to remember about these scenarios is that they work much like an If … then … else statement. That is to say, if the condition is met, the cell is formatted and if the condition is not met, the cell remains untouched.

The scenarios under the Highlight Cells Rules category are self-explanatory. Here’s a breakdown of what you can conditionally format with each scenario:

• Greater Than: A cell whose value is greater than a specified amount. For instance, you can tell Excel to format those cells that contain a value greater than 50.
• Less Than: A cell whose value is less than a specified amount. For instance, you can tell Excel to format those cells that contain a value less than 100.
• Between: A cell whose value is between two given amounts. For example, you can tell Excel to format those cells that contain a value between 50 and 100.
• Equal To: A cell whose value is equal to a given amount. For example, you can tell Excel to format those cells that contain a value that is exactly 50.
• Text That Contains: A cell whose contents contain any form of a given text you specify as a criterion. For example, you can tell Excel to format those cells that contain the text North.
• A Date Occurring: A cell whose contents contain a date occurring in a specified period relative to today’s date. For example, Yesterday, Last Week, Last Month, Next Month, or Next Week.
• Duplicate Values: Both duplicate values and unique values in a given range of cells. This rule was designed more for data clean-up than for dashboarding, enabling you to quickly identify either duplicates or unique values in your dataset.

Take a moment to work the following example of how to apply one of these scenarios. In this simple example, you highlight all values greater than a certain amount.

1. Start by selecting the range of cells to which you need to apply the conditional formatting.

2. Choose the Greater Than scenario found under the Highlight Cells Rules category, shown in Figure 5-2.

   This step opens the dialog box shown in Figure 5-3. In this dialog box, the idea is to define a value that will trigger the conditional formatting.

3. Either type the value (400 in this example) or reference a cell that contains the trigger value, and then use the box’s drop-down menu to specify the format you want applied.

4. Click the OK button.

   Immediately, Excel applies the formatting rule to the selected cells; see Figure 5-4.

The benefit of a conditional formatting rule is that Excel automatically reevaluates the rule every time a cell is changed (as long as that cell has a conditional formatting rule applied to it). For instance, if I were to change any of the low values to 450, the formatting for that value would automatically change because all cells in the dataset have the formatting applied to them.

Applying Top/Bottom Rules

The formatting scenarios under the Top/Bottom Rules category, shown in Figure 5-5, allow you to highlight those cells whose values meet a given threshold.

Like the Highlight Cells Rules, these scenarios work like If…then…else statements: If the condition is met, the cell is formatted; if the condition is not met, the cell remains untouched.

Here is a breakdown of each scenario under the Top/Bottom Rules category:

• Top 10 Items: Although the name doesn’t suggest it, this scenario allows you to specify any number of cells to highlight based on individual cell values (not just ten). For example, you can highlight the top five cells whose values are among the five largest numbers of all elected cells.
• Top 10 %: This scenario is similar to the Top 10 Items scenario: Only the selected cells are evaluated on a percentage basis. Again, don’t let the name fool you: The percent selection does not have to be ten. For instance, you can highlight the cells whose values make up the top 20 percent of the total values of all selected cells.
• Bottom 10 Items: You can use this scenario to specify the number of cells to highlight based on the lowest individual cell values. Again, don’t let the name fool you: You can specify any number of cells to highlight — not just 10. For example, you can highlight the bottom 15 cells whose values are within the 15 smallest numbers among all selected cells.
• Bottom 10 %: Though this scenario is similar to the Bottom 10 Items scenario, in this one, only selected cells are evaluated on a percentage basis. For instance, you can highlight the cells whose values make up the bottom 15 percent of the total values of all the selected cells.
• Above Average: This scenario allows you to conditionally format each cell whose value is above the average of all cells selected.
• Below Average: Allows you to conditionally format each cell whose value is below the average of all cells selected.

To avoid overlapping different conditional formatting scenarios, you may want to clear any conditional formatting you’ve previously applied before applying a new scenario. To clear the conditional formatting for a given range of cells, select the cells and select Conditional Formatting from the Home tab of the Ribbon. There, you find the Clear Rules selection. Click Clear Rules and select whether you want to clear conditional formatting for the entire sheet or only the selected workbook.

In the following example, you conditionally format all cells whose values are within the top 40 percent of the total values of all cells.

1. Start by selecting the range of cells to which you need to apply the conditional formatting.

2. Choose the Top 10 % scenario found under the Top/Bottom Rules category; refer to Figure 5-5.

   This step opens the Top 10% dialog box shown in Figure 5-6. The idea here is to define the threshold that that will trigger the conditional formatting.

3. In this example, enter 40 and then use the box’s drop-down menu to specify the format you want applied.

4. Click OK.

   Immediately, Excel applies the formatting scenario to the selected cells. See Figure 5-7.

Creating Data Bars

Data Bars fill each cell you are formatting with mini-bars in varying length, indicating the value in each cell relative to other formatted cells. Excel essentially takes the largest and smallest values in the selected range and calculates the length for each bar. To apply Data Bars to a range, do the following:

1. Select the target range of cells to which you need to apply the conditional formatting.

2. Choose Data Bars from the Conditional Formatting menu on the Home tab, as demonstrated in Figure 5-8.

   As you can see in Figure 5-9, the result is essentially a mini-chart within the cells you selected. Also note that by default, the Data Bars scenario accounts for negative numbers nicely by changing the direction of the bar and inverting the color to red.

Applying Color Scales

Color Scales fill each cell you are formatting with a color varying in scale based on the value in each cell relative to other formatted cells. Excel essentially takes the largest and smallest values in the selected range and determines the color for each cell. To apply Color Scales to a range, do the following:

1. Select the target range of cells to which you need to apply the conditional formatting.

2. Choose Color Scales from the Conditional Formatting menu on the Home tab. (See Figure 5-10.)

   As you can see in Figure 5-11, the result is a kind of heat-map within the cells you selected.

Using Icon Sets

Icon Sets are sets of symbols that are inserted in each cell you are formatting. Excel determines which symbol to use based on the value in each cell relative to other formatted cells. To apply an Icon Set to a range, do the following:

1. Select the target range of cells to which you need to apply the conditional formatting.

2. Choose Icon Sets from the Conditional Formatting menu on the Home tab.

   As you can see in Figure 5-12, you can choose from a menu of Icon Sets varying in shape and color.

   Figure 5-13 illustrates how each cell is formatted with a symbol indicating each cell’s value based on the other cells.

Adding your own formatting rules manually

You don’t have to use one of the predefined scenarios offered by Excel. Excel gives you the flexibility to create your own formatting rules manually. Creating your own formatting rules helps you better control how cells are formatted and allows you to do things you wouldn’t be able to do with the predefined scenarios.

For example, a useful conditional formatting rule is to tag all above-average values with a Check icon and all below-average values with an X icon. Figure 5-14 demonstrates this rule.

Although it’s true that the Above Average and Below Average scenarios built into Excel allow you to format cell and font attributes, they don’t enable the use of Icon Sets. You can imagine why Icon Sets would be better on a dashboard than simply color variances. Icons and shapes do a much better job of conveying your message, especially when the dashboard is printed in black-and-white.

To get started in creating your first custom formatting rule, open the Chapter 5 Samples file found among the sample files on this book’s companion website. After the file is open, go to the Create Rule by Hand tab, and then follow these steps:

1. Select the target range of cells to which you need to apply the conditional formatting, and select New Rule from the Conditional Formatting menu, as demonstrated in Figure 5-15.

   This step opens the New Formatting Rule dialog box shown in Figure 5-16. As you look at the rule types at the top of the dialog box, you may recognize some of them from the predefined scenario choices discussed earlier in this chapter. Here’s what each type does:

   • Format All Cells Based on Their Values: Measures the values in the selected range against each other. This selection is handy for finding general anomalies in your dataset.
   • Format Only Cells That Contain: Applies conditional formatting to those cells that meet specific criteria you define. This selection is perfect for comparing values against a defined benchmark.
   • Format Only Top or Bottom Ranked Values: Applies conditional formatting to those cells that are ranked in the top or bottom Nth number or percent of all values in the range.
   • Format Only Values That Are Above or Below Average: Applies conditional formatting to those values that are mathematically above or below the average of all values in the selected range.
   • Use a Formula to Determine Which Cells to Format: Evaluates values based on a formula you specify. If a particular value evaluates to true, the conditional formatting is applied to that cell. This selection is typically used when applying conditions based on the results of an advanced formula or mathematical operation.

   Data Bars, Color Scales, and Icon Sets can be used only with the Format All Cells Based on Their Values rule type.

2. Ensure that the Format All Cells Based on Their Values rule type is selected and then use the Format Style drop-down menu to switch to Icon Sets.
3. Click the Icon Style drop-down menu to select an Icon Set.
4. Change both Type drop-down menus to Formula.

5. In each Value box, enter =Average($C$2:$C$22).

   This step tells Excel that the value in each cell must be greater than the average of the entire dataset in order to get the Check icon.

   At this point, the dialog box looks similar to the one in Figure 5-17.

6. Click OK to apply your conditional formatting.

It’s worth taking some time to understand how this conditional formatting rule works. Excel assesses every cell in the target range to see whether its contents match, in order (top box first), the logic in each Value box. If a cell contains a number or text that evaluates true to the first Value box, the first icon is applied and Excel moves on to the next cell in the range. If not, Excel continues down each Value box until one of them evaluates to true. If the cell being assessed does not fit any of the logic placed in the Value boxes, Excel automatically tags that cell with the last icon.

In this example, you want a cell to get a Check icon only if the value of the cell is greater than (or equal to) the average of the total values. Otherwise, you want Excel to skip directly to the X icon and apply the X.

Showing only one icon

In many cases, you may not need to show all icons when applying the Icon Set. In fact, showing too many icons at one time may serve only to obstruct the data you’re trying to convey on the dashboard.

In the earlier example, you apply a Check icon to values above the average for the range and apply an X icon to all below-average values; see Figure 5-18. However, in the real world, you often need to bring attention to only the below-average values. This way, your eyes aren’t inundated with superfluous icons.

Excel provides a clever mechanism to allow you to stop evaluating and formatting values if a condition is true.

In this example, you want to remove the Check icons. The cells that contain those icons all have values above the average for the range. Therefore, you first need to add a condition for all cells whose values are above average. To do so, follow these steps:

1. Select the target range of cells, and then go to the Home tab and select Conditional Formatting ⇒ Manage Rules.

   This step opens the Conditional Formatting Rules Manager dialog box shown in Figure 5-19.

2. Click the New Rule button to start a new rule.

   The New Formatting Rule dialog box appears.

3. Click the Format Only Cells That Contain rule type and then configure the rule so that the format applies only to cell values greater than the average; see Figure 5-20.
4. Click OK without changing any of the formatting options.
5. Back in the Conditional Formatting Rules Manager, click to select the Stop If True check box, as demonstrated on the right side of Figure 5-21.

6. Click OK to apply your changes.

   As you can see in Figure 5-22, only the X icons are now shown. Again, this allows your audience to focus on the exceptions rather than determining which icons are good and bad.

Showing Data Bars and icons outside of cells

Bars and Icon Sets give you a snazzy way to add visualizations to your dashboards; you don’t have a lot of say in where they appear within the cell. Take a look at Figure 5-23 to see what I mean.

By default, the Data Bars are placed directly inside each cell, which in this case almost obfuscates the data. From a dashboarding perspective, this is less than ideal, for two reasons:

• The numbers can get lost in the colors of the Data Bars, making them difficult to read — especially when printed in black-and-white.
• It’s difficult to see the ends of each bar.

The solution to this problem is to show the Data Bars outside the cell that contains the value. Here’s how:

1. To the right of each cell, enter a formula that references the cell containing the data value.

   For example, if the data is in B2, go to cell C2 and enter =B2.

2. Apply the Data Bar conditional formatting to the formulas you just created.
3. Select the formatted range of cells and select Manage Rules under the Conditional Formatting button on the Home tab of the Ribbon.
4. In the dialog box that opens, click the Edit Rule button.
5. Select the Show Bar Only option, as demonstrated in Figure 5-24.
6. Click OK to apply the change.

The reward for your efforts is a cleaner view that’s much better suited for reporting in a dashboard environment. Figure 5-25 illustrates the improvement gained with this technique.

Using the same technique, you can separate Icon Sets from the data — allowing you to position the icons where they best suit your dashboard.

Representing trends with Icon Sets

A dashboard environment may not always have enough space available to add a chart that shows trending. In these cases, Icon Sets are ideal replacements, enabling you to visually represent the overall trending without taking up a lot of space. Figure 5-26 illustrates this concept with a table that provides a nice visual element, allowing for an at-a-glance view of which markets are up, down, or flat over the previous month.

You may want to do the same type of thing with your reports. The key is to create a formula that gives you a variance or trending of some sort.

To achieve this type of view, follow these steps:

1. Select the target range of cells to which you need to apply the conditional formatting.

   In this case, the target range will be the cells that hold your variance formulas.

2. Choose Icon Sets from the Conditional Formatting menu on the Home tab and then choose the most appropriate icons for your situation.

   For this example, choose the set with three arrows shown in Figure 5-27.

   In most cases, you’ll adjust the thresholds that define what up, down, and flat mean. Imagine that you need any variance above 3 percent to be tagged with an up arrow, any variance below –3 percent to be tagged with a down arrow, and all others to show flat.

3. Select the target range of cells and select Manage Rules under the Conditional Formatting button on the Home tab of the Ribbon.
4. In the dialog box that opens, click the Edit Rule button.
5. Adjust the properties, as shown in Figure 5-28.
6. Click OK to apply the change.

Notice in Figure 5-28 that the Type property for the formatting rule is set to Number even though the data you’re working with (the variances) is percentages. You’ll find that working with the Number setting gives you more control and predictability when setting thresholds.

Finding the Camera tool

Before you can use the Camera tool, you have to find it and add it to the Quick Access toolbar.

The Quick Access toolbar is a customizable toolbar on which you can store frequently used commands so that they’re always accessible with just one click. You can add commands to the Quick Access toolbar by dragging them directly from the Ribbon or by using the Customize menu.

Follow these steps to add the Camera tool to the Quick Access toolbar:

1. Click the File button.
2. Open the Excel Options dialog box by clicking the Options button.
3. Click the Quick Access Toolbar button.
4. On the Choose Commands From drop-down list, select Commands Not in the Ribbon.
5. Scroll down the alphabetical list of commands shown in Figure 5-35 and find Camera; double-click it to add it to the Quick Access toolbar.

6. Click OK.

   After you’ve taken these steps, you see the Camera tool on the Quick Access toolbar, as shown in Figure 5-36.

Using the Camera tool

To use the Camera tool, you simply highlight a range of cells to then capture everything in that range in a live picture. The cool thing about the Camera tool is that you’re not limited to showing a single cell’s value, as you are with a linked text box. And because the picture is live, any updates made to the source range automatically change the picture.

Take a moment to walk through this basic demonstration of the Camera tool. In Figure 5-37, you see some simple numbers and a chart based on those numbers. The goal here is to create a live picture of the range that holds both the numbers and the chart.

Follow these steps:

1. Highlight the range that contains the information you want to capture.

   In this scenario, you select B3:F13 to capture the area with the chart.

2. Select the Camera tool icon on the Quick Access toolbar.

   You added the Camera tool to the Quick Access toolbar in the preceding section.

3. Click the worksheet in the location where you want to place the picture.

   Excel immediately creates a live picture of the entire range, as shown in Figure 5-38.

   Changing any number in the original range automatically causes the picture to update.

By default, the picture that’s created has a border around it. To remove the border, right-click the picture and select Format Picture from the menu that appears. This opens the Format Picture dialog box. On the Colors and Lines tab, you see the Line Color drop-down list. There, you can select No Color, thereby removing the border. On a similar note, to get a picture without gridlines, simply remove the gridlines from the source range.

Enhancing a dashboard with the Camera tool

Here are a few ways to go beyond the basics and use the Camera tool to enhance your dashboards and reports:

• Consolidate disparate ranges into one print area. Sometimes a data model gets so complex that it’s difficult to keep the final data in one printable area. This often forces you to print multiple pages that are inconsistent in layout and size. Given that dashboards are most effective when contained in a compact area that can be printed in a page or two, complex data models prove to be problematic when it comes to layout and design.

  You can use the Camera tool in these situations to create live pictures of various ranges that you can place on a single page. Figure 5-39 shows a workbook that contains data from various worksheets. The secret here is that these data ranges are nothing more than linked pictures created by the Camera tool.

  As you can see, you can create and manage multiple analyses on different tabs and then bring together all your presentation pieces into a nicely formatted presentation layer.

• Rotate objects to save time. Again, because the Camera tool outputs pictures, you can rotate the pictures in situations in which placing the copied range on its side can help save time. A great example is a chart: Certain charts are relatively easy to create in a vertical orientation but extremely difficult to create in a horizontal orientation.

  It’s the Camera tool to the rescue! When the live picture of the chart is created, all you have to do is change the alignment of the chart labels and then rotate the picture using the rotate handle to create a horizontal version.

• Create small charts. When you create pictures with the Camera tool, you can resize and move the pictures around freely. This gives you the freedom to test different layouts and chart sizes without the need to work around column widths, hidden rows, or other nonsense.