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.

    Highlight the Smallest N Values in a Column in Google Sheets

    Want to visually spotlight the lowest values in your data? In this tutorial, you'll...

    Sort Each Row Individually in Excel Using a LAMBDA Formula

    Sorting rows in Excel typically refers to rearranging entire datasets based on values in...

    Sort by Field Labels Using the SORT and XMATCH Combo in Excel

    Want to sort your Excel data by column names instead of column positions? Learn...

    How to Sort Pie Slices in Google Sheets

    To sort pie slices in a pie chart, you need to sort the data...

    More like this

    Highlight the Smallest N Values in a Column in Google Sheets

    Want to visually spotlight the lowest values in your data? In this tutorial, you'll...

    How to Sort Pie Slices in Google Sheets

    To sort pie slices in a pie chart, you need to sort the data...

    Filter Items Unique to Groups in Google Sheets

    In this tutorial, we'll learn how to filter items unique to groups in Google...

    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.