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.

Practice

1. What is the main purpose of a line chart in Excel?

easy

A. To list data in a table format

B. To display data as separate bars for comparison

C. To calculate sums and averages automatically

D. To show trends and changes over time or categories

Solution

1. Step 1: Understand what line charts represent

   Line charts connect data points with lines to show how values change over time or categories.

2. Step 2: Compare with other chart types

   Bar charts show separate bars, tables list data, and formulas calculate values, but line charts focus on trends.

3. Final Answer:

   To show trends and changes over time or categories -> Option D

4. Quick Check:

   Line chart = trends over time [OK]

Hint: Line charts connect points to show trends over time [OK]

Common Mistakes:

• Confusing line charts with bar charts
• Thinking line charts calculate values
• Assuming line charts display raw data tables

2. Which of the following is the correct way to insert a line chart in Excel?

easy

A. Type =LINECHART() in a cell

B. Select data, then go to Insert > Line Chart

C. Right-click a cell and choose Format Cells > Line Chart

D. Use the Data tab and select Create Line Chart

Solution

1. Step 1: Recall Excel's chart insertion method

   Charts are inserted by selecting data and using the Insert tab, then choosing the chart type.

2. Step 2: Identify the correct menu path

   Insert > Line Chart is the standard way; other options are invalid or do not exist.

3. Final Answer:

   Select data, then go to Insert > Line Chart -> Option B

4. Quick Check:

   Insert tab > Line Chart = correct method [OK]

Hint: Use Insert tab to add charts, no formula needed [OK]

Common Mistakes:

• Trying to create charts with formulas
• Looking for line chart options in wrong menus
• Assuming right-click offers chart creation

3. Given this data in Excel: | Month | Sales | |-------|-------| | Jan | 100 | | Feb | 150 | | Mar | 120 | If you create a line chart from this data, what will the line do between Feb and Mar?

medium

A. Rise sharply

B. Fall sharply

C. Fall slightly

D. Stay flat

Solution

1. Step 1: Analyze the sales values for Feb and Mar

   Sales in Feb is 150, and in Mar it is 120, which is a decrease.

2. Step 2: Determine the line movement on the chart

   The line will go down slightly from 150 to 120, indicating a small fall.

3. Final Answer:

   Fall slightly -> Option C

4. Quick Check:

   150 to 120 = slight fall [OK]

Hint: Compare numbers to see if line goes up or down [OK]

Common Mistakes:

• Assuming line always rises
• Confusing flat line with small decrease
• Ignoring actual data values

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

medium

A. Data selected includes only one row or column

B. Chart type is set to bar chart instead of line chart

C. Data contains text instead of numbers

D. Chart is placed on a hidden worksheet

Solution

1. Step 1: Understand line chart requirements

   Line charts need at least two data points to connect with a line.

2. Step 2: Identify the cause of single data point

   If only one row or column is selected, only one point appears, so no line is drawn.

3. Final Answer:

   Data selected includes only one row or column -> Option A

4. Quick Check:

   One data point = no line [OK]

Hint: Select multiple data points to form a line [OK]

Common Mistakes:

• Confusing chart type with data selection
• Ignoring data range size
• Assuming hidden sheets affect chart display

5. You have monthly sales data for two products in columns A and B from rows 2 to 13. You want to create a line chart comparing both products over the months. Which steps correctly create this chart?

hard

A. Select A1:B13 including headers, then Insert > Line Chart

B. Select A2:B13 excluding headers, then Insert > Line Chart

C. Select C1:C13, then Insert > Line Chart

D. Select A1:C13, then Insert > Pie Chart

Solution

1. Step 1: Identify correct data range for two products

   Columns A and B hold the two products' sales data; include headers in row 1 for labels.

2. Step 2: Choose correct chart type and data selection

   Select A1:B13 to include headers and data, then Insert > Line Chart to compare trends.

3. Final Answer:

   Select A1:B13 including headers, then Insert > Line Chart -> Option A

4. Quick Check:

   Include headers + data + line chart = Select A1:B13 including headers, then Insert > Line Chart [OK]

Hint: Always include headers for labels when selecting data [OK]

Common Mistakes:

• Excluding headers causing missing labels
• Selecting unrelated column C
• Choosing wrong chart type like pie chart