Currency Formatting in Google Sheets Drop-Downs

Published on

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?

  1. In another column, for example, C2:C4, enter the numbers 2.5, 2.75, and 3 respectively.
  2. Select C2:C4 and apply Format > Number > Currency.
  3. Navigate to the cell, here cell B2, where you want the drop-down list with the default currency formatted numbers.
  4. Click Insert > Drop-down which will open the Data validation rules panel.
  5. Under Criteria, select Drop-down (from a range).
  6. Enter C2:C4 in the field below.
  7. 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

    Prashanth KV
    Prashanth KV
    Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

    Hierarchical Numbering Sequences in Excel

    Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

    How to Easily Repeat a Sequence of Numbers in Excel

    Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

    Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

    Would you like to create a sequence of dates in every nth row in...

    XMATCH Row by Row: Finding Values Across a Range in Google Sheets

    Using the BYROW function with XMATCH in Google Sheets allows us to match values...

    More like this

    XMATCH Row by Row: Finding Values Across a Range in Google Sheets

    Using the BYROW function with XMATCH in Google Sheets allows us to match values...

    Limit Formula Expansion to a Specific Row in Google Sheets

    In this tutorial, I’ll explain how to limit the expansion of an array formula...

    3-D Referencing Structured Data Tables in Google Sheets

    When you have several tables within a single sheet—not across multiple sheets in a...

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here

    This site uses Akismet to reduce spam. Learn how your comment data is processed.