Excel gurus—cell formatting question

Factual Questions
1

Yes, I use Excel for a lot of Very Important Things, but I also use it as a glorified—and very handy—calculator.

I want to format cells to recognize and treat numbers like a regular, simple calculator: If I put in a number with no decimal points, it should display it that way; if I divide two whole numbers and decimals result, I want those to appear; if a number goes beyond three digits, I want it to add a comma. That sort of thing. I know how to get commas to show up, but their not on by default. And I know how to specify decimal places, but I don’t want to see “.00x” unless the number has decimal places in it.

Is such a thing possible? Is there an alternate spreadsheet out there that does that?

2

What version of Excel?

3
  1. The “Welcome to the Wonderful World of Ribbons” version. BTW, where the hell did they put Help>About?
4

Office Button > Excel Options > Resources > About

Intuitive, no?

So, back to the OP: Conditional formatting is my first thought. Let me noodle around and see what rules I can come up with.

5

  1. Select the area you want to apply formatting to (a cell or range of cells).

  2. Right-click and select **Format Cells…
    **

  3. Choose “Custom” from the list on the left.

  4. Under Type: enter: #,##0.### (the number of “#” after the decimal point denotes how many decimal places it will show before rounding).

6

Wow, that’s awesome, thanks.

Anyone know why it’s so freakin’ hard to find the default template? Will I be able to select all cells, change the format, and have that be the starting point?

I can’t believe it’s taking me this long to find the template. No, really. Google can bite my shiny metal spreadsheet…

7

Concur. That’s much simpler than anything I was coming up with.

Although it does not address how negatives and the like will be displayed, which gets much more complex.

See here and scroll down to “Custom Number Formats.”

8

“A number format consists of up to 4 items, separated by semicolons. Each of the items is an individual number format. The first by default applies to positive numbers, the second to negative numbers, the third to zeros, and the fourth to text.” (CITE)

So for the above make it:

#,##0.###;#,##0.### (will display negative numbers in red)

9

That’d still show the decimal point (though not the decimal digits themselves), right?

10

Yes.

11

It’s only briefly mentioned in the KB article linked above but if you want to be swish you could use Conditional Formatting.

[INT (A1) = A1] Format this way
[INT (A1) <> A1] Format this other way
etc.

12

Okay, I give up (angrily so, but I can’t decide whether to direct it at my own incompetence or Mircosoft).

How do I change the default template?!

I’ve tried following a handful of directions on the net, but to no avail. Even the MS KB article failed–there’s no “save as template” option under save as.

13

My 2007 says “Excel Template (*.xltx)” under “Save as type.” Any chance you overlooked it?

14

Ah, one step closer.

It’s not under the regular “save as” pane, but under “save as Other Formats” and then it’s buried eight or nine down–below Web pages and whatnot.

So I changed the format, went through the save as template, and it dropped it into C:\Users\Rhythmdvl\AppData\Roaming\Microsoft\Templates.

New files are not showing the change. I changed it to Book, Book1, Sheet, and Sheet1. Nothing. Opening the template file directly shows the change, but I’m allllmmooosssst there.

(bonus question: Is there any way to easily bind keys in Excel? The marco wizard lets me add Ctrl+ a key, but I want to use Alt.)

Still Googling to see if I can help myself, but I’m finding myself helpless :frowning:

15

I see what you did and why you weren’t seeing it now. I never pay any attention to those things. :slight_smile:

What are you calling your template? The info I’m looking at says it needs to be named Book.xltx or Sheet.xltx, depending.

16

Why, Excel, do you create a template in a different directory than you want it stored in? Or why don’t you put the default template in with others?

There were three XLStart folders. It finally worked by putting it in:
C:\Program Files (x86)\Microsoft Office\Office\XLStart

I don’t know whether it needs to be named Book or Sheet: in my frustration I copied the template and used both names.

But it seems to be working, which is soooo veeerrrryyyyyyyyyyyyyy gooooooood.

Thanks!

17

Making it the Book template will only use it when you create a new Workbook. Adding a new sheet to a workbook uses the Sheet template. Saving it as both makes it work either way. Good job.

18

Again, thanks. That book/sheet difference makes sense. I took the time to change the ‘normal’ formatting for currently active/used spreadsheets and as a result there’s joy in Mudville.