2014 act congress - mc12 neale blackwood paper

Excel Pivot Tables from Basics to Advanced

by

Neale Blackwood

A4 Accounting

[email protected]

www.a4accounting.com.au

CPA Congress November 2014

Pivot Table from Basics to Advanced by Neale Blackwood

© 2014 Neale Blackwood of 42

Introduction

Pivot tables are one of Excel’s most powerful tools. They allow you to summarise huge data sets in

seconds, without writing a single formula or typing a solitary character. Using just your mouse you

can create reports that could take hours to create using formulas.

Added to that, pivot tables have in-built sorting and filtering abilities which are difficult or impossible

to re-create using just formulas. Users can interact with the table and easily amend it to see the data

they are after.

We will cover the basics in the first half of the session and the more advanced topics in the second

half. There will be a break in the middle. We will finish with a demonstration of PowerPivot.

Versions This session will be based on Excel 2010 as it is the most popular version at the time of writing. I will

include some Excel 2013 details as well.

Limitations Pivot tables have some limitations, but once you know what they are and how to overcome them

you can produce amazing reports, quickly.

Advantages Easy to create and modify a report

No formulas required

Data tables can be in other systems

Data tables don’t have to be imported into an Excel sheet

All data is included in the report (subject to Refresh and data source limitations)

Filtering and sorting the report is easy

You can create grouping (subtotals) that aren’t built-in to the data

Disadvantages Report can only be based on a single data table (see PowerPivot section below for a

solution) Note: Excel 2013 has made it possible to create relationships between tables.

Data doesn’t automatically update (you have to Refresh)

Report layouts are limited

Report formatting is restricted

Terminology used in headings eg (Sum of) is unnecessary and distracting

PowerPivot Microsoft introduced a new product as a free Add-in for Excel 2010. PowerPivot takes Excel’s data

handling capabilities to another level and enables you to interrogate multiple data tables at once

and produce reports based on relational database structures.

PowerPivot comes standard in some versions of Excel 2013. It is probably the biggest single

improvement in Excel since pivot tables.



PowerPivot removes the one table, one report limitation. It also adds extra functionality to your

reports. A new language called DAX allows you to perform complex calculations across data tables

using functions similar to Excel’s existing functions.

This session won’t be going in to any detail about PowerPivot (it deserves its own Master Class) but

we will review an example of PowerPivot at the end of the session to demonstrate one of its

features that allows you to combine data tables for a pivot table report.

Data Cleansing Sometimes to remove a data limitation you may need to amend the data. This can be as a simple as

adding fields (columns) to a data table that can simplify the pivot table creation. In many cases

knowing what you want out of a pivot table will help you focus on getting the data right.

I will offer solutions to some common issues in getting the data ready for a pivot table.

Terminology When working with data it is useful to be aware of the terminology that Excel uses.

Field - a column of data, typically the entries in a field are all similar e.g. all dates or all values.

Record - a row of data, made up of different fields.

Field Heading - a column heading, typically in row 1 of the sheet.

Pivot Cache An important concept to understand when working with pivot

tables is how they handle the data. When you create a pivot table

Excel will copy all the data that you define, whether in Excel or

from an external source, into memory (RAM). This is called the

Pivot Cache.

The pivot table is created from this Pivot Cache. There is a direct

link from the Pivot Cache to the pivot table. There is no direct link

from the data to the pivot table.

Changing the data will not automatically update the pivot table.

To update a pivot table you must Refresh it.

You are in effect refreshing the Pivot Cache, which in turn

(immediately) updates the pivot table. Pivot tables are so fast

because you are interacting with RAM, which is extremely fast.

When you copy a pivot table, the new pivot table will share the Pivot Cache with the original pivot

table, this has some implications for editing the reports that will be explained later in the session.

Each time you create a new pivot table, you create a separate Pivot Cache in RAM.



Data Rules What follows are a few simple rules to use when creating data tables in Excel. If you follow these it

will speed up the processes involved in creating pivot tables as well as other Excel data-related

processes.

1. Don’t have blank rows in your data - when Excel encounters a blank row it assumes the

data has finished and it usually will not include any data below the blank row. I have seen

people use blank rows to segregate a data list. If you need to segregate a list use a higher

row height rather than a blank row.

2. Don’t have blank columns in your data - when Excel encounters a blank column it assumes

the data has finished and it usually will not include any data to the right of a blank column.

It is ok to have a column heading with no data below it but you should have a heading. If

your data comes in with blank columns you may need to manually add column headings. I

will often use the letters of the alphabet in lowercase to fill in blanks in the header row.

3. Make the column headings row bold – using the bold font format for the header row helps

identifies the row as a headings row.

Data One of the advantages with pivot tables is the ability to report on data in external systems and not

having to bring in the data in the first case.

Experience has taught me that it is it useful to initially bring that data into Excel to view it. When you

are working with external databases field names and the content of the fields may not always be

what you are expecting. Viewing the data can assist in making sure the data you are working with is

what you are expecting.

After initially checking the data you can then delete it and summarise via the pivot table. We will

look at using external data after the break.

Data Examples

The examples used in this manual are not complex. The data used is straightforward which allows

me to spend less time explaining the data and more time explaining pivot tables.

The Quick Access Toolbar (QAT) In my training sessions I am amazed how few people know

about the Quick Access Toolbar (QAT).

Initially the QAT is positioned above the ribbon and displays

only a few icons. I suggest you show it below the ribbon which

can be done by right-clicking it and selecting Show Quick

Access Toolbar Below the Ribbon.



This toolbar is customisable. You can right click

any icon on the ribbon to add it to the QAT.

As we go through the session I will recommend a

few icons to add to your QAT to speed up your

work with pivot tables.

Pivot Table Wizard

In the older versions of Excel pivot

tables were created via a step by

step Wizard interface.

That is still available using Alt d p

(keys pressed in sequence).

I don’t advocate using this Wizard

anymore as it doesn’t recognise

formatted tables – an important

topic to be discussed next.

There was an option to use Multiple

consolidation ranges, which can only

be accessed via this Wizard.



Format As Table

This feature on the Home ribbon is essential to creating low maintenance pivot tables.

The feature does a lot more than just adding formats to a table. It automatically expands

the table when rows or columns are added to the table. When you Refresh the Pivot

Table any extra rows will be automatically included in the report, any extra columns will

automatically be included in the Field list ready to be added to the pivot table.

Any formula columns in the data table will automatically extend as new rows are added. This is ideal

when adding columns to add extra options to the pivot table.

Many of the features that are added when you use Format as Table are useful for reports that use

formulas. I will not be covering these features as we will concentrate on the advantages with using

Format as Table for pivot tables.

The data in the Tables sheet has a few issues which we will work on in the next section.

PRACTICE:

1. In the Table sheet click anywhere inside the data table and then click the Format as Table

icon on the Home ribbon.

2. This will bring up the gallery of possible

formats. Choose a format by clicking it.

You can change your selection later if you

wish.

You can use Ctrl + T when you select a cell

in the table to apply the default format.

3. Excel will estimate where the table is and display the

range in the dialog box. Excel will stop at any blank

rows or blank columns.

NOTE: In general you should not have any blank rows

or blank columns in your table.

A column with a heading, but no data, is acceptable.

4. Make sure the My table has headers option is ticked and click OK.

5. The table will be

formatted and the Filter

icons will appear next to

the column headings.



6. At the bottom

of the

formatted

table a small

icon in the

bottom right

cell denotes the end of the table.

7. You may notice that there is a new Design

ribbon tab visible when you select a cell

within the table.

One of the options in that ribbon is the

ability to name the table. I recommend

naming your tables.

The Table Name box is in the top left corner of the ribbon.

In this case I will name this table tblData. (I use the prefix of tbl to help identify tables.)



Getting the Data Right

Sometimes the data isn’t in a layout that a pivot table can use as per the example in the previous

Format as Table section. There are some common issues that stop you being able to create a pivot

table.

Don't give up! Many times I have helped people get the most out their data by helping them get their data right.

This can include performing data cleansing processes which fix structural and data errors. In general

I find people give up too easily. Excel is incredibly powerful and there are solutions to most data

issues.

Even if you can't find a built-in all formula solution to your problem you can always fall back on using

macros. There is a huge amount of information available on the Internet for macros. I have included

some macros in the accompanying files that can speed up some of the manual processes involved in

amending pivot tables. These macros are described in the Appendix.

Below are some common fixes for the types of data problems you will face with pivot tables. These

can be modified to handle many other instances where the data isn't quite right.

Data cleansing is a whole topic on its own and I will just touch on a few quick fixes that can remove

some pivot table limitations and frustrations.

Blank Cells

Blank cells can affect many of Excel's shortcuts and also slow down development time for pivot

tables. You may not know it, but blank cells can cause some frustrating issues with pivot tables

when a pivot table report defaults to COUNT instead of SUM it is probably because of

blank cells - even a single blank cell can cause this

when you try and group by dates (something we will look at later) Excel will stop you if

there is a single blank cell in the date range

a field has dates but the filter option doesn’t treat them like dates because of blanks

Blank Values

In our value cells in the previous example we need to replace all the blanks with a zero. In our

example column E contains values and some of those cells are blank. This could appear to be a huge

task in a long list and a manual one, but there is a quick and easy way to do it.



Filtering

In our formatted table we already have our filter icons visible so we can use them to filter the table

so that we see the blank cells in column E, the Amount column.

PRACTICE:

1. Click the filter icon for the Amount column and select

Blanks.

NOTE: Blanks always appear at the bottom of the

filtered options.

2. When you work with a filtered list any entries made to

the filtered list only affect the visible rows.

Excel has a shortcut that can enter a value into

multiple cells in one step.

3. Select all the blank cells in the filtered table in column E.

4. Type 0 and hold down the Ctrl key and press Enter.

5. This will populate all the blank cells with a zero.

6. You can now remove the filter. That fixes the first

problem.

Missing Entries

You will have noticed in our data that we have three columns, Date, State and Account that have

missing entries. Unfortunately data imported into Excel can have this type of omission. One popular

accounting package does this all the time.

Fixing the issue can be done manually, which typically takes more time, or you can use a special

technique that includes a formula that can do it reasonably quickly.



To populate the missing entries we can use the F5 function key to select all the blank cells and then

use a technique using the control key to populate all of those cells with a formula. Finally we can

then use paste values to capture formula results.

Note: this technique can also be used if you have multiple value columns that need blanks filled with

zeroes.

PRACTICE:

1. To select the whole table click any cell within the table

and press Ctrl + *. You can also use Ctrl + A.

2. Press the F5 function key and click the Special button.

3. The Go To Special dialog is displayed and we need to

click the Blanks option and click the OK button.

4. This will select all the blank cells in one step.

5. The layout of the report is such that all the blank cells

should be linked to the cell above.

6. We need to look in

the Name Box to see

which cell is active.

The Name Box is to

the left of the formula

bar and above the

column letters.

In this example the

active cell is C3.

7. We need to create a

formula that will be

entered into all of the blank cells. This formula is going to refer to the cell above the active

cell. This will populate a relative reference in all of the blank cells so that they all refer to the

cell above.

8. In the formula bar type =C2 DON'T PRESS ENTER

9. Hold the Ctrl key down and then press Enter.

10. As we saw from the previous example this will make the same entry in all of the selected

cells. Because the formula is a relative formula i.e. no $ signs have been used, then a relative

formula is entered in all of the cells. This formula refers to the cell above each cell.



11. The final step is to capture the values from each formula. Even though the data looks good,

we cannot leave it with these formulas, in case the data is sorted. Sorting would corrupt the

entries in the cells. In general you should never use formulas in a database that refer to

another row in the database, because sorting will affect the relative positions.

To capture the values we can simply copy the columns and then use Paste Special, Values.

Extra Fields

Another common issue you will encounter, usually after you create a pivot table, is that there is a

measure or category you need that isn’t in the data table. The typically solution is to use the

VLOOKUP function to add the required data to the table. I am not going into a lot of detail for the

VLOOKUP function, but supply it as a solution to missing fields.

PRACTICE:

1. In our example we have a trial balance with account number and amount, but we don’t

know what category the account number is and that is what we want to report on. There is a

separate table with account number and category. In most cases the table would be on a

separate sheet.

2. The VLOOKUP function is the common function for this task. The formula for cell C2, which

can be copied down, is

=VLOOKUP(A2,$E$2:$G$5,3)

3. A quick way to copy down is to

double click the Fill Handle.



Excel 2013 has a new Relationships option

which can remove the need to use the

VLOOKUP function as shown above.



Part 1: The Basics

Creating a Pivot Table

There are only two simple steps in creating a pivot table.

1. Define the data

2. Design the layout of the report

Define the Data

This is easy if you have followed the data rules mentioned earlier.

As also mentioned earlier I recommend all Excel tables used for pivot tables have the Format as

Table feature applied to them. It is also worth naming the formatted table.

Click anywhere within the data and click the Insert ribbon tab and then click the Pivot

Tables icon. Excel will automatically “guess” where the data is. When it “guesses” it

stops at blank rows and blank columns. Using a formatted table simplifies the selection

process.

You can choose between internal data (existing sheet) or external data (other database or other

source). We will focus initially on internal data and look at external data later in the session.

In most cases you will create the report in a separate, new worksheet. In general you should avoid

having the report and the data in the same sheet.



Design the Layout of the Report

On the left of the sheet

is a blank area where

the pivot table will be

created. On the right is

the PivotTable Field List

interface that allows you

to design the report.

The four sections at the

bottom of the dialog are

explained below.

You create the pivot

table report by using the

mouse to click hold and

drag the fields (column

headings) from the field

list at the top to one of

the four sections below.

Report Filter Section

Dragging a field to the Filter section won’t amend the layout of the report, but it will add a filter drop

down in the top left of the report area and allow you to filter the report by that field. I don’t

recommend using the filter section (I will explain why later). I recommend you use a feature added

in Excel 2010 called Slicers, which will be covered in detail later in the session.

Row Labels Section

The fields you drag here will be summarised in the rows of the report. The order they are listed is the

order they are shown in the report. The first field listed will be broken down by the second field

listed which in turn is broken down by the third field listed. Changing the order of the fields changes

the layout of the report.

Unique Entries - when you initially drag the field to the Rows section all the entries in the field will

be listed. This can be a quick way to extract unique entries from a field.

Dragging field names back to the field headings section at the top will remove it from the section

and the report. Also un-ticking the field name at the tops of the dialog will remove it from the

section and report.

Column Labels Section

The fields you drag here are will be summarised across the columns of the report. The order they are

listed is the order they are shown in the report. The first field listed is broken down by the second

field listed which in turn is broken down by the third field listed. Changing the order of the fields

changes the layout of the report.

Values Section

Dragging fields here will populate the values for the report based on the row and column headings.

The order in the section determines the sequence they are shown on the report.



The default setting for the calculations is SUM. This is overridden if there are blank cells or text cells

in the value field columns. COUNT is used in those instances.

A sample pivot table with its settings is shown below.

To change COUNT to SUM click the Options

ribbon tabs and click the Summarize Values By

drop down.

You can also click the drop down next the Value

field and choose Value Field Settings - see image

below.



The order that you populate the sections doesn’t matter. The report will automatically update as

you drag fields to the sections or move them around between sections. This is part of the power of

pivot tables, the ease and speed with which you can modify the report.

That’s actually how the pivot table got its name. Because it was so easy to move fields between

columns or rows it was easy to “pivot” the report from one view to another.

Pivot tables are ideal for creating ad hoc reports because of the ease with which you can modify the

report. In general the more fields (columns) you have in your data the more useful pivot tables can

be. But having said that, in terms of quickly creating summaries, pivot tables have their place in all

sorts of reports.

The major advantage of pivot tables is the fact that all entries are automatically listed. Any new

codes are automatically included when a pivot table is refreshed. This assumes no filters are in place.

PRACTICE:

1. Use the example on the previous page and create a simple report.

Try moving fields between the rows and columns sections. Try dragging fields underneath

one another.

2. Filter example – create a pivot table using the following settings.

3. The filter selection drop down appears above the

pivot table.

Filtering a pivot table using a single filter option

works well. Applying multiple selections however,

does present a problem.

In the Filter drop down there is an option to Select

Multiple Items. Using this option can prove

problematic as shown on the next page.



I have applied the filter below but the report only shows Multiple Items. Hence the reader will not

know what filter has been applied.

This is not acceptable for most

reporting requirements.

For this reason I recommend using a new feature called Slicers for your pivot table filtering. We will

examine Slicers later in the session.

Formatting the Pivot Table Unfortunately the default number

format displays cents and doesn’t use

the comma separator for thousands.

This means in most cases you will

have to change the number format

for your pivot tables.

To fix the formatting, right click any

number in the pivot table report and

choose Number Format.

Then select your format. I

frequently use the one

shown here and click OK.

Double Clicking a Cell One pivot table feature that many people are unaware of and many others find out by accident is

that double clicking a value cell in the pivot table will create a separate new sheet that is populated

with a copy all the records (rows) from the data table that makes up that particular value cell.

This can be a quick and easy way to split up a data table into separate categories on sheets.



Adding a Field

Currently the data contains the sales value and the cost. It doesn't contain a margin value. You can

add another field to your data to calculate a margin, which we'll do. Later in the session we will look

at all alternative to adding a field to your data table.

PRACTICE:

1. In the Sales_Data sheet click in cell N1 and type a word Margin.

2. In cell N2 to enter a formula to subtract the cost in column M from the Sales_Value in

column H. When you press enter the formula should be copied all the way down column N.

Note the table names used in the formula.

3. To be able to view the new column you will need to Refresh (Alt + F5) the pivot table and the

new column will be visible in the fields list. You can now add the margin to the report.

Pivot Charts

Pivot charts are a special interactive chart based on a pivot table. They are dynamic and

directly linked to the pivot table. Changing the pivot chart will change the pivot table,

and changing the pivot table will change the pivot chart. The same refresh issues apply

to pivot charts.

When you start to use pivot charts you will quickly see that you will usually need one pivot table for

your reporting requirements and a second, more summarised, pivot table for your charting

requirements.

Pivot tables tend to be reasonably large, but a large pivot table in general creates an unreadable

chart. Pivot table used for charts tend to be compact.

The exception to this is time series data that can produce useful line or scatter plot charts.

Interactive Pivot charts include drop downs that allow the user to filter the chart, these affect the pivot table.

Note: You can’t sort by the field used as a filter.

PRACTICE:

1. Click any cells in the pivot table and

then click the PivotChart icon.

2. Select the type of chart. I will use the

standard Clustered Column chart.



3. The chart below is created.

4. The legend can be

moved and sizes

changed to make the

chart more readable.

See changes below.

5. The row and columns

drop downs allow for

filtering.

6. Any changes to

the chart will be

mirrored in the

pivot table.

7. Making changes to the pivot table eg adding fields can make the chart unreadable.



Filtering with Slicers

A new feature added to Excel 2010 was a Slicer. This is a graphic interface that allows you to filter

pivot tables. Their use has been expanded in Excel 2013 to include formatted tables. Slicers used for

date fields have also been improved in Excel 2013.

As demonstrated earlier, applying filters in pivot tables can be problematic when you filter by

multiple entries.

Slicers have a few advantages over using the Report Filter section:

Slicers are graphic objects and show precisely what filters are in place

They are quicker to access and you can use the mouse, Ctrl and Shift keys to speed up

selections

One slicer can control multiple pivot tables

a new type of slicer was added in Excel 2013, which is a time series slicer

PRACTICE:

1. Select any cell in the pivot table. Click the Options ribbon tab and select the

Insert Slicer icon.

2. A list of fields will be displayed. You can select as many fields

as you want. Click OK. I wil use the Supply_State

3. A Slicer will be added to the sheet. You can move it around

and place it where ever you like. It is not limited by the

columns or rows.

4. When the Slicer is selected an Options ribbon tab is available where you can change the

colour scheme. The Slicer is a graphic object so you can resize it normally. Right clicking the

Slicer also provides shortcuts to many of the Options available.

5. Using the Slicer is reasonably intuitive. Using your mouse you can hold your Ctrl key down to

select multiple items or you can use your Shift key to select a group of items.



6. After you make a selection in a Slicer the

remove filter icon will be available. You

can click this to clear all the Filters applied

by the Slicer.

7. One of the advantages with using a slicer is the ability to control multiple pivot tables. When

you right-click the slicer you will see the PivotTable Connections option.

8. Clicking this will allow you to specify other pivot tables to be controlled by a single slicer. The

pivot tables need to use the same data source.

9. This of course raises a similar filtering

issue on other sheets because the Slicer

may appear on a separate sheet to the

report, hence you may wish to place a

text box, or a cell note on the report

mentioning that the report is filtered by

a Slicer on a separate sheet.

New in Excel 2013

Excel 2013 includes a Timeline Slicer that works better with dates.



The Timeline slicer has sections you can click on to select a month or months.

There are double- headed arrows that

allow you to drag selections to extend

them.

You can choose the type of period to use.

That concludes the basics of pivot tables. These provide you with enough instructions to take a data

table and display it in the layout that you require. The next section will expand upon what we have

learnt and show you how you can get even more out of your pivot tables.



Part 2: Advanced Techniques

Calculated Fields Earlier I mentioned one of the advantages of pivot tables was that there are no formulas required.

Whilst this is true, it doesn’t mean you can’t use formulas to add extra calculations to your pivot

tables.

There are two features that allow you to add extra calculations to a pivot table.

The Calculated Fields option allows you to add extra columns to your pivot table report without

those columns being in the data table. The other option is a Calculated Item which we will cover

after Calculated Fields. These are useful when working with external data that you can't really adjust

using normal Excel functions and formulas.

Earlier we added a Margin column to the data table to allow margin reporting this could have been

done via a Calculated Field. Some calculations cannot however, be done by using an additional

column in the data table.

If we wanted a margin percentage we can’t use an extra column because in the report we can’t SUM

the percentages to arrive at a summary level result. We can use a Calculated Field to provide

percentage-based calculations.

PRACTICE:

1. Select a cell in one of the pivot tables. Click the Options ribbon tab and click the

Fields Items & Sets icon drop-down and choose Calculated Item.

2. The Dialog that is displayed allows you to create a

formula for your calculation. The Name: box is a free-

form text entry where you can use spaces to create your

name. I tend to use the underscore character in my

names rather than spaces.

3. The Formula: box allows you to

create a calculation by referring to

the other fields in the table. You can

use some basic Excel functions like IF

in these formulas. We will create a

margin calculation as per the

formula that follows. You do not

need to type the field names in you

can double-click the field name with

the mouse to insert it in the

Formula: box.

= (Sales_Value- Cost)/ Sales_Value



4. Click OK to complete adding the Calculated Field

5. The calculated Field will automatically be added into the pivot table report as well as the

Field List as shown above. No refresh is required.

6. This Calculated Field has two issues. The first is the value display which is a formatting issue.

The second is the use of the words “Sum of” in the title. This is inaccurate as a calculation is

performed, not a sum.

7. To fix the display you can right-click one of the values on the

column and choose a Number Format and select the

percentage format.

8. To remove the “Sum of” from the title you can do it manually

in the cell but you must ensure that the name of the column

is different to the actual calculated item. For example add an

extra space to the end of Margin_%.

Note: Excel will not allow a report column name that is

exactly the same as a field name.

Calculated Fields are shared when a Pivot Cache is shared.



Calculated Items

A Calculated Item allows you to create a new

entry in a column. This new entry is based on

other entries in the column. These are used

less frequently than Calculated Fields, but

they can provide solutions in certain

situations.

In our example we have a listing from a

General Ledger system that lists account

numbers and their type.

All the values are shown as positive. The

Account Type column determines the sign of

the value in terms of debits and credits.

So a positive Revenue value is a credit

whereas a positive Expense is a debit.

We cannot use this data to create a profit

report.

We can add a Calculated Item that calculates

profit and then use that in the report.

PRACTICE:

1. First we need to

create a pivot

table.

As we can see

this is not the

result we

expected.

2. When creating a

Calculated Item

you need to

select a report heading cell before clicking the Fields, Items & Sets icon drop down.

Note: Calculated Item will be greyed out if a

heading is not selected.



3. The Name: box is a free-form text entry.

I have used the name Profit.

4. The Formula: box is where you create

the formula for the Item. In this case we

select the Account Type on the left.

When that is selected all the Items in

that column will be listed in the Items

box on the right.

5. We can use those Items listed on the

bottom right to perform our profit calculation. The formula used is

= Revenue - Expense

You can double-click an

Item with the mouse to

include that Item in the

Formula box. Click OK.

6. The Item is

automatically displayed

in the pivot table, no

refresh is required.

7. In our case we need to make two slight changes to

the report to complete it.

We can move the Revenue column to the left of

the Expense column which is the normal way to

display Revenue.

To do this simply click, hold and drag the Revenue

cell C4 to the left of the Expense cell and release.

8. The other change required is to remove the Grand

Total column which is redundant and incorrect.

9. You can right-click the Grand Total cell and select delete Grand

Total.

Calculated Items are

shared when Pivot

Caches are shared.



Grouping

This term is the most over used in Excel. If you say Grouping in Excel it can mean at least four

different features.

In pivot tables Grouping means creating an extra summary level of the data. Grouping is commonly

applied to date fields eg grouping transactions into months, but it can be applied to other types of

fields as well.

Grouping is one of the features affected by blanks and text. If you have banks or text in a date field

for example you will not be able to Group that field.

Grouping can provide summarisations that aren’t included in the data. For example you may want to

classify the NSW, VIC as Eastern States and WA as the western state in your report.

Date Grouping

When you have daily data it is a common requirement to group by months. You can also group by

weeks. When grouping by month there is trick you need to use when the data contains multiple

years’ data.

PRACTICE:

1. To apply monthly grouping on a date field, right click the

pivot table where the dates are shown and click Group.

An error message will display if there are blanks or non-

dates in the field



2. If the data has multiple years’ data you need to click the

Month option and then hold the Ctrl key down and click the

Years option. This will summarise by months, across years.

Other Grouping

Let’s take the situation where you want to apply a level of summary that isn’t in the data. We will

use the Eastern / Western example mentioned above.

PRACTICE:

1. Select the range A6:A7. Right-click the selected

range and choose Group.

2. This will create a group for the two states.

3. You can click cell A6 and change it to Eastern States.

4. In the Design ribbon tab you can click the Subtotals

icon drop down and select Show all Subtotals at

Bottom of Group.



Sorting Excel has a feature that allows you to sort in a specific order that is not alphabetic.

Custom List

You have seen a custom list in action when you drag a month or day name with the Fill Handle and

then see the other names appear in sequence. These are the built-in custom lists. You can create

your own custom list to sort data tables and pivot tables in the order that you require.

A well as sorting, custom lists can save time when creating lists of commonly used categories,

departments, business units or any other list that you use.

PRACTICE:

1. In the Custom List sheet there is a list of departments. The order of the list is important and

that order is used consistently throughout an organisation. To enable us to sort in that order

we can create a custom list. Select the range A1:A8 press in sequence (not held down)

Alt t o a this opens the Advanced option in Excel Options in the File ribbon tab.

2. Scroll to the very

bottom and click

the Edit Custom

Lists button.

3. Existing Custom

Lists are shown on

the left. Click the

Import button to

add a new Custom List.

4. Click OK twice. The list is now

created ready to use.

5. Type Production into a cell and

drag the cell with the Fill Handle

for about 20 rows.

6. Right click the new list and

select Sort and Custom Sort.



7. In the order drop-

down select the

Custom List.

8. You then select the

Custom List to Use and

click OK twice to sort.

9. This will sort the list as per

the Custom List and not

alphabetic.

When you create a Pivot table that includes a Custom List, Excel will automatically sort by it.

NOTE: A Custom List is saved to the PC. It is not available on other PC’s unless you create it in those

other PCs. You only need to create it once on each PC.

Value Sorting

You can sort by columns or totals in the

report area. Right click a value and choose

Sort and then select the order.



Filtering - Advanced

Apart from the filtering already covered there are more options depending on the type of filter you

need.

Label Filters You can filter based on the label. These

are text-based filters. See image right.

Values Filters

You can also apply filters based on

the values in the pivot table. See

image right.

The Top 10 option is actually more

flexible you can specify the number

and choose between items and

percentages.

The tops 10 option is useful for

Pareto (80/20) analysis.



GETPIVOTDATA Function

If you have ever tried to link to a pivot table you may have noticed that Excel automatically creates a

function to extract the cell you linked to.

One of the problems with Pivot Tables is that while they produce great summary reports, those

reports aren’t always presentation quality.

The GETPIVOTDATA function allows you to extract values from a Pivot Table. The example below is a

simple example, but it does demonstrate the power of the GETPIVOTDATA function.

The important limitation with GETPIVOTDATA is that the value you want to extract MUST exist in a

single cell within the Pivot Table. This means you must be careful when amending any Pivot Table

that is used by a GETPIVOTDATA function.

This is the layout in the Data sheet.

It has detailed sales records for January to April 2014.

This is a Pivot Table in the PT_Gadget sheet that uses the Data sheet as its source.

This summarises sales (Values) by State, Colour and Size.

The field list for the report is shown on the right.

The report is filtered by product and the filter is currently

Gadget. The above report is for gadget sales only.

Our goal is to recreate this report in a different layout.

Note: not all colours are sold for all sizes.



The layout of the report is

shown here. Rows 1 to 3 and

columns A, B and C are all

helper cells that will make

creating the GETPIVOTDATA

function easier. The helper

cells will be hidden using the

Grouping icons on the left and

above the grid.

I find the easiest way to create

a GETPIVOTDATA function is to

first create the Pivot Table.

Then link to a specific cell

within the Pivot Table.

In this example cell E7 needs

to be linked to cell J6 in the

PT_Gadget sheet.

When you link to a cell within

a Pivot Table Excel

automatically inserts the

GETPIVOTDATA function for you - see below for the formula it creates.

=GETPIVOTDATA("Value",PT_Gadget!$A$3,"State","NSW","Colour","White","Size","Small")

As you can see it summaries the various elements that describe the cell linked to. All we have to do

is replace all the arguments with cell links to create a flexible report.

We can amend the automatically generated function to work with the helper cells. The result for cell

E7 is shown below.

=GETPIVOTDATA(""&$A$3,PT_Gadget!$A$3,$C7,$D7,E$1,E$6,$A7,$B7)

The trick with this formula is the very first argument. Note the ""&$A$3, this forces Excel to treat

the entry as text. If you just link to a text cell it doesn’t work, this workaround sorts out the issue.

This formula cannot be copied down and across yet because it will returns the #REF! errors in the

BLUE and BLACK columns because there are no BLUE or BLACK colours in the Small category. To get

around the issue we use the IFERROR function.

The final formula for cell E7 is

=IFERROR(GETPIVOTDATA(""&$A$3, PT_Gadget!$A$3,$C7,$D7,E$1,E$6,$A7,$B7),0)

This formula can also be copied to the other size reports below.

If you add Volume to the Pivot Table in PT_Gadget, then changing cell A3 to Volume will report that

measure.



Report Layouts

The standard pivot table report usually summarises $ values. There are other

built-in report layouts that perform calculations using the report values.

These may also be useful if you are interested in % splits or rankings between

categories.

With the pivot table selected click the Options ribbon tab and click the Show

Values As icon drop down.

You might need to use some trial and error to find the best report that suits

your needs.

Examples of some of the layouts are shown below.

Keep these types of reports in mind when creating your pivot table reports as they provide the

functionality you require eg a running total is a standard report.



Conditional Formatting

Conditional formats are formats that automatically change based on a cell’s value. These can be

used for exception reports and for dashboard reports. As well as colours there are also icons that

can be included.

Excel 2010 and later versions simplified applying conditional formats to pivot tables. There are extra

options now available when creating a conditional format for a pivot table. See image.

You will usually avoid

the Selected cells (first

option) as it doesn’t

handle changes to the

pivot table structure.

The options beneath it,

which will be specific

to the pivot table

involved, and handle

situations where the

pivot table structure is

changed.

Obviously if fields are

removed from the

pivot table any

conditional formats

relating to them are

lost.

The types of rules are

limited in pivot tables.

Experiment with some conditional formats with some of the existing reports and see what happens

to those formats when you amend the pivot table.



External Data

Excel can extract data directly from most databases. Pivot tables can create reports directly from

external databases without the data appearing in Excel.

NOTE: In most cases corporate databases are secure and you will need permissions and you will

usually need to involve your IT section to provide folder paths and names to enable you to get into

the databases. Once created a connection is saved, so that future connections are straightforward.

WARNING 1: Most corporate databases are relational databases. This means they have multiple

tables that are linked together via common codes in each table. Because pivot tables can only access

one table at a time you will most likely work with a View. A View is a summary that combines data

from a multiple tables in to a single table that a pivot table can report on. You may need to get the

data base administrator to create a View of the data you require.

WARNING 2: the field headings used in modern databases aren’t always self-explanatory and you

may need to involve your database administrator to find where the data you want is located.

PowerPivot Note: The limitation of one table per pivot table is removed by PowerPIvot which will be

demonstrated at the end of the session.

Access Database

As an example of using an external source for the pivot table I will use an Access data base. This is

quite a simple process. Linking to corporate databases tends to require passwords and paths.

PRACTICE:

1. In the External sheet click the Data ribbon tab and click the Access icon.

2. Navigate to where the Access file is and double click

it.

3. Select the PivotTable Report option. Before clicking

the OK button click the Properties button and

examine the options you can change in terms of

refreshing the data. Click OK in the Properties

window. (see next page for the options)

Click OK to continue



4. You will see the same interface as per previous

pivot tables.

In this case no data is held in the spreadsheet, it

is all held in the Pivot Cache.

This can save file size. Your file will need to be

able to access the database when you open the

file.

Note: whilst bringing in the data direct from an external data base is quick it can still be safer to

initially bring the data in to a sheet and examine it first to ensure the data is what you are expecting.

Once you are happy with the data you can delete the sheet and then bring it in directly via the pivot

table.



PowerPivot Demonstration

This section is only meant to be a demonstration section. I will cover a very small part of the

functionality of PowerPivot. I will include a few screen shots to show the processes involved.

Pivot tables can only summarise one data table. PowerPivot removes that limitation.

PowerPivot also provides a way to link or join two tables that don’t have a common field. This is

similar to the example we looked at earlier where we had to add an extra column to be able to

report on it. PowerPivot allows you to seamlessly create pivot tables based on two or more separate

tables.

This is a small example to show the abilities of PowerPivot. PowerPivot can work with data tables

that have millions of rows.

Just like a pivot table all the data goes into memory.

We will use

an Access database which we just used for sales

a separate CRM (Customer Relationship Management) Access database

an Excel table with regions

The Diagram View will display the

three tables in a graphic layout that

allows you to drag and drop field

names to create relationships

between the tables.



Below you can see that you can drag

and drop field names to create

relationships



In its basic form PowerPivot offers users the ability to get over Excel one table report limitation.

This demonstration just scratches the surface of what is possible with PowerPivot.

You can write formulas similar to Excel formulas but these formulas work on the database fields. You

can also do complex SQL queries to limit the data that you extract.

If you work with data I highly recommend that you investigate PowerPivot. See Further Resources

that follow for recommended websites and books.

The report above looks

straightforward but it is incredible!

It is bringing together one field from

each table to create a final report.

Two separate Access databases plus

an Excel table.

In the space of a few minutes we

can produce reports that previously

would have required a lot of effort.



Further Resources

Excel websites

www.ozgrid.com - a WA site that has great resources and a forum. The layout of the site is not the

easiest to navigate, but the search option will usually provide lots of resources.

www.mrExcel.com - the premier US site for Excel, includes a forum. Run by Bill Jellen an Excel MVP.

www.chandoo.org - the premier Indian site. Run by Purna Duggirala another Excel MVP.

www.a4accounting.com.au - my website, it has an Excel blog and many tips plus free webinar

recordings and manuals.

Pivot Table books

Pivot table and data related books I own and recommend

Excel 2007 PivotTables Recipes by Debra Dalgeish (Publisher: Apress)

Business Intelligence Tools for Excel Analysts by Michael Alexander, Jared Decker and Bernard

Wehbe (Publisher: Wiley)

Pivot Table Data Crunching by Bill Jellen and Michael Alexander (Publisher: Que)

Excel and Access Integration by Michael Alexander and Geoffrey Clark (Publisher: Wiley)

Excel 2007 Advanced Report Development by Timothy Zapawa (Publisher: Wiley)

PowerPivot Add-in download for Excel 2010

http://office.microsoft.com/en-au/excel-help/power-pivot-add-in-HA101811050.aspx

PowerPivot websites

http://www.powerpivotpro.com/

http://www.powerpivotblog.nl/

PowerPivot books

PowerPivot and related books I own and recommend

PowerPivot for Excel 2010 by Macro Russo and Alberto Ferrari (Publisher: Microsoft Press)

DAX Formulas for PowerPivot by Rob Collie (Publisher: Holy Macro! Books)

PowerPivot Alchemy by Bill Jellen and Rob Collie (Publisher: Holy Macro! Books)

Sams Teach Yourself SQL in 10 Minutes - Third edition - by Ben Forta (Publisher: SAMS)



Appendix

Macros

WARNING: Macros cannot be undone and running a macro clears the undo list. So I recommend

you always save a file before running a macro or save it as a different name, so that you can close

without saving to restore to the pre-macro condition if something goes wrong.

Remove Sum Of

I have included two macros in this file to remove the “Sum of” in headings of pivot tables.

I am not discussing these macros, only providing them for your use.

The first macro amends the current pivot table, the other works on all the pivot tables in a file.

Two macros that remove the Sum of from pivot table headings

RenamePTFields - works on the current pivot table

RenamePTFieldsAll - amends all the pivot tables in the file

Amend Number Format

In the same way there are two macros that amend the number format to #,##0 which is no decimal

places and uses the comma separator.

The first macro amends the current pivot table, the other changes all the pivot tables in the file.

Two macros that change the format of the values to #,##0 comma separator and no decimals

NumberFormatCurrentPivotTable - works on the current pivot table

NumberFormatALLPivotTables - amends all pivot tables in the file

Running the Macros

Save your file first.

You need to have the Excel macro file open. You must

enable the macros (click the Enable Content button when

the file opens).

For the macros that work on one pivot table you need to

select a cell within a pivot table before running the macro.

Press Alt + F8, from the drop down for Macros in: select

the file. Then double click the macro you want to run to

run it. Check the pivot tables. If there are any issues you

may want to close the file without saving to revert back

the version before running the macro.

2014 act congress - mc12 neale blackwood paper

Documents

multiple data tables

excel pivot tables

data limitation

single data table

data cleansing

data doesnt

data disadvantages report

pivot table report