Excel custom format millions with comma

Excel custom format millions with comma

It is possible to do an Excel custom format in millions with a comma, but on some computers this will not work and it has very little to do with Excel.

First, in order to get a big number like 12 000 000 to be shown in a report as 12 we can create a custom format.

The benefit of a custom format is that although it will show 12 in the cell, you will see that the formula bar still contains 12000000 and all formula linked to the cell will use 12000000 (the first 2 arrows in the image below).

excel-custom-format-millions-with-comma

The key to this is to use the Custom Format options.

As shown above we have gone to FORMAT CELLS and chosen Custom.

We then used the # ##0 option. What is difficult for you to see is that we have included 2 spaces after the format (in the red circle). Why spaces?

On my computer the regional settings (part of windows and not part of Excel) have been set up to separate thousands with a space. On other peoples computers they may have their regional settings set up to separate thousands with commas.

If your computer uses commas to separate thousands then you would need to use #,##0,, as the code (notice the 2 commas at the end).

Whether you use a comma or a space you need 2 of them at the end. This is because each comma/space represents a thousand. So if you used  #,##0, (one comma at end) the cell would show 12,000 (useful if you want to show the number in thousands but not for millions). The more commas/ spaces you include the more thousands are chopped off the end.