Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to display a number in millions or thousands in Excel and Google Sheets
Excel & Google Sheets
=TEXT(B2,"#,##0,,M")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Number | Formatted Number |
| 123456789 | |
| 9876543 | |
| 456789 | |
| 12345 | |
| 123 |
=TEXT(B2,"#,##0,,M")→#,##0,,12
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the cell where you want to display the formatted number.
- 2Enter the formula using the TEXT function to format the number in millions or thousands.
- 3Replace B2 with the reference to your data cell.
Tips
- Ensure your data is numeric to avoid errors in the formula.
- Adjust the formatting string in the TEXT function to suit your needs (e.g., "#,##0,K" for thousands).
- Be aware of locale differences that might affect number formatting.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Can I use this method for negative numbers?
Yes, the TEXT function will handle negative numbers correctly.
What if my number is already in millions?
The formula will still work, but it will display it as millions of millions. Adjust the divisor in the formula accordingly.
Is there a way to automatically switch between millions and thousands based on the size of the number?
Yes, you can use nested IF statements to determine the appropriate format dynamically.