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
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
Watch below video to learn how to create Excel Combo Chart with Secondary Axis and Explain Sales Numbers History to Find Pattern
Source Files used in the Video