How do I include filtered out numbers in a SUBTOTAL?

[Guest]

I am creating a spreadsheet that several different people (who all want to
see different aspects of the spreadsheet) need to use. I want to be be able
to use the autofilter so that the various people can easily and quickly zero
in on ONLY the information that they want to see, but then the SUBTOTAL
functions get messed up. I do not want to use the SUM functions because there
are so many subtotal lines it would be difficult to specify what needs be
included/excluded in a SUM function.

 

[Dave]

CMDMHHS

What version of Excel are you using? I believe that this is addressed in
Excel 2003.

Dave

 

[Debra Dalgleish]

Instead of filters and subtotals, you could create a pivot table to
summarize the data. There are examples and links here:

http://www.contextures.com/xlPivot01.html

 

[Guest]

Hi Debra,

Thanks for the response. Is there anyway to permanently override the pivot
table formatting? I find that whenever I use pivot tables/charts, if I try to
use any custom formatting (for example, resizing the columns so they fit
better when printed, using colors to highlight certain totals, etc), my
formatting is eliminated when the pivot table is refreshed.

 

[Guest]

Hi Dave,

I am using Excel 2003 SP1

According to the Excel help files, I can use function_num = 9 to include
"hidden" rows, but it will not include filtered out rows.

 

[Debra Dalgleish]

Right-click on the pivot table, and choose Table Options.
Turn off AutoFormat table, and your formatting should stick