Can't format cells in excel?? 2

[Ime532]

We have a reoccurring problem in our office with excel 2000 where if we export records from a database or export from an Access report and perhaps even other times, if we right click on individual cells it does not allow us to format them. It will allow us to select the entire column and format the cells, just not individual cells, or even a selective group of cells. Any ideas on why this is happening??? I can repeat the problem over and over again.

In the same document as the one I paste the information into, if I insert a new sheet, the same problem is present in that sheet with no data in it. It's not until I open a blank document in excel and copy and paste the info from the first document into this new document that it allows me to format individual cells. Thanks in advance!

 

[KenWright]

Sounds like your data is being exported as text. What happens if you type a 1 in an empty cell, (make sure it is numerical), then copy it, select all your data and do Edit / paste Special / Multiply. If you can now format all your data, then that is what the problem was.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Ime532]

How do i make sure it is numerical if I can't format any cells to tell it it should be numerical?

I tried it anyway and am still not able to format any cells.

 

[KenWright]

Format any cell as number using Format / Cells (You will see no change at this stage) and then enter a 1. If it is right aligned it is likely to be numeric, but if it is left aligned it is likely to be text still.

You cannot simply use Format / cells on data that has been imported as text and see it change. You need to coerce it back to numeric. Try on any of the cells that are obviously textual numbers, formatting the cell as numeric (You still won't see a change yet), and then hit F2 and Enter. If it now reverts back to numeric, then your problem is as I described, so do what i suggested at the beginning of this note to get a numeric value 1, and then do as suggested in the previous note with the Paste Special bit.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[jbicking]

I've had experiences where if I've imported numbers from Access into Excel, I cannot perform any "numeric" functions (i.e., adding, etc.). I've gone back into Access and changed the data field type from text to number and then re-export the information. I then can peform those numeric-type functions. Are your fields in your database set up as numeric or text? Just a thought...

 

[foundryqa]

I have to agree with jbicking. I've experienced exactly what you're talking about and that was the fastest way for me to correct. Besides, when I found out they weren't numeric in Access, it made sense to change them anyway.

Fred

 

[Ime532]

Well not all of these fields are numeric. Some of them are just text. I can't format any cells on the sheet!!! I'm not even worried about performing mathematical functions on them, just formatting any of them. What good would changing a few of the fields to numeric do if I can't format a single cell on the entire sheet?

 

[AnotherHiggins]

Ime532,

I know it's a weird, frustrating problem. But just try the following:

[ul][li]Go to any empty cell a couple of columns to the right of the populated range and type "1" (no quotes)[/li]
[li]copy that cell[/li]
[li]select the range that you're having trouble with[/li]
[li]right click and choose "Paste Special"[/li]
[li]tic the box by "Mulitply'[/li]
[li]click ok[/li][/ul]

Now try formatting any cells you wish!

That really should do it.
John

 

[AnotherHiggins]

P.s. It doesn't matter if some fields are text, you can still do the "Paste Special" on them - it won't hurt anything.

John

 

[Ime532]

1. I went into access and used tools-> office links -> analyze it with excel.
2. Chose an empty cell and entered a 1
3. Copied the cell containing the one
4. Selected the entire range of data since I can't format any cells on the entire sheet (including blank ones)
5. Pasted special, chose multiply

Still can't format any cells on the entire sheet (including empty ones). Any other ideas?

 

[Ime532]

quick notes. Step 1 creates the excel document with the problem, and in step 4 I only selected populated cells, not the entire sheet.

 

[Niki]

IME532.... You have made me look like an Excel Goddess!

I was given this very issue just minutes ago.... did a quick search in TekTips and within 10 minutes of my superior scratchin' at my door for help (from a documentation specialist ....that's me!) & (that was hard for him to admit his weakness!!) I was able to shine!

Thanks a million.... great instructions....

Niki

 

[Niki]

ANOTHER HIGGINS.... the post of making me look like a Goddess....should have been directed to you... but I slipped in reading the names.... however IME.. .thanks for your posting as well! everyone who gave their 2cents is appreciated!

Niki

 

[AnotherHiggins]

Thanks, Niki, but all I really did is reiterate - and provide instructions for - what Ken had already stated.

The first time I ever ran into this problem I nearly ripped my hair our looking for a solution, so I'm glad you were able to find an answer quickly!

John

 

[Ime532]

So i still have this issue, anytime I export to excel, I cannot even bring up the dialog box for formatting cells. No more ideas???

 

[KenWright]

So what happens when you try to bring up the dialog box then? Is it just all greyed out or something, or do you not get any menu commands or what? Is it possible that the file being created is a protected one somehow? If you go to Tools / Protection / do you see 'Protect Sheet' and 'Protect Workbook', or do you see 'UnProtect Sheet' and 'UnProtect Workbook'?

Given that we've explored all the usual options, is there any chance you could mail me a copy of one of these so I can see first hand what it is you have in front of you?

ken.wright at ntlworld.com

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Ime532]

i get absolutely nothing when i try to format. No dialog boxes come up at all. I've just sent the file, let me know if you don't receive it and/or when you reply so I know I should be expecting it. We've had problems with blocking emails we should be getting here at work. Thanks!

 

[KenWright]

OK, sat here at 14:48 UK time, with no email yet. Will keep you posted.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

Hi Steve - Got the file, and it's bugging me now I get the same as you in Excel 2000 but have no problems in 2002 or 2003. I have found a way round it though, by simply extending the range of whatever set of numbers I am selecting to include either an empty row or column next to the data - this allows me to bring up the format dialog box by either right-clicking or via the menu. I'll keep looking to see if I can find something though.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

Another fix is to save the file down as a *.htm file, then resave that as a *.xls file, and it seems to re-enable all the formatting options.

Regards
Ken......................................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------