How to make a Pivot Table
Questions
Student Gallery
Course info
Overview
- Create a quote & invoicing form.
- Cleaning & formatting messy imported data.
- Inventory spreadsheet.
- Pivot tables
- Regional Sales Report
- Profit & loss spreadsheet.
- GST & Tax calculations
- Graphs for use in Word, PowerPoint, InDesign & Illustrator
- Creating spreadsheets that work within Word documents.
- This course is designed for people who have little or no previous experience in Microsoft Excel. You will start right at the beginning and cover all the basics.
- Only basic computing skills are necessary - if you can send emails and surf the internet then you’ve found the right training.
- By the end of the course, you will be producing real world results with Excel.
- No previous Microsoft Excel experience necessary.
- You'll need Excel 2016 installed on your laptop. The standard installation of Excel 2016 or the Microsoft Office 365 version is fine.
Course duration 2 hours
Daniel Walter Scott
Founder of Bring Your Own Laptop & Chief Instructor
I discovered the world of design as an art student when I stumbled upon a lab full of green & blue iMac G3’s. My initial curiosity around using the computer to create ‘art’ developed into a full-blown passion, eventually leading me to become a digital designer and founder of Bring Your Own Laptop.
Sharing and teaching are a huge part of who I am. As a certified Adobe instructor, I've had the honor of winning multiple Adobe teaching awards at their annual MAX conference. I see Bring Your Own Laptop as the supportive community I wished for when I was first starting out and intimidated by design. Through teaching, I hope to bring others along for the ride and empower my students to bring their stories, labors of love, and art into the world.
True to my Kiwi roots, I've lived in many places, and currently, I reside in Ireland with my wife and kids.
Downloads & Exercise files
Transcript
All right, it's the one everybody's been waiting for, it's Pivot Table time.
What is a pivot table? It is a way to take longer data like this, where there's lots going on, and break it down to usable chunks. I've created one, I just wanted to show you a bit of a demo.
So, on this other sheet down the bottom here, I've broken down the data to show me the ‘Products’ by the ‘Years’, and how much they were sold. I can break it down even further, and I want to say, the ‘Products’ that were sold by just 'Daniel Scott', the sales person, or maybe, 'Ben Samuels'. So it's a way of taking this data and feeding it into a sheet. And we have to kind of re-jig it to prove a point to extract data, to see it in a different way. So let's go off and build our pivot table.
To create a pivot table, open up the exercise files, there's one there called 'Pivot Table Data 1'. Open that up, and what we need to do is, have our cursor anywhere inside this data set. You might have to select it if you've got lots of different groups of data you're working with. In my case, it's just one big chunk, so I'm going to have it anywhere in here, and I'm going to go to insert, and this first one here called 'Pivot Table'.
Everything is fine, this one here, ‘New Worksheet' is just interesting. So we've got our workbook which is the Excel file. Within this workbook, we can have worksheets. I've got 'Sheet1' down here. And you can have multiple ones in here, it's just like pages in a document. So it's going to create a new one of these. I’m going to click 'OK'. And you'll see down here, here's 'Sheet1' still, with all my data, but I've got this new sheet too which is going to have my pivot table on it. Let's just name it, let's double click 'Sheet1' and call this one 'Raw Data'. And 'Sheet2' is going to be my 'Pivot Table', just so we know.
What we need to do is, we're going to use these pivot fields here. This is the most interesting, or important thing when you're building a pivot table. We're going to use these fields down the bottom here. The most important ones are rows and values, you'll use these the most. We'll use columns a bit more, and filters even less but rows and values. So rows are this. So the ups and downs, the Y-axis, the vertical. I'm going to put in, say, my 'Products' into here. So you click, hold and drag them into rows.
You can see here, it's listed them down here. So we're cleaning up our data from that nice, big raw data set, and kind of just pulling the bits we want. So I want to know to know the 'Products', and I want to know how much was 'Order'ed, the values. You can see here now, I've pulled from my raw data. I've pulled out the 'Products', and how much was spent on them. I can use a lot of these.
Let's say I want to get rid of the 'Products'. I can click on it, and there's one that says 'Remove Field'.Actually I want to find out actually-- from my 'Sales People', there he is there under 'Rows'. I can see, the rows down here, this is the totals that they've earned as sales people. I can 'bin' that one as well. I find rows and values often gives me most of the things that I want.
Let's say we're looking at our customers as well. These are the customers, and this is how much they've ordered. That's rows, down the side here, and the values. Columns is kind of when you want to break out even further. Say my question is, I want to find out ‘Products’, and I want to know in which ‘Quarter’ of the year they were sold. So, I'm going to 'bin' this 'Customer1'. And I'm going to drag in my 'Products'. And in the columns here--
So that's 'Products', but what I want to do is add some columns along here. 'Q1', 'Q2', 'Q3', 'Q4', so I can put in 'Quarter', and it breaks down my 'Apples', and when they were 'Sold'. So, in 'Q1', 'Blueberries' were sold, but there were no 'Apples' sold in 'Q1'. It might be nicer for this one, instead of 'Products'--
I’m going to remove this one, I'm going to say 'Sales People'. So there's the sales for 'Daniel Scott' in this quarter, and all the quarters, plus their 'Totals' at the end. By far, 'Daniel Scott' is the best sales person on the planet, good work, Dan. Poor Tayla! But that's all right. So that's a pivot table, and there's a lot of--
When you try to work it out you'll know that dragging it like this means this here, or that there. Does that look better? Sometimes you end up with the 'Product' along the top here. And I'm going to get rid of this one. Kind of, doesn't work. Kind of a strange looking pivot table. So you end up swapping these around, so don't worry. Often, their values are the same. It's the price, the quantity, the units sold. It's their numbers, it's their digits. And these columns and rows. Rows mainly, and then columns to kind of break out of it with more detail.
Now the last one here is filtering, and I find that I use this not very often. I'm going to show you two other ways of doing it. It just means I can filter it even further because I've got my X and Y-axis done. So what I want to do is, I want to kind of-- I want to break it down, actually I just want the results for Apples’. I don't want the rest of the data.
So instead of filtering it, what I can do is, you can see, here it says 'Row Label', this drop down here. And I can untick 'Select All', I just want to show 'Apples', and it breaks down that content a little more. And 'Apples' were only sold in 'Q3'. Or I can go into here, and say, actually not 'Apples', I meant, I want to check on how 'Boysenberries' are going. Boysenberries were sold in all four quarters. And it's their grand total, so we're kind of filtering down.
So you can use these little filters. It doesn’t matter if you're doing it up here. I'm going to turn all these back on. And you can see, up here, I can say I just want to filter by 'Q1'. Let's say 'Q1' and 'Q2' because 'Q3' and 'Q4' haven't finished yet, so we don't want to add those 12 totals. So you can do these filters thatare within the labels here. I'm going to turn all these back 'on'. So when's a good use to use these filters? It's when you want to add an extra filter. That's actually not displayed here because we're filtering by our'Quarters', and by our 'Products'.
Let's say you want to filter by the 'Sales Person' now, but we can't because it's not there. So this is where filters are useful. So in here, I'm going to add 'Sales Person'. You see along the top here, there's this extra filter. Something weird plays for me up, I don't like it sort of at the top here. Drop down here, and I’m going to say, I want to see what Daniel Scott's doing.
It's kind of this third filtering I've got. I got my 'Quarters', I've got my 'Products', but I want to filter it a little bit more. And I want to break it down by 'Sales Person'. Now, I don't really like filters just because there's a nicer way of working with this same function here. So I'm going to drop this down, and go to 'Remove Field'. And we're going to use something called 'Slice', or 'Slicer'.
I clicked in my table here, I'm at 'Analyze', and there's this one here called 'Insert Slicer'. I'm going to pick the exact same thing I had before, 'Sales Person'. And it gives the exact same details, but on a cool little push button version rather than this ugly thing at the top that's insignificant.
So I can say, actually I want to go by Daniel. I want to figure out what Tayla's doing, and I want to see what Ben's doing. The same thing, it's a filter. They call that a slicer. And it's this cool little box out here, you can click multiple things. I want to find 'Ben', so there's this option here. I can move out 'Ben' and 'Daniel'. It gives me those options, and I can clear it out as well, clear this filter.
To get rid of it, I can click on the edge, hit 'Delete', and that gets rid of the slicer. You can have more than one slicer. You can puddle them up next to each other so you can make some cool combinations if you need to. So, I want to find out who earns the most as a client. So, what I'm going to do is I'm going to pick my 'Clients'. You don't see 'Clients', it's 'Customers'. Drag him into here, get rid of my 'Products'. And I want to find out their-- actually I'm just going to get rid of this one as well. So this gives me all my clients, and how much they've earned. And I can either just look through, and see where this client is, or I can use my more traditional sorting functions. So I can click in here, and I can say--
Actually I want to sort it by largest to smallest. These guys here, 'Fabrikam' have been the best client for me for that data set. But say I want to break it out a little further. I want to know, across the quarters, who the best is. So I click on 'Quarters', and I'm going to drag that into columns. So I can break it out a little bit.
Couple of things to know when you're working with pivot tables is-- I don't like these little labels, they're cool for re-sorting things, but once you're done with them they just kind of confuse the pivot table. So, under 'Analyze', there's an option here that says 'Field Headers', and I'll turn this off. The graph's still perfect, except you don't have these little things you can re-order by. And nothing, it just cleans up the table.
So, up to you whether you have those on or off. If you do lose this thing on the side here, this 'Pivot Table Fields' list, it's this option here, 'Field List', you can turn it on or off. Also, if you lose it, it's probably because you got your cursor clicked out here. Click over this, double check that's on.
The other thing, and probably the most important, and I like to save this till the end, is that, if you'veupdated your raw data, weirdly, your pivot table doesn't update automatically. So I go into here, and I decide that actually this was wrong. And I put that to '0', I've copied that across, lots of them are '0' now. Even more to make it more obvious. I hit 'Save', and I go back to my pivot table, it's not updated. So what I need to do, whenever I update my raw data I click in here, I go to 'Analyze', and I go to this one that says 'Refresh', and it will go and re-jig it. So that's a pretty big one for the pivot table. A big marker right next to your desk. Raw data did not update pivot table.
So that my friends is how pivot tables work. I'm going to set you a little exam, a little project. I would like you to create a pivot table that shows me who the best sales person of Boysenberries were. I want you to go through and create a quick little pivot table, just to show me. There's a couple of ways of doing it, and how to display it, so I'd like you to display it, and send me little screenshots. Stick it in the comments, or links, any which way, Dropbox, any which way you can. I'd love to see it.
So that's it for pivot tables. Let's get on to the next tutorial video.
How do I create a pivot table in Excel?
Click inside your data, choose Insert > PivotTable, and place it on a new worksheet. Drag a category such as Product into Rows and your order amount into Values to summarise the data. Add columns, filters or slicers to explore the results, and refresh the pivot table after changing the source data.
How to Make a Pivot Table in Excel
Key Takeaways
Turn a long list of sales records into useful totals.
Understand what belongs in Rows, Values, Columns and Filters.
Compare products, salespeople and customers across quarters.
Use slicers for simple, clickable filtering.
Sort your results and keep them up to date with Refresh.
All right, it’s pivot table time. A big spreadsheet might contain everything you need, but that doesn’t mean it makes your questions easy to answer. Which products brought in the most sales? Who was the strongest salesperson? Which customers ordered the most?
A pivot table takes that long list of data and breaks it into manageable chunks. The nice part is that you can rearrange those chunks without rebuilding the report from scratch.
What is a pivot table?
A pivot table is a way to summarise and reorganise a larger dataset. Instead of reading through individual orders, you can pull out the categories and numbers that matter to a particular question.
For example, you could show products down the left, years across the top, and sales amounts in the middle. Then you could narrow the same report to sales made by Daniel Scott, or switch to Ben Samuels.
The underlying records stay on your raw data sheet. The pivot table gives you a separate, more useful way to look at them.
Start with the question you want to answer. That makes choosing your fields much easier than dragging things around and hoping something interesting appears. Although, to be fair, a little dragging things around is part of learning.
1. Create your pivot table on a new worksheet
The example uses a sales dataset containing customers, products, order amounts, dates and salespeople. If you have the exercise workbook, open Pivot Table Data One. Otherwise, follow along with a similar set of sales records.
Click any cell inside the dataset.
Go to the Insert tab.
Choose PivotTable.
Check that Excel has selected the intended data range.
Choose New Worksheet, then click OK.
If your sheet contains one continuous block of data, clicking inside it is usually enough for Excel to identify the range. If there are several separate groups of data, you may need to select the correct range yourself.
Choose New Worksheet to keep the summary separate from your source records.
A quick terminology check: your workbook is the Excel file. The worksheets are the individual tabs inside it, a bit like pages in a document.
Excel leaves the original data on its existing sheet and creates another sheet for the pivot table. Double-click the sheet tabs and give them useful names, such as Raw Data and Pivot Table. Much better than trying to remember what Sheet1 and Sheet2 contain.
2. Get comfortable with the four field areas
The PivotTable Fields pane is where most of the work happens. It lists the available fields and gives you four areas to drag them into:
Rows: the categories listed vertically down the left.
Values: the numbers you want to summarise.
Columns: another category used to split the results horizontally.
Filters: an extra way to narrow the report without displaying that field as rows or columns.
Rows and Values are the two to learn first. They can answer plenty of useful questions on their own. Columns add another level of detail, and Filters let you narrow the report further.
Start with products and sales totals
Suppose the question is, “How much was ordered for each product?”
Drag the product field into Rows.
Drag the numeric order amount field into Values.
The pivot table now lists each product with its total order amount. You have gone from a long list of individual records to a compact product summary.
The distinction matters: an order amount tells you the value of the sales. A quantity field would answer a different question, such as how many units were sold.
Swap products for salespeople or customers
Now ask, “How much did each salesperson sell?” You don’t need a new pivot table.
Open the menu for the product field in Rows and choose Remove Field.
Drag the salesperson field into Rows.
Leave the order amount in Values.
The totals are now grouped by salesperson instead of product. Replace salesperson with customer, and you get the amount each customer ordered.
That is the basic pattern: change the category in Rows, keep the measure in Values, and answer a different question.
3. Add columns to compare sales by quarter
A total is useful, but sometimes you need to know when those sales happened.
To compare product sales across quarters, use this arrangement:
Rows: Product.
Values: Order amount.
Columns: Quarter.
Drag the available quarter field into Columns. The report now breaks each product’s sales into Q1, Q2, Q3 and Q4, with a grand total alongside them.
Quarter columns show when each product contributed to the overall sales total.
In the example data, apples have no sales in Q1, while blueberries do. Filtering to apples later reveals that their sales appear only in Q3. That is the sort of detail a single annual total hides.
You can also replace Product in Rows with Salesperson. Now each person has a quarterly breakdown and an overall total. In this sample, Daniel Scott has the strongest total. Good work, Dan.
Don’t worry if the first layout looks strange
You will sometimes put a field in Columns, look at the result and think, “No, that’s not what I meant.” That’s perfectly normal.
Move it back to Rows, remove a field, or try a different arrangement. Values will usually hold the numeric measure, such as an amount, quantity or units sold. Rows and Columns organise that measure into a readable report.
The aim isn’t to find one perfect arrangement for every dataset. It’s to make the answer to your question obvious.
4. Filter the report using its row and column labels
You don’t always need to use the Filters area. If the field is already displayed in the pivot table, its label drop-down can narrow the results.
Show just one product
Open the Row Labels drop-down.
Untick Select All.
Tick the product you want, such as Apples.
Click OK.
The pivot table now shows only that product. Change the selection to Boysenberries, and you can inspect their quarterly sales and grand total instead.
Use Select All again when you want the full product list back.
Include only the quarters you need
The column-label drop-down works in the same way. For example, you might select only Q1 and Q2 because Q3 and Q4 are not yet complete.
That narrows the report and its totals to the selected quarters. Before comparing results, check which products and quarters are currently included.
5. Use Filters for a field that isn’t displayed
Here is where the Filters area earns its place. Imagine that Products are in Rows and Quarters are in Columns, but you only want sales made by Daniel Scott.
Salesperson isn’t displayed in either direction, so there is no salesperson label drop-down to use.
Drag the salesperson field into Filters.
Find the new filter above the pivot table.
Open its drop-down and select Daniel Scott.
You still have products down the side and quarters across the top, but the figures now relate only to Daniel.
Think of it as an additional layer: Rows and Columns organise the report; Filters restrict which records contribute to it.
6. Add a slicer for easier, clickable filtering
The report filter works, but I prefer a slicer. It does the same sort of job with a handy set of buttons on the worksheet, rather than a small drop-down tucked above the report.
To replace the salesperson report filter with a slicer:
Remove the salesperson field from the Filters area.
Click inside the pivot table.
Go to Analyze > Insert Slicer.
Select the salesperson field and click OK.
A salesperson slicer makes switching between individual sales results straightforward.
Click Daniel’s button to show his results, then choose Taylor or Ben to change the selection.
Select multiple people: use the slicer’s multi-select option, then choose the names you need, such as Ben and Daniel.
Restore all results: use the slicer’s clear-filter control.
Remove the slicer: click its outside border and press Delete.
You can have more than one slicer and position them beside each other to create useful filtering combinations. They are still filters, just in a friendlier, more visible form.
7. Sort the totals to find your best customer
A pivot table can list customer totals, but you shouldn’t have to scan every number to find the largest.
Put the customer field in Rows.
Keep the order amount in Values.
Remove Quarter from Columns for a simple overall comparison.
Click a number in the totals column.
Use the sorting command to sort Largest to Smallest.
The highest-spending customer moves to the top. In the example dataset, Fabrikam comes out ahead.
Want the quarterly detail as well? Drag Quarter back into Columns. You can now see the customer totals alongside their quarter-by-quarter breakdown.
This gives you both a ranking and the detail behind it, without returning to the individual sales records.
8. Tidy the report and recover the field list
Hide field headers for a cleaner presentation
The Row Labels and Column Labels controls are useful while you are arranging and filtering a pivot table. Once the report is ready, you may prefer a less cluttered appearance.
Click inside the pivot table, go to Analyze, and turn off Field Headers. The report and figures remain, but the field-header controls disappear.
It’s a preference rather than a requirement. Keep them visible if you still need the drop-downs, or turn them back on when you want to adjust those filters.
Bring back the PivotTable Fields pane
If the field list disappears, don’t panic. You may simply have clicked outside the pivot table.
Click back inside the pivot table.
Go to Analyze.
Check that Field List is switched on.
That brings back the pane you need to rearrange Rows, Columns, Values and Filters.
9. Refresh after changing the raw data
This is the big one: in this workflow, changing the raw data does not automatically update the pivot table.
Suppose you correct several source order amounts to zero. You save the workbook, return to the pivot table, and the old totals are still there. Saving is not the same as refreshing.
After editing the source records:
Click inside the pivot table.
Go to Analyze.
Click Refresh.
Excel updates the summary to reflect the changes. If you remember one maintenance habit, make it this: edit the raw data, then refresh the pivot table. Put a reminder beside your desk if you need to.
Try it yourself: Who is the best salesperson for boysenberries?
Here is a small challenge to bring everything together: create a pivot table showing which salesperson generated the highest order amount for boysenberries.
There is more than one valid layout. One approach is to put Salesperson in Rows, order amount in Values, and use a Product filter or slicer to select Boysenberries. Sort the amounts from largest to smallest so the answer is easy to spot.
You can add Quarter to Columns if you also want to see how that performance was spread across the year.
The useful skill isn’t memorising a particular layout. It’s knowing how to turn a question into categories, numbers and filters, then arranging them so the answer is clear.
Key terms
Pivot table
A summary of a larger dataset that you can rearrange to compare categories and explore totals.
Workbook and worksheet
A workbook is the Excel file. A worksheet is an individual tab within that file.
Field
A category or measure from the source data, such as Product, Customer, Salesperson or order amount.
Rows, Columns and Values
Rows and Columns organise the categories vertically and horizontally. Values contains the numbers being summarised.
Slicer
A visible set of buttons used to filter a pivot table by a selected field.
Refresh
The command that updates the pivot table after changes to its source data.
FAQs
What should I put in Rows and Values?
Put the category you want to compare in Rows, such as products, customers or salespeople. Put the numeric measure you want to summarise in Values, such as order amounts or quantities.
What is the difference between a pivot table filter and a slicer?
A report filter uses a drop-down above the pivot table. A slicer provides visible buttons on the worksheet, making it easier to select items and see the available filtering choices.
Why hasn’t my pivot table changed after I edited the data?
The pivot table needs refreshing. Click inside it and choose Analyze > Refresh; saving the workbook alone does not update the summary in this workflow.
Why has my PivotTable Fields pane disappeared?
You may have clicked outside the pivot table. Click inside it again, then check Analyze > Field List to make sure the pane is enabled.
Written by Daniel Walter Scott — Founder of Bring Your Own Laptop & Chief Instructor
