Currency Formatting in Google Sheets Drop-Downs

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.

Table of Contents

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