Having two currencies in one sheet

edited 12/09/19 in Formulas and Functions

I currently manage vendors in Japan but need to also have currency in dollar for our finance/accounting in the US. 

I think the only current option is to change the whole account setting to change the currency, but that will prevent me from having two currencies, and will also change the entire setting to Japanese. 


Is there a way to fix this? Have US as the country setting and have other currencies in the sheet? 


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭


    You can set currencies per cell so it shouldn't be a problem.

    Hope that helps!

    Have a fantastic day!


    Andrée Starå

    Workflow Consultant @ Get Done Consulting


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Alison
    Alison Employee

    Hi there,


    You can use the currency formatter to indicate each cell's currency, as Andrée was mentioning.  The default currency will likely not show an indicator.  By this, I mean that, for example, my account is in English (U.S.), so by default, my currency settings are USD.  If I format a cell/column/row in USD, it will display $25.00 and not USD$25.00.


    However, it will put the indicator for any other currency I format in my sheet.   For example, it will put CAD in front of Canadian-formatted items in my sheet.  Here's some information on formatting a currency in your sheet.


    Here's some information on setting your default currency settings.



  • Barry Bowles
    Barry Bowles ✭✭✭✭

    Hi there - I read this suggestion but its not working for me - here's why. I have 3 currencies being entered into one sheet because I do not want to complication of trying to consolidated 3 sheets. So my sheet is a global data sheet of invoices and the users enter the invoice information by form. Then thye have to go into smartsheet and select the currency one by one for each invoice - klunky ! I DO have column that contains a region code which can tell me which currency is relevant to the invoice but I am unaware of a way to "auto - apply" the appropriate currency.. For example the region may be EU so I would want the currency for EU applied to the "Amount" cell for that same row.

    Doing it manually is generating errors as some users are typing the currency code when entering the amount and that then sets the cell as "text" and makes reporting and formulas impossible.

    Does anyone know if it is possible to do this either as an automation or conditional formatting or some other technique?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could have 3 columns. One for each currency type.

  • Sasha Johnson

    I have the same issue as Barry. For some time I have been using Smartsheet as a tool for Finance approvals.

    I am moving away from the role and am creating "forms" for the new users to complete which will kick off the approval process to Finance. Issue being that I would normally manually change the row to USD where required. By entering the data through the form, the user cannot do this.

    I do have a column which has EU, USD, and AUD options in a drop down but what I would really like to see if a formula where the user submits the form and selects USD and this auto picks up this change in the line. I believe in excel this is done via conditional formatting but smartsheet conditionally formatting is quite restricting.

  • John Jonassen
    John Jonassen ✭✭✭✭

    Is the real question not so much about the different currencies being used, but the exchanged values on both sides of an international transaction?

    Would it not be beneficial to have a Source Sheet that houses the exchange rates between the whatever your home currency is and the different international partners?

    Then in your sheets, you can calculate the exchanged value by way of a lookup and some added math? From a process perspective, because exchange rates change, once your transaction reaches an end state, copy or move the row to a different landing sheet that will break the calculation and journalize the final values across all needed currencies.

  • Jim B
    Jim B ✭✭✭

    Sorry to unearth an old thread but I'm having the same problem as @Sasha Johnson and Barry here. Did either of you find a solve for this? How can I (at the VERY least) flag to my users 'Hey this cost is in USD - I want to see all <THESE VALUES> as USD?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!