PivotTable formatting in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a PivotTable in Google Sheets?

A PivotTable is a tool that helps you quickly summarize and analyze large sets of data by organizing it into a simple table format. It lets you group, count, and total data without changing the original data.

beginner

How do you change the number format in a PivotTable?

Click on a value in the PivotTable, then go to the 'Pivot table editor' on the right. Under 'Values', click the dropdown next to the field, and select 'Number format' to pick how numbers appear (like currency, percentage, or decimal places).

intermediate

What does 'Show as' option do in PivotTable formatting?

The 'Show as' option changes how the numbers are displayed, like showing values as a percentage of the row, column, or grand total. This helps you understand proportions easily.

intermediate

How can you apply conditional formatting to a PivotTable?

Select the PivotTable cells you want to format. Then go to Format > Conditional formatting. Set rules like 'greater than' or 'text contains' to highlight cells with colors based on their values.

beginner

Why is refreshing a PivotTable important after changing source data?

Refreshing updates the PivotTable to show the latest data from your source. Without refreshing, the PivotTable might show old or incorrect summaries.

Where do you find the option to change number formats in a Google Sheets PivotTable?

A In the Pivot table editor under 'Values'

B In the main menu under 'File'

C By right-clicking the sheet tab

D In the toolbar under 'Insert'

Number formats are changed in the Pivot table editor by selecting the value field and adjusting its settings.

What does the 'Show as' feature in PivotTables help you do?

A Sort the data alphabetically

B Change the font size of the table

C Display values as percentages of row, column, or total

D Add new rows to the source data

'Show as' changes how values are displayed, such as showing percentages relative to rows, columns, or totals.

How do you apply conditional formatting to a PivotTable?

A Select cells and use Format > Conditional formatting

B Right-click and choose 'Format cells'

C Use the 'Data' menu to add formatting

D Conditional formatting is not available for PivotTables

Conditional formatting can be applied by selecting cells and using the Format menu option.

What happens if you don't refresh a PivotTable after changing the source data?

A The PivotTable updates automatically

B The PivotTable shows old data and summaries

C The source data is deleted

D The PivotTable deletes itself

Without refreshing, the PivotTable keeps showing old data and does not reflect changes.

Which of these is NOT a way to format a PivotTable?

A Changing number formats

B Applying conditional formatting

C Using 'Show as' to display percentages

D Changing the sheet tab color

Changing the sheet tab color does not affect PivotTable formatting.

Explain how to change the number format of values in a Google Sheets PivotTable.

Look for the settings next to the value fields in the editor.

Describe how conditional formatting can help make a PivotTable easier to understand.

Think about how colors can guide your eyes to key data.

Practice

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

easy

A. To change the source data of the PivotTable

B. To delete unnecessary data from the PivotTable

C. To add new rows and columns automatically

D. To make the data summary easier to read and understand

Solution

1. Step 1: Understand PivotTable formatting

   Formatting changes how data looks, like colors and fonts, to improve clarity.

2. Step 2: Identify the main goal of formatting

   The goal is to make the summary easier to read and understand, not to change data itself.

3. Final Answer:

   To make the data summary easier to read and understand -> Option D

4. Quick Check:

   PivotTable formatting = clearer data [OK]

Hint: Formatting improves readability, not data content [OK]

Common Mistakes:

• Thinking formatting changes the data itself
• Confusing formatting with data editing
• Assuming formatting adds or removes data

2. Which of the following is the correct way to format numbers in a PivotTable in Google Sheets?

easy

A. Use the formula bar to enter formatting codes

B. Double-click the header and type the format code directly

C. Right-click a value cell, choose 'Number format', then select the desired format

D. Edit the source data to change number formats

Solution

1. Step 1: Locate formatting options in PivotTable

   Right-clicking a value cell in the PivotTable opens formatting options.

2. Step 2: Apply number format correctly

   Choosing 'Number format' from the menu lets you pick formats like currency or percentage.

3. Final Answer:

   Right-click a value cell, choose 'Number format', then select the desired format -> Option C

4. Quick Check:

   Right-click > Number format = correct method [OK]

Hint: Right-click values to format numbers easily [OK]

Common Mistakes:

• Trying to format by editing headers
• Changing source data instead of PivotTable
• Using formula bar for formatting

3. You have a PivotTable showing sales totals by region. You apply a currency format to the sales values. What will be the result in the PivotTable?

medium

A. The PivotTable will show an error message

B. Sales numbers will show with a currency symbol and two decimal places

C. Sales numbers will be replaced by text labels

D. The source data will change to currency format

Solution

1. Step 1: Understand currency formatting effect

   Applying currency format adds a currency symbol and sets decimals for numbers.

2. Step 2: Confirm formatting applies only to PivotTable display

   The source data stays unchanged; only the PivotTable display updates.

3. Final Answer:

   Sales numbers will show with a currency symbol and two decimal places -> Option B

4. Quick Check:

   Currency format = symbol + decimals [OK]

Hint: Currency format changes display, not source data [OK]

Common Mistakes:

• Thinking source data changes
• Expecting text labels instead of numbers
• Assuming errors appear after formatting

4. You tried to apply a custom date format to a PivotTable but the dates still show as numbers. What is the likely cause?

medium

A. The source data is not recognized as dates

B. PivotTables cannot format dates

C. You must refresh the PivotTable after formatting

D. Custom formats only work on text fields

Solution

1. Step 1: Check source data type

   If source data is stored as numbers, formatting as dates won't work properly.

2. Step 2: Understand PivotTable formatting limits

   PivotTables can format dates, but only if source data is recognized as dates.

3. Final Answer:

   The source data is not recognized as dates -> Option A

4. Quick Check:

   Source data type affects formatting success [OK]

Hint: Ensure source data is date type before formatting [OK]

Common Mistakes:

• Assuming PivotTables can't format dates
• Forgetting to check source data format
• Thinking refresh fixes formatting issues

5. You want to highlight the top 3 sales regions in your PivotTable with a green background. Which steps correctly achieve this in Google Sheets?

hard

A. Select the sales values, go to Format > Conditional formatting, set rule 'Top 3', choose green fill

B. Manually color the top 3 cells green without any rules

C. Change the source data colors and refresh the PivotTable

D. Use a formula in the PivotTable to add green text

Solution

1. Step 1: Select the sales values in the PivotTable

   Highlight the cells showing sales totals to apply formatting.

2. Step 2: Apply conditional formatting for top 3 values

   Use Format > Conditional formatting, choose 'Top 3' rule, and set green fill color.

3. Final Answer:

   Select the sales values, go to Format > Conditional formatting, set rule 'Top 3', choose green fill -> Option A

4. Quick Check:

   Conditional formatting highlights top values [OK]

Hint: Use conditional formatting for dynamic highlights [OK]

Common Mistakes:

• Manually coloring cells (not dynamic)
• Changing source data colors instead of PivotTable
• Trying to add color via formulas in PivotTable