Visualizing and Exploring Data
using Excel
Topics
• Data Visualization
• Data Queries: Using Sorting and Filtering
• Statistical Methods for Summarizing Data
• Exploring Data Using PivotTables
Data Visualization
Creating Charts in Microsoft Excel
Select the insert tab.
Highlight the data.
Click on chart type, then subtype.
Figure 3.1
Use chart tools to customize.
Figure 3.2
Data Visualization
Example 1 Creating a Column Chart Highlighted Cells
Figure 3.3
Data Visualization
Example 1 (continued) Creating a Column
Chart
Choose column chart (clustered or stacked).
Add chart title (Alabama Employment).
Rename Series1, Series2, and Series3
(ALL EMPLOYEES, Men, Women).
Figure 3.4
Data Visualization
Example 1 (continued) Creating a Column
Chart Clustered
Column
Chart
Figure 3.5
Data Visualization
Example 1 (continued) Creating a Column
Chart Stacked
Column
Chart
Figure 3.6
Data Visualization
Example 2 Line Chart for U.S. Exports to China
Figure 3.7
Data Visualization
Example 3 Pie Chart for Census Data
Data Visualization
Example 4 Area Chart for Energy Consumption
Data Visualization
Example 5 Scatter Chart for Real Estate Data
Data Visualization
Example 6
Bubble Chart for Comparing Stock
Characteristics
Data Visualization
Miscellaneous Excel Charts
•Stock chart
•Surface chart
•Doughnut chart
•Radar chart
•Geographic mapping
Data Queries: Using Sorting and Filtering
Example 7
Sorting Data in the Purchase Orders Database
Sort by Supplier
Data Queries: Using Sorting and Filtering
Pareto Analysis
An Italian economist, Vilfredo Pareto, observed in
1906 that a large proportion of the wealth in Italy
was owned by a small proportion of the people.
Similarly, businesses often find that a large
proportion of sales come from a small proportion
of customers.
A Pareto analysis involves sorting data and
calculating cumulative proportions.
Data Queries: Using Sorting and Filtering
Example 8 Applying the Pareto Principle Sort by
Figure 3.15
75% of the bicycle inventory value comes from 40% (9/24) of items.
Data Queries: Using Sorting and Filtering
Example 9 Filtering Records by Item Description
Highlight A3:J97
Data tab
Sort & Filter group
Filter
Click on the D3
dropdown arrow.
Select Bolt-nut
package to filter out
all other items.
Data Queries: Using Sorting and Filtering
Example 9 (continued)
Filtering Records by Item Description
Filter results for the bolt-nut package
Data Queries: Using Sorting and Filtering
Example 10 Filtering Records by Item Cost
To identify items that
cost at least $200
•Click on dropdown
arrow for item cost
•Number Filters
Greater Than Or
Equal To…
Data Queries: Using Sorting and Filtering
Example 10 (continued) Filtering by Item Cost
Custom AutoFilter dialog box
Click OK
Only items
costing at least
$200 is then
displayed.
Data Queries: Using Sorting and Filtering
AutoFilter criteria is based on the data type.
Number Filters includes numerical criteria.
Date Filters include tomorrow, next week, etc.
AutoFilter can be used sequentially.
First filter by one variable.
Then filter those data by another variable.
Data Queries: Using Sorting and Filtering
Analytics in Practice: Discovering Value
of Data Analysis at Alders International
Duty free operations at airports, seaports, etc.
Maintain a data warehouse to track point-of-sale
information and inventory levels.
Pareto analysis revealed that 80% of profits were
generated from 20% of their product lines.
Allows selective elimination of less profitable items.
Statistical Methods for Summarizing Data
A statistic is a summary measure of data.
Descriptive statistics are methods that
describe and summarize data.
Microsoft Excel supports statistical analysis in
two ways:
1. Statistical functions
2. Analysis Toolpak add-in for PCs
(for Macs, StatPlus is similar)
Statistical methods are essential to Business Analytics
Statistical Methods for Summarizing Data
Example 11 Constructing a Frequency Distribution
for Items in the Purchase Order Database
Copy Column D (Item Description) to Column A in a new worksheet
Statistical Methods for Summarizing Data
Example 11 (continued) Constructing a Frequency
Distribution for Items in the Purchase Order Database
Figure 3.21 Figure 3.22
Statistical Methods for Summarizing Data
Example 11 (continued) Constructing a Frequency
Distribution for Items in the Purchase Order Database
Statistical Methods for Summarizing Data
Example 12 Constructing a Relative Frequency Distribution
for Items Purchased
Compute relative
frequencies by
dividing each
frequency by 94.
Statistical Methods for Summarizing Data
Example 13 Frequency and Relative Frequency
Distribution for A/P Terms
Statistical Methods for Summarizing Data
Excel’s Histogram Tool
Using the Analysis Toolpak
Data
Data Analysis
Histogram
Fill in the Input Range and Bin Range (optional).
Choose Labels if columns have headers rows.
Choose Chart Output.
Statistical Methods for Summarizing Data
Example 14
Using the Histogram Tool for A/P Terms
A/P data in H3:H97
Bins below in H99:H103
Month
15
25
30
45
Statistical Methods for Summarizing Data
Example 14 (continued)
Using the Histogram Tool for A/P Terms
Table above is
not linked to
chart.
Statistical Methods for Summarizing Data
Example 3.15 Constructing a Frequency
Distribution and Histogram for Cost Per Order
5 groups with a
$26,000 group width
Statistical Methods for Summarizing Data
Example 15 (continued) Constructing a
Frequency Distribution and Histogram for Cost
Per Order
10 groups with a
$13,000 group width
Statistical Methods for Summarizing Data
Example 16 Computing Cumulative Relative
Frequencies for the Cost Per Order Data
Statistical Methods for Summarizing Data
Example 17 Computing Percentiles
Compute the 90th percentile for cost per order in the
Purchase Orders Data.
•Rank of kth percentile =
•n = 94 observations
•k = 90
•Rank of 90th percentile = 94(90)/100+0.5
= 85.1 (round to 85)
•Value of the 85th observation = $74,375
Statistical Methods for Summarizing Data
Example 18 Computing Percentiles in Excel
Compute the 90th percentile for cost per order.
•Excel function for the kth percentile:
=[Link](array, k)
=[Link](G4:G97, 0.90)
= $73,737.50
•Excel does not use the formula on previous
slide.
Statistical Methods for Summarizing Data
Example 19 Excel’s Rank and Percentile Tool
Data
Data Analysis
Rank and Percentile
90.3rd percentile
= $74,375
(same result as
manually computing
the 90th percentile)
Statistical Methods for Summarizing Data
Example 20 Computing Quartiles in Excel
Compute the Quartiles of the Cost per Order data
Excel function for quartiles:
=[Link](array, quart)
=[Link](G4:G97, 1) = $6,757.81
=[Link](G4:G97, 2) = $15,656.25
=[Link](G4:G97, 3) = $27,593.75
=[Link](G4:G97, 4) = $127,500.00
Statistical Methods for Summarizing Data
Example 21 Constructing a Cross-Tabulation
Sales Transactions database
Identify the number (and percentage) of books
and DVDs ordered by region.
Statistical Methods for Summarizing Data
• Example 21 (continued) Constructing a Cross-
Tabulation
Statistical Methods for Summarizing Data
Example 21 (continued) Constructing a Cross-Tabulation
Excel’s PivotTable (covered next) makes this easy.
Exploring Data Using PivotTables
Data
Tables
PivotTable
Follow wizard steps.
PivotTables allow:
Quick creation of cross
tabulations
Numerous custom-
made summary tables
and charts
Exploring Data Using PivotTables
PivotTable Field List
Select the fields for:
Report Filter
Column Labels
Σ Values
Row Labels
Or, before choosing
PivotTable, you can
select a cell in the data
and let Excel prepare a
default PivotTable.
Exploring Data Using PivotTables
Example 22
Creating a
PivotTable
Default
PivotTable for
Regional Sales
by Product
(sum of CustID is
meaningless)
Exploring Data Using PivotTables
Example 22 (continued) Creating a PivotTable
Pivot Table Tools
Options
Active Field
Field Settings
Change summarization
method in Value Field
Settings dialog box
Select Count
Exploring Data Using PivotTables
Example 22 (continued) Creating a PivotTable
PivotTable for Count
of Regional Sales by
Product
PivotTable results
match those shown
earlier in Table 3.1.
Exploring Data Using PivotTables
Example 22 (continued)
Creating a PivotTable
Drag Source into the
Row Labels box.
PivotTable for Sales
by Region, Product,
and Order Source
Exploring Data Using PivotTables
Example 23
Using the Pivot
Table Report Filter
Drag Payment into
Report Filter box.
PivotTable Filtered
by Payment Type.
Exploring Data Using PivotTables
Example 23 (continued)
Using the PivotTable Report Filter
Click on the drop-down arrow in row 1.
Choose Credit-Card.
Obtain this cross-tabulation
PivotTable for credit card
transactions.
Exploring Data Using PivotTables
Example 24 A PivotChart for Sales Data
Create a chart using the PivotTable for
Sales by Region, Product, and Order Source.
• Insert
• Column Chart
To display only Book
data, click on the
Product button and
deselect DVD.
Key Terms
• Area chart Data profile (fractile)
• Bar chart Descriptive statistics
• Bubble chart Doughnut chart
Frequency distribution
• Column chart
Histogram
• Contingency table
kth percentile
• Cross-tabulation
Line chart
• Cumulative relative
Ogive
frequency
Pareto analysis
• Cumulative relative
Pie chart
frequency distribution
Key Terms (continued)
• PivotChart Stock chart
• PivotTable Surface chart
• Quartile
• Radar chart
• Relative frequency
• Relative frequency
distribution
• Scatter chart
• Statistic
• Statistics