Excel Conditional Formatting and Sparklines

Beyond the classic chart

New ways to visually analyze data

Data analysts and managers have two new tools in our toolbox. The release of Microsoft Office 2010 gave us two new features. They are Conditional Formatting and Sparklines. This is exciting because it gives users more options to present data for analysis than the traditional chart choices.

In fact some types of data trends and patterns can be better illustrated with conditional formatting or Sparklines than a chart. Examples are data sets where you want to see where data falls within the range of values. Another examples is when there are so many rows of data to compare, a normal line chart has too many lines to see a pattern or trend.

Conditional Formatting

Conditional Formatting is a feature in Office 2010 and 2013 that changes the appearance of a cell based on the content of the cell. We can make rules that use cell content to change the formatting of a cell. The rule we make will change cell fill, font color or place an icon in a cell based on the value of the cell.
Line and bar charts are good for seeing trends in highly summarized data showing a limited number of years, time periods and categories. But if you need to show other things besides high-level trends conditional formatting can help.
The major categories of Conditional Formatting include:

Top / Bottom Rules

This is a great collection of pre-set conditional formatting rules. Sometime a quick and simple analysis is all that is required to see a trend or make a decision. Use

these pre-set rules to see the top 10, bottom 10 or data that is above average or below average.

Data Bars

“When you use conditional formatting to show data bars, Excel draws a bar in each cell whose length corresponds to the value of the cell relative to the other cells in the selected range.” (Microsoft Office Support)

Color Scales

The Microsoft Office Support Website says, “Using colors, you can highlight values to show a range and compare highs and lows.” As I mentioned above, sometimes you want to easily see where the data in a cell falls within a range of values.

Icon Sets

The Microsoft Office Support Website explains,

“Use an icon set to present data in three to five categories that are distinguished by a threshold value. Each icon represents a range of values and each cell is annotated with the icon that represents that range. For example, a three-icon set uses one icon to highlight all values that are greater than or equal to 67 percent, another icon for values that are less than 67 percent and greater than or equal to 33 percent, and another icon for values that are less than 33 percent.”

This is very similar to the color range formatting. However, there are still good reasons to use icon sets. One is that the color range formatting can be a little overwhelming for some people and projects. Icons can provide a visual cue yet maintain a cleaner look for the report. As the Office Support Website article points out, “Icon sets can be combined with other conditional formats.” I think the ability to use icon sets in combination with other formatting rules is a major advantage.

Sparklines

A Sparkline is a like a mini line chart for a single series of data that fills up one cell. The Microsoft Office Support Website tells us, “A sparkline can display a trend based on adjacent data in a clear and compact graphical representation.” Later they write, “You can quickly see the relationship between a sparkline and its underlying data, and when your data changes you can see the change in the sparkline immediately.”

Sparklines are also better than a chart if you have many rows of data to analyze. A gcflearnfree.org Website article has a good example.

“Imagine you have 1000 rows of data. A traditional chart would have 1000 data series to represent all of the rows, making relevant data difficult to find. But if you placed a sparkline on each row, it will be right next to its source data, making it easy to see relationships and trends for multiple data series at the same time.”

Too many data series on a chart is too overwhelming for people to identify relevant trends and deviations that need attention. Sparklines are a better tool for meaningful analysis.

Use Both

Now that we have learned about these wonderful new tools, imagine the possibilities if we apply both conditional formatting and a column for a Sparkline to our data tables.