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.
Watch the video:
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
Generate accurate VBA code in seconds with AutoMacro
AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.
Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.
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.
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.
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.
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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the ‘Excel Ninja’ in your office. It’s amazing what things other people know.
- 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.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don’t go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: