Intro to Excel Pivot Tables

Excel Pivot Tables provide you with a quick and easy tool to summarize, aggregate, and reorient your data to display your needed results. I tend to use my Pivot tables in a manner similar to some basic queries I might write in Access; using them to Sum the number of Billed Hours, count the number of Employees, or change the representation of the data from my raw data set.

Please use the below file if you would like to follow along through this quick intro to pivot tables. I will be writing more posts on Pivot tables in the near future that go into more detail on everything you can do.

File:
Excel_Pivot_Intro


Make a Pivot Table

Using the attached spreadsheet, let’s start off our pivot by making a new WorkSheet to put our pivot on. This can be done by clicking the “Insert Worksheet” button at the bottom left of our Excel Screen. You also have the option of right clicking on your sheet tab (Sheet1) and choosing Insert.
InsertWS

Now that we are on our new sheet…

  1. Click in Cell A1
  2. Go to the “Insert” Tab
  3. Click the Pivot Table Button

Excel is now going to prompt you for your Pivot Table Range and/or Data Source. With this prompt window still open…

  1. Click on Sheet1 (note that Sheet1! has now appeared in your data source)
  2. Select your data, A1:F11 if you are using the above data.
    1. You can Select this data by using your mouse, or by using one of the Selection methods we learned earlier such as Ctrl+Shift+End
  3. Click OKExcelInsertPivot

Your Pivot table has now been created but is completely blank at the moment.  Notice on the right side you now have a panel that displays the column names (field names) from  your data as well as:

  • Report Filter
  • Column Labels
  • Row Labels
  • Values

Placing a field in the report filter box will allow you to use that field as a filter for the entire pivot table.  I tend to place fields here that I don’t want to see the actual data for but want to give the users of my report the field to filter by.

By default, date and time fields are automatically placed in the Column Label section; however, if you are like me, most date and time fields I work with are in a text format and won’t go here unless you place them here manually.

By default, non-numeric fields are placed in the Row Label section. This section will display your fields in a similar format to how they appear in your raw data.

By default, numeric fields are placed in the Values section and will default to either a Sum or a Count of the field.


Pivot Table 1

Let’s summarize our data by showing the number of hours our employees have worked.

  1. Add the “Name” field to Row Labels by either checking the box next to Name or by dragging and dropping the field into the Row Labels section.
  2. Add the “Hours” field to Values by either checking the box next to Hours or by dragging and dropping the field into the Values section.

You should now see a quick summary of hours by employee.
HoursByEmployee.png


Pivot Table 2

It’s likely that Pivot Table 1 does not give you the data you are looking for.  All we are showing right now are the total hours our employees have billed.  Perhaps you would like the hours billed to be represented within a specific time frame.

Luckily, we also have a Date/Time field called “Month” in our Data.
Check the box next to Month to add it to our Pivot Table.
HoursByMonthEmployee

Now we can see our Employees Billed Hours by Month. You might notice that the months are out of order. That is because Field sorting will default to an Ascending order (F>J>M).

We can place January 2017 above February 20017 by:

  1. Click on a Jan2017 cell.
  2. Place your cursor on the edge of the cell until the cursor changes to a + sign with directional arrows
  3. Drag Upwards until your see a grey placement line above Feb2017
  4. Let go of your mouse button to place the selection

ExcelPivotItemOrder


Pivot Table 3

Let’s say that my data set is getting too long, making it hard to compare Greg’s, Lilly’s, and Holly’s hours to each other. I can Pivot this information by placing the Month field into the Column Label Section, allowing me to see the data as a trend over time.

This can be done by dragging the Month Field from the Row Label Section to the Column Label Section.

Pivot3 Ex


Stay Tuned as we continue to dive more into pivots in future posts.

Please like and share if you found this content helpful. Thanks!



Categories: Excel, Pivot Tables

Tags: , , , ,

Leave a Reply

Show Buttons
Hide Buttons
%d bloggers like this: