Formatting Issues

How to Change Excel Currency Formatting Based on a Dropdown Selection

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a cell's currency format to update automatically depending on the specific currency code chosen from a dropdown list located in another cell.

Product: Excel
Device & OS: not provided
Scenario: Building financial models or multicurrency data entry sheets where numerical values must adapt to reflect a user's selected currency symbol (e.g., switching from $ to €).
Observed behavior: The currency formatting needs to dynamically change to match the selected dropdown value automatically, requiring a solution beyond static cell formatting.

Before you start

Determine how many currencies you need to support. For just 2-3 currencies, conditional formatting is the easiest approach, but for a larger list, enabling the Developer tab and using a VBA macro is highly recommended.

Solution 1Recommended

Use Conditional Formatting for Limited Currency Options

This method is ideal if your spreadsheet only needs to switch between a small set of currencies. It uses formula-based rules to change the symbol.

Conditional formatting allows you to override the default number format of a cell based on the value of another cell. However, because you have to create a separate rule for every currency symbol, it can become tedious if you are dealing with more than a handful of currencies.

Create the currency dropdown list

Select the cell where you want the currency choice to appear (e.g., A1). Go to Data > Data Validation, choose 'List', and type your currency codes (e.g., USD, EUR, GBP).

Initiate conditional formatting

Select the cell or range containing the numerical amounts. Navigate to Home > Conditional Formatting, and select 'New Rule'.

Set the condition formula

Choose 'Use a formula to determine which cells to format'. Enter a formula that references the dropdown cell, such as =$A$1="EUR".

Apply the currency format

Click the Format button, go to the Number tab, select 'Currency', pick the corresponding symbol (e.g., €), and click OK. Repeat the New Rule process for each currency in your dropdown.

Performance Tip: Having dozens of complex conditional formatting rules can slow down workbook performance. Stick to this method if you have fewer than 5-10 currencies.

Solution 2

Use a VBA Macro for Dynamic Formatting

Using VBA is the most robust solution for dynamically updating currency symbols across many cells without creating numerous conditional formatting rules.

Efficient Multicurrency Spreadsheets

Handle Complex Formatting Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful data validation, comprehensive conditional formatting options, and full VBA support, making it simple to build dynamic multicurrency templates efficiently.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook requiring dynamic formatting.
2. Set up Data Validation: Navigate to the Data tab to insert a dropdown menu containing your desired currency abbreviations.
3. Apply Conditional Formatting: Go to the Home tab, click Conditional Formatting, and set up formula-based rules to change symbols dynamically.
4. Use VBA if necessary: For extensive currency lists, access the Developer tab to write or run macros using WPS Spreadsheet's robust VBA engine.

Fully compatible with Microsoft Excel formats (.xlsx and .xlsm)Intuitive interface for managing multiple conditional formatting rulesRobust developer tools for reliable VBA macro executionFree and lightweight alternative for complex data tasks

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the TEXT function to change the currency format instead?

Yes, you can use a formula like =TEXT(B2, IF(A2="EUR","€#,##0.00","$#,##0.00")). However, the TEXT function converts the numerical value into a text string, which means you cannot easily use that result in further mathematical calculations.

Why isn't my VBA macro running when I change the dropdown selection?

Ensure that macros are enabled in your Trust Center settings. Additionally, verify that your VBA code is placed inside the specific Worksheet module (not a standard module) and that it is using the Worksheet_Change event subroutine.

Does conditional formatting work if I copy and paste the cells?

Yes, but you must ensure your conditional formatting formula uses the correct absolute or relative references. For example, if the dropdown is always in A1, the formula must use $A$1 so the rule continues to check the exact dropdown cell even after copying.

Document & File Issues