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

Finishing the Pivot Table

2:43 with Tyler Tallon

Now let's try building our pivot table by dragging fields into the four pivot table boxes.

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

    Okay, now the fun part, we get to start dragging fields into the boxes. 0:00
    One important thing to remember is that the order 0:05
    in which fields are added in each box will affect how it displays in the pivot table. 0:07
    It's usually best to start with the broadest category followed 0:12
    by logical subsets. 0:15
    Let's practice navigating the pivot table here a bit. 0:17
    Let's move State down to Rows, we can just click on it, and then drag and 0:20
    release once you are in the box you want to go in. 0:24
    Then let's place city in the rows box also, right after state. 0:27
    Now let's say we want to look at sales dollars by quarter, 0:34
    let's drag quarter to the columns box. 0:38
    And then sales dollars to the sum Values box. 0:43
    Now I usually like to format the data because I hate how messy it looks without 0:50
    formatting. 0:54
    Let's highlight the data, right-click > Format Cells, And 0:56
    then select number with comments and no decimals. 1:01
    There, that looks better. 1:08
    Okay, now let's say we want to see the sales by month also. 1:12
    Drag month right after quarter in the Columns box. 1:18
    Now let center the months column headers. 1:21
    Not mandatory but I think it looks better centered. 1:24
    Now we are getting somewhere. 1:30
    Here we can see sales by month for each city and also the state totals. 1:32
    We also have quarterly totals and grand totals for 1:37
    the first two quarters of the year. 1:40
    Now remember we have two products in our data, product a and product b. 1:42
    And since we haven't selected product yet for any of the boxes, 1:47
    it will default to show the total of both products. 1:50
    Let's try dragging the product down to the filter box. 1:53
    Remember, the filter works just how it sounds, it let you filter, in case, 1:57
    between products. 2:02
    Right now, it shows all. 2:04
    But if I go up and select product A, 2:06
    you will see the data change in the pivot table. 2:07
    And now, we are just looking at sales for just product day. 2:11
    Now, let's choose product B. 2:14
    Let's say we want to see units sold instead of sales dollars. 2:17
    The way we will do that, select Units Sold, and move to sum Values box. 2:20
    And we can either keep it like this where we can see both sales and units sold. 2:25
    Or remove sales dollars by the dragon or unchecking the sales dollars. 2:30
    All right, that covers some of the basic functionality for a pivot table. 2:35
    In the next video, we'll discuss a few more tips and tricks. 2:40

    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