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
________
Just to be clear, i take it that instead of freezing row 17 at the time when you could also see rows 1 – 16 (i.e. you were scrolled to the top), you scrolled down the worksheet until row 17 was at the top, and thent freezed panes?
I.e. scroll first, freeze later.
Jeff, that’s right, and thanks for asking for clarification. I added a sentence to the article to make the steps clearer.
Your article reminded me of some of my most frustrating times in Excel… when someone froze the panes while using filters, then cleared the filters but never unfroze the panes. There I am going up and down and left and right with the arrow keys, but NOTHING is happening on the screen. Fortunately (sort of), it’s happened to me enough times so now I know it’s related to freeze panes.
Bob R, I don’t recall running into that problem (yet!), so thanks for the warning.
Dear Debra,
What a great idea for a new blog, the Excel Serendipity blog, where people share what they learnt.
I had three such happenings in the last few days. One was a horror story, the second a “Wow” moment and the third a lesson well learnt.
The horror story comcerns DATES, MACROs and EXCEL
i have a column of text formatted dates that come form our ERP system
05.07.1998
when i amnually replace the . with a slash i correctly get 5/07/1998,
HOWEVER, when i perform the same operation via VBA i get 7/05/1998 and for my purposes this is incorrect.
I solved the problem in a convoluted way by writing the following code:
dot1 = InStr(1, x, “/”, 1)
dot2 = InStr(InStr(1, x, “/”, 1) + 1, x, “/”, 1)
xDay = Mid(x, 1, dot1 – 1)
xMonth = Mid(x, dot1 + 1, dot2 – dot1 – 1)
xYear = Mid(x, dot2 + 1, Len(x) – dot2)
newdate = xDay & “/” & xMonth & “/” & xYear
Range(Cells(r1, c1), Cells(r1, c1)).Value = CDate(newdate)
Range(Cells(r1, c1), Cells(r1, c1)).NumberFormat = “dd/mm/yyyy”
Where x is the text string. Thankfully this works.
Happy discovery was the following =TEXT(A1,”dddd”) returns the day of the week for a given date.
Learning for the week was thanks to the BRILLIANT paper on SUMPRODUCT on the XLDYNAMICS.com website. Now understand why “*” was required, also learnt that with “–” you do not need “*” in in SUMPRODUCT
Kanti, thanks for sharing your Excel experiences from the past few days. I’m glad the horror story turned out okay in the end. 😉