Resetting the scroll bar in Excel (5 solutions)

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.

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

Table of Contents

• Why does the tiny scroll bar occur?
• Size of the used range
• Size and placement of worksheet objects
• Fixing problems caused by the used range
• Option 1: Press Esc & Undo
• Option 2: Clear all the unnecessary content & save
• Option 3: Clear all the unnecessary content & reset used range
• Fixing problems caused by worksheet objects
• Option 4: Identifying comments / notes
• Option 5: Identifying charts, shapes and other objects
• Anything else?

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

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).

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

ActiveSheet.UsedRange

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.

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.

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…

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.

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.

---

What next?

Discover how you can automate your work with our Excel courses and tools.