Best 100+ Excel Hacks & Tips with Excel 2016. Take the course for only $10 !!

Arrow up
Arrow down
Menu

Create a Line Chart with KPI Bands and Track Conversion Rate

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!)

 


 

Last modified onSunday, 13 December 2015 11:52
Rate this item
(1 Vote)