Dynamic Step Chart
Charts and Visualization

Dynamic Step Chart in Excel

In this article you will learn how to create a dynamic Step Chart in Excel. In this chart we can show the rolling 7 days price or sales.

Let’s say we have dates on column A (“1-Jan to 3-Feb”) and sales on column B. For this data set we will create the dynamic step chart.

Data set for step chart
Data set for step chart

Below are the steps to create the Dynamic Step Chart in Excel-

  • Go to the Developer Tab>>Insert>>Scroll bar (Form Control)
  • Drag the scroll bar on the worksheet.

 

Scroll bar (Form Control)
Insert Scroll bar (Form Control)

 

  • Right click on the scroll bar and click on Format Control.
Format Control
Format Control

In the Format Control window-

  • Put Minimum value as 1.
  • Put Maximum value as 27. We are taking 27 here because we have 34 dates in our data set and we will display 7 days on the charts at a time. So “34 – 7 = 27“.
  • Cell link as “$J$1”

 

Format Control window
Format Control window

Now we will create support columns as Date, Sales and Data label to create this chart.

  • Create Date on column G.
    • Formula for G2 is “=INDEX(A:A,$J$1+1)
    • Formula for G3:G4 is “=INDEX(A:A,$J$1+2)
    • Formula for G5:G6 is “=INDEX(A:A,$J$1+3)
    • Formula for G7:G8 is “=INDEX(A:A,$J$1+4)
    • Formula for G9:G10 is “=INDEX(A:A,$J$1+5)
    • Formula for G11:G12 is “=INDEX(A:A,$J$1+6)
    • Formula for G13:G14 is “=INDEX(A:A,$J$1+7)
  • Create Sales column on H
    • Put formula “=IF(ISEVEN(ROW()),VLOOKUP(G2,A:B,2,0),H1)” on H2.
    • Fill down the formula for “H2:H14
  • Create Data Labels column on I
    • Put formula ” =IF(ISEVEN(ROW()),VLOOKUP(G2,A:B,2,0),””) ” on I2.
    • Fill down the formula for “I2:I14
Support Column to creating the chart
Support Column to creating the chart
  • Select the range “G1:H14”
  • Go to the Insert tab>>Charts>>Insert Line Chart(without marker)
Insert Line Chart(without marker)
Insert Line Chart(without marker)
  • After inserting the chart successfully, remove the Gridlines and add Data Labels by using Chart Elements (+button) of chart.
Chart Elements
Chart Elements
  • Right click on the data label and click on Format Data Labels.
Format Data Labels
Format Data Labels
  • In Format Data Labels window choose Label Position as Center.
  • Tick the Value From Cells available under Label Contains.
Format Data Labels Window
Format Data Labels Window
  • Data Label Range window will be opened
  • Select range I2:I14.
Data Label Range window
Data Label Range window
  • Now remove the tick from Value and Show Leader Lines available in Format Data Labels window.
Remove Value and Show Leader Lines
Remove Value and Show Leader Lines
  • Now fill the background color in data labels.
  • Select the data labels and go to Format Tab>>Shape style>>Choose a light blue color style.
Shape Style
Shape Style

 

  • Chart After formatting the data label will look like blow image.
Chart After formatting the data label
Chart After formatting the data label
  • Now select the entire chart and go to Format Tab>>Shape style>>Choose a Black color style.
Fill Black color in entire chart
Fill Black color in entire chart
  • Right click on the chart area and click on Send to back so that Scroll bar will be above the chart.
Send to back the chart
Send to back the chart
  • Put the scroll bar on the Top-Right side.
Position the scroll bar
Position the scroll bar
  • Select the data label and change the font color as Black.

Our Chart is ready will look like below image-

Dynamic Step Chart
Dynamic Step Chart

 

Click here to download Excel file of Dynamic Step Chart.

 

Watch the video tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

2 thoughts on “Dynamic Step Chart in Excel

  1. Hi PK,

    In the example Dynamic Step Chart in Excel.

    One step which says :
    Tick the Value From Cells available under Label Contains.
    Can you please let me know whow to deal this in Excel 2010.

    Regards
    Amol

    1. Hi Amol,

      Value from Cells options is not available in Excel 2010. You need to connect the data label with cells one by one. Select one data label and go to formula bar and type cell address like “=$A$1”

Leave a Reply

Your email address will not be published. Required fields are marked *