Line charts in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see the monthly sales trends for the first half of the year to understand how sales are changing over time.

📊 Data: You have monthly sales data for January to June, including the month name and total sales amount.

🎯 Deliverable: Create a line chart that shows the sales trend from January to June.

Sample Data

Month	Sales
January	1200
February	1350
March	1500
April	1600
May	1700
June	1650

1

Step 1: Enter the monthly sales data into two columns: Column A for Month and Column B for Sales, starting from cell A1.

No formula needed. Just type the data as shown in sample_data.

Expected Result

Data is organized with months in A2:A7 and sales in B2:B7.

2

Step 2: Select the data range including headers from A1 to B7.

Select cells A1:B7.

Expected Result

The data range is highlighted and ready for chart creation.

3

Step 3: Insert a line chart to visualize the sales trend over the months.

Go to Insert tab > Charts group > Click on 'Insert Line or Area Chart' > Choose 'Line'.

Expected Result

A line chart appears showing months on the horizontal axis and sales on the vertical axis.

4

Step 4: Add chart title to describe the chart clearly.

Click on the chart title area and type 'Monthly Sales Trend (Jan-Jun)'.

Expected Result

Chart title is updated to 'Monthly Sales Trend (Jan-Jun)'.

5

Step 5: Format the horizontal axis to show all month names clearly.

Right-click horizontal axis > Format Axis > Ensure axis labels show all months.

Expected Result

Months January to June are clearly visible on the horizontal axis.

Final Result

Monthly Sales Trend (Jan-Jun)

 1800 |                       *
 1700 |                    *  
 1600 |                 *     
 1500 |              *        
 1400 |           *           
 1300 |        *              
 1200 |     *                 
       --------------------------------
        Jan Feb Mar Apr May Jun

✓Sales increased steadily from January to May.

✓There was a slight dip in sales in June compared to May.

✓The overall trend shows growth in the first half of the year.

Bonus Challenge

Add a second line to the chart showing the sales target for each month and compare actual sales against targets.

Show Hint

Add a new column with sales targets next to the sales data, then select all data including targets before inserting the line chart.

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