This tutorial shows you some basic techniques for how to use pivot tables with a simple bank account data example. It includes a video demo and an Excel download. We start with some bank account data for one month (and your online banking service will provide similar e.g. Halifax) and use pivot table techniques to view it by category and week as shown:
How to use Pivot Tables Tutorial
Watch this video to see how it is done:
How to use Pivot Tables Example: Tutorial 5-Step Summary
- Get your data into Excel and make sure the data is contiguous (i.e. no completely blank rows or columns), also make sure your data has column headers in one row (i.e. not merged headers over two rows). My example (Excel file HERE) has four columns of data (date, transaction, CR, and DB).
- Press Alt+D+P to open the New Pivot Table dialogue and enter your source range as well as destination (top-left) pivot table cell. Then drag the CR and DB fields into the VALUES section of the pivot table
- Add AcType column and create a category for each transaction, also add a Cashflow column with the to show the net cash flow (i.e. CR-DB) for each transaction, and finally add a Week Commencing column to get the Week Commencing (Monday) date for each transaction
- Change the pivot table datasource to include your additional columns
- Drag the Week_Commencing field into the Columns section, drag the AcType field into the Rows section, and drag the Cashflow field into the Values section
Applying these Basic Pivot Table Skills
I have been using Excel for data analysis professionally for ca. 10yrs across 5 industries and pivot tables are by far the best Excel tool for ad-hoc data analysis. This article is designed as a primer or starter guide to pivot tables showing their utility by using data we are pretty familiar with.
The pivot table basics taught in this lesson are applicable to any dataset where you want to aggregate and cross-tabulate calculations–e.g. survey results analysis, analyzing sales trend data, operational or production data analysis, splitting marketing data by channel, etc. Look out for other articles on Analyze6.com for more advanced pivot table technique tutorials.
Techniques Learned in this Tutorial
changing pivot table data source, modifying underlying pivot table data, create a pivot table, add a calculation to pivot table, pivot table value formats, basic pivot table skills, changing pivot table settings, creating a pivot table cross-tab