Creating 3 dimensional charts in excel
Have you been asked to create a chart with 3 data variables?
Let’s understand with the help on an example.
Your manager come to you on an evening and says that he wants you to present the performance of a list of companies in an industry, on a single slide for a client discussion the next morning.
What can you do in the next few hours? How would you present the findings it in a most effective way?
Let’s gets started.
Depending on the context, you can quickly collect some data points from Company Annual reports, investor presentations or from a reliable data source (eg. Bloomberg)
In this example, we have collected information on revenues for the year 2014 & 2019 and EBITDA (Earnings Before Interest, Tax, Depreciation and Amortization) for year 2019.
What can we do with this data? How should we proceed?
We can create a 3-dimension bubble chart in excel.
A Bubble chart is an extension of a scatter plot chart, where in addition to a scatter plot (2 dimensions represented by the x axis and y axis), the size of the bubble represents the 3rd variable.
Let’s first start with the calculations and prepare the chart data.
We have the 2014 and 2019 revenues here. We can calculate the compounded annual growth rate (CAGR) from year 2014–2019 using the following formula.
CAGR(%)=((End value/Beginning value)^(1/(no of years ))-1)
This is how you can calculate the Revenue CAGR (%)for last 5 years in excel.
Then calculate the EBITDA margin. It can be calculated as EBITDA margin=EBITDA/Revenues
In the bubble chart, we can show Revenue growth on x-axis, EBITDA margin on y-axis and 2019 revenue represented by size of the bubble.
Here is the data prepared for our chart.
Now lets start with the chart preparation.
Click on data, Then Go to insert, Select Chart, then select XY (Scatter) under All charts, then Select 3D bubble chart and Click OK.
You get the Bubble chart.
Format the bubble chart as per the requirement.
- Remove gridlines
- Remove chart title
- Add data labels
- Remove chart outline
You get the following chart once the above steps are complete.
Next step is to add axis names.
Insert “Text box” and type Revenue Growth for x-axis and EBITDA margin % for Y-axis.
We see that the data labels are reflecting EBITDA margin, and not the company name.
To modify this, Click on data labels, Right click, Select — Format data labels, Select value from cells, Enter the name range (For Company A to J). Deselect Y Value (as we do not need EBITDA margins as data labels).
Select Label Position to Center and Change the color of labels, if required.
This is the chart you get.
Looks good.
Now what next, come up with insights and impress your manager.
Enjoy and Keep Learning online excel course
Want to go from beginner to an Expert in Data Analytics skills in Excel? Join our The Data Analyst Skills Training (DAST) program to learn Excel online to build analytical skills quickly through hands-on projects.
Want to become a PowerPoint specialist to create client-ready business presentations from scratch through a real life case illustration? Join our Business Presentation Skills Training (BPST) power point course.
For more details, visit our website www.skillfinlearning.com
