PivotTable formatting in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a pivot table from data in range A1:C10.

Google Sheets

=QUERY([1], "select Col1, sum(Col3) group by Col1", 1)

Drag options to blanks, or click blank then click option'

AA1:B10

BB1:C10

CA1:C10

DA2:C11

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a range that excludes headers.

Selecting a range missing the column with values to sum.

The data range A1:C10 includes all columns needed for the pivot table.

2fill in blank

medium

Complete the code to format the pivot table values as currency.

Google Sheets

=ARRAYFORMULA(TEXT(B2:B[1], "[2]"))

Drag options to blanks, or click blank then click option'

A10

B20

C"$#,##0.00"

D"#,##0"

Attempts:

3 left

💡 Hint

Common Mistakes

Using a range too short to cover all data.

Using a number format without currency symbol.

We format values in rows 2 to 20 as currency with two decimals using "$#,##0.00".

3fill in blank

hard

Fix the error in the formula that sums values in column C for the pivot table.

Google Sheets

=SUM([1])

Drag options to blanks, or click blank then click option'

AC2:C10

BC:C2

CC10:C2

DC2:C

Attempts:

3 left

💡 Hint

Common Mistakes

Using reversed ranges like C10:C2.

Using incomplete ranges like C:C2.

The range C2:C10 correctly sums values from row 2 to 10 in column C.

4fill in blank

hard

Fill both blanks to create a pivot table that filters rows where sales are greater than 1000.

Google Sheets

=QUERY(A1:C20, "select A, sum(C) where C [1] [2] group by A", 1)

Drag options to blanks, or click blank then click option'

A>

B<

C1000

D500

Attempts:

3 left

💡 Hint

Common Mistakes

Using less than symbol instead of greater than.

Using wrong numeric filter values.

The query filters rows where sales in column C are greater than 1000.

5fill in blank

hard

Fill all three blanks to create a pivot table that groups by product, sums sales, and filters for sales above 500.

Google Sheets

=QUERY(A1:C30, "select [1], sum([2]) where [2] [3] 500 group by [1]", 1)

Drag options to blanks, or click blank then click option'

AA

BB

CC

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up column letters for grouping and summing.

Using wrong column for filtering.

The pivot table groups by product in column A, sums sales in column C, and filters sales greater than 500.

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