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.

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!!!

Nov 25, 2014 - The drop-down arrow for a data validation list disappears when another cell is selected. Learn how to keep it. Excel Drop-down List Always Visible When Another Cell Selected. Tips for Split Screen in Windows & Mac.

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