When working in Excel, sometimes you might notice that the scroll ranges of the horizontal or vertical scroll bars on the worksheet are too long and don’t match the data range, as in the case shown below:
Why is Your Scroll Bar Too Long?
A longer scroll bar indicates that there is data in far-off cells in the worksheet, and the scroll bar length is set in such a way that when you scroll to the end, it will take you to the last cell in the used range in the worksheet.
In the example above, the black scroll boxes on the horizontal and vertical scroll bars are tiny, although I can see the entire dataset in the worksheet without scrolling.
There’s a mismatch between the visible data range and the scroll bars’ scroll ranges because Excel shows the scroll bar according to the last cell in the used range. So, even if you think that your data is limited to what you see on the sheet, the used range can be bigger.
The mismatch is normally caused by the presence of content, formatting, comments, and notes in rows far below the data range or columns on the far right of the data range.
The mismatch can also happen because there are objects such as shapes and icons on the drawing layer of the sheet way below or on the far right of the worksheet.
In this tutorial, I will show you options to reset the scroll bar when it’s too long.
Option #1: Use the ‘Clear All’ Option and Reset the Used Range
A mismatch between the scroll bar ranges and the data range can be caused by contents, formatting, comments, and notes in rows far below the data range or columns on the far right of the data range.
Deleting the offending contents, formatting, notes, and comments and saving the worksheet can reset the scroll bars to match the data range.
You can optionally add the step of resetting the used range using VBA.
Reset the Vertical Scroll Bar
Use the below steps to reset the vertical scroll bar:
- Select a cell in column A in the empty row immediately below the last used row in the data range.
- Press CTRL + Shift + End to select the empty row to the last used row in the worksheet.
- On the Home tab, open the Clear drop-down menu on the Editing group and choose ‘Clear All.’
The above step clears formatting, notes, comments, and contents from the selected cells.
- Press CTRL+ S to save the worksheet.
If the issue with the vertical scroll bar was caused by contents, formatting, notes, and comments way below the data range, the above steps should reset the scroll bar to match the data range.
Reset the Horizontal Scroll Bar
Use the steps below to reset the horizontal scroll bar:
- Select a cell in row 1 in the first column immediately after the last used column in the data range.
- Press CTRL + Shift + End to select across to the last used column in the worksheet.
- On the Home tab, open the Clear drop-down menu on the Editing group, and choose ‘Clear All.’
The above step clears all contents, formatting, notes, and comments from all the selected cells.
- Press CTRL+ S to save the sheet.
Reset the Used Range Using VBA
Sometimes, the steps in the sections above may not reset the scroll bars. In that case, you can take the further step of resetting the used range using VBA.
Here’s how to do it:
- On the active sheet, press ALT + F11 to open the VB Editor.
- Copy and paste the code snippet below into a standard VBA module.
Sub ResetScrollBars()
ActiveSheet.UsedRange
End Sub- Run the code snippet.
Also read: Smooth Scrolling in Excel
Option #2: Delete Unnecessary Objects on the Worksheet
A mismatch between the horizontal and vertical scroll range and the data range can be caused by unnecessary objects on the drawing layer of the sheet way below or on the far right of the worksheet.
You can reset the horizontal and vertical scroll bars by deleting the objects and saving the worksheet using the below steps:
- On the Page Layout tab, click the Selection Pane button on the Arrange group.
The above steps open the Selection pane on the right of the Excel window.
The Selection pane reveals, in this example, that the worksheet contains two hidden objects.
- Press CTRL + G to open the ‘GoTo’ dialog box and click the Special button.
The above step opens the ‘Go To Special’ dialog box.
- On the ‘Go To Special’ dialog box, select the ‘Objects’ option and click OK.
The above step selects all objects on the drawing layer of the worksheet.
- Press Delete.
The above step deletes all the offending objects and clears the Selection pane on the right side of the Excel window.
- Press CTRL + S to save the worksheet.
If the deleted objects caused the issue with the scroll bars, the above steps should reset the scroll bars to match the data range.
Option #3: Copy Data to a New Sheet
A corrupted worksheet can cause an incorrect scroll bar range.
In this case, you can resolve the issue by copying the data to a new worksheet in the same or a different workbook and then deleting the problematic worksheet.
I have shown you options for resetting the scroll bars so their scroll ranges match the data range on the worksheet. I hope you found the tutorial helpful.
Other Excel articles you may also like:
