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:
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.
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.
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
Related posts:
- Images and Pictures in Charts - Excel Graphs Quick an easy method of making your Excel graphs more...
- Automatic Updating “Dynamic” Charts in Excel One of the most time consuming parts of producing a...
- How to Create a Pareto Chart in Excel Creating Pareto Charts...
- Removing #N/A error in Excel using ISNA() The #N/A error happens most often when a lookup function...
- User Passwords in Excel - Multiple Passwords Simple multiple user password protection in Excel....