/ How to

How to Graph Multiple Data Line Sets in Excel Charts

Excel provides a variety of chart options to visualize data, including line, bar, column, pie, scatter plots, and more. When you have multiple data sets that you want to plot on the same chart, you can create a combination or “combo” chart in Excel. Here is a step-by-step guide on how to graph multiple data line sets in the same Excel chart.

Step 1: Organize Your Data

Before creating the chart, organize your data clearly in an Excel spreadsheet. It is easier to select the correct data range for the chart when the data is organized. Each data set should be arranged in columns or rows.

For example, the spreadsheet could contain:

  • Year in the first column
  • Sales data of Product A in the second column
  • Sales data of Product B in the third column

Ensure the data ranges of the different products match (i.e. same number of rows of data for each product).

Step 2: Select the Data Range

Once your data is organized, select the data range to be plotted on the chart. To select the entire data range:

  • Click on the first cell of the data range
  • Scroll to the last cell of the data range
  • Hold down the Shift key
  • Click on the last cell to select the entire data range

Alternatively, you can click and drag your mouse to visually select the data range.

Step 3: Insert a Combo Chart

Go to the Insert tab and click on the ‘Insert Combo Chart’ button. This allows you to combine multiple chart types (such as line and column) into the same chart.

Alternatively, you can:

  1. Click on the Insert Chart button
  2. Click on All Charts
  3. Select Combo from the list

Step 4: Select the Chart Subtypes

When inserting a combo chart, a dialog box will appear asking you to select the chart subtypes.

For example, to plot Product A as a line graph and Product B as a column graph:

  1. Click on the first Chart Type dropdown (Product A data)
  2. Select Line
  3. Click on the second Chart Type dropdown (Product B data)
  4. Select Column
  5. Click OK

This will insert a combo chart with a line graph and column graph combined into a single chart.

Step 5: Edit the Chart

Once the combo chart is inserted, you can further edit and customize it:

Add Chart Titles and Axis Labels: Makes the chart easier to understand

Change Colors: Emphasize differences between data sets

Move the Legend: Ensure it does not overlap with the plotted data

Add Data Labels: Display values next to data points

Tips for Readable Combo Charts

Here are some tips to ensure your combo chart with multiple data sets is easy to understand:

  • Use clearly descriptive legends so that readers can differentiate the data sets
  • Use different colors and dash types for the different data sets
  • Ensure the axis scales are appropriate for accurate data representation
  • Add a secondary vertical axis if needed for dual Y-axis charts
  • Display no more than 2-3 data sets on the same chart
  • Add a benchmark line if helpful for comparisons

Common Use Cases

Some common examples of using combo charts to display multiple data sets include:

  • Sales trends over time of different products
  • Website traffic from different sources
  • Comparing budgets to actual expenses
  • Monitoring business metrics against targets

Limitations

Excel combo charts have some limitations:

  • Limited customization compared to dedicated data visualization software
  • Not optimized for interactive visualizations
  • Difficult to plot more than 3-4 data sets clearly

For more advanced data visualization needs, consider using Power BI, Tableau, or other dedicated data visualization software.

Conclusion

By following these steps, you can create Excel charts with multiple data sets plotted as different chart subtypes such as lines, columns, and areas all in one chart. This allows effective visualization of trends across different metrics. Pay attention to organization, selection, proper combo chart configuration, customization, readability, and use cases for maximum impact.

Related Posts