Google Sheets Pivot Tables for Analysis Questions and Answers

Question 1

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?

Accepted Answer

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

Answer

Use a Slicer to add the new data.

Answer

The pivot table will automatically detect and include the new rows.

Answer

Delete the pivot table and create a new one.

Question 2

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?

Accepted Answer

A Calculated Field

Answer

A Filter

Answer

The 'Group' function

Answer

A Slicer

Question 3

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?

Accepted Answer

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

Answer

Add a new helper column in the source data using a formula to determine the quarter for each date.

Answer

Manually select the dates for each quarter within the pivot table and create a group.

Answer

Use the 'Filter by condition' option to create four separate filters for each quarter's date range.

Question 4

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?

Accepted Answer

Show as: % of grand total

Answer

Summarize by: AVERAGE

Answer

Summarize by: COUNT

Answer

Show as: % of column

Question 5

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?

Accepted Answer

Slicers

Answer

Calculated Fields

Answer

Filter Views

Answer

Data Validation rules

Question 6

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?

Accepted Answer

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

Answer

To filter the entire table based on the values in that field.

Answer

To create the column headers for the pivot table.

Answer

To create the row labels for the pivot table.

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?

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?