Default values for cell formatting and Pivot tables

I

Idoia

Hi

Each time I format a cell as a "Number" I have to go through a series of 3
clicks: decrease decimal to 0, add separator comma, choose the format for
Negative values. Is there a way to have Excel automatically have my favourite
as "default", so I can just hit Format Cells--> Number and nothing else? I
know I can create an easy macro to accomplish this in just one click, but I
want to know if I can change the Excel defaults.

On the same line, whenever I create a new pivot table i'd like the
"automatically format table" to be unclicked (default is clicked.)

Also, on a Pivot Table, when dragging numerical fields into the Data area,
Excel comes up with a Count of the field about 90% of the times, instead of a
Sum, so I have to go and change manually each and every field to Sum (I need
a sum about 95% of the times). I don't know how Excel decides whether to use
Sum or Count, but is there a way to make it use count always?

Thanks
 
D

Debra Dalgleish

You can create a new default template, with your preferred settings:

Open a new workbook.
Choose Format>Style
From the Style Name dropdown, select the Comma style
Click Modify, and select your preferred Number settings.
Click OK, click OK

To save the template, choose Save As
From the Save As Type dropdown, choose Template (*.xlt)
Name the File "Book.xlt"
Save it in the XLStart folder
(look in C:\Documents and Settings\YOUR_NAME\
Application Data\Microsoft\Excel\XLSTART)

When you click the New button on the toolbar, the new workbook will be
based on this template.

To format a number, click on the Comma button on the Formatting toolbar.

In a pivot table, you can't change the default function. If there are
blank cells, or cells with text, in the column, Excel will default to
the Count function when the field is added to the data area.

If the column contains only numbers, it should default to Sum.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top