Embedded Files
Adding Columns
Adding Columns
Let's look at two more features of pivot tables that will allow you to do more complex investigations of your data. We learned that a Row in a pivot table specifies an aggregation or grouping of items for which you want to compute a value. A Column in a pivot table is just another grouping, but it displays the values across the top of the table.
Do the following:
➤ Using the Pivot table editor, select the Add button for Columns, then select gender from the list of options.
This will group your data by gender. The resulting table shows the average rating and count for each movie, but it is now also broken down by gender. The pivot table also preserves the "Grand Totals" which is what the data would look like if no columns were specified.
Filtering Pivot Tables
Filtering Pivot Tables
Applying a filter to a pivot table does the same thing as it does in a regular spreadsheet - it allows you to filter out values from the raw data.
The video provided shows first filtering out 14-year-olds from the calculations, and then filtering out some of the movies. You don't have to do this, but in some instances it can be a very useful tool.
Do the following:
➤ Experiment with adding a Filter for age, date, movies, etc.
Manipulating The Pivot Table
Manipulating The Pivot Table
If you want to maninpulate the data further, to sort or filter, you shouldn't do it in the live, active pivot table. Instead you should copy the table, and paste the values into a new spreadsheet. Note: "Paste Values" is not the same as a normal "Paste".
Do the following:
➤ Select the entire range of the pivot table and copy it, then create a new tab in the spreadsheet.
➤ From the Edit menu, select Paste special >> Values only
➤ From the Edit menu, select Paste special >> Values only
This will paste just the values from the Summary table into a new spreadsheet tab for further manipulation.
This will paste just the values from the Summary table into a new spreadsheet tab for further manipulation.
Why Paste values only instead of just Paste?
Why Paste values only instead of just Paste?
If you copy a pivot table and do a normal paste it will paste another copy of the active, live, responsive pivot table into a new tab. We don't want the active table; we just want the values it produced.
You probably want to add/change column headings to display on the table, especially to use it for charting.
Do the following:
➤ Manually edit the table so that it matches the example provided, including:
Change the column heading and make them bold.
Limit the decimal places to 2 and adjust the column widths accordingly.
From cleaning that up, plus some filtering we can make a chart of movies where the differences between male and female ratings are significant.
You can learn more about further analyzing your data to create charts in the Visualizing Data Tutorial.
Visualizing Summary Tables
Visualizing Summary Tables
A summary table can be a good first step toward a great visualization. You often want to summarize data first, then chart it, so you can see larger connections or patterns. Summary tables also don't have to be small! You might make a summary table that is still too big or full of numbers to see any trends in the data.
However, if you chart the results, you will more easily be able to see trends and patterns emerge from the data visualization.
Page updated
Google Sites
Report abuse