FIGURE 3-17: The Create Sparklines dialog box.
FIGURE 3-18: Line sparklines and column sparklines for the data in Table 3-1.
How else would you use a sparkline? Figure 3-19 shows two column sparklines integrated into a Word document. It takes a little maneuvering to copy and paste properly, and you have to paste the sparkline as a picture. I think you’ll agree that the results are worth the effort.
FIGURE 3-19: Sparklines in a Word document.
The Win/Loss sparkline nicely summarizes a sports team’s progress throughout a season. Created with the Win/Loss button in the Sparklines area, the sparklines in Figure 3-20 represent the monthly records of the teams in the National Basketball Association’s Atlantic Division for the 2020–2021 season.
FIGURE 3-20: Win/Loss sparklines for the 2020–2021 NBA Atlantic Division, featuring the magnificent Brooklyn Nets.
In the data, 1 represents a winning record for the month (more wins than losses), –1 represents a losing record, and 0 (not in this dataset) means the team won as many games as they lost. In the sparkline, a winning month appears as a marker above the middle of the Sparkline cell, a losing month appears as a marker below the middle of the Sparkline cell, and a break-even month (again, not in this data set) is a blank.
The magnificent Brooklyn Nets, you’ll note, was one of only two teams in the Division to have a winning record in each of the five months. (Yes, I know they went on to lose in the semifinals to the ultimate NBA champs. Don’t go there. Seriously.)
Passing the Bar
Excel's bar chart is a column chart laid on its side. This is the one that reverses the horizontal-vertical convention. Here, the vertical axis holds the independent variable, and it's referred to as the x-axis. The horizontal axis is the y-axis, and it tracks the dependent variable.
When would you use a bar chart? This type of chart fits the bill when you want to make a point about reaching a goal, or about the inequities in attaining one.
Table 3-2 shows the data on home Internet usage. The data, from the US Census Bureau (via the US Statistical Abstract), are for the year 2013. Percent means the percentage of people in each income group.
TABLE 3-2 Use of the Internet at Home (2013)
| Household Income | Percent |
|---|---|
| Less than $25,000 | 48.4 |
| $25,000 to $49,999 | 69.0 |
| $50,000 to $99,999 | 84.9 |
| $100,000 to $149,999 | 92.7 |
| $150,000 and more | 94.9 |
Data from U.S. Census Bureau
The numbers in the table show a clear trend. Casting them into a bar chart shows the trend even more clearly, as you can see in Figure 3-21.
FIGURE 3-21: A bar chart of the data in Table 3-2.
To create this graph, follow these steps:
1 Enter your data into a worksheet.Figure 3-22 shows the data entered into a worksheet.
2 Select the data that go into the chart.For this example, the data are cells A1 through B8.
3 Choose Insert | Recommended Charts from the main menu and then choose the chart you like from the list on the left side of the screen.I selected the first option: Clustered Bar. Figure 3-23 shows the result.
4 Modify the chart.The first modification is to change the chart title. One way to do this is to click the current title and type the new title. Next, I add the axis titles. To do this, I click the Chart Elements button, that button labeled with a plus sign (+). Selecting the Axis Labels check box on the menu that appears adds generic axis titles, which I then change. Finally, I bold the font on the axis titles as well as the axis numbers. The easiest way to do that is to select an element and press Ctrl+B.
FIGURE 3-22: Table 3-2 data in a worksheet.
FIGURE 3-23: The initial Excel bar chart.
The Plot Thickens
You use an important statistical technique called linear regression to determine the relationship between one variable, x, and another variable, y. For more information on linear regression, see Chapter 14.
The basis of the technique is a graph that shows individuals measured on both x and y. The graph represents each individual as a point. Because the points seem to scatter around the graph, the graph is called a scatterplot.
Suppose you're trying to find out how well a test of aptitude for sales predicts salespeople's productivity. You administer the test to a sample
