To apply a currency format in Google Sheets, select the amount cells and choose Format > Number > Currency, or Custom currency for another symbol. The format only changes how a number looks. The cells must already hold real numbers, so check that statement amounts sum correctly before and after you format them.
Applying the built-in currency format to a Sheets amount column
The built-in currency formats sit under Format > Number. Google's help on formatting numbers in a spreadsheet gives the basic step: select the cells, open Format > Number, and choose a format. The toolbar also has a currency button, shown as the symbol of your spreadsheet's locale.
For a statement table, select the Debit, Credit and Balance columns together, then apply the format once. Selecting whole columns by their letters also formats rows you add later, which saves redoing it every month.
The options that matter for money:
- Currency shows the symbol, thousands separators and two decimals.
- Currency rounded shows the symbol with no decimals. Useful for summaries, risky for transactions, since 6.75 displays as 7.
- Accounting lines up the symbols at the left edge of the cell and shows negatives in brackets.
- Financial shows negatives in brackets without a symbol.
Rounded display does not change the stored value. A column of amounts shown as whole dollars still adds up to the exact cents. That is helpful for accuracy and confusing for readers, because the displayed figures may not add up to the displayed total.
Currency, accounting or financial format for statement amounts?
Use Currency for a transaction list you read row by row, Accounting for a report where symbols and decimals should line up, and Financial when you want brackets for negatives without symbols cluttering every cell. All three store the same number; the choice is only about how quickly a reader can scan the column.
The table below shows how example values look under each format with a United States locale. The amounts are made up.
| Stored value | Currency | Accounting | Financial | Currency rounded |
|---|---|---|---|---|
| 1240 | $1,240.00 | $ 1,240.00 | 1,240.00 | $1,240 |
| -42.1 | -$42.10 | $ (42.10) | (42.10) | -$42 |
| 0 | $0.00 | $ - | 0.00 | $0 |
Exact spacing and the way zero is drawn can differ slightly between locales and app versions, so treat the table as a guide and look at a few cells in your own file.
Brackets for negatives are a convention many accountants expect, so Accounting or Financial suits a file you are sending to one. For a household budget, Currency with a minus sign is often easier to read.
Pick one format per column. A balance column in Currency next to a debit column in Accounting makes the eye jump between two layouts.
Custom currency formats for euros, pounds and other symbols
Custom currency lets you show a currency other than your locale's default, such as euros in a sheet set to United States. Google's help lists the steps: highlight the data, choose Format > Number > Custom currency, search for a currency or type your own, and click Apply. A drop-down beside the box sets the number of decimals.
Use it for a single account held in another currency, or for a sheet that holds a foreign account next to your local ones. Search for "euro", "pound" or a country name and pick the version that shows the symbol where you want it, before or after the number.
The format does not convert anything. Applying a euro format to 100 dollars shows €100.00; it does not turn the value into euros. The stored number stays 100, and the symbol is only a label. Converting between currencies needs a rate and a formula, covered in the last section.
Writing a custom number format for debit and credit amounts
A custom number format gives full control over positives, negatives, zeros and text. Google's help explains that a format can have up to four parts separated by semicolons, in the order positive, negative, zero and non-numeric, and that you can add a colour in brackets such as [Red]. Choose Format > Number > Custom number format to enter one.
Formats worth copying for statement data:
$#,##0.00;[Red]-$#,##0.00;"–"shows negatives in red with a minus sign and zeros as a dash.$#,##0.00;($#,##0.00);"-"shows negatives in brackets, like Accounting, but keeps the symbol next to the number."€"#,##0.00;-"€"#,##0.00shows a euro sign before the number, typed as quoted text.#,##0.00" CR";#,##0.00" DR"labels positives as credits and negatives as debits, the way some banks print balances.
In these codes, 0 always shows a digit, # shows a digit only when needed, and the comma sets thousands grouping. Text in double quotes is shown exactly as typed.
To see how the parts work, take the first format above and three example cells. A stored 1240 uses the first part and shows $1,240.00. A stored -42.1 uses the second part and shows -$42.10 in red. A stored 0 uses the third part and shows a dash. If you leave out the third part, zeros fall back to the first part and show as $0.00.
Test a new format on a few cells with a positive, a negative and a zero before applying it to the whole column. A misplaced semicolon is easy to miss and sends values to the wrong part of the format, so a negative can end up shown without its sign or brackets.
How does the spreadsheet locale change the currency symbol?
The spreadsheet locale sets the default currency symbol, decimal mark and date style for the whole file. Google's help on changing the locale and time zone says the locale changes the spreadsheet's default currency, date and number formatting, and that it sits under File > Settings.
That is why the toolbar currency button can show $ in one file and £ in another, even under the same account. A sheet someone created in the United Kingdom keeps a United Kingdom locale when shared with you. Cells with the plain Currency format then show pounds.
Two effects to know:
- Plain Currency follows the locale. Change the locale from United States to Germany and $1,240.00 becomes 1.240,00 €.
- Custom currency stays put. A cell set to a specific currency through Custom currency keeps that symbol after a locale change.
For statement files, set the locale first to match the bank's country, then apply formats. If you work with a foreign account, give its columns a custom currency so a later locale change does not relabel them.
Why does a currency-formatted amount still refuse to add up?
Formatting cannot fix a value stored as text. If an amount came in as the text "$1,240.00", applying a currency format changes nothing and SUM skips it. The sign is that the cell stays left-aligned and the format seems to have no effect. The fix is to convert the text to a number first.
Text amounts usually come from copying out of a PDF, a web page or OCR. Opening a scanned statement with Google Docs often yields amounts as text with symbols attached; the guide to Google Docs OCR on a PDF shows what that route returns. Photos give the same problem, as covered in turning an image into Google Sheets cells.
To convert, put a formula in a spare column:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", ""))
It removes the dollar sign and commas and turns what is left into a number. Copy the results, paste them back with Edit > Paste special > Values only, then apply the currency format. Repeat with the right symbol for other currencies.
A quick test catches the problem before it spreads: select the column and look at the sum shown at the bottom right of the window. If it is lower than the statement total, or shows a count instead of a sum, some cells are text.
Mixed currencies in one Google Sheets statement file
Keep each currency in its own columns, or add a currency column, rather than formatting rows of one column with different symbols. A column that mixes dollars and euros will still add up, but the total means nothing because it adds two units together.
A layout that works for accounts in more than one currency:
- Add a Currency column holding a code such as USD or EUR on every row.
- Keep the Amount column as a plain number with two decimals and no symbol.
- Add a Converted amount column with a formula that applies a rate to the Amount, and format that column in your home currency.
- Total the Converted amount column, and use SUMIF on Currency for per-currency totals.
Where you get the rate depends on the purpose. For tax or accounting, use the rate your accountant or the relevant authority asks for, typed in as a value. Live rates from a formula change every day, which makes last month's totals change too.
When not to bother: if a foreign account is small and you only need its own balance, a separate tab with a custom currency format is simpler than conversion columns.
Clean, numeric amounts from the start save all of the fixing above. FastBankConvert is being built to read statement PDFs into separate amount and balance columns, and it is getting ready to open. Those columns would be meant to go straight into Sheets, like the files described in one-click PDF to Excel conversion. Get started to hear when trial conversions are available.