Automatically format currency based on adjacent column - Excel

Question

Using Excel and need a little help with a simple formula.

I know conditional formatting should do the trick here, however, I need help creating the exact formula for my need.

In column A I write the expenses, i.e. 750, 260 etc. In column B I note which currency it is, i.e. EUR, GBP or USD.

What I need is for Excel to automatically format the numbers in column A as their respective currencies based on what I put in column B.

For example, if I enter "700" in column A, and then in the adjacent cell in column B I enter "USD", then the number "700" in column A should automatically be formatted as USD currency. If I put in GBP it should be formatted as GBP currency and so on.

Any help would be appreciated, thanks.

Solution

1. Select cell A1, then select column A (this is to ensure that A1 is the active cell within the selected range).

2. Open the conditional formatting dialog (the one from the New Rule option) and pick the last option; using a formula to determine the formatting.

3. In the entry box there, insert:

   =B1="EUR"
   

4. Click on the Format button and format it as you would format EUR amounts.

5. Repeat steps 1-4 for GBP and USD, changing the formula in step 3 to =B1="GBP" and =B1="USD" respectively.

Comments

[Inscrutable]

Awesome! Before you gave me this solution, I tried this formula, but instead of step 1 - I dragged down the cell and selected "paste formatting only" but that didn't help. Even after manually deleting the $ sign in the formula, when I dragged down the cell - Excel didn't fill out the other cells correctly. But this works excellent. Thank you so much.

[Jerry]

You're welcome @Inscrutable :)