Statistical Analysis with Excel for Dummies (Notes - Pie Charts & Line Charts & Sparklines)

Pie Charts: To show the percentages that make up one total, a pie chart gets the job done efficiently.

Here's how to create a pie chart using the data below:

2.  Select the data that go into the chart.  I want the names in column A and the data in column F.  The trick is to select column A (Cells A2 through A7) in the usual way and then press and hold the Ctrl key.  While holding this key, drag the cursor from F2 through F7.  Two non adjoining columns are selected.

3.  Select Insert | Recommended Charts and pick the Pie Chart.

4.  Modify the chart.  To add data labels to the slices, select the chart (not just one slice) and right-click.  On the pop-up menu, select Add Data Labels | Add Data Labels.  Select the other option, Add Data Callouts, for an eye-catching effect.  To change the data label color from black to white, select the data labels and work with the Font Color button in the Font area of the Home tab.  Finally you can pull the number out of the thinnest slice to make things easier to see, this is done by dragging it to it's new location.

This is what the pie chart with modifications looks like:

Pie chart's are 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.

Line Charts:

The preceding example focused on one column of data.  This example will focus on one row.  The idea is to trace the progress of one space-related industry across the years 1990-1994.  In this example, I graph the revenues from Satellite Services.

To do this:

1.  Select the data that go into the chart.  For this example, that's cells A3 through F3.  Yes I include the label.  You can't hold down the Ctrl key and select the additional cells this time (the top row for the X axis).  If you do that Excel thinks 1990, 1991, 1992, 1993, and 1994 are just another series of data points to plot on the graph.

2.  Click Insert | Recommended Chart Types.  This time, choose Line with Markers from the options.

3.  Modify the chart.

The line on the chart is hard to see.  Clicking the line and then selecting Design | Change Colors gives a set of colors for the line.  Choose black.

Next, add the titles for the chart and for the axis.  One way to add the axis titles is to use the set of tools that appears when you select the chart.  Labeled with a plus sign, the first tool enables you to add elements to the chart.  Clicking it and checking the Axis labels check box puts axis titles on the chart.  Then click an axis title, highlight the text, and type the new title.

To put the years on the x-axis, right click inside the chart to get a pop-up menu and click on Select Data.  In the box labeled Horizontal (Category) Axis Labels, clicking the Edit button opens the Axis Labels dialog box.  A blinking cursor in the Axis label range box shows it's ready for business.  Selecting cells B1 through F1 and clicking OK closes the dialog box.  Clicking OK closes the Select Data Source box and puts the years on the x-axis.

This is what the line chart looks like:

Sparklines:  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.

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 like.  It shows wins and losses.

To show what they look like, I apply the first two to the following table:

To do this:

1.  Insert two columns between Column A and Column B.
2.  In the new blank column B, select cell B2.
3.  Select Insert | Sparklines |Line to open the Create Sparklines dialog box.
4.  In the data range box, enter D2:H2 and click ok.  Then autofill the column.

Repeat these steps for Column C but with the Sparklines Column button.  The results are below:

The Wins Losses sparkline nicely summarizes a sports team's progress throughout a season.  Created with the Wins Losses button in the Sparklines area, the sparklines shown below show the week-to-week progress of the teams in the NFL's NFC division in 2011.  In the data, 1 represents a win and -1 represents a loss.  A win appears as a marker above the line, a loss below the line.

*If you want to delete a sparkline, you don't delete in the usual way.  Instead, you right-click it and select Sparklines from the pop-up menu.  This presents a choice that allows you to clear the sparkline.