This is probably not the most advanced Excel question out there, but one whose solution has been vexing me for years.
Changing a cell’s format is easy. But here’s the format I want to have as my default: I want it to use commas as a 1000 separator (for ease of reading off the screen) and I want decimals to show up only when there they have any meaning. Isn’t this how most of us use/write numbers?
If I go to Cell Format, I can choose General Number, which gets me the decimals as I want, but no commas. I can choose Number Format and select the commas box, but then I have to select a number of decimal places that will show up regardless of the number I’m working with. Again, not pretty or easy to read.
Is there any way I can combine the two? Any way I can make that the default cell format? If not, what about writing a macro that will accomplish this type of formatting?
Sorry to wander off into IMHO without an answer to your actual question, but no to this one. In a spreadsheet I would want all the numbers in a particular group to have the same format. It’s purtier that way
You can go to Format - Cells - Custom and enter #,###.## as your custom format. That last two #'s tell how many places after the decimal to show. You can add more if you work with numbers that need it.
That will do almost exactly what you want except there will be a decimal even after whole numbers (nothing after the decimal though; it doesn’t look bad).
You can format all the cells on a worksheet that way and save it as a default template. If you decide not to do that, the custom format will still be available. wherever you need it.
Thanks Shagnasty! If that’s as close as I can get, that’ll do. I putzed around a bit, and have it set at #,###.########### so I’ll never run out of decimal places in general use.
I agree with you gigi. I’m all for purtying up a spreadsheet when I’m working with different types of numbers. But I use Excel all the time for varying calculations in day to day life/work, and lots of times a window is open in the background with random calculations. The above format will make them much more pleasing overall.
I don’t know the application for these numbers, but I have to throw in $0.02 and mention that you may need to be conscious of significant digits, depending on what you are doing. (This is beyond the “purty” issue.) If you have a thermometer that is accurate and precise to a hundredth of a degree, and it reads exactly 72[sup]o[/sup]F, you would definitely want to show 72.00 which indicates you’ve measured it to the hundredth. If you use 72, we assume you are rounding to the nearest degree.
