Can't format cells in Excel

[doctorgonzo]

This is driving me nuts. I can't format certain cells in an Excel spreadsheet. I will right-click on a cell and select "Format Cells" and nothing happens. I will try it from the toolbar, I will try it using the Ctrl+1 shortcut, but nothing.

This doesn't happen for all cells. Most of the cells I can format just fine. Of course, the ones that are formatted wrong are the very ones I can't change. I don't have any protection turned on in the worksheet. Erasing the cell contents doesn't work either.

Is the worksheet corrupt or something?

[Force Flow]

What version of Excel?

[doctorgonzo]

Excel 2000. Windows XP Home.

[Force Flow]

Hmm...I just had this problem the other day.

Try selecting the entire row or column by pressing the row/column button(s) on the side(s).

ie - hit the "A" button if you want to format the things in column "A", etc. Then right click > format.

[EzyStvy]

Was the data imported or typed directly into the speadsheet?

[doctorgonzo]

It was typed directly into the spreadsheet. It's just a little spreadsheet I have for tracking my car's mileage. It's nothing fancy at all.

I'll try to select the whole row when I get home and I can work on it.

[doctorgonzo]

Selecting the whole row did work. It still won't let me format individual cells, but at least I was able to roughly get it to look like I want. Thanks for the tip.

[thisisfutile]

I'm sorry to revive an old thread, but I too am using Excel 2000 and when I format a cell (or as suggested above, the whole row of cells), the change isn't visible. I noticed that if I click on one of the cells, click in the formula bar and then click somewhere else, the change occurs. Unfortunately, I can't select the entire row to make this work, it only works for each individual cell. I've tried copy/paste special (values, formats) and neither of those worked. If I make several format changes to the same cell, every time I go into the format window, the last change is there but it's just not showing on the sheet.

I've even copy/pasted from one book to another because the data was imported from TXT and I figured the "refresh" feature in the original spreadsheet may have been hosing things up. Nothing seems to work except manually selecting the cell, clicking in the formula bar and then clicking anywhere else on the sheet.

*sigh*

I love computer bugs...it's what I like to call "job security" (I'm an IT Manager)

Gabe

[Chris in U.K.]

Hey Doctor,

What happens if you left-click the cell and then go to the foirmat (or format) menu ?

Also I'm having a vague recollection about merged cells sometimes not behaving as expected.

HTH

Chris

[glc]

Chris, Doc's issue was solved 2 years ago. If you address anything in this thread, please address thisisfutile's issue.

[thisisfutile]

I'll take the blame for that, this is a prime reason why old threads shouldn't be revived...or why forum coding should inlcude "old post" notation to bring that fact to people's attention. I've posted on forums and not noticed the date, it's very easy to do. Some forums will disable replies to old posts, but that doesn't make any sense to me since most situations (this one included) are still relevant after 2 years....but this is another topic entirely (and should be another post)

Nevertheless, I'll post an update...I spent two hours trying to find a way around my issue and discovered nothing other than what I pointed out in my previous post. It's a very frustrating little bug. Copy/Paste to new columns wouldn't work. Copy/Paste Special wouldn't do anything. Setting a new column to equal the "bugged" column and then formatting that new column wouldn't do anything. Just to repeat...in my case, the change I've made to the format is there because when I go back in to "Format Cell", the last setting I changed too is selected (Number, 2 decimal, use commas) but my excel spreadsheet isn't changing the display unless I specifically click in the Formula bar (even if I do nothing more) and then click out again.

Anyway, I'm done with it. I wrote a View on our database and did a data import from that and all the data is Formatable now. (is that a word)

[Chris in U.K.]

Hmmmm, deeply embarassed..........check date......not a bad plan.........

Chris

[thisisfutile]

No need to be embarrassed. You're offering solutions and that's what makes forums work...besides you have 360+ posts, that's a great number on ANY forum (this means your an asset and not a liability). I on the other hand have 3 posts now and I'm introducing myself by reviving an outdated thread.

I do have some more to contribute about this topic though! Doc was manually entering data, so I’m not sure why his sheet wouldn’t let him format properly, I on the other hand did an import into Excel 2000 using the “Import Text File” and changed the import data type to "Text". I tried a second time and this time left it at the default "General" data type. Now, I can go into each cell, change the format, and it visibly changes on the screen. *Sigh* This is most unfortunate because as a rule, I always import into Excel using "Text" (I've never noticed this bug before though). If anyone cares, I'll actually use the next two paragraphs to explain a big "find" as an IT Manager...something to watch for if you routinely work with data in Excel. (Read on)

As a "computer guy", I've learned that it's very important to know that Excel (at least 2003, 2000 and on back), by default, use the "General" data type. In short, this means Excel will do interpreting for you. While that sounds nice on the surface, it's actually the reason I have a job and the reason the guy before me does NOT work here anymore. This "general" data type means that if you have a column of Zip Codes for example (US Zips) a Zip Code of 01234 will be seen as an Integer and leading zero's have no place in Excel's integer interpretation, so it will be dropped leaving "1234" as the zip. Where I work, they had 10 years worth of data transferred from an old network to a new network and all the data got corrupted because the guy chose to use Excel and didn't change the General data type to Text. Large numbers were converted to Scientific notation, item numbers got converted to dates, and MANY zip codes and phone numbers were corrupted.

This rears it's ugly head in two ways that I know of. First, if you let Excel open a comma separated values file (CSV), REGARDLESS of whether or not you've turned off it's "auto update" features, it will see every cell of data as General data type and change everything accordingly. This is easily tested:
1) Open Excel
2) Format cell A1 to 'Text' data type.
3) Insert MAR-13 (we have an item in our warehouse with that item number).
4) Save the sheet as a CSV (File > Save As > change the "Save as type" selection to CSV)…and “Yes” or “OK” to the warnings.
5) Close the sheet. Excel 2000 even asks you to save it again...another annoying little bug.
6) Open the CSV by double clicking it. You'll see Excel's auto interpretation at work. (13, Mar)

The second way to see the General data type auto working without your approval is when using the Import Text File feature (Data > Get External Data > Import Text File), the wizard defaults to general. I always set every column to Text...but after the formatting bug mentioned in this thread, I'm forced to be more selective of when I do.