Google Sheets Pivot Tables for Analysis Flashcards

An analyst has a pivot table summarizing data from a source range of 'Sheet1'!A1:D500. If they add 100 new rows of data to the bottom of the source sheet (in rows 501-600), what must they do to include this new data in the pivot table's analysis?

Answer: Edit the pivot table and update the source data range to 'Sheet1'!A1:D600.

A pivot table is built from a specific source data range defined during its creation. If new data is added outside this range, the user must manually edit the pivot table's settings in the editor and expand the source data range to include the new rows.

A financial analyst is using a pivot table to summarize sales data which includes 'Revenue' and 'Units Sold' columns. To add a new column directly within the pivot table that calculates the 'Average Price per Unit' ('Revenue' / 'Units Sold'), which feature of the pivot table editor should be used?

Answer: A Calculated Field

Calculated Fields are designed to create new fields that perform custom calculations using the data from other fields within the pivot table. In this case, a formula like ='Revenue' / 'Units Sold' would be entered in a new calculated field.

A marketing analyst has a dataset with daily website traffic for an entire year. In a pivot table, they want to aggregate the traffic totals by calendar quarter (Q1, Q2, Q3, Q4). What is the most direct way to achieve this within the pivot table itself?

Answer: Right-click on any date in the pivot table's row/column labels, select 'Create pivot date group', and then choose 'Year-Quarter'.

Google Sheets pivot tables have a powerful built-in feature for grouping dates. By right-clicking a date field within the pivot table, users can automatically group the data by various timeframes, including Year-Quarter, without needing to modify the source data.

In a pivot table showing total sales by product category, an analyst wants to display each category's sales not as a dollar amount, but as a percentage of the grand total sales. Which option in the 'Values' section of the pivot table editor should they change?

Answer: Show as: % of grand total

The 'Show as' dropdown menu in the 'Values' section allows you to change how the summarized data is displayed. Selecting '% of grand total' will calculate each value as a percentage of the overall total for all values in that field.

A project manager is creating an interactive dashboard with a pivot table. They want to provide colleagues with simple, button-like controls to easily filter the pivot table by 'Region' and 'Project Status' without having to open the filter menu. Which of the following should they add?

Answer: Slicers

Slicers are designed to be interactive, button-based filters that can be connected to pivot tables and charts. They provide a user-friendly way to filter data without needing to interact directly with the pivot table's filter options.

A user is setting up a pivot table to analyze sales performance. What is the primary purpose of dragging a data field, such as 'Sales Amount', into the 'Values' area of the pivot table editor?

Answer: To perform a mathematical summary (e.g., SUM, AVERAGE, COUNT) on that field's numeric data.

The 'Values' area of a pivot table is specifically designed to aggregate and summarize the numerical data from a chosen field. By default, it will typically SUM number values or COUNT text values, providing the core calculations for the analysis.