Excel Dashboards - Conditional Formatting


Advertisements


Conditional Formatting for Data Visualization

If you have chosen Excel for creating dashboard, try to use Excel tables if they serve the purpose. With Conditional Formatting and Sparklines, Excel Tables are the best and simple choice for your dashboard.

In Excel, you can use conditional formatting for data visualization. For example, in a table containing the sales figures for the past quarter region-wise, you can highlight the top 5% values.

Data Visualization

You can specify any number of formatting conditions by specifying Rules. You can pick up the Excel built-in Rules that match your conditions from Highlight Cells Rules or Top / Bottom Rules. You can also define your own Rules.

You choose the formatting options that are appropriate for your data visualization - Data Bars, Color Scales, or Icon Sets.

In this chapter, you will learn conditional formatting Rules, formatting options, and adding/managing Rules.

Highlighting Cells

You can use Highlight Cells Rules to assign a format to the cells that contain the data meeting any of the following criteria −

  • Numbers within a given numerical range: Greater Than, Less Than, Between, and Equal To.

  • Values that are Duplicate or Unique.

Consider the following summary of results that you want to present −

Highlighting Cells

Suppose you want to highlight the Total Amount values that are more than 1000000.

  • Select the column – Total Amount.
  • Click on Conditional Formatting in the Styles group under Home tab.
  • Click on Highlight Cells Rules in the dropdown list.
  • Click on Greater Than in the second dropdown list that appears.
Conditional Formatting

Greater Than dialog box appears.

  • In the Format cells that are GREATER THAN: box, specify the condition as 1000000.

  • In the box with, select the formatting option as Green Fill with Dark Green Text.

Greater Than Dialog
  • Click the OK button.
Specified Format

As you can observe, the values satisfying the specified condition are highlighted with the specified format.

Top / Bottom Rules

You can use Top / Bottom Rules to assign a format to the values meeting any of the following criteria −

  • Top 10 Items − Cells that rank in the top N, where 1 <= N <= 1000.

  • Top 10% − Cells that rank in the top n%, where 1 <= n <= 100.

  • Bottom 10 Items − Cells that rank in the bottom N, where 1 <= N <= 1000.

  • Bottom 10% − Cells that rank in the bottom n%, where 1 <= n <= 100.

  • Above Average − Cells that are Above Average for the selected range.

  • Below Average − Cells that are Below Average for the selected range.

Suppose you want to highlight the Total Amount values that are in top 5%.

  • Select the column – Total Amount.
  • Click on Conditional Formatting in the Styles group under Home tab.
  • Click on Top/Bottom Rules in the dropdown list.
  • Click on Top Ten% in the second dropdown list that appears.
Top Bottom Rules

Top Ten% dialog box appears.

  • In the Format cells that rank in the TOP: box, specify the condition as 5%.

  • In the box with, select the formatting option as Green Fill with Dark Green Text.

Top Option
  • Click the OK button. The top 5% values will be highlighted with the specified format.

Top Bottom Format

Data Bars

You can use colored Data Bars to see the value relative to the other values. The length of the Data Bar represents the value. A longer Bar represents a higher value, and a shorter Bar represents a lower value. You can either use solid colors or gradient colors for Data Bars.

  • Select the column – Total Amount.

  • Click on Conditional Formatting in the Styles group under Home tab.

  • Click on Data Bars in the dropdown list.

  • Click on Blue Data Bar under Gradient Fill in the second dropdown list that appears.

Data Bars

The values in the column will be highlighted showing small, intermediate and large values with blue colored gradient fill bars.

Gradient Fill Bar
  • Select the column – Total Amount.

  • Click on Conditional Formatting in the Styles group under Home tab.

  • Click on Data Bars in the dropdown list.

  • Click on Orange Data Bar under Solid Fill in the second dropdown list that appears.

Orange Bar

The values in the column will be highlighted showing small, intermediate and large values by bar height with orange colored bars.

Colored Bar

Suppose you want to highlight the sales as compared to a sales target, say 800000.

  • Create a column with values = [@[Total Amount]]-800000.

  • Select the new column.

  • Click on Conditional Formatting in the Styles group under Home tab.

  • Click on Data Bars in the dropdown list.

  • Click on Green Data Bar under Gradient Fill in the second dropdown list that appears.

Green Color Bar

The Data Bars will start in the middle of each cell, and stretch to the left for negative values and to the right for positive values.

Positive Negative Value

As you can observe, the Bars stretching to the right are green in color indicating positive values and the Bars stretching to the left are red in color indicating negative values.

Color Scales

You can use Color Scales to see the value in a cell relative to the values in the other cells in a column. The color indicates where each cell value falls within that range. You can have either a 3-color scale or 2-color scale.

  • Select the column – Total Amount.

  • Click on Conditional Formatting in the Styles group under Home tab.

  • Click on Color Scales in the dropdown list.

  • Click on Green-Yellow-Red Color Scale in the second dropdown list that appears.

Color Scale

As in the case of Highlight Cells Rules, a Color Scale uses cell shading to display the differences in cell values. As you can observe in the preview, the shade differences are not conspicuous for this data set.

  • Click on More Rules in the second dropdown list.
More Rules

New Formatting Rule dialog box appears.

  • Click on Format all cells based on their values in the Select a Rule Type box.

  • In the Edit the Rule Description box, select the following −

    • Select 3-Color Scale in the Format Style box.

    • Under Midpoint, for Value – type 75.

Rule Description

Click the OK button.

Shaded Depicting

As you can observe, with the defined color scale, the values are distinctly shaded depicting the data range.

Icon Sets

You can use icon sets to visualize numerical differences. In Excel, you have a range of Icon Sets −

Icon Set Type Icon Sets
Directional Directional
Shapes Shapes
Indicators Indicators
Ratings Ratings

As you can observe, an Icon Set consists of three to five symbols. You can define criteria to associate an icon with the values in a cell range. E.g. a red down arrow for small numbers, a green up arrow for large numbers, and a yellow horizontal arrow for intermediate values.

  • Select the column – Total Amount.

  • Click on Conditional Formatting in the Styles group under Home tab.

  • Click on Icon Sets in the dropdown list.

  • Click on 3 Arrows (Colored) in the Directional group in the second dropdown list that appears.

Icon Sets

Colored arrows appear in the selected column based on the values.

Colored Arrows

Using Custom Rules

You can define your own Rules and format a range of cells satisfying a particular condition.

  • Select the column – Total Amount.
  • Click on Conditional Formatting in the Styles group under Home tab.
  • Click on New Rule in the dropdown list.
Custom Rule

New Formatting Rule dialog box appears.

  • Click on Use a formula to determine which cells to format, in the Select a Rule Type Box.

  • In Edit the Rule Description box, do the following −

    • Type a formula in the box - Format values where this formula is true. For example, = PercentRank.INC($E$3:$E$13,E3)>=0.7

    • Click on Format button.

    • Choose the format. E.g. Font – bold and Fill – orange.

    • Click on OK.

  • Check the Preview.

Preview

Click on OK if the Preview is alright. The values in the data set that are satisfying the formula will be highlighted with the format you have chosen.

Data Set

Managing Conditional Formatting Rules

You can manage the conditional formatting Rules using the Conditional Formatting Rules Manager dialog box.

Click Conditional Formatting in the Styles group under Home tab. Click Manage Rules in the dropdown list.

Managing Rules

Conditional Formatting Rules Manager dialog box appears. You can view all the existing Rules. You can add a new Rule, delete a Rule and/or edit a Rule to modify it.

Existing Rule

Advertisements
E-Books Store