Editor’s Note: If you would like Bill Jelen to answer your Excel question in an upcoming column, post a question at the Community Center on the right or send an e-mail note to [email protected]. If Bill selects your question to use in an upcoming column, you’ll win one of his Excel books as a thank-you.

One reader writes: “I have a worksheet with two or three screens of data. I can easily grab the vertical scroll bar and move to the top or bottom of the data set. Something happened, and now the huge scroll-bar slider (Figure 1) has become really tiny. Further, if I move it just one pixel, instead of jumping to the next screen of data, Excel will move to row 4500.”

Fig. 1

This can happen if someone presses End+Down Arrow key to move to row 1038718 (see below).

Fig. 2

You can often restore the size of the slider by moving it completely to the top of the spreadsheet. If this does not work, then there is one rogue cell way below your data that has become activated. Perhaps someone pressed the space bar or applied text formatting, etc. Follow these steps:

  • Note the last row that you believe to contain data.
  • Press the End key and then press the Home key. Excel will jump to the intersection of the last active row and the last active column. This row is usually way beyond the row that you believe to be the last row.
  • Delete all rows from the bottom of your data set to the rogue last row.
  • Save the workbook. The scroll-bar slider will return to full size.

Saving the workbook is the key. Even after you delete the extra rows, Excel will not restore the size of the workbook. In past editions of Excel, copying the worksheet was enough, but in Excel 2007, the scroll bar will not resize until you save the workbook.

CFO contributor and founder of MrExcel.com Bill Jelen is the author of 33 books about Microsoft Excel. His latest book, Learn Excel 2007-2010 from MrExcel, contains solutions to 512 mysteries like this one. Bill’s next webcast on CFO.com is “Using Excel for Advanced Financial Data Analysis,” airing on Tuesday, October 11.

, , , ,

17 responses to “Honey, I Shrunk the Scroll-Bar Slider in Excel”

  1. Deleting all excess rows and columns and saving usually works. I have one that it doesn’t. Scroll bar still thinks the last row is the previous, even though all content has deleted using shift-ctrl-end followed by “row delete” and “column delete” on both rows and columns.

  2. Try Alt+F11 to open Visual Basic.
    Ctrl+G to open the immediate box at the bottom.
    And then copy in this:
    and press enter.
    That should redefine the scroll bar size as the same as where Ctrl+End takes you, which should be the last active cell.

  3. I had the same challenge with the scrollbar resolution. None of the solutions offered here worked, sadly. I did however find the cause of my issue when I created a new worksheet, then did a copy/paste values, then repeating for formulas, formats, validation and finally comments. At the end of it all I still have the same scrollbar situation. But, after some trial and error I discovered that the scrollbars were perfect after each copy/paste until I did the copy/paste comments. It was at that point the scrollbar resolution went bad.

    I found some code by Greg Truby (posted on Mr. Excel) and adjusted it to do a single active worksheet so I didn’t have to recreate each worksheet.

    Sub ClearCommentsThisSheet()
    Dim ws As Worksheet, cmt As Comment
    Set ws = ActiveSheet
    For Each cmt In ws.Comments
    Next cmt
    End Sub

    • Thanks for you post Tim Hief, you’re an absolute legend ! I was having the same problem and spent hours trying to understand why the scrollbar was not resizing accordingly to the UsedRange. In the end it turned out to be because of the comments. Getting rid of them sorted all my problems out.

Leave a Reply

Your email address will not be published. Required fields are marked *