💡 Quick Summary
Step-by-step tutorial on applying, switching, and building custom number format strings for world currencies in spreadsheets.
1. Default Currency vs. Accounting Formats
Excel and Sheets offer two primary financial formatting modes:
- Currency: Places the symbol directly flush with the digits (
- Currency: Places the symbol directly flush with the digits (
$1,234.56). Negative numbers display as -$1,234.56 or ($1,234.56) in red.
- Accounting: Aligns the currency symbol to the far left margin of the cell and lines up all decimal points neatly in columns.2. Custom Number Formatting Syntax
To force a specific currency symbol regardless of the user's local Windows/Mac OS locale, use custom format strings:
- Euro (DE format):
- Euro (DE format):
[$€-de-DE] #,##0.00;[Red]([$€-de-DE] #,##0.00)
- British Pound: [$£-en-GB] #,##0.00
- Indian Rupee: [$₹-hi-IN] #,##0.00
- Japanese Yen (no decimals): [$¥-ja-JP] #,##03. Troubleshooting Number Errors
If Excel treats typed currency amounts as plain text rather than numbers, ensure you type raw digits (e.g.
1250.50) and apply formatting via the toolbar, rather than typing the $ or € sign manually.