There are many times when you may have a lot of data that does not look right when graphed all together. 300 data points is probably too many for a small chart. So what is the solution…scrolling charts! I will show you in this article how to create a scrolling chart without using dynamic ranges. This is a more simple technique for most Excel users, but for the more advanced, I would recommend you check out this technique.

To begin with, I have made the scrolling chart workbook available for download here:

Scrolling Charts

Look at the picture below and set your workboook up in the same fashion. In column B enter your time periods…Could be days of the week, months, years or other. In column C enter your data. Column A will contain what I call “labels”. The labels are a count of your time periods.

scrolling graph

Next, place a scroll bar on your worksheet (available on “forms” toolbar, view=>toolbars=>forms). Right click the scroll bar and choose “Format control”. On the control tab enter 1 for the current value, min value = 1, max value = number of labels, incremental change = 1, page change = 10, cell link = $D$10.
format control

Cell D1 will contain the result from the scrollbar. This is the key to making this method work.

In D3 type = D1, in D4 type =D3+1 then drag the D4 formula to the bottom of your data. In columns E and F write vlookup formulas to lookup the D column on the table A3:C17. For example, the E3 formula is =VLOOKUP(D3,$A$3:$C$17,2,FALSE) and the F3 formula is =VLOOKUP(E3,$B$3:$C$17,2,FALSE). These formulas can simply be dragged down. See the workbook for more info.

The next step is to create a column or line chart using the data in columns E and F. Do not select all of the data or there will be nothing to scroll through. In the example workbook, I selected E3:F11.

That is it! As you scroll, the number in D1 should increase. and the chart should scroll towards the last available date. I added the trend line to see what the trend was on the moving 9 month range.

Joseph Harris
Excel Help

If you found this article helpful, buy me a cup of coffee!

Related posts:

  1. Images and Pictures in Charts – Excel Graphs Quick an easy method of making your Excel graphs more...
  2. Automatic Updating “Dynamic” Charts in Excel One of the most time consuming parts of producing a...
  3. How to Create a Pareto Chart in Excel Creating Pareto Charts...
  4. Removing #N/A error in Excel using ISNA() The #N/A error happens most often when a lookup function...

This entry was posted on Friday, March 13th, 2009 at 12:05 pm and is filed under Business Analyst, Excel Hints. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

You must be logged in to post a comment.