If you want to select a value formatted as currency from a drop-down list in Google Sheets, there are three options to consider.
It depends on whether you want to use the default currency set in your sheet, a different currency, or a mix of different currencies. The approach varies accordingly.
Using the Default Currency in Drop-Down Lists
My default currency is the Pound Sterling in Google Sheets. That means when I select any number and apply Format > Number > Currency, the currency sign will be £.
I want a drop-down in cell B2 with the price list £2.5, £2.75, and £3.00. How do I set it up?
- In another column, for example, C2:C4, enter the numbers 2.5, 2.75, and 3 respectively.
- Select C2:C4 and apply Format > Number > Currency.
- Navigate to the cell, here cell B2, where you want the drop-down list with the default currency formatted numbers.
- Click Insert > Drop-down which will open the Data validation rules panel.
- Under Criteria, select Drop-down (from a range).
- Enter C2:C4 in the field below.
- Click Done.
In your drop-down, you will get currency-formatted numbers. However, when you select a value, it will appear as an unformatted number. To format it correctly, select B2 and apply Format > Number > Currency.
Note: Changes in the currency formatting in the range (C2:C4) will reflect in the drop-down, but not in the value selected in the cell. So, when you change the formatting in the range, you should also change the formatting in the corresponding drop-down cell.
Setting a Different Currency for Drop-Down Lists
When you apply custom currency formatting to the range C2:C4, for example, US Dollar by clicking Format > Number > Custom Currency, you should format cell B2 with the same currency formatting. You won’t face any issues.
Combining Multiple Currencies in Drop-Down Lists
If the price list in C2:C4 includes £2.5, $2.75, and ₹3.00 (i.e., different currency-formatted numbers), the above approaches may not work.
In this case, first, format the mixed currency numbers in C2:C4 as text. To do this, select C2:C4 and apply Format > Number > Plain Text.
Then, navigate to cell B2 (which contains the drop-down) and apply the text formatting as above.
When using this value in formulas, use VALUE(B2)
instead of B2
. If you have multiple drop-downs, for example, B2:B10
, use ARRAYFORMULA(VALUE(B2:B10))
since VALUE is a non-array function.
=SUM(ARRAYFORMULA(VALUE(B2:B10))) // Sums values in B2:B10
Resources
- How to Set Your Country’s Currency Format in Google Sheets
- How to Convert Currency in Google Sheets Using GoogleFinance Function
- Lookup Dates and Return Currency Rates in an Array in Google Sheets
- How to Convert Currency Text to Number in Google Sheets
- Format Numbers as Currency Using Formulas in Google Sheets
- Extract Numbers Prefixed by Currency Signs from a String in Google Sheets