How to add freeze panes to current Excel spreadsheets?

ML,

When you hover over row 1's tab, you will get a disclosure triangle. Clicking it reveals a menu that will allow you to Convert the row to a Header, rather than add a blank Header row. As far as I know, this menu item is only available in this contextual menu.

The same is true for columns. If you click on the first non-header column's tab triangle, you will get this option, again up to the maximum number of Headers, which is 5 for Header Rows and Header Columns. Footer Rows don't arise from this type of option, but only by adding from the Footer menu option under table, or in the Format Bar or the Inspector, and they will be blank initially.

Jerry

Be very careful with this "freezing" function.

If you change the number of header rows, Numbers doesn't just change their attributes. For example, if you have two header rows, and change the number of header rows to zero, you might expect that row freezing would be removed.

Wrong.

The actual rows are removed! The data in those rows (and/or columns) is gone!

If you change the number of header rows back to two, you get two rows of empty cells!

So you can't just turn "freezing" on and off, or move the freeze point around as you can in Excel.

For this and other reasons, I am using Excel on my Macs, and only use Numbers when I really, really MUST share spreadsheets between my Macs and my iOS devices, and then, ONLY if the spreadsheets are really tiny, like no larger than an iPad screen.

John,

There is a control for turning Freezing off and it doesn't involve changing the Header/Body status. The Freeze control is a toggle, so click it a second time to turn it off.

Also, be very careful with sharp objects.

Jerry

Hi Jerry

Thanks for the pointer, I guess it's not mandatory to destroy data in the header rows!

It's a pity that Numbers doesn't support Freezing when it reads Excel files.

I get "Import Warning - Frozen panes or split windows aren’t supported and were removed."

So I can convert Excel's top rows and leftmost columns to "Header" rows and columns with a right click function in Numbers, and then re-Freeze. I can't convert them back again. That seems to be a one-way street. But I can turn Freeze on and off.

I'm a little worried about those rows and columns having a "Header" attribute which may make them behave oddly, but i'm paranoid! I'm trying to decide if this is clumsy or just different.

Thaks again

John

John,

Those Header cells will behave oddly only if you aren't sure what they are there for. Once you get the hang of it, you will appreciate that you can differentiate how cells are treated by making their rows or columns Headers.

First of all, they will default to behaving like Text, because they are basically there to hold titles. This can be an advantage or disadvantage if you are making a Chart. Line Charts need Text for the Category Axis, so having the category axis data in a Header is very handy. Forgetting this and using a Header Row or Column for a Scatter Chart is a mistake because both axes must be Values, not text, for Scatter Charts.

Numbers has a nifty shortcut feature for referencing entire rows or columns. For instance SUM(A) produces the sum of the values in A. But if you want to have this formula in A, you will have a recursion error alert and the expression will fail. Putting the expression =SUM(A) in a Header or Footer isolates it from the body cells and the formula will work just fine even if they are in Column A. The formula will only reference the body cells in column A.

I feel sorry that you are giving up the wonderful advantages of Numbers over a fear of making a mistake. Don't forget that you have Command-Z to reverse course and recover from mistakes in the short term. Use TimeMachine and ML's Versions to allow you to recover from mistakes days ago. File > Duplicate whenever you anticipate making big changes to your document so you can easily go back and see what you had before the change and keep both versions.

Jerry

HI John,

Applications can support imported features only if they support those features in their native format. Numbers does not support freezing of individual (body) rows or of individual cells. It's not possible to support those features on imported files if you do not support them on your own files.

In the orther direction, Excel does not support Header rows (or any of the properties of Header rows on files it imports from Numbers. Nor will it do so if it ever supports importing Numbers files. Unless Header Rows become a feature supported by Excel for its own files.

Header rows don't 'behave oddly.' They probably do behave in 'ways you're not used to,' though. They are not included in sorts of the table. They allow the automatic fill down of formulas into added rows without having to themselves be part of the rule that every cell in the column above the row to be filled into must contain the same formula for that autofill to work. That may be 'different,' but I wouldn't describe it as "just" different, or as "clumsy."

Regards,

Barry

Thnak you Jerry and Barry.

I am clearly in need of better training on Numbers! I was relying on learning being intuitive, but I now see that there are significant philosophical differences in the design that are imortant, and these had completely escaped me.

I'm sure there must be learning resources out there, I will go looking!

If you have recommendations, I would be pleased to have them.

Thanks again!

John

HI John,

I'd start with at least the first four chapters of the Numbers '09 User Guide. The rest contains useful information, but my usual recommendation is to regard it as a reference, there to consult when you need it.

If you're writing (or revising), formulas, get the iWork Formulas and Functions User Guide as well.

Both are available for download via the Help menu in Numbers '09.

Regards,

Barry

Excellent, thanks. I pasted the titles into Google, and have them on my iPad! Good reading!!

Has Numbers updated to allow freezing of single rows and columns throughout the spreadsheet?

Hi FracKing,

No, but you can hide the rows and columns that are steps towards a more complex solution. That is a method that I use when working thorough a problem. My slow brain works in small steps. When the solution works, I then try to combine the small steps into a single formula. Then I can delete the rows or columns that contain the individual steps (if I am lucky 🙂).

Regards,

Ian.