Other Pivot Table Tips and Tricks2:52 with Tyler Tallon
Now that we’ve learned the basics for building and navigating a pivot table let’s look at a few more tips and tricks.
Please note that this is a new spreadsheet, different from the one you used in the previous stage:
Now that we've learn the basics for building and navigating a pivot table. 0:00 Let's look at a few more tips and tricks. 0:03 Earlier we had mentioned that it was a best practice to set up your data as table 0:06 before you create the pivot table, in case you need that add a row or column. 0:10 Let's try that. 0:14 Say we open a new store in June and need to add that to the data. 0:15 We can insert a line here right below Houston Texas and 0:19 fill in cells for a new store in Abilene Texas. 0:23 And let's say we know that we sold 1,000 units for $4,000. 0:30 Now if we go back over to a pivot table and 0:39 right-click somewhere inside the pivot and 0:42 hit refresh, you will see the Abilene store shows up now. 0:46 Another cool thing about pivot tables is that you can sort in ascending or 0:51 descending order. 0:55 So if you want to see the states with the highest unit cells, 0:57 you can click on the Total and then go to the Data tab, Sort, and 1:00 click Largest to Smallest, or descending. 1:05 You'll see under the example that California has sold the most units. 1:08 Then you can also click on a Total for one of the cities, and Sort descending. 1:11 And all the cities will now be sorted for each state. 1:16 We discussed previously how to use the filter field 1:19 to filter between what you want to see. 1:22 You can also do that wherever there a drop down on the pivot table. 1:25 For instance, you can select the Row Labels drop down and 1:29 choose a few states if you are wanting to narrow down your list. 1:33 Now let say, you don't want to see the sum of sales or unit sold but 1:39 prefer to see the average. 1:43 Under the Analyze tab, select Field Settings. 1:45 You'll notice there is several options here, 1:51 Sum, Count Average, Max, Min, and Product. 1:55 Let's select Average. 2:00 Another cool thing is the different layouts in the design tab. 2:06 If you click on Design, you can see all the different options. 2:09 Let's select a few and see how they change the look of the pivot table. 2:13 I wish we had the time to cover all the functionality of the pivot table, but 2:23 there are a few we'll have to leave out. 2:27 But I hope you explore some on your own, and 2:29 maybe check out the slicer function found in the Analyze tab. 2:31 Or try adding a calculated field if you want to know the cost per unit for 2:35 each store. 2:39 Or play around with conditional formatting, or generate a pivot chart, or 2:40 several other advanced features. 2:45 I've got a few of my favorites linked below in the teachers notes. 2:47 As you can see, the pivot table can be a very useful tool and 2:50 I hope you have fun exploring. 2:54
You need to sign up for Treehouse in order to download course files.Sign up