Home>Blogs>Charts and Visualization>Dynamic Step Chart in Excel
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.

Dynamic Step Chart in Excel
Dynamic Step Chart in Excel

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

 

 

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

 

Watch the video tutorial:


PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com

Leave a Reply