Excel Pivot Tables

[Total: 0    Average: 0/5]

Spreadsheet applications are used to fulfill many requirements. Especially in accounting and mathematical fileds. Today, we are going to learn a powerful feature in Excel called ‘Pivot Tables’, a quick and easy way to prepare valuable information using a data set.

Pre-requisites

  • Microsoft Excel 2007 or later version.
  • A set of data (Figure 01)
ABC Company Sales data
Figure 01 – ABC Company Sales data

Let’s assume we need to prepare a report to analyze sales (Items sold) by each salesmen as against each sales items. It should be something like below as in Figure 02.

Pivot Table Example
Figure 02

Let’s begin. It is assumed that you already have a datasheet as in Figure 01 above.

  1. Highlight the data-set and select ‘Pivot Table’ from ‘Table’ section under Insert ribbon as in Figure 03. Highlighting the data-set at this stage is not mandatory as this can be done now or later.

    Pivot Table Example
    Figure 03
  2. When the following dialog box appears, click ‘Ok’.  As we have already selected the data range, there is no need to select the data range now. Also we will leave ‘new worksheet’ option to place the Pivot table in a new worksheet.

    Pivot Table Example
    Figure 04
  3. You will get the following screen.
    Pivot Table Example
    Figure 05

    See the Left and Right sides of the screen carefully. The Pivot table fields are on the right side. Below that you will notice placeholders for Rows, Columns, Filters and Values. You can drag and drop these fields to any of the place holders below.

    Rows – refers to report row fields where the values are grouped in to.
    Columns – refers to report column fields where the values are grouped in to.
    Filter – refers to fields where you can apply filters on the report data.
    Values – refers to fields you can used as calculated fields/ values.

    In this instance, we will use ‘Sales Person’ field as Rows, ‘Item’ field as Column and ‘Sold’ fields as Values. Let’s avoid any filtering at this stage to understand the basic functions of Pivot Tables first.

  4. The following Figure 06 shows when the ‘Sales Person’ fields is selected as Row.

    Pivot Table Example
    Figure 06
  5. The figure 07 shows the report layout after selecting the Row field. You can see how each Sales Person’s name is displayed as ‘Rows’.

    Pivot Table Example
    Figure 07
  6. Let’s now add the ‘Item’ field as ‘Column’. The following Figure 08 shows how it will look like once it is done’

    Pivot Table Example
    Figure 08
  7. The Figure 09 shows how the report layout will look like when the ‘Item’ field is added as ‘Column’.

    Pivot Table Example
    Figure 09
  8. Finally, let’s add ‘Sold’ field under ‘Values’ placeholder. The Figure 10 illustrates this as below.

    Pivot Table Example
    Figure 10
  9. The report layout should be as follows in Figure 11.

    Pivot Table Example
    Figure 11

 

This is not the end. You can always change the report layout to make a new report. This is the most important feature of Pivot Tables. One data-set allows you to make various reports with minimum efforts. You can keep updating the data-set regularly and refresh the report to view the updated report, or you just need to drag and drop fields to each placeholder based on the expected report to change the layout. Also you can add multiple fields for each place holder as well. But it is a best practice to limit to two fields to keep the report simple, and meaningful.

Hope you learned something new today. Try it out yourself and post your comments or questions here.

Please follow and like us: