Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted
  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.

share|improve this answer
    
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. –  Inscrutable Jan 19 at 15:03
    
You're welcome @Inscrutable :) –  Jerry Jan 19 at 15:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.