Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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 :-)

497 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)

We’ll send you updates on this idea

Tobias Ljung shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

102 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Signed in as (Sign out)
Submitting...
  • Leo commented  ·   ·  Flag as inappropriate

    Go to Format and choose Custom. In the custom box, place "#,##0;-#,##0;0" without the quotes.

  • Anonymous commented  ·   ·  Flag as inappropriate

    This could be even better with an option embedded in excel advanced settings : "use thousands separator by default ". Done

  • Anonymous commented  ·   ·  Flag as inappropriate

    yea this a common problem of number format a shortcut is required in excel workbook

  • Jeff Carno commented  ·   ·  Flag as inappropriate

    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.

  • Anonymous commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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

  • John commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    Chris, one problem still there; the alignment is still a problem even though you get rid of decimals.

  • Sergei Baklan commented  ·   ·  Flag as inappropriate

    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.

  • Serg commented  ·   ·  Flag as inappropriate

    Chris, it work for currency, but it need for all numeric format too not currency only

  • Chris commented  ·   ·  Flag as inappropriate

    You can set this as a default, but it is not done in Excel. Use Control Panel settings > Currency. It will work with numbers. Here is a video I put on YouTube. https://www.youtube.com/watch?v=Q2-2IYgzKWc I tested by typing in numbers, selecting the column and clicking Comma Style in Excel 2016. Closed Excel, restarted and test in a new workbook. Still works.

  • Tobias Ljung commented  ·   ·  Flag as inappropriate

    Andre, your suggested solution is only working in the current workbook (not in new/other workbooks), which means you have to do this in every workbook…but a nice solution is a macro connected to the Comma-style (with undo-functionallity) - se Bill Jelen´s recent video here: https://www.youtube.com/watch?v=WUZf88FmS3M
    BUT...still we need a change of the standard button as suggested.

  • Andre Terra commented  ·   ·  Flag as inappropriate

    This is already possible with Excel as is. Just change the Number Format of the 'Comma' style (Alt H J, right click Comma, select 'Modify') and the Comma button will now format it to whatever format you picked.

    To make this somewhat permanent, save your blank workbook with the modified Comma style as a Template Workbook called Book.xltx under your XLSTART folder and this will be your new workbook every time you open Excel

  • Anonymous commented  ·   ·  Flag as inappropriate

    I would like to have a formatting button on the ribbon that can be customized. I have a preferred number format that I always use. Would like to be able to set a 1 click solution that is visible on the ribbon and ideally available for use with an Alt key shortcut.

← Previous 1 3 4 5 6

Feedback and Knowledge Base