Number Formatting

[Guest]

Is there a way that I can more easily format numbers to my preferred format?

I want to format all numbers to zero decimal places with a 1000 separator.
To do this I normally go to Format-Cells-Number and make my selections. But I
have to do this constantly. I do not want to use the "comma" formatting icon
because whenever a zero is entered into a cell that format leaves a "-" dash
in the cell and I want a zero to be in the cell.

I would like to change my preferences for my preferred format to rule. Or, I
would be hapy with somethign on the toolbar also.

Thanks

 

[Ragdyer]

How about *changing* what that "comma" icon does?

You can revise the formatting that's performed by the "comma" icon to
exactly the format that you wish.

<Format> <Style>,
And expand the "Style Name" window, then click on "Comma".

You'll now see the actual format that the "comma" uses.

Click on "Modify", and you'll get the standard, familiar, formatting window.

Now, just click on the Number tab, click on "Number", eliminate the decimal
places, 1,000's separator should be checked by default, and then <OK>.

You're now back to the "Style" window, where you can see the results of your
modification.
Click <OK> again, and the Comma icon on the toolbar will now revise the
formatting of all your selected cells.

This is a WB specific change, and should work on all sheets within the WB.

If you open another WB, you can easily copy this format revision by going
to:
<Format> <Style> <Merge>
And clicking on the name of the WB whose format style you wish to copy
(merge) into the current WB.

 

[Guest]

Wow, it's strange how you can use Excel forever and discover something new
that you didn't know about.


1. Now that I know how to do this, I'd like to take it one step further. I'd
like to change the default "Normal" formatting for all Excel documents that I
open. I'd like to change the appearance to a number with a thousands
separator and no decimals. Is it possible for every new doc I open to have
this formatting as a default?

 

[Ragdyer]

Here's a copy of something I read in a web article.

It should help:

<<<"If you'd like certain settings to always be applied to new
workbooks, you can override what file Excel uses as the basis for
new files. All you have to do is create a special template file
and save it in your XLSTART folder.

Before you actually save the file, you need to identify where
your XLStart folder is located (or your Startup: Excel folder if
you're using a Mac). The location varies depending on both your
Office version and operating system, so your best course of
action is to use your operating system's Find or Search feature
to locate the folder. You may actually find multiple copies of
the folder, depending on whether your system incorporates user
profiles--choose whichever makes the most sense for your needs
and make a note of its path.

Once you have the folder path, configure a workbook as you'd
like and then save it as a template. To do so, choose File | Save
As from the menu bar. Next, choose Template (*.xlt) from the Save
As Type dropdown list (the Format pop-up menu on a Mac). Using
the Save In dropdown list, locate and open the XLStart or
Startup: Excel folder. Finally, change the File Name setting to
Book.xlt (just name the file Workbook on a Mac) and click Save.">>>