Excel Pivot Tables for data analysis

How to prepare for and create Pivot Tables- 5 video lessons

Pivot tabels are one of the most powerful features of Excel, and we will show you in videos how to create them, and even add information which is not part of the original data.

Many people spend hours to enter data and manually create overviews for various reports. While this may be handy for standard reports, it is often difficult to change the lay-out and see the same information from a different perspective.

But with a slightly different approach to entering data, it is easy to produce the same overviews as well as getting the Totals across and/or down – and much, much easier to summarize any part of the data as you need it.

Rigid Tables, but with Colour function

One example of a fairly “rigid” report lay-out is our “colourfunction” table which, however, does have the added feature of being able to summarize data based on the fill colours.

Creating powerful pivot tables:

Situation: we have a bunch of detailed readings from a Weather station and now we are asked: “Does it rain more on Sundays than on Mondays, during the day?”
Looking at the raw data, that seems impossible to answer off hand, so we start working on it …

Video supported Lessons - using data downloaded from a Weather station

You can download the Excel (zipped) file “Pivots01-04_Template.zip” (3.82 MB), which includes the first 4 steps in these lessons.

The pace of the videos is suitable for beginners.

Pivots 01:How to format an Excel data list
More than 7,000 lines of weather data for every 30 minutes over a 5 month period and we show you how to format in Date/Time layouts and numbers to show same decimal points and made easier to read.
Pivots 02: How to use Excel time formulas
Our data includes weather readings every 30 minutes, and to be able to summarize all this later on we want to identify each reading and determine which Year, which Month, which Week etc.
This is done with standard Excel functions like “=Year()”, “=Month()”, “=Weeknum()”, “=Weekday()”, “=Hour()”.
Pivots 03: How to use Excel VLOOKUP
In this lesson, we use the “VLOOKUP()” function in Excel to add even further information to our data set. This is extremely useful when we want reports to show data with names of the Month or of Weekdays, and when we want the data split into specific segments of our own choosing.
Pivots 04: How to create an Excel Pivot Table
All the formulas we created in lessons 2 and 3 are copied and included in the entire data set, and we create our first Pivot table, format the table data and include report filters.
Pivots 05: How to make an Excel Pivot Chart and a Combo Chart
Our Pivot table is slightly modified (we take out the Grand Total across) and we insert a Column Chart which illustrates more clearly what the numbers in the table are telling us.
Finally, we show you how to make a Combo Chart and decorate it, including custom Titles and interactive Labels.

The YouTube Playlist

Quick links to Lessons:

Lessons

Want to download the Templates?

For “Pivots 01 – 04”:

You can follow in the Excel spreadsheet if you like. It is a relatively small zip file of 3.8 MB:

Pivots01-04_Template ⤵

For “Pivots 05”:

This version is a zip file of 20.5 MB in size, due to the large database of weatherdata:

Pivots05 -ComboCharts_Template ⤵