Microsoft Excel: Data Analysis; Getting Started with Pivot Tables
Pivot tables are an essential tool for working with and quickly extracting information and insights from a pile of data. However, pivot tables are NOT intuitive until you have spent some time with them.
In this post, I briefly cover:
- Setting Up Your Data
- Onto the Basics of Pivot Tables
- Creating Awareness of Pivot Table Possibilities
- Ready for a Challenge? Pivot Table Data Analysis Exercise
Setting Up Data
The most important aspect of pivot table setup and design is having a properly structured data set. It is important to structure the data using FIELDS and RECORDS (many times, this is referred to as a “database structure”), rather than a “Report” structure.
What does that mean?
Here is a typical dataset that I see:
- The Dates across the top of the report is repeating Dates, when dates should be ONE column, representing a DATE FIELD.
Here is a properly structured dataset.
This data set is structured as an Excel Table, which simplifies and facilitates the data analysis process.
A Pivot Table exercise resource that I put together uses the above data set and challenges the user to create basic analysis. I cover this at the end of this post.
Onto the Basics of Pivot Tables
Once the data is structured, the analysis part starts (this is where the POWER of Pivot Tables starts to show up). Pivot tables can be very simple to complicated projects. Let’s keep this simple.
Learn the basics
I use the YouTube channel, “ExcelIsFun” as one of my resources for Excel training. The resources are excellent and free! Here are a few getting started videos I suggest anyone beginning to work with pivot tables watch. If you invest 45 minutes watching these, you will be well on your way!
Data Analysis Pivot Tables & Pivot Charts, PivotTables & PivotCharts (19:44)
Grouping Dates in Pivot Tables: Years, Months or Weeks, and more (11:45)
Pivot Table Slicers, Report Filter & Show Report Filter Pages (11:43)
Creating Awareness of Pivot Table Possibilities
PivotTables Are Easy! 30 Examples for Highline College Professional Development Day 2014
This 44 min video covers a lot of Pivot Table topics and I highly recommend watching this to just “get a sense” of things that can be quickly accomplished with Pivot Tables.
The topics quickly get complicated. WATCH FOR AWARENESS, not to learn. In my mind, step one is to be aware that these things can be accomplished — you can come back and learn them once you are ready or perceive the need to learn it.
Ready for a Challenge? Pivot Table Data Analysis Exercise
A few years ago, I compiled a Pivot Table exercise, which contains a data set of bank closings over a historical period, and asks you to answer a number of questions. All of the questions can be quickly answered with a solid knowledge of pivot tables. (Download the files here)
A SOLUTION SET is also provided. The idea is to learn — use the solution set to work through the questions that you don’t have a sense for. This data exercise has been very well received (NOTE: it was compiled using Excel 2007, so there are some differences in views of the analysis — sorry for that).
Here are few comments I have received on this exercise:
The presentation below covers the specifics and highlights of the analysis exercise. Good luck!
If you got to this point, congratulations! You are well on your way building your Excel data analysis skills.
Connect with Don!
LinkedIn, Flipboard, Twitter, Snapchat
