Google Sheets Pivot Tables for Analysis Questions and Answers 1 — 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?
- Use a Slicer to add the new data.
- The pivot table will automatically detect and include the new rows.
- Delete the pivot table and create a new one.
- Edit the pivot table and update the source data range to 'Sheet1'!A1:D600. (Correct answer)
Correct 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.
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?
- A Filter
- The 'Group' function
- A Calculated Field (Correct answer)
- A Slicer
Correct 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.
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?
- Right-click on any date in the pivot table's row/column labels, select 'Create pivot date group', and then choose 'Year-Quarter'. (Correct answer)
- Add a new helper column in the source data using a formula to determine the quarter for each date.
- Manually select the dates for each quarter within the pivot table and create a group.
- Use the 'Filter by condition' option to create four separate filters for each quarter's date range.
Correct 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.
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?
- Summarize by: AVERAGE
- Show as: % of grand total (Correct answer)
- Summarize by: COUNT
- Show as: % of column
Correct 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.
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?
- Calculated Fields
- Filter Views
- Data Validation rules
- Slicers (Correct answer)
Correct 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.
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?
- To filter the entire table based on the values in that field.
- To create the column headers for the pivot table.
- To perform a mathematical summary (e.g., SUM, AVERAGE, COUNT) on that field's numeric data. (Correct answer)
- To create the row labels for the pivot table.
Correct 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.
Google Sheets Pivot Tables for Analysis Questions and Answers 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?