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

Building a Pivot Table

2:42 with Tyler Tallon

When setting up a pivot table, it’s always best to start with the end goal in mind. If you have the end goal in mind it will save you time and will guide your strategy on how you set up your pivot table.

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

    When setting up a pivot table, it's always best to start with the end goal in mind. 0:00
    Is there a specific question you are trying to answer? 0:05
    Are you just wanting to slice and dice the data to explore any relationships? 0:07
    Are you wanting to create a summary report for a presentation? 0:12
    If you have the end goal in mind, it will save you time and 0:15
    guide your strategy on how you set up your pivot table. 0:18
    All right, let's go back to our example spreadsheet to start building our 0:21
    pivot table. 0:25
    Here, we have our example spreadsheet, and 0:26
    we've confirmed the data are in their appropriate format. 0:29
    And again, if you would like to follow along, 0:32
    you can find the link to this spreadsheet in the project files. 0:34
    After we have confirmed the appropriate format, the next thing we need 0:37
    to do is create a table, by clicking somewhere in the data range. 0:41
    And then going to the Insert tab and selecting Table. 0:45
    If you've already clicked somewhere inside the range, 0:49
    then the range should set itself for the table. 0:52
    So just make sure that My Table's headers are selected, then hit OK. 0:54
    Creating a table like this is not absolutely necessary, but 0:59
    it is a best practice. 1:03
    And if you later add a row to your data, do refresh, which I'll show you later, 1:04
    will add the new row to your pivot table automatically. 1:09
    That way, you don't have to respecify your range each time a row or 1:12
    a column gets added. 1:16
    Now you can go up under Table Name and name your table. 1:17
    Let's name this one store sales. 1:21
    The name has to start with a letter or underscore and it can't include any spaces 1:24
    or be the same as an existing name already in the workbook. 1:29
    Now on the Insert tab, select Pivot Table, and for the table range, 1:34
    it automatically populates with our table name, StoreSales. 1:38
    If for some reason it doesn't, you can just type it in here. 1:42
    Now, choose where you want the pivot table to be placed, 1:45
    in your worksheet or existing worksheet. 1:48
    If you choose existing worksheet, you'll need to choose which worksheet or 1:52
    tab you want to place the pivot table in. 1:56
    I usually choose new worksheet as this keeps the work book a little cleaner in 1:59
    my opinion. 2:02
    So that's what we will do here. 2:04
    Now you can see, the blank pivot table on our new worksheet. 2:05
    And if you go to the right, 2:09
    you'll notice there are four areas in which you can drag fields. 2:10
    The first is Filters, which accesses dropdown so 2:14
    you can filter to wherever you want including in the pivot table below. 2:17
    The second is Columns, so I'll add column headers here. 2:21
    The next one is Rows, and we'll add row headers here, subcategories will nest. 2:25
    And the fourth one is sum Values, we'll add any fields that should be summed here. 2:32
    In the next video, we'll start adding our fields and see it all come together. 2:38

    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