Excel – Automatically format currency based on adjacent column – Excel

conditional formattingmicrosoft excelworksheet-function

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.

Best Answer

  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.

Related Question