Mastering Data Visualization: How to Overlay Two Graphs in Excel

Excel is a powerful tool for data analysis and visualization, and sometimes, the most impactful insights come from comparing two sets of data directly. Overlaying two graphs on the same chart is a fantastic way to achieve this, allowing you to observe trends, correlations, and differences between datasets at a glance. While it might seem daunting to new users, overlaying graphs in Excel is a straightforward process once you understand the basic steps. This article will guide you through everything you need to know, providing detailed instructions, best practices, and troubleshooting tips to help you become a master of data visualization in Excel.

Why Overlay Graphs?

Before we dive into the how, let’s explore why you might want to overlay graphs in the first place. Here are some common use cases:

• Comparing Trends Over Time: Easily see how two different datasets change over the same period. For example, you might overlay sales data with marketing spend to visualize their relationship.
• Analyzing Correlations: Quickly identify if two variables have a positive or negative correlation. A classic example is overlaying temperature data with ice cream sales.
• Highlighting Differences: Show the discrepancy between two datasets. You could compare actual sales figures with sales targets, for instance.
• Visualizing Multiple Variables: Display several related datasets on a single chart to provide a more holistic view of your information.
• Presenting Clear and Concise Information: Overlaying graphs allows you to present information in a more organized and efficient way, making it easier for your audience to understand the data.

Different Techniques for Overlaying Graphs

There isn’t one single way to overlay graphs in Excel, and the best method often depends on the type of data you’re working with and the visual effect you want to achieve. Here are the most common approaches:

• Using a Combination Chart: This is the most versatile method and involves combining different chart types (e.g., line and column) on the same chart.
• Adding a Secondary Axis: Ideal when the two datasets have different scales or units. It allows you to display both datasets on the same chart without distorting the visualization.
• Overlaying Line Graphs: Simplest method when you want to compare two trendlines.

Method 1: Overlaying Graphs Using a Combination Chart (with Secondary Axis)

This is the most versatile approach, and it allows for overlaying different chart types and handling different scales using a secondary axis. Let’s break it down step by step:

Step 1: Prepare Your Data

Make sure your data is organized correctly in your spreadsheet. Ideally, you should have:

• A column containing the shared categories (e.g., dates, months, product names) – this will be your x-axis.
• A column for each dataset you want to graph, usually to the right of the category column.

For example, you might have:

Step 2: Insert a Combo Chart

1. Select your data, including the column headers.
2. Go to the “Insert” tab on the Excel ribbon.
3. In the “Charts” group, click on “Insert Combo Chart”.
4. Select “Create Custom Combo Chart”.
5. Excel will present you with a dialog box. In the “Choose the chart type and axis for your data series” section you will have option to change the chart type for each of your series as well as where to place the series, Primary or Secondary axis.
6. For each series, choose the chart type you want. Generally, use line or area for one data series and column chart for the second series. Also, choose secondary axis for the data series you want to overlay, usually, the one with a different scale.
7. Click “OK”.

At this point, Excel should display a graph with your two data sets overlaid. If your scales are very different the secondary series might look flattened or barely visible, so this means you need to use secondary axis

Step 3: Adjust Secondary Axis

After creating the combo chart, you may need to adjust your secondary axis so that both data sets can be interpreted clearly and visible:

1. Double-click on the secondary axis.
2. The “Format Axis” pane will appear on the right.
3. Adjust the “Minimum” and “Maximum” values as needed. You can also change the tick mark intervals. This may require several attempts to find the best fitting scale.
4. If your data is not always positive, adjust the minimum of the secondary axis.
5. Close the Format Axis pane.

Step 4: Customize Your Chart (Optional)

Now you can customize your chart to make it more presentable and informative:

• Chart Title: Click on the chart title to edit it. Give your chart a clear and concise title that describes the data being visualized.
• Axis Labels: Click on the primary and secondary axis titles to edit them. Include the units of measure so they are easily interpretable.
• Legend: Ensure the legend labels for both data sets are clearly and easily understandable. You can change the position of the legend if it is overlapping with the data.
• Colors and Styles: Modify colors, line styles, and column styles to match your brand or presentation needs. Be consistent in color use for visual clarity.
• Gridlines: Add or remove gridlines to suit your preference.
• Data Labels: Consider adding data labels to your graphs for more precision, but don’t clutter the graph with too many labels, especially in the case of a lot of datapoints.

Step 5: Best Practices for Combination Charts

Here are some things to consider when overlaying graphs using a combo chart:

• Use different chart types wisely: Different chart types such as column, line and area chart draw user attention to different characteristics of your data. Choose the chart type that highlights the message you want to deliver.
• Don’t overload the graph: Use only relevant information on the graph. Do not over-use colors or data labels. Be consistent with colors in data series.
• Consider a secondary axis carefully: Don’t use a secondary axis if the two data sets have the same scale.
• Keep readability in mind: Make sure the chart is clear and easy to understand for anyone who sees it.

Method 2: Overlaying Line Graphs

If both your datasets are best represented as lines (e.g. both are trends over time), you can overlay two line graphs easily. Here’s how to do it:

Step 1: Prepare Your Data

As with the combo chart method, make sure your data is organized with a common x-axis and then columns for each data series, similar to the data organization provided in method 1.

Step 2: Insert a Line Chart

1. Select all of your data, including the column headers.
2. Go to the “Insert” tab on the Excel ribbon.
3. In the “Charts” group, click on “Insert Line or Area Chart” and select the type of line chart you prefer.
4. Excel will create a line chart of all your data series. By default it will plot all lines on a single axis.

Step 3: Adjust Axis (if necessary)

If your datasets have different scales, they may look distorted on a single axis. In that case, use the secondary axis following the steps in Method 1:

1. Click on one of the lines to select its data series.
2. Right-click and select “Format Data Series”.
3. In the “Format Data Series” pane, under “Series Options”, select “Secondary Axis”.
4. The selected line series will now appear on the secondary axis. You can adjust the secondary axis values using the formatting option in Method 1.

Step 4: Customize Your Chart (Optional)

As with the Combination chart method, you can customize your chart:

• Chart Title: Click on the chart title to edit it. Give your chart a clear and concise title that describes the data being visualized.
• Axis Labels: Click on the primary and secondary axis titles to edit them. Include the units of measure so they are easily interpretable.
• Legend: Ensure the legend labels for both data sets are clearly and easily understandable. You can change the position of the legend if it is overlapping with the data.
• Colors and Styles: Modify colors, line styles, and column styles to match your brand or presentation needs. Be consistent in color use for visual clarity.
• Gridlines: Add or remove gridlines to suit your preference.
• Data Labels: Consider adding data labels to your graphs for more precision, but don’t clutter the graph with too many labels, especially in the case of a lot of datapoints.

Troubleshooting Tips

Here are some common issues you might encounter and how to solve them:

• Data is not displayed correctly: Double-check that your data is organized correctly and you selected the correct range when creating the chart.
• Scales are distorted: If the two datasets have very different ranges, use a secondary axis to properly display them.
• Chart is cluttered: Simplify your chart by using clear color coding, avoiding unnecessary data labels, and adding the minimum amount of gridlines necessary.
• Chart is confusing: Make sure your labels are clear and the overall chart is easy to interpret. If your chart is too complex, consider simplifying it further or using multiple charts.
• Legend is overlapping with the graph: Reposition the legend to a position that does not overlap with the data series.
• Chart is not formatted well: Adjust colors, fonts, and styles to make your chart professional and easy to understand.

Conclusion

Overlaying graphs in Excel is a powerful technique for comparing and analyzing multiple datasets. By mastering the methods described in this article, you’ll be able to create insightful visualizations that can help you make better data-driven decisions. Whether you’re using combination charts with secondary axis or overlaying line graphs, always keep clarity and accuracy in mind. Remember that the goal is to communicate information effectively and let the data speak for itself.

Experiment with different chart types and formatting options to find what works best for your specific needs. With practice, you’ll be a pro at overlaying graphs in Excel in no time, and you’ll be able to reveal hidden trends and patterns in your data with ease. Keep practicing, and soon you will be creating beautiful and insightful graphs!