Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Pivot Tables

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Pivot Tables

Resolved · Low Priority · Version 2007

Reply Fri 19 Feb 2010, 16:13Delegate Yda said...

Yda has attended:
Excel Advanced course

Pivot Tables

to change the default of Counting everything to SUM. Sometimes if you pull a headin into the table the first time is SUM's it up but when you add anything after that it goes straight to default of Count

For upcoming training course dates see: Pricing & availability

Reply Mon 22 Feb 2010, 15:08Trainer Anthony said...

RE: Pivot Tables

Hi Yda, thanks for your query. When it comes to Pivot Tables SUM is the default for values; COUNT is the default for columns that contain text entries, dates, or times. If a column contains even one text entry other than the column heading, you can only COUNT the entries. Try double-checking the cell formats of the data you are pivoting, I'm willing to bet there are some text formatted entries in there. It's actually impossible (without code) to change the default analysis of a Pivot Table (see link below) which is why I reckon you've got text or date/time entries in your source data.

http://tinyurl.com/pivottabledefaultanalysis

Let me know how you get on,

Anthony

Mon 1 Mar 2010: Automatically marked as resolved.

Excel articles

· Don't Get Lost In A Sea Of Data, Use Excel Navigation
· Experience The Power Of PowerPivot
· 3 Reasons Why Excel Training Courses For Employees Can Improve Your Business
· Data Validation In Excel
· Plug Into The Big Grid

Please browse our web site to find out more about
excel training class and other Microsoft training courses.

Excel tip:

Use the SUBTOTAL function in Excel

You can create subtotals in your spreadsheet using the SUBTOTAL function, which looks like this:

=SUBTOTAL(9,cell:cell)

9 represents the function being used (SUM), followed by the range of cells the function is operating on.

The neat thing about using the Subtotal function is that if you have used it several times in the same column or row, clicking on the AutoSum button at the end of the column or row will make Excel add only the results of cells containing the Subtotal function in that column or row.

View all Excel hints and tips