UsingPivot Tables in Excel

Pivot tables give us another set of tools for performing computations and visualizations of our data. A pivot table is a summary of your data – a way to group data together in different ways to more easily draw conclusions.

The “pivot” part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table in order to view it from a different perspective.

Pivot tables are particularly useful if you have long rows or columns that hold values that you want to easily compare to one another. Note that you’re not adding/subtracting/otherwise changing your data when you make a pivot table. It is just a way of reorganizing your data from long columns and rows, to gain new insights from it.

We will once again be working with the sensor data that we explored last week, and again we’ll be watching a video to learn how to use these Excel features. Rather than working in the same sheet as last week, however, let’s start again from scratch:

You’ll see that this sheet has the same data, although there are some minor differences in the formatting. In particular, I’ve applied time formatting to the “Time” column, which will cause it to behave differently than it did before (in some cool ways).

There is also a worksheet for today’s activity, which will be the deliverable that you will submit to Canvas:

1. Start by adding a Reconcile column

2. Begin the Video Tutorial

Begin by viewing the video below. Along the way, there are timestamps where you will want to pause, as we did last week (They should be easy to spot, as they usually correspond with a “Tip” being shown onscreen).

PAUSE AT 4:02 Tip #1 – Data should be listed vertically with column tile.

Visually verify that the data in our data set meet this criterion.

3. PAUSE AT 4:22 Tip #2 – Make sure there are no blank rows in your data.

Remember “Cleaning” your data? These next couple of steps fall into that category. This is a big dataset, so checking visually will be time consuming and prone to error. So, instead, you can use this feature to locate any blank cells:

• Edit —> Find —> Go To —> Special —> Blanks 
• (Notice that there are lots of other potentially helpful features here, as well). 

4. PAUSE AT 4:45 Tip #3 – Avoid having extra “data” in your spreadsheet.

Make sure there is no extra data to the side or underneath. This shouldn’t be a problem in this new spreadsheet, but if you’d been working in the sheet from last week, you would have needed to remove your tables and graphs.

5. PAUSE AT 5:30 Tip #4 – Format your data as a table.

• Select the range that contains your data
• Home –> Format as Table –> Check “My table has headers” –> click “OK” –> select a format that you like
• You can also do this from your computer’s main menu bar: Insert –> Table

Note: if you get a “Large Operation” warning, cancel and rather than selecting the entire sheet, just select the range that contains your data. 

6. PAUSE at 8:00 and create your pivot table 

• Home —> Format as Table
• Insert —> Pivot Table (select whichever table is listed)
• Choose New Worksheet

Now, watch through to the end of the video. Then, we will start to do some real manipulation of our own dataset.

7. First, let’s look at Temperature by Date

• Drag Date —> Rows
• Temperature —> Values

Look at the data that is being shown here. Is it meaningful in any way?

How could we make this more useful? 

• Click the “i” beside Temperature, and switch to Summarize by Average
• You can also click the “Number” button at the bottom to change the number of decimal places. (1 decimal place would be good).
• Now also add the Solar Radiation Average to the table.
• (This view of the data should look familiar from last week).
• Now, for fun, return to the PivotTable Fields menu, and drag “Values” down into “Rows” (with “Date”).

How does this change the data that is being displayed?

Create a graph that looks similar to the one you created for these data last week. (Hint: this works the same way as it did in the regular spreadsheet).

Take a screenshot of this graph and insert into the worksheet.

8. Organizing data by Time

• Clear all items from the pivot table.
• Drag “Time” into the “Rows” field.

What do you notice about the way the row labels are formatted?

How is this different than the way the raw data is formatted?

Is there an “Hours” field in your raw data? Can you explain why one appears in our pivot table?

• Drag “Temperature” into “Values” and set to display minimum temperature
• Drag “Temperature into “Values” a second time, and set to display maximum temperature

In your own words, explain what these data represent. What, specifically, do the temperature values mean?

• Now, drag “Date” into “Filters.”
• Open the “Date” menu, and deselect all.
• Now just select “5/24”

What is the minimum temperature reported at 1:00 a.m.? What is the maximum temperature reported at 1:00 p.m.?

• Now just select “5/25”

What is the minimum temperature reported at 1:00 a.m.? What is the maximum temperature reported at 1:00 p.m.?

Does this help you better understand the data being displayed? ‘

9. Dealing with Outliers

• Select all dates in the “Date” menu, and create a line chart with this data table
• What doesn’t look right here? Identify the date and time of this anomaly by selecting and deselecting dates in the “Date” menu.

On what date and time does this temperature anomaly occur?

• Go back to the raw data sheet, and change that value to match the nearest previous value.
• Return to the pivot table

How did that change the graph?

As you’ve seen, the way we have our pivot table set up, changes to the raw data are not automatically reflected in the pivot table. In order to update the pivot table:

• Data –> Refresh (or Refresh All)
• Now, create (or return to) a line graph that shows the min and max temperatures for every date.

Take a screenshot of this graph, and insert into your Word document.

10. On your own

Recall the data you summarized when you compared “any two variables (your choice) in the dataset.”

• Use the pivot table to recreate this summary data

Take a screen shot of this pivot table summary and insert into the Word document.

• Now, graph this table.

Take a screenshot of the new graph. (It should look similar to the one you completed in last week’s activity).

When you have completed all activities, submit your completed Word document to Canvas.