Excel’s Conditional Formatting

Last week, I wrote about how to make a slope chart in Excel when the data series are in different units. Today, I’ll use those same data to show you how to use Excel’s Conditional Formatting menu to highlight your data. Next week, I’ll use Conditional Formatting to show you how to take this one step further to create a Heatmap in Excel.

The Conditional Formatting menu is one of Excel’s hidden gems. Located in the Home tab of the Ribbon, Conditional Formatting enables you to highlight cells with a certain color or icon based on their values.

In this example, I’m using the same data for the slope chart tutorial—the OECD’s Better Life Index. The tabulated data are provided in an Excel file and contain 46 different elements of the index for 34 countries and 3 aggregated regions. This may not be the largest data set you’ll ever use, but it will work for this example.

Highlight a single cell

Let’s start simply and say you want to highlight values of Household Net adjusted disposable income (the 4th data column) that exceed $25,000.

• First, select the data you want to format.
• Second, on the Home tab, click Conditional Formatting, Highlight Cells Rules, Greater Than…
• 
  • Then, in the subsequent menu, enter 25000 in the box and click Ok. (You can change the format of the highlight by using the options under the drop-down menu on the right).

  

  The data will now include cells with red highlights and red text that meet the criterion you just specified.

  

  There are many other default options you can use, such as Less Than, Top 10%, Below Average, and so forth. Each of which basically requires you to input the cutoff(s) you want to apply to your data.

  Conditional Formatting Formulas

  Let’s say you don’t just want to highlight specific cells, but you want to highlight the entire row for those values of household income that exceed $25,000. This is not terribly difficult to accomplish; it just requires using formulas within the Conditional Formatting options.

  • First, select the entire data table.
  • Next, on the Home tab, click Conditional Formatting, Highlight Cells Rules, More Rules…

  

  • Select the Use a formula to determine which cells to format option at the bottom of the list.

  

  • Finally, in the Format values… box, enter the following formula: “=$G9>25000” and select Ok.

  

  You’ll now have red stripes across the entire table for those countries that have household incomes that exceed $25,000. It’s now much easier to identify those countries that meet our cutoff value.

  

  Let’s pull the formula apart so you can see what’s going on:

  1. “=” It’s an Excel formula, so you need the equal sign.
  2. Why the $ symbol? The dollar sign is Excel’s symbol for absolute (and relative) references and are used to fix the row or column reference that follows it—here the G column. (If I were in an Excel spreadsheet and typed “=$G9” in a column and then copied and pasted that cell anywhere else, the reference to “G” would never change while the “9” would.) Here, we want to apply the formatting in column G to the entire table. In other words, by using the dollar sign, you tell Excel to only look at the data in column G.
  3. Why G9? You want to start at the top of the data column.
  4. And, of course, the condition, “>25000”.

  So that’s it. Using formulas in Excel’s Conditional Formatting menu can allow you to color or band your data file so that you can more easily highlight your data.

  I’m a big fan of Excel’s Conditional Formatting menu and it can be used for your analytics or for data visualizations you wish to publish. Dave Bruns over at ExcelJet has some great tutorials on Conditional Formatting and other Excel tricks, so if you want to learn more, I encourage you to check them out.

  Next Week: How to build a Heatmap in Excel using Conditional Formatting.