PivotTable formatting in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

PivotTable Grand Total Display

You created a PivotTable in Google Sheets summarizing sales data by region and product. You want to hide the Grand Total row at the bottom but keep the Grand Total column on the right visible.

Which setting should you adjust in the PivotTable editor?

A Uncheck 'Show grand totals' under Rows only

B Uncheck 'Show grand totals' under Columns only

C Uncheck 'Show subtotals' under Rows

D Uncheck 'Show grand totals' under both Rows and Columns

💡 Hint

Grand totals for rows and columns can be controlled separately in the PivotTable editor.

To hide the Grand Total row but keep the Grand Total column, you uncheck 'Show grand totals' under Rows only. This hides the bottom total row but leaves the right total column visible.

❓ Function Choice

intermediate

Choosing the Right Format for PivotTable Values

You have a PivotTable showing monthly sales amounts. The values are large numbers like 1234567.89. You want to display these numbers with commas as thousand separators and two decimal places.

Which Google Sheets feature or function should you use to format these values inside the PivotTable?

A Apply conditional formatting with a custom formula to add commas

B Wrap the PivotTable formula with the TEXT function like TEXT(value, "#,##0.00")

C Use the 'Number format' option in the PivotTable editor and select 'Number' with 2 decimal places

D Use the ROUND function on the source data before creating the PivotTable

💡 Hint

PivotTables have built-in formatting options for values.

The best way is to use the 'Number format' option in the PivotTable editor and choose 'Number' with 2 decimal places. This applies formatting directly to the PivotTable values without changing source data.

❓ data_analysis

advanced

Analyzing PivotTable Filter Effects

You have a PivotTable summarizing sales by product category and month. You apply a filter to show only categories with total sales greater than $10,000.

After applying this filter, what happens to the Grand Total row and column in the PivotTable?

A Grand Total row and column show totals for all categories, ignoring the filter

B Grand Total row and column update to reflect only the filtered categories

C Grand Total row disappears but the column remains

D Grand Total column disappears but the row remains

💡 Hint

Filters in PivotTables affect what data is included in all summaries.

When you filter categories, the Grand Total row and column recalculate to include only the filtered data. So totals reflect only categories with sales over $10,000.

🎯 Scenario

advanced

Customizing PivotTable Row Labels with Indentation

You want to make your PivotTable easier to read by indenting subcategories under main categories in the row labels. Google Sheets does not provide a direct indent option in PivotTables.

Which approach will achieve this indentation effect?

A Add spaces or special characters before subcategory names in the source data

B Use the 'Group' feature in the PivotTable editor to create indentation

C Apply conditional formatting to indent rows based on category level

D Change the font size of subcategories to make them appear indented

💡 Hint

Think about modifying the source data text to simulate indentation.

Google Sheets PivotTables do not support indentation directly. Adding spaces or special characters (like dashes) before subcategory names in the source data creates a visual indent effect in the row labels.

🧠 Conceptual

expert

Understanding PivotTable Refresh Behavior with Formatting

You apply custom number formatting to values in a Google Sheets PivotTable. Later, you refresh the PivotTable after updating source data.

What happens to your custom number formatting after the refresh?

A Custom number formatting is preserved after refresh

B Custom number formatting is lost and resets to default

C Formatting is preserved only if applied using conditional formatting rules

D Formatting is preserved only if applied via the PivotTable editor's Number format option

💡 Hint

Consider how formatting applied inside the PivotTable editor differs from manual cell formatting.

Number formatting applied through the PivotTable editor's Number format option stays after refresh. Manual formatting directly on cells is lost when the PivotTable refreshes.