Stock Chart with Scroll and Zoom

Stock charts come in various shapes. There are candlestick stock charts, line and bar stock charts, “Kagi” stock charts, moving average stock charts and everything in between. For a financial service provider how to present financial information pertaining to a specific stock can a critical question. On one hand, if you give too many options, you loose out on user comfort and increase API complexity. On the other, give too few options and you end up losing more demanding customers. Interesting to note is that both Yahoo and Google tend to use the same type of stock chart displays though personally I tend to use the former.


This stock chart has the ability to scroll across historical share price data and yet allows the user to zoom into any specific portion that may need to be studied with greater detail. This is something that would’ve taken me more than a few page refreshes to achieve using a conventional stock chart (something that is still used by a number of financial service providers even today). And that got me thinking, is there a way to make a stock chart in excel that has both these abilities. The good news is that I was able to get pretty close to creating a similar looking stock chart with scroll and zoom.


The stock chart above that has two controls – the first one allows to you move across a timeline and view changes in share price and volume over the complete time horizon. The second control allows you to control the amount of data you see in the chart. You can move the slider and compress or expand the time duration for which data is shown in a single frame. The obvious advantage of using this type of a chart is that rather than having a large number of smaller charts to study the stock’s behavior over a period of time, you can use a single chart to view the entire available data in one go and narrow down into specific portions the stock chart that warrant closer scrutiny.

To create a stock chart in Excel with scroll and zoom functionality, follow these steps:

1. Place the data required for the stock chart (stock price and volume in our case) in an excel spreadsheet. Ensure that the data is clean and free from errors. While placing the data in the excel spreadsheet, ensure that the successive data elements are placed in adjacent columns. Although it is not a mandatory step, it will help the data layout look neat and help debug formulae without errors.

2. Create another blank range which is of the same width and height as the original data set (in point 1).

3. Now in the first cell of the new range (range2) , use the OFFSET function to offset the corresponding cell in original data set by X number of rows (hint use =OFFET(original_cell, X_rows_to_offset, 0)). Copy this formula to all cells in the range such that each cell in range2 is offset by the same number of rows. Now, the exact number of rows to offset (represented by X) will be controlled by a particular cell. You can use any cell in the spreadsheet for that purpose such that it does not interfere with the chart data. Replace X_rows_to_offset in the above formula with the address of the cell that controls the offset. In step 9 we will get to make this cell change its value dynamically by attaching this to a scroll bar.

4. Create a chart in the spreadsheet using ‘Insert’ -> ‘Chart’ option from the menu.


5. Format the chart as per your needs (you can use bar charts, line charts, box plots or any other chart type that serves your purpose and the available data.)

6. Enable the Forms Toolbar by clicking ‘View’ -> ‘Toolbars’ -> ’Forms’ from the menu.

7. Important – Click anywhere on the chart window. From the Forms Toolbar, select the scrollbar icon. Now draw a neat little scrollbar within the chart area.

8. Double click the scrollbar and in the ‘Controls’ tab choose the ‘Cell Link’ box. Select the cell we choose the cell that controls the offset value of the cells in range2 (refer step 3). Provide the minimum value as 1 and the maximum value as the count of the number of rows that you have in your original data set.


9. Now press Esc a couple of times. Your stock chart should be nearly ready at this point. Simple play around with the scrollbar, drag it around and you will see that the chart scrolls along the x-axis. If nothing happens, check through all the previous points especially 3 and 8.

10. Once you have the scroll functionality working on the stock chart, its time to add the functionality to stretch the graph. The ability to stretch or collapse a time frame allows the user to narrow down or broaden the amount of historical stock price data that he/she gets to see at one go. It can be useful to quickly identify points of interest and then analyze them in greater detail.

11. To add the stretch-collapse functionality to the stock chart, repeat step 7 to add another scrollbar within the chart.

12. For the second scrollbar, repeat step 9. Select the linked cell as a new cell adjacent to the one linked to the first scrollbar. The minimum and maximum values should be somewhere between 7 and the number of days for which you’d like to see the data together.

13. Now comes the tricky part. To ensure that our data points in the stock chart expand/contract as per the user needs, we will have to delete the earlier data series and create a dynamic named range that stretches and contracts dynamically.

14. Create a new named range using “insert’ -> ‘Name’ -> ‘Define’ from the menu. In the Define Name box, enter the following formula (=OFFET(original_cell, 0, 0, Y_rows_to_offset, 0)). This is a slightly different variation of the OFFSET function which lets you expand or contract a range by a certain amount of rows or columns. (See named range for more on this topic). You will have to create as many of these named ranges as the number of data series you want to show on your chart (plus one for the label). The ‘original_cell’ here is the first cell of each of the corresponding data series (first cell of each column of range2) in the worksheet.

15. Right click on the stock chart and choose ‘Source data’. For each of the data series, edit the source data by replacing the source range reference with the name of the corresponding dynamic named ranges that you created in step 14. (Caution – If the named range does not result in a valid range, this will cause problems)

16. Click ok. Now play around with the second scrollbar and you should see the stock chart stretch or expand as you drag it with the mouse. If it does not, go through steps 11 to 15 again.

If you followed all these steps closely, you would have observed that we did not use VBA code anywhere in this example although I may add, that the results was as good looking as the Google stock chart.

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Peter wrote:

    Great idea! I would like to use this with stock candlestick charts of price & volume data that vary considerably in range (particularly following the GFC). So 2 questions: 1. Will this work with Excel (stock) candle charts, and 2. Is there any way to make the scales on the chart auto-adjust to suit the data being viewed in the chart window? If so, this would be very useful.

  2. Ajay wrote:

    Hi Peter,

    Fortunately the answer to both your questions is yes. Till the time the base data changes as the user moves the scrollbar around, any chart will work including the candle-stick stock chart.

    To get the scales to auto scale, just double click the scales (Excel 2003) and check the “Maximum” under the “Scale” tab. However that will cause the stock chart to “jump up and down” which may work well with the ones with higher volatility.

    Hope this helps,

  3. Jonna wrote:

    Wonderful. That’s what I was looking for.

  4. Daniel Ferry wrote:

    I’ve just emailed you a new version of your dynamic stock chart that is way more efficient. Instead of 9000 formulas it uses 3. It requires no supporting columns and scales to as many records as your version of Excel will allow in a sheet.

    The key is to use named formulas for the chart and NOT cell references.

  5. Ian wrote:

    Peter, I am so glad I stumbled on this. You definitely flattened the learning curve. And Daniel, any chance I could see your modified version?

  6. jili wrote:

    Hi, I really need to do this for my chart but I couldn’t get the step 3. would you please explain it for me alittle bit more.
    Thanks in advance

  7. Robert wrote:

    Great website. I have learned a lot browsing in just the last hour.

    I see in an earlier comment that Daniel Ferry had improved upon this scrolling method. I looked for an updated posting where this could be shown but didn’t see one. I would be very interested in it because it takes up much less space. My data sets are 120 columns by 18,000 rows. Creating additional columns is something I would like to avoid at all costs.

  8. Henna Sharma wrote:

    How to manage stacked column? I have one value field which is very large as compared to others, so I am not able to compare the other data among themselves. For example, say I have data that looks like, 5, 7, 11,4, 13, 100… Now because of this 100 all the other data looks like of the same size…
    Also I am creating the chart using Pivot tables, so all the formatting of normal chart is applicable, but I am not able to change the order of the columns. For eg, my column labels are months- Jan, Feb, Mar, Apr.. etc… but in the pivot they are getting arranged based on alphabetical order rather than on month basis. Pivot is not allowing me to change the column order. How to do that
    Please reply.



Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel