Excel Pivot Advance

Excel Pivot Advance

In data processing, a pivot table is a data summarizationtool found in data visualization programs such as spreadsheets or businessintelligence software. Among other functions, a pivot-table can automaticallysort, count, total or give the average of the data stored in one table orspreadsheet. It displays the results in a second table (called a “pivot table”)showing the summarized data. Pivot tables are also useful for quickly creatingunweighted cross tabulations.

If you have your data in a tabular format you can use thePivot Table tool to analyze the data to get insightful information.

Sales Person	Sales Units	Product	Total Sales Amount
Jane	100	Apples	50
Max	102	Oranges	60
Mark	105	Apples	70
Sara	250	Oranges	90
Jane	105	Apples	60
Max	206	Oranges	70
Mark	105	Apples	60
Sara	250	Oranges	70
Jane	250	Apples	90
Max	105	Oranges	60
Mark	206	Apples	90
Sara	105	Oranges	60

The following fields can be summarized by Sales Person andProduct Type.

Sales units

Amounts of sale

Step 1: Copy the range from above in to a blank excelworksheet into range “A1”.

Step 2: Click anywhere on the data range and click on “Insert”>”PivotTable”

Step 3: The Pivot Table Wizard appears on the screen. Noticethat the Pivot Table wizard has correctly the data range to create the Pivottable. At this stage just selects the defaults offered by the Pivot TableWizard and click ok.

Step 4: The Pivot Table wizard creates a shell to place thedata “Fields” found in the data range.

		Data
Sales Person	Product	Sum of Total Sales Amount	Sum of Sales Units
Jane	Apples	200	455
Jane Total		200	455
Mark	Apples	220	416
Mark Total		220	416
Max	Oranges	190	413
Max Total		190	413
Sara	Oranges	220	605
Sara Total		220	605
Grand Total		830	1889

 

Here are some examples, uses of Pivot tables:

1. Summarizing data like finding the average sales for each region for each product from a product sales data table.
2. Listing unique values in any column of a table.
3. Creating a pivot report with sub-totals and custom formats.
4. Making a dynamic pivot chart.
5. Filtering, sorting, drilling-down data in the reports without writing one formula or macro.
6. Transposing data – i.e. moving rows to columns or columns to rows.
7. Linking data sources outside excel and be able to make pivot reports out of such data.