I have a problem in Excel. About a week ago, the vertical scroll bar on an Excel worksheet stopped working. I lost the scrolling ability from top to bottom. So when I try to drag the right scroll bar from row 1 down to row 960, it doesn’t work. I have to use the down arrow, which takes a lot longer.
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2423: the Vertical Scrollbar stops working.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen. Today's question sent in: “I lost the scrolling ability from top to bottom when I try and drag the right scrollbar down from row one to row 960 it doesn't work.
I have to use the down arrow which takes a lot longer”.
Alright, so normally 960 rows.
You grab the scroll bar and you can easily scroll anywhere in that. But for this person when they grab the scroll bar.
Ah, like, just barely move and they are already down below the data.
It's impossible to get anywhere.
So I said, “Can you do a quick test for me?
Open the workbook, look on the right side of your keyboard to those six keys, insert, delete home end, page up, page down. Press Ctrl and the End key.
What that is supposed to do – it is supposed to take you to the last row with data.
In fact, let's come back here to one that is working.
Ctrl+End end takes me too column O, row 960. That's the right thing to do.
But here on this broken worksheet it takes me down to column O, row 1048576.
That's why the scroll bar is not working.
It thinks that you want to go all the way down there.
Now, this can easily happen when someone is in the last row with data and accidentally presses control down arrow.
And then while they're there, they do anything such as Ctrl+B or even just slap the spacebar.
Even if they delete that cell, it doesn't matter.
Excel remembers that you once went to row 1048576 on the sheet and now the scroll bar will be. Broken.
It takes just 30 seconds to fix this, but you have to carefully follow all six steps.
First, if you did the last test, you're now at row 1048576.
I need you to press Ctrl+Home to go to the top. Find a column that has data all the way down.
Ctrl+Down Arrow to jump to the last row. Go down one more row.
So now I'm sitting in row 961.
From here Ctrl+Shift+Down Arrow selects all of the rows from the first blank row to the last row in Excel.
On the Home tab, come out to the right side of the Ribbon, choose Delete. Delete Sheet Rows.
All right now, this is the place where people get tripped up.
You would think it's fixed, but it's not fixed.
The most important part from here is you have to Save. So Ctrl+S to save.
It is actually the Save that makes Excel recalculate where the last row is and things will start to work again.
Now, you mentioned that you were using the down arrow to get to row 960, which would have to be very tedious.
There are other ways to go.
On the right hand side, Page Up and Page Down keys will at least go one screen full of data.
So hold down Page Down and you can get to the bottom pretty quick.
Hold Page Up. You can get to the top pretty quick.
I also like Ctrl+End, although that's not going to work if you've activated all the way down to the bottom. And Ctrl+Home to get to cell A1.
But my favorite trick is if I want to get to the bottom of the data set, I find a column that is completely filled in like first name.
Hold down Ctrl and Down Arrow and that will jump to the last row. Then Ctrl+Up Arrow.
I can even do Ctrl+Right Arrow to jump over to the right edge.
If I need to select everything, let's say from row two on down, I am going to add the Shift key.
Ctrl+Shift+Down Arrow will select all that data.
It's very important to find a column that’s completely filled in. First name usually is a good one.
Last name isn't going to work.
If I press Ctrl+Down Arrow it only takes me to row 38. When I look it's because I have Pele in row 39.
Or Prince in row 189. Or Madonna in row 375.
So if you have Pele or Prince or Madonna in your data set, watch out for the columns that aren't completely filled in and control and the arrow key won't work.
I always recommend my new book, MrExcel 2021. It's full of great Excel tips and tricks.
Check that out with the “I” in the top right hand corner.
If you like these videos, please down below Like Subscribe and Ring the Bell.
You'll get all kinds of great little tips along the way.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast. I am Bill Jelen. Today's question sent in: “I lost the scrolling ability from top to bottom when I try and drag the right scrollbar down from row one to row 960 it doesn't work.
I have to use the down arrow which takes a lot longer”.
Alright, so normally 960 rows.
You grab the scroll bar and you can easily scroll anywhere in that. But for this person when they grab the scroll bar.
Ah, like, just barely move and they are already down below the data.
It's impossible to get anywhere.
So I said, “Can you do a quick test for me?
Open the workbook, look on the right side of your keyboard to those six keys, insert, delete home end, page up, page down. Press Ctrl and the End key.
What that is supposed to do – it is supposed to take you to the last row with data.
In fact, let's come back here to one that is working.
Ctrl+End end takes me too column O, row 960. That's the right thing to do.
But here on this broken worksheet it takes me down to column O, row 1048576.
That's why the scroll bar is not working.
It thinks that you want to go all the way down there.
Now, this can easily happen when someone is in the last row with data and accidentally presses control down arrow.
And then while they're there, they do anything such as Ctrl+B or even just slap the spacebar.
Even if they delete that cell, it doesn't matter.
Excel remembers that you once went to row 1048576 on the sheet and now the scroll bar will be. Broken.
It takes just 30 seconds to fix this, but you have to carefully follow all six steps.
First, if you did the last test, you're now at row 1048576.
I need you to press Ctrl+Home to go to the top. Find a column that has data all the way down.
Ctrl+Down Arrow to jump to the last row. Go down one more row.
So now I'm sitting in row 961.
From here Ctrl+Shift+Down Arrow selects all of the rows from the first blank row to the last row in Excel.
On the Home tab, come out to the right side of the Ribbon, choose Delete. Delete Sheet Rows.
All right now, this is the place where people get tripped up.
You would think it's fixed, but it's not fixed.
The most important part from here is you have to Save. So Ctrl+S to save.
It is actually the Save that makes Excel recalculate where the last row is and things will start to work again.
Now, you mentioned that you were using the down arrow to get to row 960, which would have to be very tedious.
There are other ways to go.
On the right hand side, Page Up and Page Down keys will at least go one screen full of data.
So hold down Page Down and you can get to the bottom pretty quick.
Hold Page Up. You can get to the top pretty quick.
I also like Ctrl+End, although that's not going to work if you've activated all the way down to the bottom. And Ctrl+Home to get to cell A1.
But my favorite trick is if I want to get to the bottom of the data set, I find a column that is completely filled in like first name.
Hold down Ctrl and Down Arrow and that will jump to the last row. Then Ctrl+Up Arrow.
I can even do Ctrl+Right Arrow to jump over to the right edge.
If I need to select everything, let's say from row two on down, I am going to add the Shift key.
Ctrl+Shift+Down Arrow will select all that data.
It's very important to find a column that’s completely filled in. First name usually is a good one.
Last name isn't going to work.
If I press Ctrl+Down Arrow it only takes me to row 38. When I look it's because I have Pele in row 39.
Or Prince in row 189. Or Madonna in row 375.
So if you have Pele or Prince or Madonna in your data set, watch out for the columns that aren't completely filled in and control and the arrow key won't work.
I always recommend my new book, MrExcel 2021. It's full of great Excel tips and tricks.
Check that out with the “I” in the top right hand corner.
If you like these videos, please down below Like Subscribe and Ring the Bell.
You'll get all kinds of great little tips along the way.
Feel free to post any questions or comments down in the comments below.
I want to thank you for stopping by and we'll see you next time for another netcast from MrExcel.
