How To Create Multiple Line Charts On Excel Secondary Axis Combo Graph – Prepare Data For Presentation

Do you need create multiple line charts on Excel Combo Graph? And then create multiple line charts on secondary axis, which helps you better answer business question? As part of this tutorial we will be creating Excel Combo Chart with multiple line graphs on secondary Axis.

We have already established relationships between monthly sales data and temperature for the imaginary store in our Tutorial Excel Combo Chart with Secondary Axis – How To Explain Sales Numbers History to Find Pattern.

But unfortunately, relationship, even though there is a relationship between sales and temperature it doesn’t explain all variations in sales, especially during the warmer weather. In this tutorial we will add average discounted rates, offered by the store to see if we have a correlation between different discounts offered by the store and sales during the summer month. And then create a awesome graph with multiple line charts on the second axis, which clearly shows the pattern?

Our initial set of data consists of Monthly Sales in one column, Average Outdoor Temperature in another column and average monthly discount data in the third column. Our combo chart will consists of 3 charts:

  • Bar chart which would represent monthly sales,
  • Line Chart would display average outdoor temperature
  • and another second Line chart, which would represent discounts.

We will also add make line charts more professionally looking by adding markers and 3d animation effect. In the end we will also attempt to answer business questions: is there a correlation between monthly sales and average discounts, especially during the warmer month? Do people shop more when there are higher discounts offered?

This tutorial is a part of multi-series of video tutorials, which teaches you from start until the end how to take business data and make it ready for professional presentation. We start with Microsoft Excel data, create chart with secondary axis, add 3D effects to the chart. Then in the second tutorial we create an additional Line graph on secondary axis. And in the last tutorial we take created chart into PowerPoint and create animated slide

Tutorial 1. Excel Combo Chart with Secondary Axis – How To Explain Sales Numbers History to Find Pattern

Tutorial 2. Create multiple line charts on Excel Secondary Axis Combo Graph

Tutorial 3. How to Animate Microsoft Excel Combo Chart with Secondary Axis

What is Secondary Axis and how is it helpful for presenting the data

When the numbers in a chart you created vary widely from data series to data series, or when you have mixed types of data (for example, sales volume, temperature and discounts), you can plot one or more data series on a secondary vertical (value) axis. The scale of the secondary vertical axis shows the values for the associated data series.

A secondary axis works well in a chart that shows a combination of column and line chart and especially useful for presentations when you need to present multiple data sets on the same chart

How To Create Multiple Line Charts On Excel Secondary Axis Combo Graph - Prepare Data For Presentation

Source Data in Microsoft Excel

Our initial set of data consists of Monthly Sales in one column, Average Outdoor Temperature in another column and average monthly discount data in the third column.

Create multiple line charts on Excel Secondary Axis Combo Graph

Final Chart The will be created

Our combo chart will consists of 3 charts – Bar chart which would represent monthly sales, Line Chart would display average outdoor temperature and another line chart, which would represent discounts. We will also add make line charts more professionally looking by adding markers and 3d animation effect. In the end we will also attempt to answer business questions: is there a correlation between monthly sales and average discounts, especially during the warmer month? Do people shop more when there are higher discounts offered?

Create multiple line charts on Excel Secondary Axis Combo Graph

Analyze data in Excel to make sure everything is present

First lets review if we have complete data set to build quality graph”

  • Monthly Sales data, Average Outdoor Temperature and Average Monthly Discounts data is present for the entire year
  • Both Average Outdoor Temperature and Average Monthly Discounts are in the same data range : single to double digits, which can be proportionally reflected on the graph

We are ready to generate Multiple Line Charts On Excel Secondary Axis Combo Graph. First step is to select source data

Create multiple line charts on Excel Secondary Axis Combo Graph

Create a Combo Chart in Excel

Microsoft Excel Combo Chart is used to highlight the different types of information. You can used it when the range of values in the chart varies widely or you have mixed types of data.

Excel Combo chart highlights different types of information. It is applicable for our example because the range between Sales Volume and Outdoor temperature is very different, but ranges for Outdoor temperate and Discount are the same. There are going to be three diagrams in one in the chart we will generate: Sales will be represented by Excel Bar Chart and Average temperature and Discounts will be represented by Excel Line Graph.

Create multiple line charts on Excel Secondary Axis Combo Graph

Navigate to Insert -> Charts->Combo->Clustered Column->Line on Secondary Axis

Create multiple line charts on Excel Secondary Axis Combo Graph

Below is the chart Microsoft Excel inserted by Default. In the next section we are going to format the graph to make it look professional. By Default Sales and Temperature have been created as Bar Charts and Discount was created as a line Chart. In our next step we would need to change Temperature from Bar Chart into Line Chart.

Create multiple line charts on Excel Secondary Axis Combo Graph

Change Temperature graph from Bar Chart into Line Chart.

To make Average temperature show up as a line change we need to select the chart, Right Mouse Click and select [Change Chart Type]

Create multiple line charts on Excel Secondary Axis Combo Graph

As you can see in the new dialog box, which appeared, Sales and temperature represented by the chart Type [Clustered Column] and initially located on the Primary Axis. Only Discount data is represented as a Line Chart and associated with Secondary Axis

Create multiple line charts on Excel Secondary Axis Combo Graph

To complete this task follow steps below:

For Temperature Series, change Chart Type to [Line] and select [Secondary Axis] checkbox

Create multiple line charts on Excel Secondary Axis Combo Graph

This is how updated chart looks like. In the next step we will update the chart title and make Bar Graphs and Line Graphs look 3D

Create multiple line charts on Excel Secondary Axis Combo Graph

Complete Excel Chart Formatting to make it 3D

Change Title for the Chart

Click on the tile for the chart and type a new name

Create multiple line charts on Excel Secondary Axis Combo Graph

Make Bar Chart look 3D

Select Sales data series then Right Mouse Click [Format Data Series]

Create multiple line charts on Excel Secondary Axis Combo Graph

In [Format Data Series] change value for Top Bevel and Material

Create multiple line charts on Excel Secondary Axis Combo Graph

Create multiple line charts on Excel Secondary Axis Combo Graph

Create multiple line charts on Excel Secondary Axis Combo Graph

Make Line Charts look 3D

Complete below steps for both Outdoor Temperature and Discounts Series, selecting different colors of your preference

01 Make line thicker

Select the orange temperature line and in Line Properties change Width to 7pt

Create multiple line charts on Excel Secondary Axis Combo Graph

02 Add Line Markers

To enable Circle marker follow this steps: In Properties select Marker->Built-In->Type Circle

To change the size of the marker: In Properties Select Marker -> Size = 17

Create multiple line charts on Excel Secondary Axis Combo Graph

03 Make Markers 3D

To make markers 3D navigate to Properties->Effects -> To Bevel and select value from dropdown box

To change the material for the markers navigate to Properties->Effects->Material and change the value for the material, by selecting the value in Special Effects section

In [Format Data Series] change value for Top Bevel and Material

Create multiple line charts on Excel Secondary Axis Combo Graph

Select Value for Top Bevel

Create multiple line charts on Excel Secondary Axis Combo Graph

Select Value for Material

Create multiple line charts on Excel Secondary Axis Combo Graph

Create multiple line charts on Excel Secondary Axis Combo Graph

Repeat steps 01- 03 for the Disount Data Series. Below is how final version of the chart looks:

Create multiple line charts on Excel Secondary Axis Combo Graph

Chart Analysis

Now lets answer the business question: “What does best explain variance in monthly Sales for hardware store”? Can both changes in Outdoor temperature and changes in promotional discounts, offered by the store explain variation in sales?

Sales volume variation can be explained by multiple variables (Outdoor Temperature and Discounts offered by the store). Fewer people shop during the winter month. But changes in outdoor temperature don’t explain all sales volume variations through the year. During warmer weather people buy more during the month when discounts are offered. There is also correlation between the percentage of the discount and Sales Volume: the higher the discount the higher the monthly sales numbers.

 

 

How To Create Multiple Line Charts On Excel Secondary Axis Combo Graph [Video]

 

Source Files used in the Tutorial

Download Sample MS Excel Combo Chart with Multiple Secondary Axis and Explain Sales Numbers History to Find Pattern

Download Animated PowerPoint Slide with Excel Combo Chart with Secondary Axis and Explain Sales Numbers History to Find Pattern

 

Download PDF version of this Tutorial

How To Create Multiple Line Charts On Excel Secondary Axis Combo Graph [PDF Tutorial]

 

Next Steps:

  1. Download Top 5 Excel Interview Questions for Job Seekers Source File
  2. Review Other Excel Interview Video Tutorials
  3. Subscribe to my email list to receive more tutorials like this one
  4.  If you would like to learn more about Microsoft Excel enroll into below online training courses:

Widget not in any sidebars