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](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Step-Chart.jpg)
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)](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-2.jpg)
- Right click on the scroll bar and click on Format Control.
![Format Control](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-3.jpg)
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](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-4.jpg)
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](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-5.jpg)
- Select the range “G1:H14”
- Go to the Insert tab>>Charts>>Insert Line Chart(without marker)
![Insert Line Chart(without marker)](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-6.jpg)
- After inserting the chart successfully, remove the Gridlines and add Data Labels by using Chart Elements (+button) of chart.
![Chart Elements](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-7.jpg)
- Right click on the data label and click on Format Data Labels.
![Format Data Labels](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-8.jpg)
- In Format Data Labels window choose Label Position as Center.
- Tick the Value From Cells available under Label Contains.
![Format Data Labels Window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-9.jpg)
- Data Label Range window will be opened
- Select range I2:I14.
![Data Label Range window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-10.jpg)
- Now remove the tick from Value and Show Leader Lines available in Format Data Labels window.
![Remove Value and Show Leader Lines](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-11.jpg)
- 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](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-12.jpg)
- Chart After formatting the data label will look like blow image.
![Chart After formatting the data label](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-13.jpg)
- Now select the entire chart and go to Format Tab>>Shape style>>Choose a Black color style.
![Fill Black color in entire chart](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-14.jpg)
- 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](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-15.jpg)
- Put the scroll bar on the Top-Right side.
![Position the scroll bar](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-16.jpg)
- Select the data label and change the font color as Black.
Our Chart is ready will look like below image-
![Dynamic Step Chart](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/StepChart/Dynamic-Step-Chart-1.jpg)
Visit our YouTube channel to learn step-by-step video tutorials
Watch the video tutorial: