Today you’ll learn how to add commas to numbers in Microsoft Excel.
Adding commas to numbers in Excel is a crucial skill. It improves readability and reduces errors. Unformatted numbers can be hard to read, especially in large financial data sets. Adding commas ensures clarity in such data.
In this tutorial, I’ll show you the simplest methods, their steps, and illustrations with real data and scenarios. To achieve this task, you’ll learn to use Excelβs built-in user interface commands, formulas, and Power Query-based approaches. Each step is clear and easy to follow. By the end, your data will look professional and organized. Letβs get started!
Using the Number Format Tool
The Number Format tool in Excel offers many number formatting styles, including those that insert commas in numerical entries.

Select your input dataset on the active worksheet and click on the General drop-down arrow in the Number commands block.
The Number Formats context menu will open.

Select the Currency option to add the thousands separator, which adds a comma by separating every three digits to the left of the decimal point.

The default currency symbol, USD, is placed just before the number and aligned with the number.
You can use the Currency format to add commas to numbers in less professional reports.

However, if you wish to accurately format all numbers along with the thousands separator, use the Accounting Number format.

In this style, the currency symbol is aligned to the left edge of the cell, and the number is right-aligned. It’s designed to mimic traditional accounting statements.
Sometimes, you may only want to add the thousand separators in the numbers of your dataset but don’t want to add the USD symbol.

Select the input dataset and press the Ctrl + Shift + 1 keys to add the commas excluding the USD symbol.
Using the Format Cells Dialog
You can also use the Format Cells tool to insert commas in numbers using custom number formatting codes.

Select the input dataset and press the Ctrl + 1 keys to open the Format Cells dialog box.
Click on the Custom option in the Category column in the left-side navigation panel and enter the following code inside the Type field on the right side.
#,##0
Click OK to apply the formatting style.

Excel will add only the thousand separators to the source dataset.

If you wish to add the USD symbol along with the thousands separator, use this custom number formatting code instead:
$#,##0

Now you get commas along with the USD sign.
Using the Flash Fill Tool
Flash Fill in Excel automatically fills data when it recognizes a pattern in your entries. As you start typing, Excel predicts the rest based on the pattern and suggests the fill. If you’re using Excel 2013 or newer edition, you can use this tool to add commas to numbers in Excel in just three steps.

Firstly, go to the source dataset and create a helper column named Thousand Separators adjacent to the source dataset where you wish to insert commas.

Now, manually insert commas into the first two numbers of the dataset.

Select the source and new helper column and press Ctrl + E to use the Flash Fill command.

Excell will figure out the comma insertion rule in your manual entries and replicate it for the rest of the source data.
You can delete the source data column and rename the helper column.
A downside of this method is that the Flash Fill tool only works if the number of digits in the source data column is consistent.
Using the TEXT Function
The TEXT function in Excel is highly useful for custom formatting numbers, especially for visual presentation. By adding commas, it improves the readability of large numbers, making them easier to interpret. However, because the result is text, it can limit further mathematical operations, requiring you to convert it back to a number if needed.

Select an adjacent blank cell and enter the following TEXT formula into the cell:
=TEXT(B2, "#,##0")

Hit Enter to calculate the cell and reformat the numbers with commas after each thousand.

Next, drag the fill handle down to apply the formula to the remaining cells in the column.

That’s it! You’ve successfully used the TEXT function to add commas to numbers in Excel.
Using the Power Query Editor
Power Query in Excel helps you quickly clean and format data. If you need to add commas to large sets of numbers, probably importing from an external data source, you can use Power Query to transform your data.
You load the data into Power Query, apply a data transformation rule, and the commas appear where you want in the source dataset. Itβs useful when dealing with long lists or reports, saving you from manual editing.

If the source dataset is in an external server, click on the Get Data command in the Data tab and hover the mouse cursor over an appropriate menu, like From Database.
Choose the final data source from the overflow menu, like From SQL Server Database, From Oracle Database, etc.

Alternatively, if you need to export an existing dataset from the active worksheet, select the data, and click on the From Table/Range command in the Data tab.

Click OK on the Create Table dialog to complete exporting data into the Power Query Editor.

Right-click on the column you need to transform, hover the mouse over the Change Type option and click on the Currency option in the overflow menu.

Power Query Editor will apply the Currency number formatting to the selected column.

Click on the File tab and choose the Close & Load To Option to export the transformed dataset to the active Excel worksheet.
π Read more: If you’ve learned something new today, you’ll enjoy these Excel tutorials too:
Conclusions
So, now you know how to add commas to numbers in Microsoft Excel using various methods, like the Number Format command in the Excel ribbon menu, the Format Cells tool, and the Flash Fill command.
You can use the Excel TEXT function if you need the output in a text format.
Lastly, if you’re importing a large dataset from an external database or workbook, you can leverage the Power Query method to transform the data and add the thousand separators.
0 Comments