Saving the Filtered Data

Now that the data has been filtered it would be great to save it so you can manipulate it further. To do
so is a rather straight forward process. Basically you will go to where you want to save it, Sheet2 in our
example, and go through the filtering process that we did above with just a couple of twists.

Steps For Advanced Filtering

On the destination worksheet (Sheet2 for example) place the cursor in a blank cell.
1

2
Select the Advanced icon with your left mouse button.
Under Action, select copy to another location
3

• In the list range, select the range finder icon.

The appears. Navigate to the appropriate

4 worksheet and select the data range not forgeting the headers, and click on the
little icon at the bottom right.
• Do the same for the criteria range.
• For the copy to range, select the first cell and select OK

11
 4

Performing Calculations on Filtered Data

Excel’s traditional formulas do not work on filtered data since the function will be performed on both
the hidden and visible cells. To perform functions on filtered data one must use the subtotal function.

The syntax is SUBTOTAL(function_num, range_reference1, range_reference2,….)The following functions

may be performed with the subtotal. The function_num within the syntax relates to the numbered
function.

Function Function Function Function

Number Number
1 AVERAGE 7 STDDEV

2 COUNT 8 STDDEVP

3 COUNTA 9 SUM

4 MAX 10 VAR

5 MIN 11 VARP

6 PRODUCT

12
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
 When you do so, the create PivotTable
dialog box appears.

• Excel will guess the data range

that you will want; correct it if it is
wrong.

• The default destination for the

PivotTable will be a new
worksheet.

• When doing so, a new sheet will

automatically be added to your
workbook.

• It is good to use a new worksheet

for the pivot table so that your
source data doesn’t accidently get
clobbered.

PivotTable Geography

Field
Pivot 2
List
Table
Report

3 4
Drop
Zones 6
5

15
 PivotTable Geography

The pivot table will be located here. The size will adjust as it needs to automatically.
1 • NOTE: If you move your mouse out of this area, the PivotTable Field List will disappear. To
get it back, merely left click your mouse within this area again and it will appear.
The fields listed here are your column headers on your original data source.
2 • These fields may be utilized in designing your PivotTable Report.
• You may use a field more than once.
Report Filters:
3 • Similar to a mentally page break. Allows the user to classify the data

4
Column Labels:
• Often created automatically by dragging data fields to the value zone. The user may also
drag data fields to this zone for grouping, etc.
Row Labels:
5
• Most common label
Values:
6 • Wide range of calculations may be performed on the values dragged to this zone.

Building a PivotTable Report – Part One

Adding row labels, adding column data, changing formulas in columns, changing headers &
number formats
In order to best illustrate how to design pivot tables, we will begin with the goal report, depicted below,
and then step through each design component.

This table will represent by department, the number of employees per department and their average
salary. We will also change a column header and the number font for the salary column.

16
Figure 1: Building a PivotTable- Part One

17
 2

Create Row Label

• Select the Dept name with your mouse, left click, and drag it to the row label
1
zone.
• The list our SCOE departments now appear to the left.
2

18
 2

Adding a COUNT and SUM Columns

• Drag down last name and salary into the Value zone
1

• Last Name
2
o Every employee has a last name. So if we use last name in a value field the
result displayed in the field will merely be the number of last names that
we have.
o Note that the formula used is count because it is a text field.
• Salary
o Excel automatically used the SUM formula.
o The SUM formula needs to be changed to the AVERAGE formula

To change the formula, we will launch the value fields setting dialog box and choose a different
formula.

19
 1

Changing a Formula

Left click on the Sum of Salary field

1

The value dialog box will appear. Using a left click on your mouse, select the Value Field Settings
2

The value field settings dialog box appears. On the summarized by tab, select the average
3
calculation from the drop down box

20