For the past 82 years (approximately), I’ve used Excel almost every day. Along the way, I’ve learned a few tricks, and produced some sophisticated workbooks.
One of the joys of Excel is that there’s always something new to learn. And this week I learned how to freeze panes to hide rows in Excel.
Too Many Heading Rows
This week, while working on a client’s files, I wanted to review some calculations in a price list. The top 15 rows are headings for the printed sheet, then there’s a row of column headings for the price calculations.
Below that are hundreds of rows with prices. Here’s a simplified version of the worksheet.
A Frozen Wasteland
I wanted to see those column headings, and as much of the price list as possible. However, if I selected row 18, and froze the worksheet, I’d be stuck with a couple of inches of wasted space, with all the headings visible at the top.
Instead of freezing, I could drag the split bar down, to split the screen, and create a small section at the top. Next, I could scroll the column headings into view in the top section of the screen.
A Splitting Headache
I’m not sure why, but I don’t use split windows too often.
Anyway, as I played with the scroll bar, and thought about splitting the window, I accidentally froze the panes. (Trust me, it’s easier to do that in Excel 2003 than in Excel 2007.)
When this happened, row 17 was at the top of the window, and row 18 was selected.
First Visible Row Frozen at Top
Miraculously, row 17 was frozen at the top of the screen, and all the rows above it were out of sight.
So, instead of wasting 2 inches of space, only 1/4 inch was used, and I could see more of the price list.
Why didn’t I know about that before? Or maybe I did know it, many years ago (or last week), and forgot about it.
A Warning for You
The only downside to this Freeze Panes trick is that my client might not notice the Freeze Panes line above row 18.
That thin line can be hard to see, especially if the worksheet gridlines are showing.
Confusing Worksheet Situation
If people don’t notice the Freeze Panes line, they might think that the first 16 rows have been hidden.
And, if they try to show those row again, by using the Unhide command, it won’t work.
Also, if they click anywhere on row 17, and try to scroll up, nothing will happen. Scrolling doesn’t work in the frozen part of the screen.
So, if you use this trick to temporarily hide rows, remember to unfreeze when you’re finished working. Be kind to your co-workers (or clients)!
What Did You Learn About Excel?
So that’s my new trick. What did you learn about Excel this week?
Something less embarrassing than my lesson, I hope!
Freeze Panes to Hide Rows in Excel
________
> For the past 82 years (approximately)
Debra is getting dyslexic. She has, of course, been using Excel for 28 years – since she was 2.
Thanks for the support Ed! Too bad I can’t remember exactly when I started using Excel. It must have been around 1989/90.
When I have too many rows on top of the freeze pane, I like to group some (e.g. rows 1:16 in your example) so I can quickly switch back and forth.
Thanks Sébastien, that’s a good use of the grouping feature.
You can do a similar trick in the VB editor as well. Each code window has that same “Split Bar” (the small rectangle at the top of the vertical scroll bar)… you can drag it down to split the code window into two separate parts, each independently scrollable. And you can do this for both the Procedure View or the Full Module View (those two icons down in the bottom left corner of each code window). In Full View, each window has all the procedures “scrollably” available all the time; in Procedure View, you have to select the procedure individually for each of the split-window views. I find splitting the code window this way quite handy when trying coordinate the code between two related procedures during development.
Thanks Rick, that’s great advice on using the VB editor windows effectively.