Hide/Unhide Scroll Bars and Reset the Vertical Slider Range in Excel

Hide, Resize, and Repair Scroll Bar and Slider in Excel
© Ted French

Scrolling in Excel refers to moving up-and-down or side-to-side through a worksheet using the scroll bars, the arrow keys on the keyboard, or the scroll wheel on the mouse.

By default, Excel displays horizontal and vertical scroll bars along the bottom and right side of the Excel screen as shown in the image above.

Hiding/Viewing Scroll Bars

If you are hiding the horizontal scroll bar in order to increase the viewing area of the worksheet, you will need to uncheck the Show sheet tabs option as well as the horizontal scroll bar. This will remove the bottom bar of the Excel window frame.

To hide the horizontal and/or vertical scroll bars in recent versions of Excel (since Excel 2010):

  1. Click on the File tab to open the File menu;

  2. Click on the Options button in the menu to open the Excel Options dialog box;

  3. In the dialog box, click on Advanced in the left-hand pane to open the Advanced Options pane on the right-hand pane;

  4. In the advanced options, scroll down to the Display options for this workbook section - (about half way down)

  5. Check (show) or uncheck (hide) the Show Horizontal Scroll bar and/or Show Vertical Scroll Bar options as required.

  6. Click OK to close the dialog box and return to the worksheet.

Resize the Horizontal Scroll Bar

If the number of sheets in a workbook increases to the point that the names of all sheets cannot be read at one time, one way to fix this is to shrink the size of the horizontal scroll bar.

To do this:

  1. Place the mouse pointer over the vertical ellipsis (three vertical dots) next to the horizontal scroll bar;

  2. The mouse pointer will change to a double-headed arrow - as shown in the image above when it is correctly positioned;

  3. Press and hold down the left mouse button and drag the pointer to the right to shrink the horizontal scroll bar or to the left to enlarge the scroll bar.

Fixing the Vertical Scroll Bar Slider Range

The slider in the vertical scroll bar—the box that moves up and down the scroll bar—changes in size as the number of rows in a worksheet containing data changes.

As the number of rows increases, the size of the slider decreases.

If you have a worksheet with a fairly small number of rows containing data, but the slider is very small and moving it even the slightest amount causes the worksheet to jump up or down hundreds if not thousands of rows, it is likely caused by a row or even a single cell far down the worksheet that has been activated in some way.

Fixing the problem involves finding and deleting the row containing the last activated cell.

Activated cells do not necessarily contain data. Changing the alignment of a cell, adding a border, or even applying bold or underline formatting to an empty cell is enough to activate a cell, and this can make finding and removing the row containing that cell tricky.

Finding the Last Active Row

The first step is to make a backup copy of the workbook. Later steps involve deleting rows in the worksheet, and if rows containing good data are accidentally deleted, the easiest way to get them back is to have a backup copy.

To find the last row in the worksheet containing a cell that has been activated:

  1. Press the Ctrl + Home keys on the keyboard to move to cell A1 in the worksheet.

  2. Press the Ctrl + End keys on the keyboard to move to the last cell in the worksheet. This cell will be the intersection point between the lowest activated row and the rightmost activated column.

Deleting the Last Active Row

Since you can't be sure that other rows have not been activated between the last row of good data and the last activated row, the surest course is to delete all rows below your data and the last activated row.

Be sure to select entire rows for deletion by clicking on the row header with the mouse or by pressing the Shift + Space keys on the keyboard.

Once the rows have been selected:

  1. Right click on the row header of one of the selected rows to open the context menu;

  2. Click on Delete, in the menu to delete the selected rows.

Check Before You Delete

Before deleting any rows, make certain that what you believe to be the last row of valuable data is, in fact, the last row of valuable data, especially if the workbook is used by more than one person.

It is not uncommon to hide data out of the current work area, so it is advisable to do a thorough search and before proceeding with the deletion.

Save the Workbook

After deleting all those rows, the last step is to save the workbook. Until the workbook is saved, there will be no change in the size and behavior of the slider in the scroll bar.