Software Section for Chapter 2

Tabular and graphical presentations using Excel

Frequency distribution for categorical data

Bar chart for categorical data

Frequency distribution and histogram for categorical data

PivotTable report

Stacked bar chart

Scatter diagram

Tabular and graphical presentations using Excel

Excel offers extensive capabilities for producing tabular and graphical summaries of data. We

show how to construct a frequency distribution, bar chart, histogram, cross-tabulation,

stacked bar chart and scatter diagram. We shall demonstrate two of Excel’s most powerful

tools for data analysis: creating charts and creating PivotTable reports.

Frequency distribution for qualitative data

Car brands

Using the data on new car purchases in Table 2.1 of the book (file: ‘Car brands.CSV’), we

begin by showing how Excel’s COUNTIF function can be used to construct a frequency

distribution. Refer to Figure 2.20 as we describe the steps involved. The formula worksheet

(showing the functions and formulae used) is in the background, and the value worksheet

(showing the results obtained using the functions and formulae) appears in the foreground.
 The label ‘Brand’ and the data for the 50 new car purchases are in cells A1:A51. We also

entered the labels ‘Brand purchased’ and ‘Frequency’ in cells C1:D1. The five car brand

names are entered into cells C2:C6. Excel’s COUNTIF function can now be used to count the

number of times each car brand appears in cells A2:A51. The following steps are used.

Step

1 Select cell D2

2 Enter =COUNTIF($A$2:$A$51,C2)

3 Copy cell D2 to cells D3:D6

The formula worksheet in Figure 2.20 shows the cell formulae inserted by applying these

steps. The value worksheet shows the values computed by the cell formulae. This worksheet

shows the same frequency distribution we constructed in Table 2.2.

Bar chart for qualitative data

Here we show how Excel’s chart tools can be used to construct a bar chart for the new car

purchase data. Refer to the frequency distribution shown in the foreground of Figure 2.20.

The bar chart we are going to construct is an extension of this worksheet. The steps are

shown below. We begin by ordering the brands from highest to lowest frequency, which

makes the bar chart easier to interpret.

Step

1 Select cells C2:D6

2 Click the Home tab on the Ribbon, then in the Sort & Filter group click

Custom Sort

3 Under Column select Frequency, and under Order select Largest to

Smallest

4 Click OK
 5 Click the Insert tab on the Ribbon, then in the Recommended Charts group

click Clustered Column

6 Highlight Chart Title on the chart, and key in Bar Chart of New Car

Purchases

7 Click Design on the Ribbon, then select Add Chart Element > Axis Titles >

Primary Horizontal. Highlight Axis Title, then key in the axis title Car

brand

8 Click Design on the Ribbon, then select Add Chart Element > Axis Titles >

Primary Vertical. Highlight Axis Title, then key in the axis title

Frequency

The resulting bar chart is shown in Figure 2.21. The bar chart in Figure 2.21 can be resized.

First, select the chart. Sizing handles will appear on the chart border. Click on the sizing

handles and drag them to resize the figure to your preference.

COUNTIF function

Figure 2.21 Bar chart of new car purchases constructed using Excel

Excel can produce a pie chart for the car purchase data in a similar fashion. The major

difference is that in step 5 we would click Pie in the Recommended Charts group. Several

styles of pie charts are available.

Frequency distribution and histogram for quantitative data

In this section we show how Excel can be used to construct a frequency distribution and a

histogram for quantitative data. We illustrate each using the audit time data shown in Table

2.4 (file: Audit.CSV’).

Frequency distribution

Audit
Excel’s FREQUENCY function can be used to construct a frequency distribution for

quantitative data. Refer to Figure 2.22 as we describe the steps involved. The formula

worksheet is in the background, and the value worksheet is in the foreground. The label

‘Audit Time’ is in cell A1 and the data for the 20 audits are in cells A2:A21. Using the

procedures described below, we make the five classes 10–14, 15–19, 20–24, 25–29, 30–34.

The label ‘Audit Time’ and the five classes are entered in cells C1:C6. The label ‘Upper

Limit’ and the five class upper limits are entered in cells D1:D6. We also entered the label

‘Frequency’ in cell E1. Excel’s FREQUENCY function will be used to show the class

frequencies in cells E2:E6.

Step

1 Select cells E2:E6

2 Type, but do not enter, the following formula:

= FREQUENCY(A2:A21,D2:D6)

3 Press CTRL + SHIFT + ENTER and the array formula will be entered

into each of the cells E2:E6

The results are shown in Figure 2.22. The values displayed in the cells E2:E6 are the

frequencies for the corresponding classes. Referring to the FREQUENCY function, we see

that the range of cells for the upper class limits (D2:D6) provides input to the function. These

upper class limits, which EXCEL refers to as bins, tell EXCEL which frequency to put into

the cells of the output range (E2:E6). For example, the frequency for the class with an upper

limit, or bin, of 14 is placed in the first cell (E2), the frequency for the class with an upper

limit, or bin, of 19 is placed in the second cell (E3), and so on.

frequency function, plus histogram

Histogram

To use Excel to construct a histogram for the audit time data, we begin with the frequency

distribution as shown in Figure 2.22. The histogram output is also shown in Figure 2.22. The

following steps describe how to construct a histogram from a frequency distribution.

Step

1 Select cells C2:C6

2 Press the Ctrl key and also select cells E2:E6

3 Click the Insert tab on the Ribbon

4 In the Recommended Charts group, click Clustered Column

5 Highlight Chart Title on the chart, and key in Histogram for Audit Time Data

6 Click Design on the Ribbon, then select Add Chart Element > Axis Titles >

Primary Horizontal. Highlight Axis Title, key in the axis title Audit Time in
 Days

7 Click Design on the Ribbon, then select Add Chart Element > Axis Titles >

Primary Vertical. Highlight Axis Title, key in the axis title Frequency

8 Right-click any of the bars in the chart, select Format Data Series, then move the

Gap Width slider to 0%

An interesting aspect of the worksheet in Figure 2.22 is that Excel has linked the data in

cells A2:A21 to the frequencies in cells E2:E6 and to the histogram. If the data in cells

A2:A21 are edited or revised, the frequencies in cells E2:E6 and the histogram will be

updated automatically to display a revised frequency distribution and histogram. Try one or

two data edits to see how this automatic updating works.

PivotTable report

Restaurant

EXCEL’s PivotTable Report provides a valuable tool for managing data sets involving more

than one variable. We shall illustrate its use by showing how to construct a cross-tabulation

using the restaurant data in Figure 2.23. Labels are entered in row 1, and the data for each of

the 300 restaurants are entered into cells A2:C301.

Creating the initial worksheet

The following steps create a worksheet containing the initial PivotTable report and

PivotTable Fields panel. The resulting PivotTable Fields panel is shown in Figure 2.24.

Step

1 Click the Insert tab on the Ribbon

2 In the Tables group, click the icon above PivotTable

3 When the Create PivotTable panel appears:

Choose Select a table or range

Enter A1:C301 in the Table/Range box

Select New Worksheet

Click OK

.
Figure 2.24 PivotTable Fields panel

Using the PivotTable Fields panel

Each column in Figure 2.23 (Restaurant, Quality Rating, and Meal Price) is considered a field

by Excel. The following steps show how to use Excel’s PivotTable Fields panel to move the

Quality Rating field to the row section, the Meal Price (€) field to the column section, and the

Restaurant field to the values section of the PivotTable report.

Step

1 In the PivotTable Fields panel:

Drag the Quality Rating field to the Row area

Drag the Meal Price (€) field to the Column

area

Drag the Restaurant field to the Values area

2 Right-click Sum of Restaurant in the Values area

Click Field Settings

3 When the PivotTable Field panel appears:

Under Summarize by, choose Count

Click OK

Figure 2.25 shows the completed PivotTable Field panel and a portion of the PivotTable

report.
Figure 2.25 Completed PivotTable field list and a portion of PivotTable Report

Finalizing the PivotTable report

To complete the PivotTable report, the following steps group the columns representing meal

prices and place the row labels for quality rating in the proper order.

Ste

1 Right-click in cell B4 or in any other cell containing meal prices

Select Group

2 When the Grouping panel appears:

Enter 10 in the Starting at box

Enter 49 in the Ending at box

Enter 10 in the By box

Click OK

3 Right-click on Excellent in cell A6

Choose Move

Select Move “Excellent” to END

4 Close the PivotTable Fields panel

information as the cross-tabulation shown in Table 2.10 in the book.

Figure 2.26 Final PivotTable Report

Stacked bar chart

Restaurant

Figure 2.8 in the book shows a stacked bar chart (produced in SPSS) for the restaurant survey

data (file: ‘Restaurant.CSV’). A similar chart can be produced in EXCEL as follows. We

start from the PivotTable report produced above, shown in Figure 2.26.

Step

1 Highlight the block of cells A4:E7

2 Click on the Insert tab on the Ribbon

3 Click on PivotChart

4 A side-by-side bar chart appears in the worksheet.

Right-click on any bar, click on Change Chart Type, click on Column, click

on 100% Stacked Column

5 Right-click somewhere around the periphery pf the chart area, click on Show

Field List
 In the PivotChart Fields panel, drag Meal Price (€) to the Axis (Categories)

area, and drag Quality Rating to the Legend (Series) area

6 Click Design on the Ribbon, then select Add Chart Element > Axis Titles >

Primary Horizontal. Highlight Axis Title, key in the axis title Meal price (€)

7 Click Design on the Ribbon, then select Add Chart Element > Axis Titles >

Primary Vertical. Highlight Axis Title, key in the axis title Quality rating

8 Click Design on the Ribbon, then select Add Chart Element > Chart Title.

Highlight Chart Title, key in the axis title Percentage distribution of quality

ratings within meal price categories

At the end of Step 4, Excel produces a stacked bar chart showing the percentage

distribution of meal prices (vertical axis) within each quality rating category (horizontal axis).

The stacked bar chart in Figure 2.8 of the book shows the percentage distribution of quality

ratings (vertical axis) for each meal price category (horizontal axis). Step 5 above swaps

around the roles of the two variables to produce the same type of stacked bar chart as shown

in Figure 2.8. The final result after Step 5 is shown in Figure 2.27.
Figure 2.27 Stacked bar chart for the restaurant survey data, using Excel

Scatter diagram

Facebook

We use the digital equipment online store data in Table 2.12 of the book to demonstrate the

use of Excel to construct a scatter diagram. Refer to Figure 2.28 as we describe the steps

involved. The data columns are to the left in the Figure, and the scatter diagram produced by

Excel on the right. The following steps will produce the scatter diagram.

Step

1 Select cells B2:C11

2 Click the Insert tab on the Ribbon

3 In the Recommended Charts group, click Scatter (the chart will appear in the

worksheet)

4 Highlight Chart Title on the chart, and key in Scatter Diagram for the Digital

Equipment Online Store

5 Click Chart Design on the Ribbon, then select Add Chart Element > Axis

Titles > Primary Horizontal. Highlight Axis Title, then key in the axis title

Number of Promotions

6 Click Chart Design on the Ribbon, then select Add Chart Element > Axis

Titles > Primary Vertical. Highlight Axis Title, then key in the axis title

Sales Volume

7 Right-click the vertical axis

Click Format Axis

8 When the Format Axis panel appears:

Enter 35 for Minimum in Bounds

Enter 65 for Maximum in Bounds

Enter 5 for Major in Units

A trend line can be added to the scatter diagram as follows.

9 Position the mouse pointer over any data point in the scatter diagram and

right-click to display a list of options

10 Choose Add Trendline

11 When the Format Trendline dialog box appears:

In the Trendline Options section

Choose Linear

The worksheet in Figure 2.28 shows the scatter diagram with the trend line added.

Figure 2.28 Scatter diagram for digital equipment online store data, using Excel