Good day learned people of the excel forum,, I have a question about a problem I am seeing with the format feature of excel, that I am hoping there is a quick and easy answer for. I have attached 3 spreadsheets. sample 1 displays what my initial spreadsheet looks like. What I want to do here is take all the amounts in column I, and change the format of the "amount" cells in column I, to read like a standard currency display, with 2 numbers after the decimal and preceded by a dollar sign. For example -16.76 I would like it to display as -$16.76 in red colour,(because it is a negative number) and the value 4.70 would display as $4.70 in black colour (because it is a positive number).

However right clicking on column I, then format, then currency, then selecting the bottom of the 4 negative number options, does not yield the correct result. It does not display the dollar sign in front of the value as it should, and when I go back in to column I, I see that it has formatted them to custom instead of currency. I then need to repeat the steps for a second time in order to display the results correctly. This problem occurs consistently not randomly so is it a programming bug or is there something I can do in the setup to have it work correctly the first time? It becomes a major time waster if I work with a lot of spreadsheets and need to repeat a step un necessarily. I thought I found the solution when I read somewhere the following:

Excel’s Advanced Options include "Extend data range formats and formulas". When enabled, Excel tries to maintain consistent formatting/formulas across columns, sometimes reverting custom Currency formats to Accounting.



But I went into my advanced features and unchecked this box, and it is still doing the same thing. Is this just an error I need to live with or is there a known workaround or fix? Many thanks

Attached Files

	excel forum sample 1.xlsx (10.1 KB, 1 views)
	excel forum sample 2.xlsx (10.2 KB, 0 views)
	excel forum sample 3.xlsx (10.2 KB, 0 views)

When I go through the exact same steps you describe, I always get:
-$16.76
-$11.11
-$3.15
$4.70
-$9.45
and the format dialog reports 'Currency'.

Have you tried repairing your Office installation?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hello Paul, good to hear it works for you. It works for me too eventually but I have to run the command twice. Do you need to run the format cells twice to get it to work or just once? I am currently using Microsoft® Excel® 2021 MSO (Version 2510 Build 16.0.19328.20244) 64-bit, with microsoft office professional plus 2021. How would I attempt to repair my office installation?

UPDATE!
the program now works on the first try. I was running it from a guest account, so when I logged in as administrator, I was prompted to agree to the license terms, and after doing this the program started formatting correctly on the first try instead of the second, even when I went back to the guest account, so maybe microsoft was waiting for me to accept the license terms or something. Anyways I will mark this thread as solved. Thanks for your help Paul