Highlight Cells Rules in Excel Dashboards and Reports

You can use conditional formatting to enhance your reports and dashboards in Excel. The formatting scenarios under the Highlight Cells Rules category, shown in this figure, allow you to highlight those cells whose values meet a specific condition.

The thing to remember about these scenarios is that they work very 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.

image0.jpg

The scenarios under the Highlight Cells Rules category are pretty self-explanatory. Here is a breakdown of each scenario:

  • Greater Than: This scenario allows you to conditionally format 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: This scenario allows you to conditionally format 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: This scenario allows you to conditionally format 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: This scenario allows you to conditionally format 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: This scenario allows you to conditionally format 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: This scenario allows you to conditionally format 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, Next Week, and so on.

  • Duplicate Values: This scenario allows you to conditionally format both duplicate values and unique values in a given range of cells. This rule was designed more for data cleanup than dashboarding, enabling you to quickly identify either duplicates or unique values in your dataset.

Take a moment to go through an 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 the following figure.

    This opens the dialog box shown in this figure. In this dialog box, the idea is to define a value that will trigger the conditional formatting.

    image1.png
  3. Either type the value (400 in this example) or reference a cell that contains the trigger value. Also in this dialog box, use the drop-down box to specify the format you want applied.

  4. Click the OK button.

    Immediately, Excel applies the formatting rule to the selected cells, as shown in this figure.

    image2.jpg

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

blog comments powered by Disqus
Advertisement

Inside Dummies.com