Statistical Analysis with Excel For Dummies. Joseph SchmullerЧитать онлайн книгу.
Colors from the menu that appears gives a set of colors for the line. I chose black.Next, I added the titles for the chart and for the axes. The easiest way to change the title (which starts out as the label I selected along with the data) is to click the title and type the change.To add the axis titles, I clicked the Chart Elements button (labeled with a plus sign) and selected the check box next to Axis Titles on the pop-up menu. (Refer to Figure 3-7.) I then clicked an axis title, highlighted the text, and typed the new title.I still have to put the years on the x-axis. To do this, I right-clicked inside the chart to open the pop-up menu shown in Figure 3-14.FIGURE 3-13: The result of choosing Line with Markers from the All Charts tab.FIGURE 3-14: Right-clicking inside the chart opens this menu.Choosing Select Data from this menu opens the Select Data Source dialog box. (See Figure 3-15.) In the box labeled Horizontal (Category) Axis Labels, clicking the Edit button opens the Axis Labels dialog box. (See Figure 3-16.) A blinking cursor in the Axis Label Range box shows it’s ready for business. Selecting cells B1 through F1 and clicking OK sets the range and closes this dialog box. Clicking OK closes the Select Data Source dialog box and puts the years on the x-axis.
FIGURE 3-15: The Select Data Source dialog box.
FIGURE 3-16: The Axis Labels dialog box.
Adding a Spark
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-1 data. 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.)
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.