Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Resetting the scroll bar in Excel

Resetting Scrollbar - Featured Image

Have you ever experienced the issue where Excel’s scroll bars become tiny?  A small movement in the mouse can change the view by hundreds of rows or columns.  This makes the worksheet exceptionally difficult to navigate around.

The problem might reveal itself with an error message when trying to insert new rows or columns.

Error message shows Resetting Scrollbar required

In this post, we’ll look at why this happens and the different options for resetting the scroll bar.

Watch the video:

Watch the video on YouTube

Why does the tiny scroll bar occur?

Two potential factors determine the size of the scroll bar:

  • Size of the used range
  • Size and placement of worksheet objects

Let’s look at each of these in detail.

Size of the used range

The sizes of the horizontal and vertical scroll bars is normally determined by the dimensions of the used range.  This is the range of cells containing any values, formulas, formatting, etc.  For example, if a worksheet has only two changes (1) Cell A1 contains the letter “A” (2) Cell F10 is empty but has been formatted, then the used range is A1:F10.

Occasionally, the used range can become excessively large, which is nearly always caused by user error.  A user might accidentally stray into cells outside of the area needed then make some changes.

For example, press Ctrl + Down Arrow and suddenly the cell the bottom of the spreadsheet is in full view and selected.  Formatting that cell, or entering data into it by accident will result in that cell, and every cell above or to the left of it being in the used range.  This is an extremely easy mistake to make (I know, I’ve made it myself many times).  Once this has happened, the scroll bar needs to be tiny as the worksheet might now be 1,048,576 rows high or 16,384 columns wide.

Size and placement of worksheet objects

The scroll bar size can also be determined by the bottom right position of a worksheet object.  That object could be a comment/notes box, a chart or shape, etc.  The scroll bars must be big enough to enable the user to view the full width or height of these objects.

Depending on the settings applied, inserting new rows or columns within the area occupied by the object, could result in that object’s size increasing.  Even if the objects are not visible, they still occupy a location on the worksheet, and the scroll bar size will be set accordingly.

Fixing problems caused by the used range

The used range is the most likely cause of issues with the scroll bars, so we will tackle this first.

Option 1: Press Esc & Undo

If you suddenly find yourself in parts of the worksheet you do not wish to populate with data, 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 below.

Option 2: Clear all the unnecessary content & save

Looking at your spreadsheet, find the cell you believe should 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.  Click Home > Clear > Clear All

Home - Clear - Clear All from Ribbon

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.  Then, once again, click Home > Clear > Clear All.

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

If that doesn’t work, repeat the same process, but delete the unnecessary rows by using Home > Delete > Delete Sheet Rows and Home > Delete > Delete Sheet Columns



Option 3: Clear all the unnecessary content & reset used range

Some users report that an additional step is required.

Follow the steps from Option 2 to clear the unnecessary content.  Open the Visual Basic Editor from the Developer tab (If you do not have the Developer tab visible, press Alt + F11).  Ensure the Immediate window is open by selecting View > Immediate Window (Ctrl + G).

Display Immediate window in Developer tab

In the Immediate Window, type the following and press Enter.

ActiveSheet.UsedRange

ActiveSheet.UsedRange in Immediate Window - Resetting the Scrollbar

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

Fixing problems caused by worksheet objects

While it is less likely that worksheet objects are the cause of scroll bar issues, if none of the above options work, then this might just be your problem.

Option 4: Identifying comments / notes

Comment/note boxes are often identified in the worksheet with a small red triangle in the corner of the cell.  However, depending on your specific settings, they might not be visible at all.

From the ribbon, click File > Options.

File - Options to Open Advanced Window

The Excel Options window will open.  From the Advanced section, scroll to the Display sub-section and select Indicators and notes, and comments on hover.  In older versions of Excel, the description will be Comment and indicators.  Click OK to close the Excel Options window.

Display indicators and notes

All the comments/notes will now be visible.  Delete or resize any which are outside of the range you wish to use.

Option 5: Identifying charts, shapes and other objects

Usually, charts, shapes, and other objects on the face of the worksheet are visible.  Just by scrolling around, we could identify if these are affecting the scroll bars.  However, these objects can be made invisible; therefore, they impact scroll bars but can’t be seen.

To solve this, click Home > Find & Select > Selection Pane

Display Selection Pane

The selection pane will open with all the worksheet objects are listed.  The eye icon with a line through indicates the item is not visible.

Selection window - hidden chart

Make all items visible.  It should now be possible to find any objects affecting the scroll bars; delete or resize them as required.

Anything else?

This list of options is not exhaustive; they are just solutions that I know about.  If you know of, or identify any others, please share in the comments.  Hopefully, we can all learn together and ensure that nobody needs to suffer from this problem going forwards.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

30 thoughts on “Resetting the scroll bar in Excel

  1. RJ says:

    Fantastic guide for detective work. In my case, it was invisible comments very far down in the sheet, so I had to go through every step. No idea how they got there, but it’s a very old worksheet with over 12,000 rows. Problem solved.
    Thank you!

    • Excel Off The Grid says:

      That’s pretty much what we want to avoid. Depending on how complex your spreadsheet that could take a long time to rebuild all the cell links and references.

  2. Gina says:

    Thanks great information!

    My issue ended up being in my VBA. I didn’t restrict my column range. Once I corrected that my scroll bar was correct.

  3. Caroline says:

    I can’t be sure exactly which solution cured my problem but a combination of clearing all formats in unused cells (I’m a sucker for a conditional format!), resetting the ranges of conditional formats and checking and adjusting the ranges my macros applied to seems to have – finally! – fixed the issue. Thanks so much for the comprehensive suggestions given here which served as a catalyst to try a few other things (most notably, the conditional formats and the macros). I know I’ve been guilty in the past of applying CF’s to entire columns simply through laziness. Lesson learned…….

  4. fyreflow says:

    Lifesaver! Thank you!

    The first few steps didn’t help – I think this was still happening due to additional rows I had already deleted a long time ago. But the VBA tip was exactly what was needed to finish the job. I’m now back to scrolling through 10k rows instead of 100k.

    Still using Office 2010, in case anyone was wondering whether this works for the legacy versions, too.

  5. Grishelda Marks-Webster says:

    Hello,
    This is a different subject matter but your assistance will be greatly appreciated.
    I know how to set the scroll area so I won’t fly down to row 1245. How do I save these changes. Whenever I close the workbook, the scroll range is deleted.
    Thank you

    • Excel Off The Grid says:

      Hi Grishelda – are you saying that the scroll bar is reset, but then it’s not saved? And you have to keep resetting to scrollbar over and over? That’s a very odd situation.

  6. Eric says:

    ‘Found a piece of text in one cell way down the sheet (don’t know how it got there). Tried all the techniques above except for the VBA coz I didn’t want to mess with something I’m unfamiliar with.

    What solved the problem was when I changed the view to Page Break view and then back to Normal view. Quick and easy!

  7. Donn Fishburn says:

    I had one sheet in a workbook on which the slider in vertical scroll bar filled the entire scroll bar. That sheet had a note and arrow to remind me of a particular step. Those were outside the print area. After seeing Option 4 I moved those objects inside the print area and the scroll bar corrected itself. Interesting that just for the heck of it I moved those items back OUTSIDE the print area and the scroll bar continues to display correctly.

  8. Donn Fishburn says:

    Addition to my comment. After closing and reopening the workbook the scroll bar was again screwed up. Moving the objects inside the print area and clicking any cell again corrected the problem. Close and open and the problem is back. This is a workbook I rarely use which is the reason for the reminder. In the future I will simply remove the reminder when I use the workbook.

  9. John Worthington says:

    Good.
    You may want to add – to find notes on a very large spreadsheet – to find notes, in Review Menu, Find Next Note

Leave a Reply

Your email address will not be published.