QCS Dispatch - Service and volume report – User Guide part B

Using Pivot Tables and other Excel features

E. Creating graphs ......................................................................................................................... 13

F. Summary – Pivot Tables ............................................................................................................ 14

G. Other Excel Features ................................................................................................................. 14

H. Typical Pivot Tables applied to the QCS Dispatch - Service and volume report ...................... 16

A. Purpose of this User Guide

The user guide for the QCS Dispatch - Service and volume report is in three parts:

• Part A Explanation and Suggested Usages

• Part B Using Pivot Tables and other Excel features

• Part C Supplement

The screens depicted in this document are based on Excel 2010. The same functionality is in earlier and

later versions of Excel, but the navigation can be different.

B. Introduction

The QCS Dispatch -Service and volume report enables either a PDF report or an Excel file to be created.

The Excel file is designed to be analysed, such as by Excel Pivot Tables.

A Pivot Table is a special type of table unique to Excel that enables you to summarize large amounts of data

and “pivot”, or re-arrange, the data to display different summaries of the information the table contains.

Creating a Pivot Table is usually an iterative process. It is typical to “play” with the data, re-arranging the

rows and the columns several times, until you have a table that makes sense and meets your business

It also provides examples of typical Pivot Tables, specifically for the QCS Dispatch - Service and volume

report.

2

Note that Excel has many more advanced features of Pivot Tables that can also be used, that are not

mentioned in this document.

As well, there are features of Excel, other than Pivot Tables, that are also very powerful and useful in

analysing the information in the Excel version of the QCS -Dispatch Service and volume report. These

include: auto-filters, freezing panes, and hiding columns.

Step 2: Select the entire file.

Click the box to the left of column A and above row 1 to select the entire file (Figure 2)

3

Step 3: Launch the Pivot Table

Click Insert, then Pivot Table. The Create Pivot Table box will appear. Click OK to place the Pivot Table on a

new worksheet. (Figure 3)

4

Step 4: Start building your pivot table.

This is done by dragging and placing fields contained in the QCS Dispatch Service and Volume report file to

either: Report Filter, Column Labels, Row Labels, or Values (Figure 4)

5

Step 5: Review and adjust

After building an initial Pivot table, look at it, and then change it as many times as needed to create the table

you need. In the example below, after the initial table was created, it seemed better to have “Class” as

another row (rather than a column) and to also report the sum of Kilos. Thus, for each partner, you can now

see the number of dispatches and the total kilos by mail class.(Figure 5)

Step 7: Specify how fields are displayed

After determining the fields to be displayed you will need to determine how they are displayed. Excel

provides various options. The most commonly used options for the QCS Dispatch Service and Volume report

6

are Counts, Sums, and Averages. Occasionally Minimums and Maximums may also be used. (Figure 7, 7a,

7b, and 7c)

Step 8: Change to outline form

7

8

D. Tips on using Pivot Tables

Tip 1: Exiting and re-entering a Pivot Table

If you click a cell outside the Pivot Table, the Field List will disappear. When you click a cell within the Pivot

Table, the Field List will re-appear. (Figure 9).

Tip 2: Drill-down of data in a Pivot Table

If you click a cell of a Pivot Table, the rows from the original worksheet that were used to compute the value

of the cell will be displayed. A new worksheet containing these rows will also be created. (Figure 10).

9

Tip 3: Grand totals - Pivot Table Options

By right clicking from within a Pivot table you can select Pivot Table Options. The most commonly used

option for the QCS Dispatch - Service and Volume report is Totals & Filters, specifically to either enable, or

disable, grand totals for rows or for columns. In some cases you will want grand totals; in other cases you will

not. (Figure 11)

of the origin IMPC code, the destination IMPC code, the mail category, mail class and mail subclass.

Sometimes it is necessary to use the indivual data elements in Pivot Tables and in other Excel functions.

Columns AA to AF can be used for this.(Figure 12)

Tip 5 Combining inbound and outbound

The QCS Dispatch - Service and volume report is either for outbound or for inbound. It can be useful to have

an Excel file that is both outbound and inbound.

This can be done by the following steps:

H. Typical Pivot Tables applied to the QCS Dispatch - Service and volume report

An almost infinite number of Pivot Tables can be applied to the Excel Dispatch - Service and volume report.

have many offices of exchange, others only one; some have direct transport to many destinations, others

Example 1

This Pivot Table displays the number of dispatches and the average time durations for each of a) lead time,

b) planned departure to planned arrival, c) planned arrival to 1

RESDES, d) closed date/time to 1

(the sum of the previous three), and e) 1

Example 2

This Pivot Table is similar in purpose to example 1, but focuses only on planned arrival to 1

RESDES. It has

Partner in the report filter and, in this example, partner VGA is selected. It has included origin IMPC as a row,

thus the two origin IMPCs (KAARXN and KABJXM) are displayed separately.

Example 3

This Pivot Table displays the volume of mail (Kilos, Items, and Receptacles) dispatched.

Example 4

In this example the kilograms dispatched are displayed by 1

transport and by closed day of week (1- Mo, 2-

Tu…7-Su).