Joseph Schmuller - Statistical Analysis with Excel For Dummies

FIGURE 3-15:The Select Data Source dialog box.

FIGURE 3-16:The Axis Labels dialog box.

The brainchild of Edward Tufte (also known as “the da Vinci of data”), a sparkline is a tiny chart you can integrate into text or a table to quickly illustrate a trend. It’s designed to be the size of a word. In fact, Tufte refers to sparklines as datawords.

Three types of sparklines are available: One is a line chart; another is a column chart. The third is a special type of column chart that sports fans will enjoy: It shows wins and losses.

To show you what these sparklines look like, I apply the first two to the Table 3-1data. First, I insert two columns between Column A and Column B. Then, in the new (blank) Column B, I select cell B2. Then I choose Insert | Sparklines ⇒ Line from the main menu to open the Create Sparklines dialog box. (See Figure 3-17.)

In the Data Range box, I enter D2:H2 and click OK. Then I autofill the column. I repeat these steps for column C, except this time I choose Sparklines | Column instead of Sparklines | Line. Figure 3-18 shows the results.

If you absolutely must show a table in a presentation, sparklines are a welcome addition. If I were presenting this table, I would include the column sparklines.

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.)

To delete a sparkline, skip the usual method. Instead, right-click it and choose Sparklines from the pop-up menu. You see a choice that allows you to clear the sparkline.

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-2shows 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-2Use of the Internet at Home (2013)

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-2data in a worksheet.

FIGURE 3-23:The initial Excel bar chart.

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.