Tag Archives: Freeze Panes

Fixing Excel when it stops scrolling

Sometimes I track system health for huge networks with huge Excel spreadsheets. And every once in a while, I run into problems with the Excel spreadsheet not scrolling. I move the cursor, and the cursor moves, but the sheet doesn’t, so I can’t see.
The problem is freeze panes. I always have the top row frozen, since I’m typically trying to track 14 or 15 things and the rows tend to blend together if I don’t freeze my header row. But sometimes Excel gets confused and freezes more than I initially told it to freeze–like, an entire screen’s worth of data. The solution is to unfreeze everything, then freeze the top row again. Then Excel behaves again.

What to do when your Excel worksheet won’t scroll

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.

Excel won't scroll
When Excel won’t scroll, the solution is hiding under this button on the ribbon.

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.

How to make persistent headers in Excel 2007

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 trick to making a persistent header that shows all the time, even after scrolling, is hidden in the View tab in Excel 2007.
Continue reading How to make persistent headers in Excel 2007