Let’s suppose you have an online learning school company, called Mellon Training, with three courses.
Every month you are responsible to track the conversion rate for those 3 courses you sell.
There are 4 bands for the rating...
From 0 to 5% the conversion rate is below average
From 5% to 10% the conversion rate is average
From 10% to 15% the conversion rate is good
From 15% to 20% the conversion rate is excellent
You have to make a visual representation of the data, creating a line chart with four bands. Keep in mind that there is not a line chart with bands in Excel, so you have to create it from scratch.
The data table has column with the months of the 2014, three columns with the courses and 4 columns with the range of the Bands. The range for each Band is 5%
Click on F6 cell. We want to create a formula with absolute reference of F4 cell. Type in equal, click on F4, press the F4 key from keyboard to insert absolute reference and press Enter. Drag the lower-right corner of the cell and drop it all the way down. Now go to G6 cell and type the following formula: =$G$4-$F$4 Drag the lower-right corner of the cell again and drop it all the way down. Don’t forget that each range of the band is 5%. Create the other two formulas for H6 and I6 cells, with the same way, like this: =$H$4-$G$4 =$I$4-$H$4
The next step is to select all 8 columns (from B5 to I17) and from Insert Tab select a line chart. We can see the 3 line charts representing the 3 courses and a horizontal line. Right click on this line and choose Charge series chart type. From Combo Category, change the chart type for Excellent, to Stacked Column. Do exactly the same with the other 3 horizontal lines…
To make the bands we have to make columns wider. It can be done by setting Gap width to 0%. Let’s do it: Right click on any of these columns and choose format data series. Now decrease the gap width to 0%.
We have to adjust the axis values. Right click on vertical axis and then format Axis. Put min to 0 and max to 0.2. Also set major unit to 0,025. Now we have to adjust the text of the months (it’s better to shorten it). Right-click on horizontal axis, format Axis, slide down to number and choose this type of date. Go to the next tab and change text direction to vertical
The last step is to apply data labels only to the last data point. How we can do that? Click only the last data point (not all the line), Design tab, Add chart element button and insert a right data label. Do exactly the same thing with the other 2 lines.
Click here to download the Excel project File
Click here to download the Excel Sample File
You can see the detailed video tutorial below or on YouTube
If you want to learn how to make amazing Dynamic Excel Charts, Dashboards and Pivot Charts in a few weeks, take our Udemy Course for only $25 !! (original price: $199 - 87% OFF!)
Latest from Mellon Training
- How to distribute your dashboard
- How to use Symbols and Arrows in Excel Formulas and Insert them into our Chart
- How to Import-Convert Data from Word Document into Excel worksheet
- How to generate Random Decimal Numbers between 0 and 1
- Blocking a user to interact outside a specific range – ScrollArea Property