Line charts in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate the average sales for the first quarter

Given the sales data in cells B2 to B4 representing January to March, which formula correctly calculates the average sales for the first quarter?

A =SUM(B2:B4)

B =SUM(B2:B4)/2

C =MEDIAN(B2:B4)

D =AVERAGE(B2:B4)

💡 Hint

Think about how to find the average of a range of cells.

The =AVERAGE(B2:B4) formula calculates the mean of the sales values in January, February, and March. Option D divides the sum by 2 instead of 3, which is incorrect. Option D finds the median, not the average. Option D sums the values without averaging.

❓ Function Choice

intermediate

Choose the correct function to plot a line chart with dates on the X-axis

You want to create a line chart showing sales over time with dates on the horizontal axis. Which Excel feature or function helps you ensure dates are correctly used on the X-axis?

A Use the 'Insert Line or Area Chart' and select the date range as the X-axis labels

B Use the SUM function to add dates

C Use the CONCATENATE function to combine dates and sales

D Use the IF function to filter dates

💡 Hint

Think about how Excel charts use data ranges for axes.

Option A is correct because inserting a line chart and selecting the date range for the X-axis labels ensures the chart plots sales over time correctly. The other options do not relate to chart axis setup.

🎯 Scenario

advanced

Identify the cause of incorrect line chart axis labels

You created a line chart from sales data with dates in column A and sales in column B. The chart's X-axis shows numbers like 1, 2, 3 instead of dates. What is the most likely reason?

A The date cells are formatted as text, not as dates

B The sales data contains blank cells

C The chart type is set to bar chart instead of line chart

D The worksheet is protected

💡 Hint

Check how Excel recognizes the date values.

If dates are stored as text, Excel treats them as categories and numbers the X-axis instead of showing actual dates. Blank sales or chart type do not affect axis labels this way. Worksheet protection does not change axis labels.

❓ data_analysis

advanced

Analyze trend using a line chart with multiple data series

You have monthly sales data for two products in columns B and C, with months in column A. You create a line chart with both products shown. Which insight can you get from this chart?

A Find the average sales per month for both products combined

B Calculate the total sales for each product

C Compare sales trends over time between the two products

D Identify the product with the highest sales in a single month using a pie chart

💡 Hint

Think about what line charts show best.

Line charts are great for comparing trends over time between multiple data series. Total or average sales require formulas, and pie charts are not suitable for time trends.

🧠 Conceptual

expert

Determine the effect of adding a secondary axis in a line chart

You have a line chart with two data series: sales volume and sales revenue. Sales volume ranges from 100 to 500 units, and revenue ranges from $10,000 to $50,000. You add a secondary vertical axis for revenue. What is the main benefit of this?

A It changes the chart type to a bar chart automatically

B It allows both series to be displayed clearly despite different scales

C It combines the two series into one line for simplicity

D It removes the legend to reduce clutter

💡 Hint

Think about how different value ranges affect chart readability.

A secondary axis lets you plot data series with very different scales on the same chart, so both lines are visible and meaningful. It does not combine lines, change chart type, or remove the legend.