Excel – Usage of PivotTable and PivotChart for data analysis

By Giang Doan

You are looking for a method to create statistical reports for the business situation, population growth, ect or any other public figures to serve your work. The solution for you is a combination of Pivot Table and Pivot Chart in Excel. Using these 2 tools, you can easily analyze data, create reports vividly and attractively.

Using PivotTable and PivotChart for data analysis in Excel 2007 is very simple, but still maintains some confusing issues. The following article will help you have a clear understanding of how to use these two tools.

Excel – Usage of PivotTable and PivotChart for data analysis

1. Creating a simple PivotTable Report

Here is a sample spreadsheet we will use in this article.

Based on this sample spreadsheet, we created a PivotTable. Statistical data on the sales of mobile devices of Store over the quarter.  Customer column displays the number of clients visting store in that Quarter.

Assuming the request is:

- Sales of Apple mobile products at each store?

To address the requirements, I will guide you how to create a simple PivotTable.

Step 1: Place the mouse cursor on any cell in a data area. In (1) Insert -> (2) PivotTable.

Excel will display a dialog CreatPivotTable.

Step 2: In Table / Range, you see the address of data area has been entered. Click on the box as shown in Figure → OK.

The spreadsheet after PivotTable was created.

Now, your precede data table was converted into a PivotTable. You can set the data to be displayed in PivotTable Field List dialog box on the right side of the screen.

Now we are going to handle the requirements.

- Sales of Apple mobile products at each store?

In PivotTable Field List dialog box, select Apple and Store.

You will see sales of Apple's mobile products in Sum of Apple column of 3 Stores at Row Labels.

You can adjust the column name as Store by double-clicking on the column name and edit it.

Depending on specific requirements, you can select the output data in accordance with the requirements in PivotTable Field List dialog box.

2. Creating PivotTable with Multiple Columns

You have learnt how to create a simple PivotTable. Now I will guide you how to create a complicated PivotTable with multiple columns.

I'll still use old data sheet to guide you how to create a PivotTable with multiple columns for the easiest way.

As mentioned ablove, you created a PivotTable to display sales of Apple mobile devices at Store. Now assuming a request as:

- Show the sales of Apple mobile products through Quarter of 3 Store.

With this requirement, same as the previous method, you just need to add Quarter in PivotTable Field List dialog box.

The results

Data is displayed but do you have any comments on this data sheet? If you want to compare the sales of the stores, how will you do? I will guide you how to arrange Quarters in columns instead of rows.

Step 1: Place the cursor on Pivot Table to activate PivotTable Field List.

Step 2: click on the arrow in Quarter in ROWS and choose Move to Column Labels.

Now Apple's sales of Quarter is displayed in column for you to easily view and compare sales of stores.

3. Creating PivotChart

To create PivotChart you must make sure that PivotTable is activated.

Step 1: Place your cursor in the data area→ Insert and choose types of chart on the toolbar.

Step 2: Select the type of Colustered Column in Column.

You will have a chart divided into 3 regions. Each region is indicated sales of each store. The items are divided into bars. Each bar is the sales data of each item shown in different color.

Comes with the charts is PivotChart Filer Pane as shown above. You can use this dialog box to filter the data output on the chart. PivotTable and PivotChart interact each other, meaning the data filtered on the map will be also displayed the same in PivotTable and vice versa.

Thus, we have guided you how to use PivotTable and PivotChart for data analysis on Excel 2007, so you can easily create final report or graph by vivid data for easier observation.

Related Articles:
Critical shortcuts in Excel
Excel – AVERAGEIF function, calculating average with conditions
Excel – Color rows and columns alternately in Excel 2013
Excel - PRODUCT function for multiplying
Excel – Turn column to row in Excel
Excel - LEFT function, for cutting left string




Using the PivotTable and PivotChart in Excel


using PivotTable and PivotChart data analysis


using PivotTable and PivotChart in Excel 2007


Related software

Latest update

Copyright BETDOWNLOAD.COM © 2014 - All rights reserved