format cells feature issue

[oscarlimerick]

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