Pivot Tables: Analyze Data Quick and Easy

Locked lesson.

Upgrade

About this lesson

We learn the basics of a pivot table, how to create a pivot, refresh and format.

Exercise files

Download this lesson’s exercise file.

Pivot Table Inventory.xlsx
484.9 KB

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
Login to download
  • 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.

Gift this course