Column and bar charts in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate total sales for chart data

You have monthly sales data in cells A2:A7 and sales amounts in B2:B7. Which formula correctly calculates the total sales to be displayed in a chart summary?

Google Sheets

A2:A7 contains: Jan, Feb, Mar, Apr, May, Jun
B2:B7 contains: 100, 150, 120, 130, 160, 140

A =COUNT(B2:B7)

B =SUM(A2:A7)

C =SUM(B2:B7)

D =AVERAGE(A2:A7)

💡 Hint

Sum the sales amounts, not the month names.

The sales amounts are in B2:B7, so summing that range gives the total sales. Summing A2:A7 or averaging it doesn't make sense because those are text labels.

❓ Function Choice

intermediate

Choose the function to count categories

You want to create a bar chart showing how many times each product appears in a list in column A. Which function helps count the number of each product for the chart?

Google Sheets

Column A: Product names like Apple, Banana, Apple, Orange, Banana, Apple

A SUM(A:A)

B COUNTIF(A:A, "Apple")

C AVERAGE(A:A)

D MAX(A:A)

💡 Hint

You want to count how many times a specific product appears.

COUNTIF counts how many cells in a range match a condition. Here, it counts how many times "Apple" appears in column A.

🎯 Scenario

advanced

Fixing a bar chart with wrong data range

You created a bar chart but it shows empty bars. Your data is in A1:B6, with labels in A2:A6 and values in B2:B6. You accidentally selected A1:B6 as the data range. What is the likely cause?

A Including the header row A1:B1 causes the chart to misinterpret labels as data

B Selecting A1:B6 is correct; the problem is chart type

C The values in B2:B6 are text, not numbers

D The data range should include only column A

💡 Hint

Headers should be recognized as labels, not data points.

Including the header row in the data range can cause the chart to treat headers as data, resulting in empty or incorrect bars.

❓ data_analysis

advanced

Interpreting stacked column chart data

A stacked column chart shows sales by region for three products. If the total height of the column for Region A is 300, and the segment for Product 1 is 120, what does the 120 represent?

A Sales amount of Product 1 in Region A

B Total sales of all products in Region A

C Sales amount of Product 1 across all regions

D Average sales of Product 1 in all regions

💡 Hint

Stacked columns show parts of a total.

Each segment in a stacked column represents the value of that part (product) within the total for that category (region).

🧠 Conceptual

expert

Choosing chart type for comparing categories

You want to compare sales numbers of 5 products side by side for 4 different months. Which chart type best shows this comparison clearly?

A Pie chart

B Stacked bar chart

C Line chart

D Clustered column chart

💡 Hint

You want to see each product's sales per month separately.

A clustered column chart shows groups of columns side by side for each category, making it easy to compare multiple products across months.

Practice

1. What is the main difference between a column chart and a bar chart in Google Sheets?

easy

A. Column charts display vertical bars, bar charts display horizontal bars.

B. Column charts show percentages, bar charts show totals.

C. Column charts can only show one data series, bar charts can show multiple.

D. Column charts are for dates, bar charts are for text labels.

Solution

1. Step 1: Understand chart orientation

   Column charts use vertical bars to represent data values, making it easy to compare heights.

2. Step 2: Understand bar chart orientation

   Bar charts use horizontal bars, which are useful when category names are long or many.

3. Final Answer:

   Column charts display vertical bars, bar charts display horizontal bars. -> Option A

4. Quick Check:

   Orientation difference = vertical vs horizontal [OK]

Hint: Remember: columns go up/down, bars go left/right [OK]

Common Mistakes:

• Confusing orientation of bars
• Thinking column charts show percentages only
• Assuming bar charts can't show multiple series

2. Which menu path do you use to insert a column or bar chart in Google Sheets?

easy

A. Data -> Chart

B. Tools -> Chart

C. Format -> Chart

D. Insert -> Chart

Solution

1. Step 1: Locate the Insert menu

   In Google Sheets, charts are added via the Insert menu, which contains chart options.

2. Step 2: Select Chart option

   Choosing Insert -> Chart opens the chart editor to create column or bar charts.

3. Final Answer:

   Insert -> Chart -> Option D

4. Quick Check:

   Insert menu contains chart tools [OK]

Hint: Charts are always under Insert menu [OK]

Common Mistakes:

• Looking under Data or Format menus
• Trying Tools menu for charts
• Not selecting data before inserting chart

3. Given this data in A1:B4:
Product | Sales
Apples | 30
Oranges | 50
Bananas | 20
If you create a column chart from this data, what will the height of the bar for Oranges represent?

medium

A. 50, the sales number for Oranges

B. 30, the sales number for Apples

C. The total sales of all products

D. The average sales of all products

Solution

1. Step 1: Identify the data for Oranges

   Oranges have a sales value of 50 in the data table.

2. Step 2: Understand what the bar height shows

   The height of the bar in a column chart corresponds to the value of that category, here 50 for Oranges.

3. Final Answer:

   50, the sales number for Oranges -> Option A

4. Quick Check:

   Bar height = category value [OK]

Hint: Bar height matches the category's number [OK]

Common Mistakes:

• Confusing bar height with other categories
• Thinking bar shows total or average
• Mixing up rows and columns

4. You created a bar chart but it shows vertical bars instead of horizontal bars. What is the most likely fix?

medium

A. Delete and recreate the chart.

B. Change the chart type from column chart to bar chart in the Chart Editor.

C. Switch the data rows and columns in the chart editor.

D. Resize the chart to be wider.

Solution

1. Step 1: Identify chart type causing vertical bars

   Vertical bars appear when the chart type is set to column chart.

2. Step 2: Change chart type to bar chart

   In the Chart Editor, selecting bar chart changes bars to horizontal orientation.

3. Final Answer:

   Change the chart type from column chart to bar chart in the Chart Editor. -> Option B

4. Quick Check:

   Chart type controls bar orientation [OK]

Hint: Switch chart type to bar for horizontal bars [OK]

Common Mistakes:

• Trying to resize chart instead of changing type
• Switching rows/columns without changing chart type
• Deleting chart unnecessarily

5. You have monthly sales data for 3 products in columns A to D (A: Month, B-D: Products). You want to create a stacked column chart to compare total sales per month and see each product's contribution. Which steps should you follow?

hard

A. Select month column only, Insert -> Chart, then choose pie chart.

B. Select only product columns, Insert -> Chart, then choose bar chart.

C. Select all data including headers, Insert -> Chart, then choose stacked column chart in Chart Editor.

D. Select all data, Insert -> Chart, then choose line chart.

Solution

1. Step 1: Select all relevant data including headers

   Include months and all product sales columns to show full data in chart.

2. Step 2: Insert chart and choose stacked column chart

   Insert -> Chart opens editor; selecting stacked column chart shows total and parts stacked vertically.

3. Final Answer:

   Select all data including headers, Insert -> Chart, then choose stacked column chart in Chart Editor. -> Option C

4. Quick Check:

   Stacked column chart shows totals and parts [OK]

Hint: Select all data and pick stacked column chart [OK]

Common Mistakes:

• Not selecting month column
• Choosing bar or line chart instead of stacked column
• Selecting only product columns without headers