Number format thousand separator and no decimals
Every time (many days a year) I train people in Excel, someone ask why there is no button or shortcut to obtain a number format with "thousand separator and no decimals". At least - in northern Europe - this is the most common used number format. If "button-solution" not possible for Microsoft in order to have common buttons world-wide, at least a shortcut for this has been much appreciated. (Note that the current button for thousand separator and two decimals could not be used, though the alignment in that format are not disired/good and two decimals used).
Today I teach student to:
1) be quick to hit Ctrl+1 -- Tab - down arrow -- Tab -- downarrow -- Tab -- space -- Enter
2) Use my macro button - but hornestly, I hate to have macro for something common and simple due to lack of "undo".
3) Maybe some day (and I said that for 10 years now) Microsoft will do it :-)
Thanks for all the support and comments about this request. We don’t have plans to add this format in the near future, but we’ll keep monitoring the votes as we prioritize this suggestion among other requests.
There are a few ways that you can enable this already, as mentioned in some of the comments.
1. Set the style used when you click the comma button.
– This can be done by modifying the cell style called “Comma”, which is used when you click the comma button. See the screen shot in the comments for details, or look at this article to modify the style – https://support.microsoft.com/office/apply-create-or-remove-a-cell-style-472213bf-66bd-40c8-815c-594f0f90cd22.
– For existing workbooks, you’d need to set the style in each workbook.
– To make it the default in new workbooks, you can create a new workbook, make the change to the “Comma” style, and then save the workbook as a template to be used for new workbooks. Follow the steps here: https://support.microsoft.com/office/customize-how-excel-starts-6509b9af-2cc8-4fb6-9ef5-cf5f1d292c19
2. Another option is to create a macro in your personal macro workbook which can be assigned to a shortcut key or added as a button to your QAT or ribbon. To do so, select a cell containing a number, then start recording a macro. Type a name for your macro, and type a letter to use as the shortcut key you want, such as T (the shortcut would be CTRL+SHIFT+T). Make sure you choose to store the macro in your Personal Macro Workbook, so it’s available no matter which file you have open. Then click OK to start recording. Then set the cell format as desired and stop recording.
With that, can select a range of cells and press CTRL+SHIFT+T to apply the number format.
The macro should have code that looks something like this:
’ Set Thousands With No Decimals
’ Keyboard Shortcut: Ctrl+Shift+t
Selection.NumberFormat = “#,##0_);(#,##0)”
Steve [Microsoft Excel]
Corey Becker commented
I appreciate the time you took to explain the workaround. Personally, I think this relates to a broader idea of having both workbook-level styles and application-level styles. I don't know how this could be done but there is definitely a need to make some standard/default styles more accessible in all workbooks, including old ones where changing the "new workbook" template doesn't help.
Steve Innes commented
Has anyone been able to properly get a template with this modification to open at excel startup? I made the template and saved it in all the locations here:
and even made my own alternate startup folder, but it won't automatically open and apply the template. The template works if I open that file directly.
Here's a screen shot of how to set the cell style called "Comma".
Jon Peltier commented
I'd like to be able to put a few favorite formats up there. A number format (the comma icon doesn't even point to a numeric format, but an accounting one), a date format or two, etc.
Bob Flanagan commented
If you use the Spreadsheet Assistant, available at www.add-ins.com, it has 8 format options as part of its 200+ features. One of them does number formatting with the thousands separator and no decimals. Each of the 8 can be customized. And assigned as a button on the quick access toolbar.
[Deleted User] commented
Use national settings for Denmark and use a separator. But it will of course only work on computer with those settings, it won't work if you send it to someone.
In Excel (365) I want to put a ficgure such as 3000 and it needs to be shown as 3.000. However it shows as 3000.0 How can I change it?
Go to Format and choose Custom. In the custom box, place "#,##0;-#,##0;0" without the quotes.
This could be even better with an option embedded in excel advanced settings : "use thousands separator by default ". Done
yea this a common problem of number format a shortcut is required in excel workbook
Jeff Carno commented
Anonymous You're missing the whole point of the suggestion. The idea is to replace 3 mouse clicks with one, or, ideally, the addition of one new icon with its own keyboard shortcut so that no mouse clicks are required at all.
Use the comma button and then the the decimal mover.
Can handle all currencies; not sure about The Lira (Turkey) not sure of what they are using. At any rate, should be easy two or three clicks. Some times, just format the whole spreadsheet, then use the format brush.
Stephaan VB commented
Dit kan geen wens zijn voor alle gebruikers! Want bv. enkel in Amerika gebruikt men de komma als scheidingsteken voor duizendtallen, terwijl bv. in België men de duizendtallen scheidt met een spatie, zo kan je ook na de komma dan getallen per duizendtallen mooi schrijven, een voorbeeld:
wij schrijven 1 235, 234 230 kg.
Je kan al in de 'landinstellingen' de schrijfwijze van getallen, notaties, datums en uren zelf al correct instellen en zo een uniforme schrijfwijze in uw data bekomen. Wil je een uitzondering dan blijft dat uiteraard een uitzondering die dan wel persoonlijk is en daar kan je geen algemene regel voor instellen.
David Benaim commented
OMG yes!!! I also have a Macro that I use daily and Im also an Excel trainer so; explain how it isn't available. This should be the default!!!! Plus number formats with k or M is also lacking, PowerBI does it automatically
There is a button to move the decimal point, requires two clicks, but fast if I can remember which button to click - I often go the wrong way.
A customizable "My format" button might be desirable.
Jim T commented
isn't this just a custom format already available? #,##0 What would be more globally helpful is the ability to save a custom format as a ribbon button in excel. Then it's not a matter of being for just one format, but giving the user the power to create a custom format and make it quickly accessable.
Joseph T commented
Sounds like just giving users the option to customize the ribbon would be the best option here. If I could pick my top 2 or 3 most-used formatting options to put in place of these, I’d love that.
Tobias Ljung commented
Chris, one problem still there; the alignment is still a problem even though you get rid of decimals.
Sergei Baklan commented
Serg, more exactly that affects Accounting format. With Chris' approach if you click on comma icon in number section of the ribbon you'll have the number with thousand separator but without decimals.
Chris, it work for currency, but it need for all numeric format too not currency only