Dealing with large numbers like 125,000 or 12,500,000 in Excel can make your worksheets hard to read and interpret. Whether you’re preparing dashboards, reports, or presentations, displaying data as 125K or 12.5M makes it far more professional and visually appealing. Excel offers several methods including both formatting and formulas to simplify these values without affecting the underlying calculations.
In this tutorial, we’ll walk through six proven methods to format numbers as thousands (K) or millions (M) and even convert imported K/M text values back to numbers. Let’s get started.
Steps to format thousands as “K” and millions as “M” in Excel:
➤ Select the range containing your numbers and open Format Cells (Ctrl + 1).
➤ Under Custom, enter the following format:
[<999950]0.0,”K”;0.0,,”M”
➤ Press Enter for output.
➤ Click OK. Values below roughly 1,000,000 now display as K, and values above 1,000,000 automatically display as M, providing a smart, consistent format across your dataset.
Format Numbers as Thousands Using Custom Number Formatting
Large datasets can quickly become cluttered when you’re working with multi-digit values like 145,500 or 980,000. For cleaner, more professional spreadsheets, Excel’s Custom Number Formatting feature lets you visually shorten these values into a “K” format without changing the actual numbers used in calculations. This is especially useful for dashboards, financial summaries, or presentations where space is limited but clarity matters.
We’ll use the following dataset:
Steps:
➤ Select the range B2:B11 that contains the numbers you want to format.
➤ Press Ctrl + 1 (or right-click and choose Format Cells) to open the Format Cells dialog box.
➤ Under the Number tab, choose Custom, then in the Type field enter: 0,”K”
➤ Click OK and your output will be visible.
➤ You can also alternatively type this in the box to include commas in your values: #,##0,”K”
➤ Click OK to apply.
Your values like now appear as 1,000K, while still retaining their full numeric value for formulas and calculations.
Convert Numbers to Thousands “K” Using the TEXT Function in a Separate Column
Sometimes you may want to display numbers as thousands (K) without affecting the original values or their formatting, especially if you need both the raw data and a human-friendly version for reports or presentations. The TEXT function is perfect for this because it transforms numbers into formatted text strings in a new column, giving you flexibility while preserving the underlying data.
Steps:
➤ In C2, type the following formula:
=TEXT(B2,"#,##0, ") & "K"
➤ Press Enter for output.
➤ Drag the fill handle down to C11 to apply the format to the remaining rows, instantly converting values like 145,500 into 146K in the new column.
Configure Large Values as Millions Using Custom Number Formatting
When working with high-value data such as company revenue, population statistics, or financial forecasts, seeing full numbers like 12,000,000 can overwhelm your worksheet and make comparisons harder. Converting these values to display with an M suffix creates a cleaner, more professional presentation without altering the original figures. Custom number formatting allows you to simplify your data for dashboards, reports, or client presentations while keeping every calculation accurate behind the scenes.
Steps:
➤ Highlight the range B2:B11 containing your large values.
➤ Press Ctrl + 1 (or right-click and choose Format Cells) >> go to the Number tab >> select Custom.
➤ In the Type field, enter: #,##0,,”M”
➤ Click OK to apply.
Values like 12,000,000 will now display as 12M, providing a concise and visually appealing format.
Display Millions with Decimals Using the TEXT Function
For datasets where precision matters such as financial reports or statistical summaries, you might want to show millions with decimal points. The TEXT function allows you to convert numbers into text strings with exact formatting, including multiple decimal places, while leaving the original values untouched. This method is ideal when you need a polished display for presentations or dashboards but still want to preserve the raw numbers for calculations.
Steps:
➤ In C2, enter the formula:
=TEXT(B2,"#,##0.000,,") & "M"
➤ Press Enter for output.
➤ Drag the formula down to fill C2:C11, converting values like 7,530,000 into 7.530M for a precise and professional presentation.
Automatically Display Numbers as Thousands “K” or Millions “M”
When you have a mixed dataset with both thousands and millions, manually applying different formats can be tedious. Using a dynamic custom format lets Excel automatically adjust the display: numbers below one million show with a K, and numbers above one million appear with an M. This keeps your spreadsheet clean, readable, and professional without affecting the underlying numeric values.
Steps:
➤ Select the range containing your numbers and open Format Cells (Ctrl + 1).
➤ Under Custom, enter the following format: [<999950]0.0,”K”;0.0,,”M”
➤ Press Enter for output.
➤ Click OK.
Values below roughly 1,000,000 now display as K, and values above 1,000,000 automatically display as M, providing a smart, consistent format across your dataset.
Frequently Asked Questions
Can I use custom formatting without changing the actual values in Excel?
Yes. Custom formatting only changes the display appearance of numbers, not their underlying values. Your formulas and calculations will continue to use the original full numbers without any alteration.
How do I show both K and M automatically in the same column?
Use the format [<999950]0.0,”K”;0.0,,”M“. Numbers under one million display with K, while anything one million or greater automatically appears with an M for easier readability in Excel.
Will the TEXT function affect sorting or calculations in Excel?
Yes. The TEXT function converts numbers into text strings. Once converted, they behave as text rather than numbers, which can impact sorting and calculations unless you convert them back to numeric format.
Can I include decimal places when formatting millions as M?
Yes. Use formats like #,##0.00,, “M” or =TEXT(B2,”#,##0.00,,”) & “M” to include two decimal places. This ensures large values like 7,530,000 display as 7.53M for greater precision in Excel.
Wrapping Up
In this tutorial, you learned multiple methods to format numbers in Excel as K for thousands and M for millions, along with ways to convert text values back to numbers. By applying these approaches such as custom formatting, TEXT functions, and dynamic formats, you can make your spreadsheets more readable, professional, and effective without altering original data or calculations. Feel free to download the practice file and share your feedback.
