Tuesday, December 27, 2011

Simplifying Excel Pivot Table and Pivot Chart Setup

Simplifying Excel Pivot

Table and Pivot Chart

Setup

Overview of Excel Pivot Tables

The Excel pivot table is an incredibly convenient tool to summarize and better understand large amounts of data by creating instant cross tabulations that can be quickly re-arranged to highlight different aspects of the data. Excel pivot tables give the user the ability to instantly summarize data in almost any possible configuration. Pivot tables allow you to quickly pull useful information out of large amounts of raw data. Important insights about the data can be readily obtained from Excel pivot tables that might be difficult to discover otherwise.

The most common use of pivot tables is to summarize data by all available categories and subcategories within the raw data. The incredible versatility of pivot tables allows you to expand and collapse all levels of data and instantly reshuffle all categories and subcategories in order to obtain just the right information and insights.

Overview of Excel Pivot Charts

The Excel pivot chart is an excellent supplement to any Excel pivot chart. The pivot chart provides a graphical representation of the pivot table's information that enables quicker comprehensive of trends and comparisons in the data. The pivot chart can instantly created in Excel in a single step after the pivot table has been constructed. The pivot chart a number of different chart types that facilitate even faster comprehension of data's messages.

All of that power and versatility can leave a person new to pivot tables wondering where to start. Fortunately, pivot tables are quite a bit more intuitive than they may initially appear. An easy, step-by-step process will be provided in this article to quickly create a pivot table with multiple configurations on the following data set:

Pivot Tables in Excel - Raw Data Table

Step 1 – Determine the Overall Objective

The most important aspect of creating an Excel pivot table is to establish the main goal and any lesser goals of the analysis. These goals will drive every aspect of the pivot table analysis going forward.

Two following goals will be established for this pivot table analysis:

  1. Compare sales results of the two salespeople

  2. Compare sales results of the two products

Step 2 – Determine the Query Question That Meets Each Goal

As often happens, getting the right answer depends on asking the right question. The query question determines how the data will be arranged and summarized within the pivot table. The following framework can be used to translate the goal determined in step 1 to a correct query question.

Here is a good way to structure the query question for each goal:

Compare _______________

For each _______________

For each _______________

Goal 1 – Compare Sales Results of the Two Salespeople

Compare: Sales Results of Each Salesperson

For each: Product

For each: Quarter

Goal 2 – Compare Sales Results of Each Product

Compare: Sales Results of Each Product

For each: Quarter

Step 3 – Create the Pivot Table That Implements Each Query Question

When the query question has been correctly created, it is usually a simple and straightforward to configure a pivot table that answers the query question. An empty pivot table dialogue box can be brought up in Excel as follows:

Pivot Tables in Excel - Insert Pivot Table

Insert / PivotTable will bring up the following empty pivot table dialogue box.

Pivot Tables in Excel - Empty Dialogue Box

The data table should be arranged in contiguous (touching) rows. The Table/Range consists of the entire table with headers.

Pivot Tables in Excel - Select Data

The location where the pivot table should be placed will be the cell address of the upper left corner of the output.

Pivot Tables in Excel - Completed Dialogue Box

Clicking OK brings up the following PivotTable Field dialogue box. All data column headers from the data table that was selected are shown in the empty PivotTable Field dialogue box.

Pivot Tables in Excel - Empty Dialogue Box

The column headers can now be arranged in the dialogue box as follows to answer the query question corresponding to each goal.

Goal 1 – Compare Sales Results of the Two Salespeople

Compare: Sales Results of Each Salesperson

For each: Product

For each: Quarter

Pivot Tables in Excel - 1st Pivot Table Field Arrangement

All columns headers have been checked. Headers of categorical (non-numeric) data are placed as Rows within the dialogue box. Categorical data include Quarter, Product, and Salesperson. These categorical data labels are placed in the dialogue box in the order that they appeared in the query question.

Numeric data to be evaluated is summed up under Values in the dialogue box.

The following pivot tables is now produced.

Pivot Tables in Excel - 1st Pivot Table

Step 4 – Create the Pivot Chart From the Pivot Table

As soon as the pivot table is created, the corresponding pivot chart for that pivot table can be created in a single step. Simply click anywhere on the pivot table, click on the PivotTable Tools Design tab and then select Pivot Chart as follows:

Pivot Charts in Excel - Insert Pivot Chart

This will bring up the the chart type dialogue box. In this case the 3-D Stacked Column chart was selected.

Pivot Charts in Excel - Chart Type

The resulting pivot chart is the following:

Pivot Charts in Excel - Pivot Chart 1

The chart provides a more intuitive comparison between sales of the two salespeople than does the table.

If the query question were changed to the following:

Compare: Sales Results of Each Salesperson

For each: Quarter

For each: Product

then the PivotTable Fields dialogue box would be reconfigured to implement the new query question by simply dragging the Product column label to the top of Rows to reflect the order in the new query question.

Pivot Tables in Excel - 2nd Pivot Table Field Arrangement

This would produce the following pivot table.

Pivot Tables in Excel - 2nd Pivot Table

These changes to the table are instantly reflected in the automatically updated pivot chart as follows:

Pivot Charts in Excel - Pivot Chart 2

Showing the data in separate columns in the pivot table for each quarter can be implemented by dragging the Quarter column header to Columns in the PivotTable Fields dialogue box as follows:

Pivot Tables in Excel - 3rd Pivot Table Field Arrangement

This instantly produces the following pivot table data for each quarter in its own column.

Pivot Tables in Excel - 3rd Pivot Table

When the data's arrangement is significantly changed within the pivot table, the pivot chart type sometimes should be changed to provide the most intuitive presentation of the data. In this case the pivot chart type was changed from a 3-D Stacked Column chart to a 3-D Clustered Column chart to create greater clarity. In the PivotChart Design tools tab, select Change Chart Type as follows:

Pivot Charts in Excel - Change Chart Type

The 3-D Clustered Chart is selected from the following Chart Type dialogue box:

Pivot Charts in Excel - Select Chart Type

This instantly produces the following pivot chart:

Pivot Charts in Excel - Chart 3

Goal 2 – Compare Sales Results of Each Product

Compare: Sales Results of Each Product

For each: Quarter

The Salesperson column label can be unchecked because the query question does not request any information about individual salespeople.

Pivot Tables in Excel - 4th Pivot Table Field Arrangement

The following pivot table is produced as a result of the Quarter and Product under Rows:

Pivot Tables in Excel - 4th Pivot Table

The following pivot chart more intuitively represents the data of this pivot table in the 3-D Stacked Column chart type:

Pivot Charts in Excel - Chart 4

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at
Labels: , , ,

2 comments:

  1. AnonymousJune 11, 2017 at 7:32 AM

    The big data integration and the usage of this data in right term are not easy when we do not understand the whole data working or following. For understanding the big data following or use them in a right way we hire the data scientist which makes the big data shorter and easier for us. And we can find these data scientist form here at this activewizards.com.

    ReplyDelete
  2. vé máy bay từ canada về việt namOctober 20, 2021 at 8:03 PM

    Đặt vé máy bay tại Aivivu, tham khảo

    săn vé máy bay giá rẻ đi Mỹ

    chuyến bay từ mỹ về việt nam covid

    chuyến bay từ đức về hà nội hôm nay

    vé máy bay giá rẻ từ nga về việt nam

    vé máy bay từ anh về việt nam vietnam airlines

    chuyến bay từ paris về hà nội

    chuyen bay chuyen gia

    ReplyDelete

Subscribe to: Post Comments (Atom)