Excel Combo Chart with Secondary Axis – How To Explain Sales Numbers History and Find Patterns -

In this tutorial we are going to start with monthly sales data for the imaginary store, captured in Microsoft Excel table, and see if there is any correlation between monthly sales and outdoor temperature. Our goal is to find out if people shop less during the winter month, when the weather is colder and if change in sales can be explained by changes in outdoor temperature. To do this task we will be creating Microsoft Excel Combo Chart with secondary Axis.

We will look at the sales data for imaginary store in Wisconsin, USA. There are fewer sales during the winter month. Our goal is to answer the question – is there a correlation between Sales Volume and Outdoor temperature? Are there fewer people shopping in the winter month?

We will build Excel Combo Graph to look at the data visually to find ou. And then create a awesome graph with multiple axis, which clearly shows the pattern? This quick tutorial will help you accomplish your goal.

Our initial set of data consists of monthly sales in one column and Average outdoor temperature for the same month in another column. Our combo chart will consists of 2 charts – Bar chart which would represent monthly sales line chart would display average outdoor temperature. We will also add make line chart more professionally looking by adding markers and 3d animation effect. In the end we will also attempt to answer business questions: can we create a correlation between monthly sales and outdoor temperature? Do people shop more during warmer weather and shop less during the colder weather.

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

In This Article:

Initial data overview

Final Chart The will be created

Analyze data in Excel to make sure everything is present

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

Both Sales data and Temperature data is present for the entire year

We are ready to generate 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

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

This chart highlights different types of information. It is applicable for our example because the range between Sales Volume and Outdoor temperature is very different

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

Complete Excel chart formatting to make it 3D

Change Title for the Chart

Make line thicker

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

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

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

Answering Business Question – Chart Analysis

Now lets answer the business question: “Is there a correlations between Sales Volume and Outdoor temperature”?

Absolutely. There is a correlation. Fewer people shop during the winter month. But changes in outdoor temperature don’t explain all variance of Sales volume through the year. There has to be something else that we may need to take into account. And this is the subject of our next tutorial where we look at impact of discounts to the sales volumes and how to best represent this data in the graph