What is a pivot table & how do I create one? (interactive tutorial)
Pivot tables are a very powerful tool for analyzing data. Learn how to create pivot tables in under 5 minutes with this interactive tutorial!
What are pivot tables? And why are they so useful?
Pivot tables are a simple, yet powerful tool for analyzing data. It takes a complicated dataset with many variables, rows & columns, and re-groups them into a table that's easier to read, allowing you to draw conclusions.
Pivot table example:
Let's say we have a dataset of grocery sales across cities:
You can transform that dataset into a pivot table that looks like this:
How to make pivot tables:
You can make a pivot table in a few seconds! Here's how to do it:
1) Come up with a question about the data (or a hypothesis) e.g. "Which cities are producing the most sales?"
2) Identify the key variables in the question i.e. Which cities are producing the most sales? Key variables are:
Quantity (of sales)
3) Input those variables into 'smart pivot.'
Try it yourself!
Select 'city' in the 'columns' dropdown
Select 'quantity (SUM)' in the 'metrics' dropdown:
In a matter of seconds, you've just created your pivot table!
If you did it correctly, it should look like this:
Boston produces the most sales, followed by New York.
Now try answering these questions:
What is the total price of every item sold in each city?
What are the top selling products in each city?
Boston sold $13,265 worth of groceries, NY sold $8,258, LA sold $7,687 and San Diego sold $4,113.
Carrots are the top selling product in Boston and LA.
Polymer will encrypt and analyze your data using intelligent AI. Then it'll create a web application like the above for you to play around with.
Polyer is a very powerful tool for analyzing your data: it has a pivot table feature, data visualization features and the 'auto-insights' feature which uses AI to generate insights on your data.
SUM, AVERAGE, COUNT explained
First of, notice how there are multiple entries for the city 'Boston:'
Quantity (SUM), adds up the quantity. This can be useful for seeing how many sales each city is producing overall.
But maybe Boston has the most sales because there are more shops set up in Boston. Maybe there are 30 shops in Boston, whilst there are only 15 in New York. In this case, we want to use "Quantity (AVERAGE)" to see how each shop is performing on average.
COUNT tells us how many 'Bostons' there are.
Here we choose 'Quantity (AVERAGE).' Looking at this pivot table, it's evident that despite Boston having the most sales, it actually performs worse than Los Angeles and New York in terms of average quantity of sales. This is because there are more instances of Boston (the #COUNT# column will testify to that).
How do I create pivot tables in Excel?
Pivot tables are more time consuming and complicated to create in Excel, but I covered that tutorial in another blog post.
How do I create pivot tables in Google Sheets?
It's pretty much the same as Excel with a few minor differences. We'll cover that in a later tutorial.
How to use pivot tables?
Pivot tables are best used when you have a question or hypothesis about the data. Once you have a hypothesis/question, all you need to do is identify the key variables (as we did above) and input them into the smart pivot fields and you'll get your question answered.
If you don't have an initial hypothesis or question, you'll need to do some data exploration. Polymer's auto-insights feature allows you to deep dive into your data to find trends, correlations and patterns. Read more about how to analyze data here.
Does Polymer require any downloads?
Nope, Polymer is entirely a web-based tool!
What are the advantages of Polymer over Excel?
Excel is universally regarded as one of the best tools for storing and editing data, but it's also one of the worst tools for analyzing data. This is where Polymer comes in.
Polymer allows you to analyze & visualize your data without technical programming knowledge. Utilizing AI technology, Polymer is powerful enough for a data analyst but easy enough for a standard citizen.