Skip main navigation

£199.99 £139.99 for one year of Unlimited learning. Offer ends on 28 February 2023 at 23:59 (UTC). T&Cs apply

Find out more

Introduction to Basic Pivot Tables

In this article, you’ll learn how to create and display the data from your Excel table in a basic pivot table.
Welcome back. In this part, we’re going to explore our first table. All right, so we just moved right into Excel where we left off. I’ve fixed a little bit the expression to have more. Remember, we did the customer A at the customer bucket, and now, it’s actually even longer. We have more buckets, so it will be more meaningful. Now, I’m in this table. Remember this object, this thing that is also formatted. I didn’t mention the fact that it’s actually formatted in this way by default, which makes it easier to see what’s in each row– this kind of zebra formatting, which is the default for a table.
Another thing that I see on the ribbon for the table is that I can summarise it with pivot tables. And remember, pivot tables was one of the things that Jack, our friendly IT person, mentioned that I should use in order to create my report. So I click on Summarise with Pivot Table. I get this Create Pivot Table dialogue that says I’m going to use table one as my source for the pivot. Table one is the default name that was given to this table. If you start to use tables more often, and you have multiple tables, it’s a good practise to actually give your tables meaningful names, and not just leave them as table one, table two, and so on.
But for now, we have just one table, and we can call it table one if we want to. I’m going to click. I click on the default. I didn’t change anything. And I get a new worksheet with the new pivot table in it. The pivot said, under pivot, I see, to build the report, choose fields from the pivot table’s field list. All right. What is the field list? Oh, this one here. This is the field list, so it has the list of all the fields. So I had columns in the table, they became fields in this list here. And I can start clicking on elements here to start building my report.
For example, I know that I want the reports to be by revenue. So I click on Revenue. What do I see? I see just the pivot, the empty pivot disappeared, and instead it became– there’s two sales, one of them is saying some of revenue, and another one contains a number. This number– what is it? It’s the total number of all revenue in all columns, sorry, in all the rows in the original table. So we already have the feeling that a pivot table is all about summaries and summarising data. The format of this revenue is not exactly what we would like.
So I may want to go right-click on the table, click Number Format, and choose a currency format for it, maybe without any decimals. So like here, and now we have it with commas for separating in the thousands, and also with a dollar sign, which is my currency. All right. So I have just total, total revenue. But it’s not exactly what I wanted. So now, I go to the country, because my first attempt was to create a summary report by country. So I go to the country, and I just click it. And immediately, I have a list of all the countries. And for each one, I have the summary of the revenue. So again, this is the nature of the pivot.
It creates summaries. And it creates summaries based on the field that I’m adding to the pivot itself. So now, I’m going to look for– I have a column called year. The year is, actually, if we switch back to my table, I can see that the year is based on the date. So if the date is February 19, the year is 2016, it was pre-prepared for me in the table, because Jack, who prepared the data, knew that I’m going to be needing some analysis by year. So I am taking the year, and I’m dragging it to columns. So let’s stop for a moment and look at this area here that have different areas.
And above its cell, it tells me, drag fields between areas below. So we have four areas for the pivot. I have values area, I have rows, I have a columns, and I have philtres. For now, we are using three out of the four. We didn’t use the philtres yet. The first one we used was the values. We just dragged something to the values area, and this became a column that was actually going to be summarised. So with direct revenue, we got summaries by revenue. If we’re going to drag another thing. For example, if we drag also the cost, we’re going to have another summary, as, you see, one is formatted nicely, one is not.
But for each combination, we got both of them. If I don’t want a field, I can just drag it out of the pivot, and just go back to my smaller version. Now, whatever is on rows, I’m going to see here, listed by default. It’s sorted by alphabetical order. And what is in columns, I’m going to see across columns for the pivot. So now, we have all the combinations of a year, and a country. And there’s a special column for the grand total for each country. In this case, in column E, I see the grand total of country. I see there is a new row for column in row 11, which is the grand total by year.
And there is one sale, which is the combination of E and 11, E11, which is the grand total of everything, which if you remember, is exactly the number that we saw in the initial, before we started to drag anything. So that’s started, already, to be kind of interesting. Now, we have very easily created a report that had the summaries of each country for each year, which is actually one of the initial summaries that we got from Jack in the beginning. Now, if I’m going to continue to drag things. For example, if I I’m going to drag state below the country, now I’m getting each country and broken into states.
Like in France, there’s sometimes they’re called states or they’re called regions. But in some countries, like England and the United Kingdom, there is no things similar to states, so it’s just one area called England inside the United Kingdom. But in others, like in the US, there are plenty, and so on. So now we have a more detailed one. And for each one of the original countries, we can click on this minus, and actually make it smaller, so that now the states do not show. So this is called collapsing. And when I click on the plus, it’s called expanding, and I can see more detail.
So we can already see that the pivot is a very flexible tool to get summaries on different things. I can place the different elements that I want to Summarise on rows or columns. We haven’t seen yet what is the role of the philtre area. So let’s try that. So what will happen if I want to, for example, take the product category, and put it in philtres? I see that I have this product category, and it says All. And it has this kind of thing, which I am pretty sure is going to be a dropdown of some kind. When I click on it, I see my different three categories of product– accessories, bikes, and clothing.
If I select one, let’s say bikes, and I click OK, the numbers are now summarising only the rows which belong to bikes. So now, we have a subset of the original rows of the table summarised here only for bikes. There is a checkbox here that says Select Multiple Items. I can allow myself to select multiple items. And now, for example, if I want to see everything except bikes, I can go and check accessories and clothing. And now, the numbers that I see here are actually the totals for those two categories that I selected, and not the bikes. Notice another thing.
Another property of the behaviour of the pivot table, which is in this report now, we only see 2015 and 2016. Well, if I’m doing it, I’m going back to the summary by bikes, I see that I have 2014, 2015, 2016. What do I learn from it? I learn from it that, in 2014, we didn’t sell anything else but bikes. The only category of products was bikes. The other two categories were added, or we expanded to selling, accessories and clothes, in 2015. So that’s why in 2015 and 2016, we have the three of them, while if I don’t count bikes, the column for 2014 which became empty, also disappeared from the pivot.
So the pivot default behaviour is to eliminate any row or column which is empty. Which also, usually, it’s very useful. And I will leave it with this default. So this was our first pivot. We already see that the pivot is really flexible, can give us lots of different combination of summaries. And in the further parts of this course, we’ll explore a lot of the more advanced features, of the pivot, and the pivot charts, and the slices, as I mentioned, that will come pretty soon. See you next time.

When you’re dealing with a large quantity of data that needs summarising, you can make use of the pivot table feature in Microsoft Excel.

Let’s start with a simple definition.

What is a pivot table?

A pivot table is one of Microsoft Excel’s most powerful functions. It might seem intimidating and although it’s a complex tool, it’s great for summarising and making sense of large datasets.

Pivot tables not only allow you to extract meaning from large datasets but also let you group data together in different ways so that you can draw helpful conclusions more easily.

It’s referred to as a pivot table because you can rotate or pivot the data in the table to view it from a different perspective.

How to create a pivot table

There are several ways to create a pivot table. No matter how you choose to create it, remember that a pivot table starts with data that is organised in a tabular format that doesn’t have any blank rows or columns. Keep in mind that data types in your columns should be the same.

Tables are a great pivot table data source. When working with Excel tables, you can summarise your data with a pivot table. You can find the summarise option on the ribbon for the table in Excel. You can select the table you want to use as your source for the pivot from the pivot table dialogue and also change the default name that Excel assigned to the pivot table.

Pivot areas

The pivot table fields section allows you to select fields to add to your report. You will notice four areas that you can use for your fields. These include:

  • Filters
  • Columns
  • Rows
  • Values
This article is from the free online

How to Use Microsoft Excel for Data Analysis

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education