Keep your chart compact while still including plenty of data with a scroll bar
by Kara Hiltz
Application:
Microsoft Excel 2002/2003/2004/2007
Operating Systems:
Microsoft Windows, Macintosh
Charting always seems to lose its glamour when you realize that you somehow need to squeeze dozens — or even hundreds — of data points into a chart without letting it grow to monster proportions. You can save space and impress your supervisor by linking a scrollbar to your chart. A scrollbar lets you cycle through all of the charted data while keeping your chart’s size under control.
To create a chart that scrolls through dozens of data points, we’ll:
• Build a line chart that uses named ranges to automatically update when we add or remove data from its source.
• Insert a scrollbar below the line chart and link it to our spreadsheet’s data.
• Modify our named range’s formula so that it cycles through our data table starting with the most recent dates.
• Discuss how to adjust the chart and the scrollbar when we make changes to the data table.
You should always create easy-to-read, eye-catching Excel charts, but this task proves especially challenging when you’re charting large data sets. Take a lesson from the web and give your chart the ability to span several screens — without actually taking up that much space. We’ll show you how you can transform a chart with flexible data ranges into a chart that scrolls through its data, as shown in Figure A.
A:
You can scroll through endless data points without adjusting your chart’s size.
Build off flexible data ranges
Our technique in this article builds off the flexible chart data ranges we set up in the article “Build a chart that updates with your data,” from the October 2009 issue. We’ll be using the OFFSET and COUNTA functions again with a few modifications to link our flexible chart to a scrollbar.
Set up the sample data and named ranges
Let’s suppose you keep daily logs of each register’s sales in your shop so you can analyze trends in your sales. This data table will grow very quickly, which means charting it will present a challenge. First, we’ll set up a chart that updates when we add new data.
To define named ranges:
1. Launch Excel and open our sample workbook, or create a new worksheet based on the data in Figure B.
2. Select cell A3 and choose Insert | Name | Define from the menu bar to open the Define Name dialog box.
3. Type Date in the Names In Workbook text box.
4. Enter the formula =OFFSET($A$3,0,0,COUNTA($A:$A)-2) in the Refers To text box.
5. Click the Add button to add the named range to the list box and then click Close.
B:
Add as much data as you’d like to your sample worksheet to see our chart scrollbar’s effectiveness.
6. Select cell B3 and open the Define Name dialog box again; type Sales in the Names In Workbook text box.<
