This post may contain affiliate links. Please read my disclosure for more info:

Resetting the Scroll Bar in Excel

Resetting the scroll bar in Excel Thumb

Resetting the scroll bar in Excel

The horizontal and vertical scroll bars in the Excel window are set by the size of the used range (the range of cells containing any value, formula, formatting etc).  Occasionally, this range can become excessively large (normally by accident).  As a result, the scroll bar becomes tiny.  A small mouse movement in the scroll bar can move the screen view by hundreds of rows of columns.  This makes the worksheet difficult to navigate around.

If the used range goes all the way down or all the way across, this problem might reveal itself with an error message.

Reset scroll bars - Error message

I think every Excel user will have experienced this frustrating issue at some point.

This post covers four options to fix this problem:

  • Esc & Undo
  • Delete the cells and save
  • Delete the cells and run macro
  • Do it all with a macro

But firstly, let’s briefly look at why this occurs.

 

Why does the tiny scrollbar occur?

This issue is nearly always caused by user error.  A user might accidentally stray into cells way outside of the area needed.

Press Ctrl + Down Arrow and suddenly a cell the bottom of the spreadsheet is in full view and selected, ready for input.  Formatting that cell, or typing into it by accident will now include that cell, and every cell above or to the left of it in the used range.  Once this has happened, the scroll bar has to be tiny as the worksheet might now be 1,048,576 rows high or 16,384 columns wide.

This is just one example of potential user error which can cause this to happen.

 

Option 1 – Press Esc & Undo

If you suddenly find yourself in parts of the workbook you do not wish to populate with information try this first:

  • Press the Escape key to exit data entry for any cell which is selected
  • Keep pressing Ctrl + Z to undo any changes made to those cells.
  • Press Ctrl + Up Arrow or Ctrl + Left Arrow to get the selected cell back to a ‘normal location’

You might be lucky, if Excel deems you’ve not changed the used range, the scroll bars will return to normal size (yay!!! whoop whoop).  If you’ve been unlucky (boo!!!) then move onto Option 2 or Option 3 below.

 

Option 2 – Delete the unnecessary content & save

Looking at your spreadsheet, find the cell you believe should actually be the last used cell (remember to look in hidden rows and columns too).  Select the entire row below this cell.  Press Ctrl + Shift + Down Arrow to select all the rows down to the bottom.  From the Home ribbon select Editing -> Clear -> Clear All

Reset Scroll Area - Contents Clear All

Select the entire column to the right of what should be your last used cell.  Press Ctrl + Shift + Right Arrow to select all the columns to the right. From the Home ribbon select Editing -> Clear -> Clear All

Now all the unnecessary content has disappeared, save the document (Ctrl + S). The used range has now been reset, and the scrollbars should return back to a more usable size.

 

Options 3 – Delete the unnecessary content & run macro

Follow the steps from Option 2 to clear the unnecessary content.  Open the Visual Basic Editor (Alt + F11).  Ensure the immediate window is open by selecting View -> Immediate Window (Ctrl + G).

Reset Scrollbar - Immediate Winow

In the Immediate Window type the following and press Enter.

ActiveSheet.UsedRange

Reset Scrollable - ActiveSheet.UsedRange

The used range has been reset, when returning to Excel, the scroll bars should return back to a more usable size.

 

Option 4 – Do it all with a Macro

The following Macro code will delete all the rows below and the columns to the right of the Active Cell, then reset the scroll bars.  Enter the code into a Module of the Visual Basic Editor.

Sub ResetScrollbars()

Dim cellSelection As Range
Set cellSelection = ActiveCell.Offset(1, 1)

Range(cellSelection, cellSelection.End(xlDown)).EntireRow.Clear
Range(cellSelection, cellSelection.End(xlToRight)).EntireColumn.Clear
ActiveSheet.UsedRange

End Sub

Save

Save

Leave a Reply

Your email address will not be published. Required fields are marked *