Hatrack River Forum: Excel Question

This is topic Excel Question in forum Books, Films, Food and Culture at Hatrack River Forum.

To visit this topic, use this URL:
http://www.hatrack.com/ubb/main/ultimatebb.php?ubb=get_topic;f=2;t=019124

Posted by Zan (Member # 4888) on :

I have had several spreadsheets recently that lock up as far as formatting goes. I put one together and save it. Later, I'll come back and add or change some things, but I can't change the formatting on the cells. When I click on format cells, I get nothing.

I've checked the sheet protection and it's not on. Does anyone know anything else I can try?

Posted by Christy (Member # 4397) on :

We used to have a lot of trouble with that in Sigmaplot (a graphing program). To get us by until the new version came out, we created a new file and just copied the data in. The files may have been corrupted somehow, I'm not sure.

In Excel, you probably can't do this without copying the format unless you paste as values. This may not be very helpful if you have formulas [Frown]

Posted by zgator (Member # 3833) on :

That does work, but it only copies the formulas. All the formatting like column widths, borders, etc. doesn't copy over.

Thanks for the suggestion, though.

Posted by John Van Pelt (Member # 5767) on :

It's counter intuitive, but you can get it to copy the column widths and formats - or at any rate with reasonable fidelity.

Select the range of cells you intend to copy, and ctrl-copy to the clipboard
Position your cursor in the new document
Select Edit > Paste Special
first choose the option "Column widths", and click OK.
Then without touching anything, successively re-select Paste Special, and choose (and apply) Formats, All except Borders, Formulas, and lastly, All.

Some variations on this order may give different, or preferred, results.
-John

[ October 17, 2003, 01:06 PM: Message edited by: John Van Pelt ]

Posted by John Van Pelt (Member # 5767) on :

Of course, I forgot the original point of the post, which was to eliminate the mysterious locking. I don't know whether my approach will bring over the locked state or not. Might be worth a try.

Maybe apply everything I said except Formats.

[ October 17, 2003, 01:10 PM: Message edited by: John Van Pelt ]

Posted by xnera (Member # 187) on :

My first guess is that the spreadsheet is protected somehow. Read this for more info on spreadsheet protection. I'm not too familiar with the latest version of Excel, so I don't know if there's some way to automatically protect a spreadsheet when you save it.