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