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.

    Cycle Highlights in Google Sheets – Rotate Highlights Daily

    Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

    Filter Rows Containing Multiple Selected Values in Google Sheets

    This tutorial explains how to filter rows in a column containing multiple selected drop-down...

    Two-Way Lookup with XLOOKUP in Google Sheets

    When you need to look up one search key vertically and another horizontally, you...

    How to Filter by Total in Google Sheets Pivot Tables

    Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

    More like this

    Cycle Highlights in Google Sheets – Rotate Highlights Daily

    Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

    Filter Rows Containing Multiple Selected Values in Google Sheets

    This tutorial explains how to filter rows in a column containing multiple selected drop-down...

    Two-Way Lookup with XLOOKUP in Google Sheets

    When you need to look up one search key vertically and another horizontally, you...

    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.