Locked lesson.
About this lesson
We learn the basics of a pivot table, how to create a pivot, refresh and format.
Quick reference
Pivot tables: analyze data quick and easy
Learn the basic uses of pivots, how to create a pivot and how to format.
When to use
Pivots are extremely useful and easy to use if you need to summarize data for reporting purposes.
Instructions
- To prepare your data as a Table helps a lot, especially when you update the data
- Pivots can be done from the Table Design tab, Summarize with Pivot or from Insert tab, Tables grouping, Insert Pivot table
- Pivot tables add 2 contextual tabs
- Create a pivot:
- Use the field list to drag fields (headings) to and from pivot areas or filters
- Move the fields into different areas until the report shows you what you need
- Format the pivot:
- To format numbers, right-click on the number in the pivot and click on Number format
- To change the heading, click on the heading and make changes in the formula bar, remember that the field names are already taken, so add something like a space after the word
- To update data:
- add data to the table (original data), the pivot table does not update automatically. Data tab, refresh all or in the pivot table, right-click and refresh
- Other structural changes:
- Use the row labels button to sort the data
- Right-click on a number to show numbers as percentages or change the sum to count
- Add levels to the pivot by adding another field in the row area, rearrange the fields if you need levels to change
- On the Pivot Table Analyse tab, click Field buttons to hide the row and column label button on the pivot
- To fix column widths, make the change as per normal and change the pivot to not update column widths otherwise the pivot goes back to other settings when it's updated. Right-click inside the pivot table, go to pivot table options and on the Layout & Format tab, untick “Autofit column widths on update”
- Add a filter to the area and further customize the pivot
- 00:04 In this example,
- 00:05 we have an inventory list of a boutique that displays inventory per warehouse.
- 00:11 And also at the bottom of the sheet, we have warehouse information for
- 00:16 warehouse number 201 that is ready to be added and updated in our table.
- 00:21 First let's create our pivot table.
- 00:23 There are two ways to do this.
- 00:25 You could click inside the table, go up to the Table Design tab and
- 00:30 click Summarize with Pivot Table.
- 00:33 Or another way is to just click on the Insert tab,
- 00:36 then click on either Recommended Pivot Tables or on Pivot Table.
- 00:40 So let's do that.
- 00:41 Pivot table recognizes the range as the table and
- 00:44 wants to put the pivot table on a new worksheet, that sounds good.
- 00:49 So let's hit OK.
- 00:49 And here we go, we have a blank area for our pivot table.
- 00:55 If I click inside this box on the left,
- 00:57 it'll show me my pivot table fields on the right and my two contextual tabs above.
- 01:02 If I click outside that box, the contextual tabs go away and so
- 01:07 does the list of fields.
- 01:08 So let's see how to get a pivot table.
- 01:12 I have my field list over here, the field list are my headings in my table.
- 01:17 And let's say I want my total items per warehouse.
- 01:20 So let's add warehouse into columns.
- 01:23 We do that by clicking and dragging the field down into one of these windows.
- 01:28 Okay, that looks a little weird.
- 01:29 Maybe let's try warehouse in rows.
- 01:31 Again, just click and drag the field to move it and that definitely looks better.
- 01:37 Okay, now I want to add my total cost per warehouse.
- 01:41 And that I'm going to add to my calculation column down here under values.
- 01:47 And that summarizes per warehouse my total inventory.
- 01:51 The formatting doesn't look good to me though.
- 01:54 So I'm just going to click on a number.
- 01:57 Let's click one at the top.
- 01:59 We right click and change my number format.
- 02:02 I can change my number format from inside the pivot table,
- 02:08 change it to accounting, no symbols, no decimals, and hit OK.
- 02:14 All right, that looks better.
- 02:15 All right, let's say I want to change the name of this heading a little bit.
- 02:19 Easiest way to do this is click in the formula bar, change it there.
- 02:24 I want to change it to total cost, and I can press Enter.
- 02:29 But if I do that, Excel will give me an error message because this header is
- 02:33 actually the same name as a field name.
- 02:36 You see the error message is pivot table name already exists.
- 02:39 Well, let's hit OK, and we can get around this error.
- 02:43 We can just add an extra space at the end of the table heading, and
- 02:47 now it won't complain.
- 02:49 Just a little trick there.
- 02:50 Okay, let's get the new warehouse data in here.
- 02:55 On the inventory sheet, go down to where the new data from warehouse tool one
- 03:00 is located and click in that first cell.
- 03:02 Let's use our keyboard shortcuts to move to the bottom of the data.
- 03:06 That's Ctrl and arrow down.
- 03:08 That puts us in the last row of data.
- 03:12 Now we need to select all this data, and we can drag and
- 03:15 drop it into our table above.
- 03:17 Let's use our shortcuts again.
- 03:19 Select the entire row using Ctrl + Shift + Right and
- 03:24 then Ctrl + Shift + Up to select all the data in the rows above us.
- 03:30 Now, warehouse 201 data is selected.
- 03:33 Let's drag and drop that to the bottom of our table,
- 03:38 and all my data is included and added.
- 03:41 So let's go back to our pivot table.
- 03:42 Where's warehouse 201?
- 03:45 It's not there.
- 03:47 Remember a pivot table needs to be updated manually anytime you add new data.
- 03:52 So you can either refresh by clicking on the Data tab, and then the Refresh All
- 03:57 button, or you can just right click on your pivot table and click on Refresh.
- 04:03 So that added the data for warehouse 201.
- 04:06 If this data is not sorted nicely enough for you, you can click on a number,
- 04:12 right click > Sort > Smallest to Largest, and that looks better.
- 04:18 You can also right click and Summarize Value by Sum,
- 04:23 Count, Average, Max and Min.
- 04:26 Or you can choose to show the values as a percentage of the grand total, like this.
- 04:33 That's interesting.
- 04:33 But for now, let's show these numbers again with no calculation.
- 04:38 Let's try something else.
- 04:39 Let's add more detail to the pivot table.
- 04:43 I want to know which items are in the warehouse.
- 04:45 So let's click and drag items down into the rows section here.
- 04:52 Now you can see warehouse 216 has accessories, shirts, t-shirts.
- 04:59 Let's see what happens now if we move warehouse over to columns.
- 05:03 So that changes the design of our pivot table here.
- 05:06 Now I have the items listed in this column on the left and
- 05:10 we have a really cool look at our data.
- 05:13 I can even add the detail field to my rows.
- 05:16 So now this is a nice looking report.
- 05:19 If I want, I can also collapse and expand some of this data.
- 05:23 If all these individual items are adding too much detail,
- 05:27 we can collapse the accessories and blouse items, and now I have subtotals.
- 05:33 But if you don't want the expand and collapse buttons,
- 05:36 go to your Pivot Table Analyze tab and just untick that option.
- 05:40 It's the plus minus buttons one here, and then they go away.
- 05:45 You also have column labels and row labels with a drop down.
- 05:49 If you click on the drop down, you could click on sort or
- 05:53 you can filter the warehouses.
- 05:55 This is all optional too.
- 05:57 So if you're not interested in any of that, you can click on the field headers
- 06:01 button and now your report is a little less cluttered.
- 06:04 But you can also do basic things like change the column widths,
- 06:10 let's do that real quick.
- 06:12 Okay, that's better.
- 06:12 Now if you update the column widths, to make them stick,
- 06:18 right click and pivot table options.
- 06:23 And on the layout and format tab,
- 06:25 untick autofit column widths on update and click OK.
- 06:30 Now those column widths that you set will stick even when you do a new data update.
- 06:35 So now one last thing I can show you is filters.
- 06:40 Let's say you want to filter everything in this table by a certain value.
- 06:43 Like, let's try colors.
- 06:44 I can drag color down into filter.
- 06:49 And then up in the upper left,
- 06:52 I can choose to show only certain colors that I want.
- 06:57 Let's check off a few.
- 06:58 It's almost sitting outside the pivot table but
- 07:01 it still has an influence on the values here.
- 07:04 So that's what filter does.
- 07:06 All right so the best way to learn about pivot tables and
- 07:10 how to use them is to play around with the data.
- 07:13 I've given you a nice database in this example with lots of different fields and
- 07:18 number fields that you can do calculations on.
- 07:21 So feel free to play with it and enjoy learning about pivot tables.
Lesson notes are only available for subscribers.