I have this annoying problem that I can't figure out the cause of, here is what I did:

1.I did a very simply auto sum function for a couple of columns

2.under advanced options I deselected "use system separators" and under "decimal separator" I put comma instead of default dot because when I copypaste numbers from internet they mostly use commas so if it is any other it won't autosum at all and then I would have to manually replace dot with comma for each number. So it's just a convenience.

3. I formatted the autosum cell so it displays my preferred currency, set the decimal places to 2 and clicked OK
but that OK did nothing, when I open the formatting cell again it says 5 decimal places, it simply reverts to 5 every single time, and if I put decimal place to 0 it reverts back to 3.

4.so instead of getting correctly formatted number like 1441,75 I always get stupidly formatted number 1441,750,00




Why does it keep doing that, it doesn't make any sense!

also how do you switch columns?

For example I have columns A, B, C and I would like to put everything in A between B and C so B becomes A.

No responses? Is this something very simple so no one can be bothered?

Hi Metamag,

It is not simple, and it is not that no one is bothered, the reason is perhaps
No one has cone up with a solution yet.

I tried, and found what you observed was true. But cannot solve it. Looks like a bug, which only microsoft can fix.

Well, I hope it is added to their bug list then, it's a actually a pretty big bug for what this software is suppose to do.

And what about the column switching in my second post?

I guess you changed only the decimal separator to "," and the thousands separator is now the same with the decimal separator, which is not a bug, is a mistake. If you use "," as a decimal separator, and "." as thousands separator, in formatting the cell select 2 decimal places and check the option "Use 1000 separator (.)" , the entry 1441,75 will be displayed as 1.441,75.
If you keep "," as decimal AND thousands separator, AND you check the option "Use 1000 separator (,)" (notice that the option now has comma in the paranthesis), excel is confused, will consider that your entry is an integer with no decimals, because of using the comma as the 1000 AND (!!) decimals separator , thats why it shows ",00" to display the decimals......
As for switching between columns, there is no such concept in excel, as far as i know. Use VBA or do it manually.

No.

First, what you said is only relevant for number category, not currency category, if under number category(under format cells) you select "use 1000 Separator(,) you indeed get formatting like 1441,750,000.

Not only was this deselected by default so if I format the cell under number category I do get the correct format of 1441,75, but this option does not exist under currency category.

Also under system separator of course I changed to comma for both decimal and thousands the first time.

Nothing you said is relevant, the problem still stands.

P.S.
I guess I could artificially "fix" this problem by formatting the autosum as number category and in the column next to it just type the currency, but that's not a real solution.

That's the point where the problem starts..
If you check the custom category, the format you used when you used comma for both separators , even for currency, is saved there... it will look like : [$$-409]#,##0,00 for US dollars. Means that every time you use currency format, excel will use this custom format.
You should delete this format from the list, otherwise this is the format excel will use when you format to currency.

You should learn more about formatting before saying that "Nothing you said is relevant". From what i see, you understood nothing from what i said.

Just go to advanced options, use different separators for decimal and thousands, then in the custom formats list, select the wrong formats and delete them so they will not be automatically used to format the currency .

Where is that, is it called "custom formats" or something else?

In Home tab, at Number section, in the format drop down list, the last line is More number formats. Click on it, it will open the format window. On the Number tab, on the left side, the last entry in the list is Custom. Click on it, then in the right side you will see a list of custom format. You will find in that list the wrong formats that must be deleted... select the entry , and Under that list there is a Delete button.

I just selected 0,00 type but it's OK now, it formats correctly under currency.

Anyway, thanks for the solution!

I misread what you were trying to say because having two different separators was counter-intuitive to me.

Glad it's ok now.
You can mark your thread as solved, then..(top of the page, under thread tools-mark this thread as solved.)