Freezing in Excel Hell

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.

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.

Freeze01

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.

SplitScreen01

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.

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.

Freeze03

Why didn’t I know about that before? Or maybe I did know it, many years ago, and forgot about it.

So, a new world of freezing opportunities has opened for me, and not just because the Canadian winter is just around the corner.

The Downside

The only downside that I can see is that my client might think the first 16 rows have been hidden, and using the Unhide command won’t make them visible. I’ll have to remember to unfreeze when I’m finished working.

So what did you learn about Excel this week? Something less embarrassing than my lesson, I hope!

Excel Giveaway Reminder

On Wednesday, I announced the prize winners for the Very Scary Fall Giveaway for Excel Nerds! Please email me at ddalgleish @ contextures.com if you’re a prize winner, and haven’t yet claimed your prize. The deadline is 5 PM (Eastern time zone) on Monday November 9, 2009, or your prize will be forfeited.

________

You may also like...

14 Responses

  1. Ed Ferrero says:

    > For the past 82 years (approximately)

    Debra is getting dyslexic. She has, of course, been using Excel for 28 years – since she was 2.

  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.

  3. 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.

  4. Thanks Sébastien, that’s a good use of the grouping feature.

  5. Rick Rothstein (MVP - Excel) says:

    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.

  6. Thanks Rick, that’s great advice on using the VB editor windows effectively.

  7. jeff weir says:

    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.

  8. Jeff, that’s right, and thanks for asking for clarification. I added a sentence to the article to make the steps clearer.

  9. Bob R says:

    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.

  10. Bob R, I don’t recall running into that problem (yet!), so thanks for the warning.

  11. Kanti Chiba says:

    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

  12. Kanti, thanks for sharing your Excel experiences from the past few days. I’m glad the horror story turned out okay in the end. ;-)

  13. Rick Rothstein (MVP - Excel) says:

    Kanti,

    Assuming the variable x contains your date value with the “dot” delimiter, these three lines of code should do exactly what your posted code does…

    Parts = Split(x, “.”)
    Cells(r1, c1).Value = DateSerial(Parts(2), Parts(1), Parts(0))
    Cells(r1, c1).NumberFormat = “dd/mm/yyyy”

    Note that I assumed your Range(Cells(r1,c1),Cells(r1,c1)) reference was meant as a reference to a single cell… that construction is identical to Cells(r1,c1), which is what I used in my code above. You might want to look up the Split and DateSerial functions in the help files to better understand what my code is doing.

  14. Kanti Chiba says:

    Dear Rick,

    Thanks for your input, i will certainly try your suggestion. So much to learn so little time.

    regards

    kanti

Leave a Reply

Your email address will not be published. Required fields are marked *