I have a monster Excel spreadsheet with tens of thousands of rows, correlated. Its gigabytes of data taught me a lot. Including things it wasn’t supposed to, like what to do when Excel won’t scroll.
This thing is pretty fragile. Among other things, the largest of the sheets will stop scrolling. The scrollbar on the right scrolls, but the display doesn’t move. I can’t scroll down, I can’t scroll right, or do anything useful with the data.
But I stumbled on a quick solution.
For some reason, Excel randomly freezes the panes on these worksheets from time to time. So, when I click on a tab and the screen won’t scroll, the solution is to click on View, select Freeze Panes, and select Unfreeze Panes. Now you’ll be able to scroll in Excel again, like magic.
Then, since I want the top row frozen, I scroll to the top, click on the top row, click Freeze Panes again, and select Freeze Top Row.
For the record, I don’t think avoiding use of freeze panes really prevents this problem. It’s a useful feature; it’s just that sometimes it gets enabled with goofy settings that cause a problem. Once you know the workaround, it’s still annoying but not a terribly big deal.
If you have the same problem, hopefully this solves it for you.
If this tip helped you, I have a collection of a few dozen more Office tips and fixes I’ve collected over the years here.
My boss and I are compiling a huge Excel spreadsheet that summarizes everything our organization has ever done. It’s as big of a pain as it sounds. What makes it worse is having to scroll all the way back to the beginning to view the headers. The solution: make persistent headers in Excel.
The trick to making a persistent header that shows all the time, even after scrolling, hides in the View tab in Excel 2007.