Excel will draw in the line charts, one in each cell.

Here are default sparklines.

Figure 1162 Default sparklines have no labels or markers.

Excel will let you add markers to your sparklines.

With a sparkline selected, the Sparkline Tools De- sign tab will be available in the ribbon. You can toggle on all points by choosing Markers, but a more interesting option is to choose High Point and

Low Point

Figure 1164 Change the color of the markers.

Figure 1163 Choose which points to add to the line.

After you’ve added the high and low point, use the Marker Color dropdown to choose a color for each type of point.

SPARKLINES ARE NOT SCALED TOGETHER

Problem: The vertical scale on the sparklines seems to be wrong. This chart is showing that Chicago and Miami have similar January temperatures.

4

Figure 1165 Monthly temperatures in Miami are not like Chicago.

Strategy: by default, each sparkline has its own vertical scale. This works out great when the sparklines are comparing how different items trend together over time.

Figure 1166 Each sparkline has its own vertical scale.

In Professor Tufte’s first examples, he was comparing unlike variables that had a relationship. This is why

Microsoft chose to keep the axes separate for each line.

When you are comparing items that all have the same scale, you have to force the minimum and maximum axis to be the same.

At the very least, use the Axis dropdown and choose Same For All Sparklines for both the minimum and maximum value.

Figure 1167 Make all of the axes the same.

If you are going to be labeling sparklines or shading sparklines as described later, you will want to use a Custom Value for both the min and max value options.

WHAT IS THE WIN LOSS SPARKLINE FOR?

Problem: What is the win/loss sparkline used for?

Strategy: The win/loss sparkline shows streaks of wins or losses. You might use it to plot sports teams, stock prices, or bid desk results. In a win/loss sparkline, any positive value (such as 1) is plotted as an upward facing marker. Any negative value (such as -1) is plotted as a downward facing marker. Any zero values get no marker.

If you have some stock closing prices, use =SIGN(Today-Yesterday). If the price went up, the sign is posi- tive and you get a +1. If the price went down, you get a -1.

Figure 1168 Generate +1, 0, -1 with SIGN().

For long Win/Loss Sparklines, increase the column width and row height to show more detail.

Figure 1169 Did those green markers happened after the Kinect debuted?

Living in northeast Ohio, I am used to baseball season ending in June as the Cleveland Indians are elimi- nated from the playoffs. This year, they got off to a stellar start, but were just swept by the Yankees this weekend. The right quarter of this chart is the ballclub that I know and love.

Strategy: You can use one of the three new data visualization tools on the Conditional Formatting menu: data bars, color scales, and icon sets.

Adding a data bar to a range adds an in-cell bar chart to each cell. You can see which cells have the largest values by seeing which cells have the most color.

To add data bars, you select a range of numbers and then select Home, Conditional Formatting,

Data Bars, choose a color. Excel offers six gradients and six solid. You can choose More Rules to add any of 16 million colors.

Figure 1176 Choose a color for the data bars

Below, you can see that Wednesday is the busiest day. Calls fall of on Friday for everyone except Missy. Missy is consistently the strongest performer.

Figure 1177 Easily spot trends in the data.

Gotcha: You should not include any total cells in your selection when applying conditional formatting.

4

The relative size of the totals would make all the detail numbers receive small bars. Below, the 904 in cell

G6 makes all the cells in B2:F5 look relatively the same.

Figure 1178 Don’t include totals in a visualization.

Additional Details: You can use color scales to apply a mix of colors to a range. Excel offers built-in three-color scales such as red-yellow-green as well as two-color scales. The two-color scales look better than three-color scales when printed in monochrome. You can also use More Rules to design your own color scheme. Below, the largest numbers are in the darker green, and the smallest numbers are in the lighter yellow.

Figure 1179 Each cell is lighter or darker based on size.

The final new visualization is icon sets.

In Excel 2010, there are 20 sets of icons. Some have three symbols, others have four, and some have five.

Figure 1180 Twenty icon sets are available.

Note that for many of these sets you need to print in color in order for the reader to differentiate the symbols. If you are printing in monochrome, the arrows or power bars are good choices.

After you choose an icon set, Microsoft will display the icon at the left of each cell. Since numbers are usu- ally right-aligned, the number from B2 and the icon from C2 are too close together and many will think that they go together.

I’ve begun using Ctrl+1 to visit the Format Cells dialog. On the Alignment tab, use a horizontal alignment of Right (Indent). You can then increase the indent to 2 or 3 to move the numbers closer to their icons.