Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Intermediate Excel

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Other Pivot Table Tips and Tricks

2: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.

Downloads

Please note that this is a new spreadsheet, different from the one you used in the previous stage:

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    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

      You need to sign up for Treehouse in order to set up Workspace

      Sign up