Formatting Issues

How to Change Excel Currency Display with a Dropdown List

Partner Editor Oct 1, 2026 870 views

Question details

The user wants to create a dropdown list in a spreadsheet to easily switch the currency display (e.g., USD, EUR, GBP) globally without altering the underlying numeric values.

How to Change Excel Currency Display with a Dropdown List

Product: Microsoft Excel
Device & OS: not provided
Scenario: Setting up a dynamic budget spreadsheet where the currency symbol or code updates everywhere based on a single dropdown cell selection.
Observed behavior: Needs to apply a specific currency label or format conditionally based on a dropdown choice, but struggles to achieve this without converting the actual numeric values into plain text.

Before you start

Determine whether you need the updated currency cells to remain fully calculable numbers or if it is acceptable for them to be converted to text for display purposes.

Solution 1Recommended

Use Data Validation Dropdown and a TEXT Formula

Create a simple dropdown list and append the selected currency code to your calculations using the TEXT function.

This method is excellent for final reports where the result only needs to be viewed, not calculated further.

Create the Dropdown List

Select a cell (e.g., A1) to act as your master currency selector. Go to the Data tab, click 'Data Validation', choose 'List' from the Allow dropdown, and type 'USD, EUR, GBP' into the Source box.

Apply the Formula

In your result cell, use a formula that calculates your numbers and appends the dropdown choice. For example: =IF(E8="","",TEXT(E8-D8,"0.00")&" "&$A$1).

Use Data Validation Dropdown and a TEXT Formula

Result Format: Using the TEXT function converts the output to text (e.g., '150.00 USD'). If you try to SUM these cells later, Excel will return an error or zero.

Solution 2

Apply Conditional Formatting for Pure Numeric Values

Change the custom number format based on the dropdown selection to ensure the underlying values remain fully numeric for downstream calculations.

Easily Manage Dynamic Budget Formatting with WPS Spreadsheet

WPS Office provides highly intuitive Data Validation and Conditional Formatting tools, enabling you to build complex, dynamic multi-currency budget templates with ease.

1. Open Your Sheet in WPS: Launch WPS Spreadsheet and open your existing budget file.
2. Insert the Dropdown: Navigate to the Data tab, click 'Validation', select 'List', and input your currencies (USD, EUR, GBP).
3. Format Automatically: Go to the Home tab, select 'Conditional Formatting', and set up rules to apply currency symbols based on your Validation cell. Save seamlessly as an Excel file.

Fully compatible with Microsoft Excel (.xlsx) formats and conditional formatting rules.Advanced Data Validation features to quickly set up dynamic dropdown lists.Lightweight, fast, and completely free to use for both personal and professional needs.

microsoft office alternative - wps office

Frequently Asked Questions

Does changing the currency display automatically convert the exchange rate?

No, changing the currency display via formatting or text formulas only changes the visual symbol (e.g., $ to €). If you want to convert values, you must multiply the original amounts by a live or fixed exchange rate in a separate formula.

Why do my formulas return a #VALUE! error when referencing the currency cells?

If you used the TEXT formula method, your numbers have been converted into text strings. Mathematical formulas like SUM or multiplication cannot calculate text. To fix this, use the Conditional Formatting method instead to keep the values purely numeric.

Can I add more currency options to the dropdown list later?

Yes. Simply select the cell containing your dropdown list, go to Data > Data Validation, and add more currency codes (like JPY or CAD) separated by commas in the Source box. Alternatively, you can select a range of cells containing your currencies to make it easily editable.

Excel & Data Problems