Error when using Accounting format in Excel

Question

Hi

I'm from Denmark and have a very annoying issue when using the format Accounting in Excel 365 for formatting cells. The Danish currency symbol kr. is supposed to be aligned on the left in cells formatted with Accounting and the amount aligned on the right in the cells. This works fine with $ and other currencies, but when choosing kr. or some other currencies (have checked many), it goes wrong. This used to work correctly in older versions of Excel, but on my two Windows 10 machines with Office 365 it doesn't work correctly. On my wifes Windows 7 machine with Office 365 it does work as supposed. How do I fix this?

Correct (from my wifes PC):

Incorrect (from my PCs):

Thanks a lot.

Carsten

Answer 1

Carsten wrote:

The Danish currency symbol kr. is supposed to be aligned on the left in cells formatted with Accounting and the amount aligned on the right in the cells. [....] On my wifes Windows 7 machine with Office 365 it does work as supposed. How do I fix this?

Also works correctly in Excel 2010.  Try entering the following Custom format:

_ [$kr.-406] * #,##0.00_ ;_ [$kr.-406] * -#,##0.00_ ;_ [$kr.-406] * "-"??_ ;_ @_

(There is a space after the last underscore.  Or just eliminate the last part after and including the last semicolon.)

I formatted as Accounting with Danish currency (kr), then clicked on Accounting to see how it was defined.

The same procedure should work on your wife's computer with Excel 2007.  You might prefer to rely on the Custom format on your wife's computer, just in case there is something different between your (Danish) Excel and my (US) Excel.

Answer 2

Carsten wrote:

Why has this behaviour changed? I would really wish someone from MS would look at this!

In theory, post the problem (with my solution) to excel.uservoice.com.  Generally, a waste of time, IMHO.  But it's the best way for us to report defects, if we don't have a corporate support contract.

As for "why has this behavior changed?", I've deleted my rant. <wink>

Answer 3

Thanks for answering! You're correct, this works. I actually had that figured out opening an old Excel file and looking at Accounting, it then had it as a custom format, but when opening on my wives PC it's correctly listed in accounting.

Why has this behaviour changed?

I would really wish someone from MS would look at this!

Carsten

Answer 4

FWIW, using 365 Business these cells are accountancy formated, all being different countries