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

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 or 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 when inserting new rows or columns.

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

20 thoughts on “Resetting the Scroll Bar in Excel

  1. Wendy says:

    Thank you sooooooooooo much! I have had a workbook with a tab/spreadsheet that refused to scroll properly for over a year. This fixed it!

  2. Keith says:

    Another method is just copy the data you want to keep to a new worksheet and delete the original worksheet.

    • Excel Off The Grid says:

      Sure, that works when you have a simple model. But when the worksheet with the issue is dependent on and or proceeds formulas on other worksheets it’s not a great solution; you will have to rebuild a lot of the model by pointing formulas to the new worksheet.

  3. Mary says:

    None of this worked for me. After waaaay to long of trying EVERYTHING, I figured out it was a Comment that had gotten really big due to rows being inserted and STAYEd really big, even when I just copied small chunks at a time into a different worksheet. I hope this helps someone

  4. Ian says:

    No luck with any of these for me. I have had similar issues and resolved them this way in the past, but not with the current spreadsheet I am having issues with. Strangely, once I have tried to delete the extra rows the excel workbook bloats from 6Mb to 80Mb and the scroll bars remain as they were – any ideas!?

  5. Yona Gama says:

    Thanks, option 2 followed by option 3 did the job for me. Not sure why 2 on its own wasn’t enough, even after a save and close, but it’s fixed!

  6. Laura says:

    None of these worked for me. The last time this happened, I had to copy and paste into a new workbook but I can’t keep doing that. Ugh. These scroll bars are driving me nuts.

  7. Bruce says:

    I had some issues and finally cut the data (not the whole sheet so that empty areas are excluded) and pasted to a new tab. The formulas followed since these were all dependent for all other tabs. Then i deleted the old/original tab and then renamed my new tab the same as before for consistency.

Leave a Reply

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