Line charts in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate the average sales for a line chart

You have monthly sales data in cells A2:A13. Which formula correctly calculates the average sales to be used as a reference line in your line chart?

A =AVERAGE(A2:A13)

B =SUM(A2:A13)

C =COUNT(A2:A13)

D =MAX(A2:A13)

💡 Hint

Think about the formula that finds the middle value of a range.

The =AVERAGE() function calculates the mean of the numbers in the range, which is useful for showing an average line in a chart.

❓ Function Choice

intermediate

Choose the function to create a cumulative total for a line chart

You want to show a running total of sales over months in a line chart. Which formula in cell B2, copied down, correctly calculates the cumulative total from sales in column A?

A =SUM(A$2:A2)

B =SUM(A2:A$13)

C =SUM($A$2:A2)

D =SUM(A2:A2)

💡 Hint

Look for a formula that fixes the start of the range but allows the end to move down as you copy.

The formula =SUM(A$2:A2) fixes the start row at 2 and expands the end row as you copy down, creating a running total.

🎯 Scenario

advanced

Identify the correct data range for a line chart with multiple series

You have sales data for three products in columns B, C, and D from rows 2 to 13. Column A has the months. You want to create a line chart showing all three products over time. Which data range should you select?

A B2:D13

B A1:D13

C A2:D13

D A1:C13

💡 Hint

Remember to include the header row and the column with months for the chart to label the x-axis correctly.

Including the header row (row 1) and the months column (A) ensures the chart has labels and all product data.

❓ data_analysis

advanced

Analyze the effect of missing data points on a line chart

If your sales data for some months is missing (cells are blank) in a line chart, what will Google Sheets do by default when plotting the line?

A It will show an error message and not create the chart

B It will break the line at missing points, showing gaps

C It will treat missing data as zero and plot accordingly

D It will connect the points with a straight line, skipping the missing data

💡 Hint

Think about how line charts usually handle missing points to keep the trend visible.

By default, Google Sheets connects points with a line, ignoring blanks to keep the trend continuous.

🧠 Conceptual

expert

Determine the number of data points plotted in a line chart

You create a line chart using data in cells A1:B10. Column A has dates, and column B has sales values. If cells B4 and B7 are empty, how many points will the line chart plot?

A 9 points

B 10 points

C 8 points

D 7 points

💡 Hint

Count how many sales values are not empty in the range.

There are 10 rows total, but 2 sales values are missing, so only 8 points are plotted.

Practice

1. What is the main purpose of a Line chart in Google Sheets?

easy

A. To display data as separate categories with bars

B. To show how values change over time or order

C. To list data in a table format

D. To create a pie chart of percentages

Solution

1. Step 1: Understand the purpose of line charts

   Line charts are used to show trends or changes over time or sequence.

2. Step 2: Compare with other chart types

   Bar charts show categories, pie charts show parts of a whole, tables list data without trends.

3. Final Answer:

   To show how values change over time or order -> Option B

4. Quick Check:

   Line chart = show changes over time [OK]

Hint: Line charts track changes over time or order [OK]

Common Mistakes:

• Confusing line charts with bar or pie charts
• Thinking line charts show categories only
• Using line charts for static data without order

2. Which of these is the correct way to insert a line chart in Google Sheets?

easy

A. Select data, then Insert > Chart, and choose Line chart

B. Right-click a cell and select 'Add Line Chart'

C. Type =LINECHART() in a cell

D. Use Format > Chart > Line chart

Solution

1. Step 1: Recall the chart insertion steps

   In Google Sheets, you select your data, then go to Insert > Chart.

2. Step 2: Choose the chart type

   After Insert > Chart, you pick Line chart from the Chart Editor.

3. Final Answer:

   Select data, then Insert > Chart, and choose Line chart -> Option A

4. Quick Check:

   Insert > Chart > Line chart = correct method [OK]

Hint: Use Insert menu to add charts, then pick Line chart [OK]

Common Mistakes:

• Trying to insert charts via right-click menu
• Typing formulas to create charts
• Looking for chart options under Format menu

3. Given this data in A1:B4:
Date | Sales
Jan | 100
Feb | 150
Mar | 120
If you create a line chart with this data, what will the line show?

medium

A. Sales increasing from Jan to Feb, then decreasing in Mar

B. Sales staying constant across all months

C. Sales decreasing from Jan to Feb, then increasing in Mar

D. Sales values plotted as bars

Solution

1. Step 1: Analyze the sales data values

   Sales are 100 in Jan, 150 in Feb, and 120 in Mar, so they rise then fall.

2. Step 2: Understand line chart behavior

   The line connects points in order, showing increase from 100 to 150, then decrease to 120.

3. Final Answer:

   Sales increasing from Jan to Feb, then decreasing in Mar -> Option A

4. Quick Check:

   Line chart shows trend: up then down [OK]

Hint: Line charts connect points in order showing trends [OK]

Common Mistakes:

• Assuming sales stay constant
• Confusing line chart with bar chart
• Ignoring the order of data points

4. You created a line chart but it shows only one data point instead of a line. What is the most likely cause?

medium

A. Your data has empty cells

B. You used the wrong chart type

C. You forgot to sort the data

D. You selected only one column of data instead of two

Solution

1. Step 1: Check data selection for the chart

   Line charts need at least two columns: one for labels (like dates) and one for values.

2. Step 2: Understand effect of selecting one column

   If only one column is selected, Google Sheets treats it as a single series without x-axis labels, showing one point.

3. Final Answer:

   You selected only one column of data instead of two -> Option D

4. Quick Check:

   Two columns needed for line chart data [OK]

Hint: Select both labels and values columns for line charts [OK]

Common Mistakes:

• Selecting only values without labels
• Choosing wrong chart type
• Ignoring empty cells in data

5. You have monthly sales data for two products in columns A to C:
Month | Product A | Product B
Jan | 100 | 90
Feb | 150 | 120
Mar | 120 | 130
How do you create a line chart to compare both products' sales trends over time?

hard

A. Select Month and Product A columns only, then Insert > Chart

B. Select only Product A and Product B columns, then Insert > Chart

C. Select all three columns, then Insert > Chart and choose Line chart

D. Create two separate line charts, one for each product

Solution

1. Step 1: Identify data needed for comparison

   You need the Month column for x-axis labels and both product columns for lines.

2. Step 2: Select all three columns and insert line chart

   Selecting Month, Product A, and Product B lets Google Sheets plot two lines over the months.

3. Final Answer:

   Select all three columns, then Insert > Chart and choose Line chart -> Option C

4. Quick Check:

   Include labels and all series for comparison [OK]

Hint: Select labels and all data series to compare in one chart [OK]

Common Mistakes:

• Omitting the Month column for x-axis
• Selecting only one product column
• Making separate charts instead of one combined