Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .