Pivot Tables

Pivot tables are a part of the excel tool set and enable users to analyse large data sets quickly and efficiently with limited use and knowledge of formulas.

Pivot Tables can be designed to show different statistics using 4 main controls:

Filter- search for specific records from specific fields
Columns - show data from particular fields
Rows - group data by specific fields
Values - display summaries of grouped data
- Sum
- Count
- Average
- Minimum
- Max

Graphs

Pivot tables allow you to summarise large amounts of information into easy to read graphs to help you identify patterns and trends in specific scenarios.

Activity: Pivot Tables, Pivot Charts and Dashboard Creation

Download the relevant spreadsheet and work through all three activities. You may need headphones or might want to work in pairs.

Complete a short summary on your process throughout. Document how the organisation of this data might lead to specific insights, and how the data could be used to improve a business model (predictive data analysis, prescriptive data analysis).

Part 1

Associated document:

Intro-to-Pivot-Tables-Part-1.xlsx

Keyboard Shortcuts Used in the Video

Here is a list of all the keyboard shortcuts used throughout the video.

Ctrl+Drag Right with Mouse – Copy/Duplicate a Worksheet
Alt+; (semicolon) – Select Visible Cells
Ctrl+Enter – Fill Values/Formula to Selected Cells
Alt+F5 – Refresh Pivot Table
Ctrl+Shift+End – Select Cells to Last Cell in Data Range
Ctrl+Down Arrow – Go To Last Cell in Column
Ctrl+A – Select All Cells in Data Range
Alt+A+C – Clear All Filters

Part 2

Associated documents:

Intro-to-Pivot-Tables-Part-2.xlsx

Sales-Data-for-January-2015.xlsx

Here are some of the questions we answer in this video:

“What are the top 10 product categories?”
“What is the average unit price for each category?”
“How many orders did we have for each category?”
“Who are the sales reps selling in each category?”
“Which categories make up over 50% of our total revenue?”

Part 3

Associated document:

Intro-to-Pivot-Tables-and-Dashboards-Part-3.xlsx

Topics:

The Report Filters area explained.
Group dates into months and years to create a summary trend report and chart.
Group amounts to create a distribution chart (histogram). One of my favorites!
Resize all charts to be the same size.
Prevent charts from resizing when column widths and row heights are changed.
Add slicers to make the dashboard interactive.

Stretch: How to use Pivot Tables in Excel

The following resources can be useful towards increasing your understading of Pivot Tables and Pivot Charts

09 PivotTables.pdf

OLDER exercise:

We will watch this 6:21 video on Pivot Tables. Please download the sample file and recreate the Pivot Tables indicated.

Once you've done that exercise try to accomplish the following:

Create a PivotChart to visually show the weight of Large/Normal and Small orders.
Connect two sheets together to find data to rank the top 5 customers.
Answer how the two previous dot points might be used to find important information. How could that be used to improve a business? (Making meaningful actions based on data)
Think of another use for PivotTables and PivotCharts using this data that could positively affet the business. Justify and explain your process.

Report abuse