PivotTable
Introduction
PivotTable is a powerful tool that savvy business analysts cannot live without. It provides an interactive environment for summarizing and exploring data, where insights can then be harvested.
PivotTable can also be used to plot distribution charts (or so called histogram or frequency charts). This method is better than Excel's own Histogram tool under Data Analysis. We illustrate this below with an example.
Please use the comment section below to post your questions and thoughts!
Explore data and gain insights with PivotTable: product sales in different markets
Problem
A company is testing two products, code named Azure and Peach, in three regional markets (Atlanta, Boston, and Chicago). Now the data are in for three months from January to March (download link below)
Use PivotTable to analyze the data, and answer the following questions:
(1) What are the sales trends for the two products?
(2) What can we say about the popularity of these items in the three regions?
Explore data with PivotTable: car reservations
Problem
An online bike rental company is doing quite well, except that it often runs out the most popular type of bikes and have to offer customers free upgrade, which costs the company some lost margins. The company decides to look into past reservation data in order to find out some patterns of how people make reservations, such as:
- On which week day do people make reservations most?
- How long is the reservation period?
- How advance in time do people book a reservation?
They hope that by mining that data the company can develop some useful strategies that would improve the frequent stock-out situation.
Use PivotChart to draw histograms
Problem
Draw frequency plot (or histogram) of monthly change in NASDAQ composite index using Pivot Table.
