Chapter 5

Formatting Your Way to Visualizations

In This Chapter

arrow Using conditional formatting

arrow Working with symbols in formulas

arrow Using the Camera tool

arrow Creating a waffle chart

Visualization is the presentation of abstract concepts or data in visual terms through some sort of graphical imagery. A traffic light, for example, is a visualization of the abstract concepts of stop-and-go.

In the business world, visualizations help us communicate and process the meaning of data faster than simple tables of numbers. Excel offers business analysts a wide array of features that can be used to add visualizations to dashboards and reports.

In this chapter, you explore some of the formatting techniques you can leverage to add layers of visualizations that can turn your data into meaningful views.

Enhancing Reports with Conditional Formatting

Conditional formatting is the term given to Excel’s capability to dynamically change the formatting of a value, cell, or range of cells based on a set of conditions you define. Conditional formatting adds a level of visualization that allows you to look at your Excel reports and make split-second determinations on which values are “good” and which are “bad,” simply based on formatting.

In this section, you enter the world of conditional formatting as you see how to leverage this functionality to enhance your reports and dashboards.

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.

image

Figure 5-1: The predefined conditional formatting scenarios available in Excel.

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.

image

Figure 5-2: The Highlight Cells Rules scenarios apply formats if specific conditions are met.

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.

image

Figure 5-3: Each scenario has its own dialog box you can use to define the trigger values and the format for each rule.

image

Figure 5-4: Cells greater than 400 are formatted.

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.

image

Figure 5-5: The Top/Bottom Rules scenarios apply formats if specific thresholds are met.

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.

remember 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.

image

Figure 5-6: Each scenario has its own dialog box you can use to define the trigger values and the format for each scenario.

image

Figure 5-7: With conditional formatting, you can easily see that September through December makes up 40 percent of the total value in this dataset.

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.

image

Figure 5-8: Applying Data Bars.

image

Figure 5-9: Conditional formatting with Data Bars.

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.

image

Figure 5-10: Applying Color Scales.

image

Figure 5-11: Conditional formatting with Color Scales.

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.

image

Figure 5-12: Applying Icon Sets.

image

Figure 5-13: Conditional formatting with Icon Sets.

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.

image

Figure 5-14: With a custom formatting rule, you can tag the above-average values with a check mark and the below-average values with an X.

remember 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.

    tip 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.
image

Figure 5-15: Select the target range and then select New Rule.

image

Figure 5-16: Select the Format All Cells Based on Their Values rule and then use the Format Style drop-down menu to switch to Icon Sets.

image

Figure 5-17: Change the Type drop-down boxes to Formula and enter the appropriate formulas in the Value boxes.

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.

image

Figure 5-18: Too many icons can hide the items you want to draw attention to.

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.

image

Figure 5-19: Open the Conditional Formatting Rules Manager and click New Rule.

image

Figure 5-20: This new rule is meant to apply to any cell value that you don’t want formatted — in this case, any value that’s greater than the average of the range.

image

Figure 5-21: Click Stop If True to tell Excel to stop evaluating those cells that meet the first condition.

image

Figure 5-22: This table is now formatted to show only one icon.

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.

image

Figure 5-23: Showing Data Bars inside the same cell as values can make it difficult to analyze the data.

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.
image

Figure 5-24: Edit the formatting rule to show only the Data Bars, not the data.

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.

image

Figure 5-25: Data Bars, cleanly placed next to the data values.

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.

image

Figure 5-26: Conditional Formatting Icon Sets enable trending visualizations.

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.
image

Figure 5-27: The up arrow indicates an upward trend; the down arrow indicates a downward trend; and the right arrow indicates a flat trend.

image

Figure 5-28: You can adjust the thresholds that define what up, down, and flat mean.

tip 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.

Using Symbols to Enhance Reporting

Symbols are essentially tiny graphics, not unlike those you see when you use Wingdings or Webdings or other fancy fonts. However, symbols are not really fonts. They’re Unicode characters. Unicode characters are a set of industry standard text elements designed to provide a reliable character set that remains viable on any platform regardless of international font differences.

One example of a commonly used symbol is the copyright symbol (©). This symbol is a Unicode character. You can use it on a Chinese, Turkish, French, or American PC, and it will reliably be available, with no international differences.

In terms of Excel presentations, Unicode characters (or symbols) can be used in places where conditional formatting cannot. For instance, in the chart labels you see in Figure 5-29, the x-axis shows some trending arrows that allow for an extra layer of analysis. This couldn’t be done with conditional formatting.

image

Figure 5-29: Use symbols to add an extra layer of analysis to charts.

Let me take some time now to review the steps that led to the chart in Figure 5-29.

Start with the data shown in Figure 5-30. Note that you have a designated cell — C1 in this case — to hold any symbols you’re going to use. This cell isn’t all that important. It’s just a holding cell for the symbols you’ll insert.

image

Figure 5-30: The starting data with a holding cell for symbols.

Now follow these steps:

  1. Click in C1 and then select the Symbol command on the Insert tab.

    The Symbol dialog box shown in Figure 5-31 opens.

  2. Find and select symbols by clicking the Insert button after each symbol.

    In this scenario, select the down-pointing triangle and click Insert. Then click the up-pointing triangle and click Insert. Close the dialog box when you’re done.

    At this point, you have the up-triangle and down-triangle symbols in cell C1, as shown in Figure 5-32.

  3. Click the C1 cell, go to the Formula bar, and copy the two symbols by highlighting them and pressing Ctrl+C on the keyboard.
  4. Go to the data table, right-click the percentages, and then select Format Cells from the menu that appears.
  5. In the Format Cells dialog box, create a new custom format by pasting the up- and down-triangle symbols into the appropriate syntax parts; see Figure 5-33.

    In this case, any positive percentage will be preceded by the up-triangle symbol, and any negative percentage will be preceded by the down-triangle symbol.

    tip Not familiar with custom number formatting? Chapter 3 covers the ins and outs of custom number formatting in detail.

  6. Click OK.

    The symbols are now part of your number formatting! Figure 5-34 illustrates what the percentages look like. Change any number from positive to negative (or vice versa), and Excel automatically applies the appropriate symbol.

image

Figure 5-31: Use the Symbol dialog box to insert symbols into the holding cell.

image

Figure 5-32: Copy the newly inserted symbols to the Clipboard.

image

Figure 5-33: Create a custom number format using the symbols.

image

Figure 5-34: Your symbols are now part of the number formatting.

Because charts automatically adopt number formatting, a chart created from this data shows the symbols as part of the labels. Simply use this data as the source for the chart.

This is just one way to use symbols in your reporting. With this basic technique, you can insert symbols to add visual appeal to tables, pivot tables, formulas, or any other object you can think of.

Wielding the Magical Camera Tool

Excel’s Camera tool enables you to take a live picture of a range of cells that updates dynamically while the data in that range updates. If you’ve never heard of it, don’t feel bad. This nifty tool has been hidden away in the last few versions of Excel. Although Microsoft has chosen not to include this tool on the mainstream Ribbon, it’s actually quite useful if you’re building dashboards and reports.

Finding the Camera tool

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

tip 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.

image

Figure 5-35: Add the Camera tool to the Quick Access toolbar.

image

Figure 5-36: Not surprisingly, the icon for the Camera tool looks like a camera.

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.

image

Figure 5-37: Enter some simple numbers in a range and create a basic chart from those numbers.

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.

image

Figure 5-38: A live picture is created via the Camera tool.

tip 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.
image

Figure 5-39: Use the Camera tool to get multiple source ranges into a compact area.

Making Waffles with Conditional Formatting and the Camera Tool

After you have a few tricks under your belt, you can have a bit of fun and use conditional formatting and the Camera tool together to make a waffle chart!

A waffle chart is an interesting visualization that helps display progress toward a goal. As you can see in Figure 5-40, a waffle chart is basically a square divided into a 10 x 10 grid. Each grid box represents 1 percent toward a goal of 100 percent. The number of grid boxes that are colored or shaded is determined by the associated metric. This kind of chart is a relatively effective option when you want to add an interesting visualization to the dashboard without distorting the data or taking up too much dashboard real estate.

image

Figure 5-40: Three waffle charts, side by side.

Waffle charts are relatively easy to build using a little conditional formatting know-how. Follow these steps to create your first waffle chart:

  1. On a new worksheet, dedicate a cell for your actual metric and then create a 10 x 10 grid of percentages that range from 1% to 100%.

    Figure 5-41 demonstrates the initial setup you need.

  2. Highlight the 10 x 10 grid and select Home ⇒ Conditional Formatting ⇒ New Rule.
  3. Create a rule that colors each cell in the 10 x 10 grid if the cell value is less than or equal to the value shown in the metric cell (A2 in this example).

    Figure 5-42 illustrates what the formatting rule should look like.

  4. Click the OK button to confirm the conditional format.

    remember Be sure to apply the same color format for both the fill and the font. This ensures that the percentage values in the 10 x 10 grid are hidden.

    Now make sure the grid has a clean background color when the boxes are not lit up by your conditional formatting.

  5. Highlight all cells in the 10 x 10 grid and apply a default gray color to the cells and font. Also apply a white border to all cells.

    At this point, the 10 x 10 grid should look similar to the one shown in Figure 5-43. When you change the metric or target percentages, the grid should automatically adjust colors to reflect the data change. It’s time to use the Camera tool to shape and position your waffle chart.

  6. Highlight the waffle chart and then select the Camera Tool icon on the Quick Access toolbar.

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

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

    Excel immediately creates a linked picture that can be resized and positioned where you need it.

  8. To add a label to the waffle chart, click on the Insert tab on the Ribbon, select the Text Box icon, and then click the worksheet to create an empty text box.
  9. While the text box is selected, place your cursor in the Formula bar, type the equal sign (=), and then click the cell that contains the metric cell.
  10. Overlay the text box containing your label on top of the waffle chart.
image

Figure 5-41: The initial setup you need for the waffle chart.

image

Figure 5-42: Add conditional formatting to the 10 x 10 grid.

image

Figure 5-43: Your waffle chart is ready for the Camera tool.

You can repeat these steps to create a separate waffle chart for each of your metrics. After you’ve created each waffle chart, you can line them up to create an attractive graphic that helps your audience visualize performance against a goal for each metric. (See Figure 5-44.)

image

Figure 5-44: Create multiple waffle charts for a nice at-a-glance view for your audience.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.217.132.15