For example, the shortcut for Edit Cell in Windows is F2, and on a Mac, its Control + U. Finally, some Excel shortcuts are just plain different on a Mac. In programming parlance, this is known as the 'used range' of a worksheet.Just different. Suddenly your scroll bars move you into uncharted areas, such as column TX or row 5,000. It's frustrating when Excel acts as if the active area of a worksheet is significantly larger than the actual area where you have data.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.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. Unfortunately, there are more points than the chart can display ( Figure A).This simple line chart compensates for the numerous points by inhibiting the display of some of them.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. 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. 906 deleting, 910911 filtering, 908909 organizing, 910911 renaming.
The position isn't important right now - you can move it later.If the Developer tab isn't visible, click the File tab (the Office button in Excel 2007). Drag the scroll bar to size it. 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. 3: Set the scroll bar's propertiesProperties 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. ![]() This cell will store the control's current value.Customize these settings to accommodate your 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. Cell Link: B1 - Use any empty cell you like. Because we're displaying monthly values, 12 seems appropriate. Online games for mac and windowsThen, 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)The first argument references the first column of data (don't reference the header cell). 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. 6: Spot a problem!At this point, you can use the scroll bar control to update the matrix values and subsequently, the chart. Figure C shows the results: a matrix that comprises the first 12 rows of data in the original data range.With the scroll bar's value set to 1, the matrix displays the first 12 rows of data. Then, copy the formulas in cells A4:B4 to A5:B15. 5: Copy the formulasAfter entering the anchor formula, copy it to column B. 7: Tweak a propertyFortunately, the fix is easy. Instead, the last available view should display the last 12 records (rows 23 through 34).The scroll bar goes too far. You can't let the scroll bar display the last record as the first row of data in the matrix. Figure D shows what happens when you move the scroll bar to the far right - you get several rows of invalid data. As you click the control's arrows, the value in the linked cell (B2) changes, updating the second argument in the INDEX() functions. Remember, the matrix doesn't have to be next to the chart. To do so, select the matrix range - A3:B15 - and use the instructions in step 1 to embed a line chart in the Dashboard sheet, as shown in Figure F. 8: Chart the matrixInstead of charting the original data range, you chart the matrix on the Dashboard page. If you add or delete records, you must update this property by increasing or reducing the setting by the number of records you add or delete.The matrix should always display 12 valid records. Now, the last possible view displays the last 12 rows of data, as shown in Figure E. Change the Maximum Value property to 22 and click OK. You might have noticed that the horizontal axis isn't displaying the actual dates from column A. 10: Add a bit of polishThe technique is now complete, but you'll want to position and format the chart and the scroll bar advantageously. As shown in Figure G, the matrix and the chart both adjust appropriately.The chart displays the matrix values, which change as you manipulate the scroll bar. To do so, click the arrows or drag the thumb. 9: Use the scroll barWith the pieces in place, use the scroll bar to update the chart. (As I noted earlier, this means that the referenced cells throughout this article won't match the downloadable demo.) In addition, you might want to inhibit the sheet visuals by clicking the View tab and unchecking the Gridlines, Formula Bar, and Headings options in the Show group.Finally, protect the sheet contents as follows: I also inserted a new column to the left of the matrix to center the components a bit. Under Axis Type (in the Axis Options pane, which should be the default), click the Text Axis option.You might consider enlarging the control's width to match the chart. Right-click the horizontal axis and choose Format Axis. Excel Spreadsheet Delete Scrollbar In Excel Password If You(Enter a password if you like.)After protecting the sheet, shown in Figure H, the only thing you can select is the scroll bar.Polish the sheet to make it a bit more dashboard-like - and don't forget to protect the sheet. Select only the Select Unlocked Cells option and click OK. From the Format drop-down in the Cells group (on the Home tab), choose Protect Sheet.
0 Comments
Leave a Reply. |
AuthorMike ArchivesCategories |