Excel formatting problem

[ladyfear]

Hi all,

I am using Excel 2003 and am having a problem formatting. Specifically, when I copy from one spreadsheet and paste special/values to another spreadsheet, sometimes the formats from the original spreadsheet are also pasted. Why is this and how can I fix it? I have looked at the protections on the original, have tried the "Calc Now" button, etc. Any insight provided would be greatly appreciated. Thanks!

Ladyfear

 

[Gerald Higgins]

Hi, welcome to the board.

Which formating feature comes across ? Number format ? Font ? Colours ? Can you give us specific examples of the data that is afected ?

 

[ladyfear]

For example, let's say I'm copying a column of numbers from WB1 to WB2. The original values are derived from formulas, formatted in "Comma". When I paste special/values to WB2, I expect the values to be formatted in the formats set in the new sheet, say, "Accounting." But for whatever reason, the "Comma" format is prevalent. When I manually try to format the pasted data in WB2, the new formats don't take hold. Only when I hit F2 for each cell does the change take place. It's really annoying when I'm pasting thousands of cells. Please help! Thanks!

 

[Smitty]

Welcome to the Board!

You can apply the format you want, then select the range and goto Data-->Text to Columns-->Finish. That will apply it to all of the cells at once.

Hope that helps,

 

[ladyfear]

Thanks you for the welcomes, and for the help, I really appreciate it. And thanks for the tip. Curious, is this just an Excel quirk, or is there a permanent fix? It happens, too, when I just attempt to format a row/column. I don't mind the text to columns, but again, annoying that I have to go through an extra step just to get my data to format the way I want it to.

Also, I have noticed that in the past for some of my spreadsheets, the whole workbook won't update unless I hit F2 in the cells which need to update. Example: A cell on Sheet 1 which is linked to a formula on Sheet 2 will not update even if the formula or corresponding data has been updated, and not even if I hit the "Calculate Now" button in options. Seems like a similar problem.

 

[therios]

For example, let's say I'm copying a column of numbers from WB1 to WB2. The original values are derived from formulas, formatted in "Comma". When I paste special/values to WB2, I expect the values to be formatted in the formats set in the new sheet, say, "Accounting." But for whatever reason, the "Comma" format is prevalent. When I manually try to format the pasted data in WB2, the new formats don't take hold. Only when I hit F2 for each cell does the change take place. It's really annoying when I'm pasting thousands of cells. Please help! Thanks!

When you choose to copy the values only then that is all that you are going to get. Maybe try Edit --> Pase Special --> select "values and number formats" and try that.

 

[ladyfear]

Thanks for the reply, Therios. Unfortunately, it's not the formats that I want to copy over, it's just the data/values.