excel pivot tables - blue pecan computer training ltd€¦ · 1 to use the pivot chart wizard that...

© Blue Pecan Computer Training Ltd - Onsite Training Provider www.bluepecantraining.com :: 0800 6124105 :: [email protected]

Excel Pivot Tables

http://www.bluepecantraining.com/


1



2

Table of Contents Pivot Tables ............................................................................................................ 3

Preparing Data for a Pivot Table .................................................................................. 3

Creating a Dynamic Range for a Pivot Table ............................................................ 3

Creating a Pivot Table ....................................................................................................... 4

Removing a Field ................................................................................................................ 5

Change the Position of a Field ....................................................................................... 6

Format the Value Field ..................................................................................................... 6

Change the Calculation Performed on the Value Field......................................... 6

Perform “Show Value As” Calculations ............................................................................... 6

Group Values such as Dates ............................................................................................ 7

Show Report Filter Pages ................................................................................................ 8

Display Records for Value Field Values ..................................................................... 8

Pivot Charts .......................................................................................................................... 8

Refreshing Pivot Data ....................................................................................................... 9

Applying an AutoFilter to a Pivot Table..................................................................... 9

Slicers (Excel 2010 only) ................................................................................................. 9

Creating Slicers .......................................................................................................................... 10

Formatting Slicers .................................................................................................................... 13

Connecting Slicers to Multiple Pivot Tables................................................................... 14

Creating Multiple Pivot Tables from the Same Data ........................................... 14

Replacing Blank Cells with Zero Values ................................................................... 15



3

Pivot Tables

Preparing Data for a Pivot Table Ideally, when you prepare data for a Pivot Table the data should

be in tabular layout. This means that there should not be any

empty rows or columns and that every column has a column

heading.

You should ideally also enter data in each field for each record in

your table. If you leave blank cells in a numeric field, Excel

assumes the data in that field is text which can cause problems if

you intend to use that field in the values area of the Pivot Table –

it will by default count rather than sum the data.

Do not use sub-headings in your table to indicate that records

belong to a group – always enter values in each field to complete a

record.

Creating a Dynamic Range for a Pivot Table One of the problems you may encounter with a Pivot Table is

when you want to add more records to the underlying data. Data

that is added outside the original range gets ignored when you

refresh the Pivot Table. One way around this is to create a named

range that uses the OFFSET function, in later versions of Excel you

can use Excel Tables.

Please see our video tutorials at

Using the OFFSET Function

http://www.youtube.com/watch?v=Ws3TdpG2QMs

Using Excel Tables

http://www.youtube.com/watch?v=xtz6-ittsx4


http://www.youtube.com/watch?v=Ws3TdpG2QMs

http://www.youtube.com/watch?v=xtz6-ittsx4


4

Creating a Pivot Table 1. During the course we created a simple Pivot Table based on

the table below. Note that the table has columns headings

and does not have blank rows or columns. This would be

the ideal way to set up a database for Pivot Table analysis.

2. To create a Pivot Table, click anywhere in the table you

want analyse.

3. Excel 2003 users

i. Click Data | PivotTable and PivotChart

Report

ii. Click Finish

3. Excel 2007/10 users1

i. Click Insert [tab] | PivotTable

ii. Click OK

4. An empty Pivot Table will appear on a separate sheet2

1 To use the Pivot Chart Wizard that was available in Excel 2003, use ALT D P to access it. This is particularly relevant if you want to Pivot on multiple ranges as this is an option in the Wizard. 2 Excel 2007/10 users may get a slightly different layout for the empty PivotTable. If this is the case click Options [tab] Options | Options | Display [tab] Classic PivotTable layout



5

5. Drag the fields in the field list into the appropriate part of

the Pivot Table. If you drop a field into the:

Row fields area - the Pivot Table will vertically list all

unique values in that field . In the example below, the

Location field has been placed here

Column fields area - the Pivot Table will horizontally

list all unique values in that field. In the example

below, the Role field has been placed here

Value fields area - the Pivot Table will perform a

calculation on the field based on the categories you

have set out in the row and column fields. In the

example below, the Annual Salary field has been

placed here

Filter Fields area - the Pivot Table will filter on any

unique value or combination of unique values1 in that

field. In the example below, the Weekend field has

been placed here.

Removing a Field 1. Drag the field label out of the Pivot Table

1 Excel 2003 users can only filter on one value at a time.



6

Change the Position of a Field 1. Drag the field label to its new position within the Pivot

Table

Format the Value Field 1. Double-click on the Value Field label. In our example this

appears as Sum of Actual Salary in our Pivot table

2. Click on the Number Format and select the format you

require.

Change the Calculation Performed on the

Value Field 1. Double-click on the Value Field label. In our example this

appears as Sum of Actual Salary in our Pivot table

2. Choose the type of calculation you want to perform from the

list

Perform “Show Value As” Calculations

To see what other types of calculations Pivot Tables can peform

see our video at

http://www.youtube.com/watch?v=JJHwXzHMKA4


http://www.youtube.com/watch?v=JJHwXzHMKA4


7

Group Values such as Dates 1. In the example below, we need to group dates into months.

2. Click anywhere in the field you want to group. In this

example we would click anywhere in the Date field.

3. Excel 2003 users - on the Pivot Table toolbar click

PivotTable | Group and Show Detail Group

3. Excel 2007/2010 users – click Options [tab] Group

Selection

4. Select the appropriate group option, in our example this

would be Month

NB If you are grouping dates and want to

group by weeks, you will notice that there

is not an option for this. However if you

group by days and then set the number of

days to 7 you will effectively group by

weeks. Make sure your starting at date is

set to the beginning of the first week ie the

first Monday.



8

Show Report Filter Pages 1. Place the field that you want to show Report Filter Pages for

in the Filter Fields area of your Pivot Table

2. Excel 2003 users - on the Pivot Table toolbar click

PivotTable | Show Pages

2. Excel 2007/2010 users – click Options [tab] Options |

Show Report Filter Pages

3. Select the field you want to show pages for

4. A sheet will be created for each unique value for that field

Display Records for Value Field Values 1. Double-click on any value in the Value Fields area.

2. A separate sheet will display records relevant to that value

Pivot Charts 1. Once you have created your Pivot Table it is possible to

create a Pivot Chart based on it.

2. Excel 2003 users - on the Pivot Table toolbar click the Chart

Wizard button

2. Excel 2007/10 users –

i. Click Options [tab] Pivot Chart

ii. Select the chart type you want

The x-axis of your chart will correspond to the row area of your

Pivot table and the y-axis will correspond to the column area.

You cannot use XY scatter, bubble or stock charts when creating a

Pivot chart. To get around this you could convert your Pivot table

into hard values by performing a copy and paste values – you can

then create a standard chart using this data. If you want to keep

the Pivot table, then create an additional table that links back to

the values in the Pivot table. You will need to turn the Generate

GetPivotData option off to achieve this.



9

Refreshing Pivot Data 1. To refresh data in your Pivot Table:

Excel 2003 users

On the Pivot Table toolbar click Refresh Data (red

exclamation)

Excel 2007/10 users

Click Options [tab] Refresh

Applying an AutoFilter to a Pivot Table If you try and apply an AutoFilter to a Pivot Table you will find

that the option is greyed out. There is a way around this! Select

the cell immediately to the right of the last title in your Pivot

Table as shown below.

Now the option to apply an AutoFilter will be available for you to

use.

Slicers (Excel 2010 only) Excel 2010 includes a new feature which Microsoft have called

Slicers. Slicers essentially perform the same functionality as the

filters that are already available in fields within the Pivot Table

(as shown below).



10

Slicers provide a better interface for filtering and allow you to

perform the same filter across multiple Pivot Tables.

Creating Slicers

To create a Slicer, click anywhere in your Pivot Table and on the

Ribbon select the Options tab, then click the Insert Slicer1

button.

1 If the Insert Slicer button is greyed out then you are probably using a 97-2003 Excel file. You will need to convert the file to the new file type to use the Slicer functionality.



11

Excel may prompt you to refresh your Pivot Table before

proceeding.

The Insert Slicers dialog box allows you to

select which fields you want to use Slicers

to filter on. Each tick will create a separate

Slicer.

The resulting slicers can be repositioned anywhere in your

worksheet.

To filter your Pivot Table using the slicers, select the values you

want to filter on within the Slicers. To select multiple values hold

down the CTRL button.



12

To clear a filter click on the clear filter button as shown below.



13

Formatting Slicers

To change the appearance of a slicer, first of all select it (easiest

way to do this would be to click somewhere on its border or near

the top where the field name is) and then click on the Slicer Tools

Option tab on the Ribbon.

To change the colour of your Slicer, use one of the slicer styles.

You can display values within columns within your slicer which is

useful when there are a lot of values to display. Use the column

setting as shown below to achieve this.

The department Slicer is shown using two

columns.



14

Connecting Slicers to Multiple Pivot Tables

You can use a Slicer to filter

several Pivot Tables at the

same time. To achieve this

right-click a Slicer and select

PivotTable Connections

from the menu.

You can then select the Pivot

Tables you want to connect

to.

Creating Multiple Pivot Tables from the Same

Data If you create multiple Pivot Tables from the same data you will

bloat the file size considerably if you do not share the pivot cache.

When you create a Pivot Table, Excel stores a snapshot of the data

in the pivot cache – which is why a Pivot Table must be refreshed.

If you share the cache amongst your Pivot Tables you make a

considerable saving on resources.

In Excel versions previous to Excel 2010, Excel gives you the

option to share the cache, but Excel 2010 does not. The only way

around this in Excel 2010 is to copy and paste a Pivot Table to

another location: the copied version of the Pivot Table shares the

cache of the original.

Pivot Tables that share the same cache also share the following:

Calculated fields

Calculated items

Grouping settings

If you refresh a Pivot Table all other Pivot Tables that share the

cache are also refreshed.



15

Replacing Blank Cells with Zero Values To replace blank cells with Zero Values, do the following:

Excel 2007/10

1. Select any cell within your Pivot Table

2. Click the Options tab on the Ribbon

3. Click the Options button as shown below

4. On the Layout & Format tab, enter 0 for the For empty cells

show: option

5. Click OK to confirm.


excel pivot tables - blue pecan computer training ltd€¦ · 1 to use the pivot chart wizard that...

Documents