1. Wrap Text Excel For Mac
2. Import Text File To Excel
3. Import Text Excel For Mac Scroll Bar Not Working
4. Scroll Bar In Excel Disappeared

Sometimes a chart's underlying data doesn't fit in the chart window. When this happens, Excel tries to accommodate by shaving off a few values. You can make the chart bigger, but that won't always be feasible, especially in a dashboard sheet. Besides, you might have more points than even a large window can display. When this is the case, you can add a scroll bar that lets the user scroll through the data. While I've seen some complex techniques for employing this solution, my technique requires just 10 quick and easy steps.

Look for a file that is named Excel Toolbars (12) or Microsoft Excel Toolbars. If you locate the file, move it to the desktop. If you cannot locate the file, the application is using the default preferences. If you locate the file and move it to the desktop, start Excel, and check whether the problem still occurs. Learn everything you need to master the Mac 2016 version of the world's most popular spreadsheet program. Curt Frye provides a comprehensive overview of Excel for Mac 2016, including manipulating workbook and cell data, using functions, printing worksheets, and collaborating with others.

Note: In step 10, I insert a column to center the dashboard components. Doing so changes many of the referenced cells and ranges throughout the first nine steps. If you're working with the downloadable demo, don't let those differences confuse you — they'll all be off by one column.

1: Determine your needs

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. Unfortunately, there are more points than the chart can display (Figure A).

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. In Excel 2003, choose Chart from the Insert menu. 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.

2: Add the scroll bar control

After you know the size of your chart window, embed a scroll bar into the dashboard sheet. 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. To generate the chart, click the Developer tab and choose Scroll Bar Form Control from the Insert Controls option in the Controls group. In Excel 2003, this control is on the Forms toolbar. Drag the scroll bar to size it. 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). Then, choose Options in the left pane or Excel Options, respectively. In the left pane, choose Customize Ribbon. Select Developer in the Main Tabs list to the right and click OK. In Excel 2007, select Show Developer Tab In The Ribbon and click OK.

3: Set the scroll bar's properties

Properties determine the scroll bar's behavior, so click Properties in the Controls group and set them as shown in Figure B. Right-click the 2003 control to access its properties. Then, click OK to return to the sheet. These settings determine how the control behaves:

Wrap Text Excel For Mac

• Current Value: 1 — Stores the control's current value; 1 is an anchoring value.
• Minimum Value: 1 — The control's first value.
• Maximum Value: 33 — Initially, use the number of records in your data range.
• Incremental Change: 1 — This is the number of data points the scroll bar will scroll with each click (increasing or decreasing the current value).
• Page Change: This represents the number of data points displayed. Because we're displaying monthly values, 12 seems appropriate.
• Cell Link: B1 — Use any empty cell you like. This cell will store the control's current value.

Figure B

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.

4: Generate the charted data

The easiest way to display a subset of data in a chart is to create a subset of data. Because we're viewing monthly values, displaying 12 points at a time seems reasonable. We'll use a matrix of INDEX() functions to create the subset. First, copy the heading labels from the data range to the Dashboard sheet. Then, in the top-left corner of the subset table (Dashboard!A4 in our example) enter the following function:

The first argument references the first column of data (don't reference the header cell). The second argument references the scroll bar's linked cell on the dashboard sheet.

5: Copy the formulas

After entering the anchor formula, copy it to column B. Then, copy the formulas in cells A4:B4 to A5:B15. Figure C shows the results: a matrix that comprises the first 12 rows of data in the original data range.

Figure C

With the scroll bar's value set to 1, the matrix displays the first 12 rows of data.

6: Spot a problem!

At this point, you can use the scroll bar control to update the matrix values and subsequently, the chart. For instance, click the right arrow five times and the matrix displays rows 6 through 18 instead of rows 2 through 13. As you click the control's arrows, the value in the linked cell (B2) changes, updating the second argument in the INDEX() functions.

Figure D shows what happens when you move the scroll bar to the far right — you get several rows of invalid data. You can't let the scroll bar display the last record as the first row of data in the matrix. Instead, the last available view should display the last 12 records (rows 23 through 34).

Figure D

The scroll bar goes too far.

7: Tweak a property

Fortunately, the fix is easy. Right-click the scroll bar and choose Format Control from the resulting submenu. Change the Maximum Value property to 22 and click OK. Now, the last possible view displays the last 12 rows of data, as shown in Figure E. 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.

Figure E

The matrix should always display 12 valid records.

8: Chart the matrix

Instead of charting the original data range, you chart the matrix on the Dashboard page. 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. Remember, the matrix doesn't have to be next to the chart. I'm doing so to simplify the example, but you can display both or just the chart on your dashboard.

Figure F

The chart displays the matrix, not the original data range.

With the pieces in place, use the scroll bar to update the chart. To do so, click the arrows or drag the thumb. As shown in Figure G, the matrix and the chart both adjust appropriately.

Figure G

Import Text File To Excel

The chart displays the matrix values, which change as you manipulate the scroll bar.

10: Add a bit of polish

The technique is now complete, but you'll want to position and format the chart and the scroll bar advantageously. You might have noticed that the horizontal axis isn't displaying the actual dates from column A. If that matters, fix it as follows:

1. Right-click the horizontal axis and choose Format Axis.
2. Under Axis Type (in the Axis Options pane, which should be the default), click the Text Axis option.
3. Click OK.

You might consider enlarging the control's width to match the chart. I also inserted a new column to the left of the matrix to center the components a bit. (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:

1. Right-click B1, the linked cell that stores the scroll bar's current value, and choose Format Cells.
2. On the Protection tab, uncheck Locked.
3. From the Format drop-down in the Cells group (on the Home tab), choose Protect Sheet.
4. Select only the Select Unlocked Cells option and click OK. (Enter a password if you like.)

Import Text Excel For Mac Scroll Bar Not Working

After protecting the sheet, shown in Figure H, the only thing you can select is the scroll bar.

Figure H

Scroll Bar In Excel Disappeared

Polish the sheet to make it a bit more dashboard-like — and don't forget to protect the sheet.