Scribd
Upload
23 views2 pages

Understanding PivotTables in Excel

The document explains the use of the SUBTOTAL function in Excel, specifically how it can sum filtered data, compared to the SUM function which includes all data. It provides an overview of PivotTables, highlighting their ability to summarize and analyze large data sets interactively, with features like filtering, sorting, and custom calculations. Additionally, it outlines best practices for setting up data for PivotTables, emphasizing the importance of structured data without blank rows.

Uploaded by

hersheys281
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
23 views2 pages

Understanding PivotTables in Excel

The document explains the use of the SUBTOTAL function in Excel, specifically how it can sum filtered data, compared to the SUM function which includes all data. It provides an overview of PivotTables, highlighting their ability to summarize and analyze large data sets interactively, with features like filtering, sorting, and custom calculations. Additionally, it outlines best practices for setting up data for PivotTables, emphasizing the importance of structured data without blank rows.

Uploaded by

hersheys281
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

An example of the formula is: =SUBTOTAL(9,E12:F19) The English translation is using the ninth

subtotal function, which is SUM, add up all of the data within the range that is selected by the filter.
For comparison, included is the SUM function for the same range which brought back the total for all
of the data cells, hidden or displayed.

PivotTables
Defined
The foundation of what is a PivotTable report is explained as follows:

As long as you can connect to the data, whether it be locally in the same workbook or remotely
in other locations, you can built PivotTable reports that rearrange the raw data and change
it into meaningful information

A pivot table is an interactive way to quickly summarize large amounts of data; to analyze numerical
data in detail and to answer unanticipated questions. They are especially designed for:

• Querying large amounts of data in many user-friendly ways


• Subtotaling and aggregating numeric data, summarizing data by categories and subcategories,
and creating custom calculations and formulas
• Expanding and collapsing levels of data to focus your results, and drilling down to details from
the summary data.
• Moving rows to columns or columns to rows (or “pivoting”0 to see different summaries of the
source data.
• Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset
of data to enable you to focus on the information that you want.

13
Thou Shalts in PivotTable Land

1 Headers should be in columns, not rows

2 No blank rows between the headers and the data

Best to have the pivot table on a separate worksheet so it does not accidently clobber the
3
data

4 Best to have simple data, rows and columns of data.

5 Best to format your area as a table, especially when you will be adding data to it. The table
is automatically expanded when data is added to the next row. Now when you launch
create a pivot table the range will be the table name, and not the cell addresses

Basic PivotTable Data

Pivot Tables work best when you have simple data in rows and columns.

• Headers are across the top in the first row


• Data consists of a single row across numerous columns
• There is not a blank row between the headers and the rows

Inserting a Pivot Table


• Select any active cell within your data
table
• Navigate to the Insert tab and select
PivotTable

14

You might also like