Statistical Analysis with Excel For Dummies. Joseph Schmuller
Чтение книги онлайн.
Читать онлайн книгу Statistical Analysis with Excel For Dummies - Joseph Schmuller страница 30
3 Choose Insert | Recommended Charts from the main menu and pick Pie Chart from the list on the left side of the screen.
4 Modify the chart.Figure 3-11 shows the initial pie chart (after I added the title) on its own sheet. To get it to look like Figure 3-10, I had to do a lot of modifying. First, I formatted the legend as in the preceding example.The numbers inside the slices are called data labels. To add them, I select the chart (not just one slice) and then click the Chart Elements button. I then check the box next to Data Labels.To change the font color of the labels, click one of the data labels and select Text Options in the Format Data Labels pane that appears. Click the Solid Fill radio button and change the color from black to white. Press Ctrl+B to make the font bold.
FIGURE 3-11: The initial pie chart, on its own sheet.
Whenever you set up a pie chart, always keep the following in mind… .
A word from the wise
The late, great social commentator, raconteur, and former baseball player Yogi Berra once went to a restaurant and ordered a whole pizza.
“How many slices should I cut,” asked the waitress, “four or eight?”
“Better make it four,” said Yogi. “I'm not hungry enough to eat eight.”
Yogi’s insightful analysis leads to a useful guideline about pie charts: They’re more digestible if they have fewer slices. If you cut a pie chart too fine, you’re likely to leave your audience with information overload.
When you create a chart for a presentation (as in PowerPoint), include the data labels. They often clarify important points and trends for your audience.
(That Yogi anecdote appears in the previous four editions of this book. Did it really happen? We can’t be sure. As Mr. Berra once famously said: “Half the lies they tell about me aren’t true.”)
Drawing the Line
In the preceding example, I focus on one column of data from Table 3-1. In this example, I focus on one row. The idea is to trace the progress of one space related industry across the years 1990–94. In this example, I graph the revenues from Satellite Services. The final product, shown on its own sheet, is shown in Figure 3-12.
FIGURE 3-12: A line chart of the second row of data in Table 3-1.
A line chart is a good way to show change over time, when you aren't dealing with many data series. If you try to graph all six industries on one line chart, it begins to look like spaghetti.
How do you create a chart like Figure 3-12? Follow along:
1 Enter your data into a worksheet.Once again, it’s already done.
2 Select the data that go into the chart.For this example, that’s cells A3 through F3. Yes, I include the label.Whoa! Did I forget something? What about that little trick I showed you earlier, where you hold down the Ctrl key and select additional cells? Couldn’t I do that and select the top row of years for the x-axis?Nope. Not this time. If I do that, Excel thinks 1990, 1991, 1992, 1993, and 1994 are just another series of data points to plot on the graph. I show you another way to put those years on the x-axis.
3 Choose Insert | Recommended Charts from the main menu and select a chart type.This time, I select the All Charts tab, pick Line in the left column, and choose Line with Markers from the options. Figure 3-13 shows the result.
4 Modify the chart.The line on the chart might be a little hard to see. Clicking the line and then choosing Chart Design | Change 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