Creating a PivotTable in Google Sheets - Practice Exercises

Challenge - 5 Problems

📊 Formula Result

intermediate

PivotTable Row Labels and Values

You have a sales data table with columns: Product, Region, and Sales. You create a PivotTable with Product as rows and sum of Sales as values. What will the PivotTable show?

A A list of regions with the total sales for each region summed across all products.

B A list of products with the average sales per region for each product.

C A list of regions with the average sales per product for each region.

D A list of products with the total sales for each product summed across all regions.

💡 Hint

Think about what you put in the rows and what you put in the values area of the PivotTable.

When you put Product in rows and sum of Sales in values, the PivotTable groups sales by product and sums all sales for each product across all regions.

❓ Function Choice

intermediate

Choosing the Right Function for PivotTable Values

You want to create a PivotTable that shows the average sales per region. Which aggregation function should you choose for the values?

A COUNT

B AVERAGE

C SUM

D MAX

💡 Hint

Think about which function calculates the mean value.

The AVERAGE function calculates the mean sales per region, which is what you want.

🎯 Scenario

advanced

Filtering Data in a PivotTable

You have a PivotTable showing total sales by product and region. You want to see only sales for the "East" region. What should you do?

A Add a filter to the PivotTable and select only "East" in the Region filter.

B Delete all rows except those with "East" in the source data.

C Change the row labels to Region and remove Product.

D Sort the PivotTable by sales in descending order.

💡 Hint

PivotTables have built-in filters to show specific data.

Adding a filter on the Region field and selecting "East" will show only sales data for that region without changing the source data.

❓ data_analysis

advanced

Interpreting PivotTable Grand Totals

You create a PivotTable with Region as rows and Product as columns, showing sum of sales. The Grand Total row shows 5000. What does this number represent?

A The average sales per product.

B The total sales for the last region only.

C The total sales for all products across all regions.

D The total number of products sold in the last region.

💡 Hint

Grand Totals sum all the data in the PivotTable.

The Grand Total sums all sales values in the PivotTable, so it represents total sales for all products and regions combined.

🧠 Conceptual

expert

Understanding Refreshing PivotTables

You update the source data of a PivotTable by adding new sales records. However, the PivotTable does not show the new data. What is the correct action to see the updated data in the PivotTable?

A Manually refresh the PivotTable to update it with the new data.

B Close and reopen the spreadsheet; the PivotTable updates automatically.

C Change the PivotTable layout; it will refresh automatically.

D Delete and recreate the PivotTable to include new data.

💡 Hint

PivotTables do not update automatically when source data changes.

PivotTables require a manual refresh to include changes made to the source data.

Practice

1. What is the main purpose of a PivotTable in Google Sheets?

easy

A. To enter data manually into cells

B. To create charts and graphs automatically

C. To sort data alphabetically only

D. To quickly summarize and analyze large sets of data

Solution

1. Step 1: Understand what a PivotTable does

   A PivotTable helps organize and summarize data quickly without changing the original data.

2. Step 2: Compare options to the purpose

   Only To quickly summarize and analyze large sets of data describes summarizing and analyzing data, which is the main use of PivotTables.

3. Final Answer:

   To quickly summarize and analyze large sets of data -> Option D

4. Quick Check:

   PivotTable = Data summary [OK]

Hint: PivotTables summarize data fast, not just sort or enter [OK]

Common Mistakes:

• Confusing PivotTables with charts
• Thinking PivotTables only sort data
• Believing PivotTables are for manual data entry

2. Which menu path correctly starts the creation of a PivotTable in Google Sheets?

easy

A. Data > Pivot table

B. Insert > Chart

C. Format > Conditional formatting

D. Tools > Script editor

Solution

1. Step 1: Recall the menu for PivotTables

   PivotTables are created from the Data menu, under 'Pivot table'.

2. Step 2: Eliminate unrelated menu options

   The other options relate to scripts, charts, and conditional formatting, not PivotTables.

3. Final Answer:

   Data > Pivot table -> Option A

4. Quick Check:

   PivotTable menu = Data > Pivot table [OK]

Hint: PivotTables start under Data menu, not Insert or Format [OK]

Common Mistakes:

• Choosing Insert > Chart instead of Data > Pivot table
• Looking under Format or Tools menus
• Confusing PivotTable creation with chart creation

3. Given this data in A1:B5:

Product | Sales
Apple   | 10
Banana  | 15
Apple   | 5
Banana  | 10
Cherry  | 20

What will be the total sales for 'Apple' in a PivotTable with 'Product' as rows and 'Sales' summed as values?

medium

A. 20

B. 10

C. 15

D. 25

Solution

1. Step 1: Identify sales for 'Apple'

   Apple has two sales entries: 10 and 5.

2. Step 2: Sum the sales values

   10 + 5 = 15 total sales for Apple.

3. Final Answer:

   15 -> Option C

4. Quick Check:

   Sum(10,5) = 15 [OK]

Hint: Sum all sales for the product in PivotTable values [OK]

Common Mistakes:

• Choosing only the first sales number
• Adding wrong product sales
• Confusing count with sum

4. You created a PivotTable but it shows no data. Which fix will most likely solve this?

medium

A. Check if the data range selected includes all your data

B. Change the sheet's background color

C. Delete the PivotTable and start over without selecting data

D. Add more columns to the original data

Solution

1. Step 1: Verify the data range used in the PivotTable

   If the range is too small or wrong, the PivotTable will show no data.

2. Step 2: Adjust the data range to include all relevant rows and columns

   Expanding the range fixes missing data issues.

3. Final Answer:

   Check if the data range selected includes all your data -> Option A

4. Quick Check:

   Data range correct = Data visible [OK]

Hint: Always confirm your data range covers all data [OK]

Common Mistakes:

• Ignoring data range selection
• Changing unrelated sheet formatting
• Deleting PivotTable without fixing range

5. You want to analyze monthly sales by product and region using a PivotTable. Which layout should you choose to see products as rows, regions as columns, and total sales as values?

hard

A. Rows: Sales, Columns: Product, Values: Sum of Region

B. Rows: Product, Columns: Region, Values: Sum of Sales

C. Rows: Region, Columns: Product, Values: Count of Sales

D. Rows: Product, Columns: Sales, Values: Region

Solution

1. Step 1: Assign 'Product' to rows to list each product vertically

   This organizes products down the side.

2. Step 2: Assign 'Region' to columns to separate data by region horizontally

   This creates columns for each region.

3. Step 3: Set 'Values' to sum sales to get total sales per product and region

   Summing sales shows total amounts.

4. Final Answer:

   Rows: Product, Columns: Region, Values: Sum of Sales -> Option B

5. Quick Check:

   Correct layout = Product rows + Region columns + Sales sum [OK]

Hint: Rows = products, Columns = regions, Values = sum sales [OK]

Common Mistakes:

• Swapping rows and columns incorrectly
• Using count instead of sum for sales
• Placing sales or region in wrong areas