The second argument references the scroll bar's linked cell on the dashboard sheet. The first argument references the first column of data (don't reference the header cell). Then, in the top-left corner of the subset table (Dashboard!A4 in our example) enter the following function: =INDEX('Scrolling chart'!A2:A34,$B$1) First, copy the heading labels from the data range to the Dashboard sheet. We'll use a matrix of INDEX() functions to create the subset. Because we're viewing monthly values, displaying 12 points at a time seems reasonable. The easiest way to display a subset of data in a chart is to create a subset of data. To see how the scroll bar works, move the thumb or click the arrows and watch the value in the linked cell (B1) change. This cell will store the control's current value.Ĭustomize these settings to accommodate your data.
These settings determine how the control behaves: Right-click the 2003 control to access its properties. Properties determine the scroll bar's behavior, so click Properties in the Controls group and set them as shown in Figure B. In Excel 2007, select Show Developer Tab In The Ribbon and click OK. Select Developer in the Main Tabs list to the right and click OK. In the left pane, choose Customize Ribbon. Then, choose Options in the left pane or Excel Options, respectively. If the Developer tab isn't visible, click the File tab (the Office button in Excel 2007). The position isn't important right now - you can move it later. In Excel 2003, this control is on the Forms toolbar. To generate the chart, click the Developer tab and choose Scroll Bar Form Control from the Insert Controls option in the Controls group. You could work in the same sheet as the data, but because we're simulating a dashboard environment, we'll work on a new sheet called Dashboard. 2: Add the scroll bar controlĪfter you know the size of your chart window, embed a scroll bar into the dashboard sheet. We won't actually use this graph, but it's wise to create a default chart so you can see how well the chart window will accommodate the data.
In Excel 2003, choose Chart from the Insert menu. To generate the chart, select the data, click the Insert tab, click the Line option in the Charts group, and select the first (and simplest) option.
This simple line chart compensates for the numerous points by inhibiting the display of some of them. Unfortunately, there are more points than the chart can display ( Figure A). The data in the sheet named Scrolling Chart has a record for each month, beginning with January 31, 2010, and ending with September 30, 2012. If you're working with the downloadable demo, don't let those differences confuse you - they'll all be off by one column. Doing so changes many of the referenced cells and ranges throughout the first nine steps. Note: In step 10, I insert a column to center the dashboard components. While I've seen some complex techniques for employing this solution, my technique requires just 10 quick and easy steps. When this is the case, you can add a scroll bar that lets the user scroll through the data. Besides, you might have more points than even a large window can display. You can make the chart bigger, but that won't always be feasible, especially in a dashboard sheet. When this happens, Excel tries to accommodate by shaving off a few values. Sometimes a chart's underlying data doesn't fit in the chart window.
0 kommentar(er)
