Why advanced charts tell deeper stories in Google Sheets - Challenge Your Understanding

Challenge - 5 Problems

🎯 Scenario

intermediate

Choosing the Right Chart Type for Sales Data

You have monthly sales data for three products over a year. You want to show how each product's sales trend changes over time and compare them clearly. Which chart type in Google Sheets best tells this story?

A Line chart with months on the X-axis and sales on the Y-axis for each product

B Pie chart showing total sales per product for the year

C Scatter plot with sales on both axes

D Bar chart showing total sales per month combining all products

💡 Hint

Think about showing trends over time for multiple items.

A line chart is best to show trends over time for multiple products. Pie charts show parts of a whole but not trends. Scatter plots are for relationships between two variables, not time series. Bar charts can show totals but don't show trends clearly.

📊 Formula Result

intermediate

Calculate Percentage Change for Chart Labels

You have sales data in column B for January and column C for February. You want to calculate the percentage change from January to February for each product in column D to use as labels in your chart. What formula in D2 correctly calculates this?

Google Sheets

=(C2-B2)/B2*100

A =(B2-C2)/C2*100

B =(C2-B2)/B2*100

C =(B2-C2)/B2*100

D =(C2-B2)/C2*100

💡 Hint

Percentage change is (new - old) divided by old, times 100.

The correct formula is (February - January) divided by January, times 100. Option B matches this exactly. Other options reverse the order or divide by the wrong value.

❓ data_analysis

advanced

Identifying Outliers in Sales Data Using Conditional Formatting

You want to highlight sales values in column B that are more than 20% higher than the average sales in that column. Which custom formula for conditional formatting will correctly highlight these outliers?

A =B2 > AVERAGE(B:B) * 1.2

B =B2 > AVERAGE(B$2:B$100) * 1.2

C =B2 > AVERAGE($B$2:$B$100) * 1.2

D =B2 > AVERAGE(B2:B100) * 1.2

💡 Hint

Use absolute references to fix the range for all rows.

Option C uses absolute references to fix the range B2:B100 so the average is consistent for all rows. Options A, C, and D use entire column or relative ranges that change per row, causing incorrect highlighting. Option C has mixed references that may cause errors.

❓ Function Choice

advanced

Best Function to Aggregate Sales by Region

You have a table with sales data including a 'Region' column and a 'Sales' column. You want to create a summary table showing total sales per region. Which Google Sheets function is best suited for this?

A SUMIF(range, criteria, sum_range)

B VLOOKUP(search_key, range, index, [is_sorted])

C COUNTIF(range, criteria)

D ARRAYFORMULA(range)

💡 Hint

Think about summing values based on matching criteria.

SUMIF sums values in sum_range where criteria in range match. VLOOKUP finds a single value, COUNTIF counts matches, ARRAYFORMULA applies formulas to ranges but does not aggregate by criteria.

🧠 Conceptual

expert

Why Use Combo Charts for Complex Data Stories?

You have monthly revenue and profit margin data. Revenue is large numbers, profit margin is a percentage. You want to show both clearly on one chart to tell a deeper story. Why is a combo chart the best choice?

A It converts all data to percentages for uniform display.

B It combines all data into one axis, making it easier to read.

C It automatically highlights the highest revenue month without extra setup.

D It allows plotting revenue as bars and profit margin as a line with separate Y-axes for clear comparison.

💡 Hint

Think about showing different types of data with different scales on one chart.

Combo charts let you use two chart types and two Y-axes, so large revenue numbers and smaller percentage margins can be shown clearly together. Other options do not address scale differences or clarity.