Forum Discussion
Conditional currency
Dear community
I'm in the process of generalizing my Excel for increased automation. I'm in need of some sort of conditional formatting for currency. I have the setup as below where the corresponding currency is given in a cell, and I want to match this currency in the Price column (for cell copy).
In other words, I want to be able to copy an arbitrary cell i column B without regards to which currency it relates to - and of course get the correct visualisation.
Is there a smooth way to do this with conditional formatting that I'm not familiar with?
Thanks in advance!
/Q
You could create a rule for each currency. The cell values themselves should be just numbers; the number format will determine how they are displayed.
Select the range in column B that you want to format. I will assume that B11 is the active cell in the selection.
- On the Home tab of the ribbon. select Conditional Formatting > New Rule...
- Select 'Use a formula to determine which cells to format'.
- Enter the formula =$A11="SEK"
- Click the Format... button.
- Activate the Number tab.
- Select Custom in the list of categories.
- Enter #.##0,00 "SEK" in the Type box.
- Click OK, then click OK again.
Repeat these steps for each currency, for example with the formula =$A11="EUR" and the number format #.###,00 "EUR"
4 Replies
I can't see the complete row numbers, but let's say the top row in the screenshot is row 10.
What do you want to happen if you copy B11 to B14? Should it remain 71.00 SEK, or should it become 71.00 EUR. or should it become the equivalent amount in EUR (currently 6.97 EUR)?
Hello!
Sorry for bad cropping, but you were correct in your assumption, first row with numbers is 11. I want the end result to look as it looks now, and I want to be able copy B11 to B14 and get 100,00 EUR (as dictated by cell formula, not seen here) in the target cell. Any conversion has already been carried out, and it is purely formatting which I want to ascertain.
You could create a rule for each currency. The cell values themselves should be just numbers; the number format will determine how they are displayed.
Select the range in column B that you want to format. I will assume that B11 is the active cell in the selection.
- On the Home tab of the ribbon. select Conditional Formatting > New Rule...
- Select 'Use a formula to determine which cells to format'.
- Enter the formula =$A11="SEK"
- Click the Format... button.
- Activate the Number tab.
- Select Custom in the list of categories.
- Enter #.##0,00 "SEK" in the Type box.
- Click OK, then click OK again.
Repeat these steps for each currency, for example with the formula =$A11="EUR" and the number format #.###,00 "EUR"
