Scribd
Upload
34 views33 pages

Excel Pivot Table Guide

The document outlines steps to normalize a data set by creating columns for year and month, formatting the table, and creating multiple pivot tables with specific configurations. It also details how to format values, expand and collapse pivot tables, filter and refresh them, and create a dashboard with charts. Additionally, it includes instructions for grouping options in pivot tables and inserting slicers to connect charts.

Uploaded by

Daredevil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
34 views33 pages

Excel Pivot Table Guide

The document outlines steps to normalize a data set by creating columns for year and month, formatting the table, and creating multiple pivot tables with specific configurations. It also details how to format values, expand and collapse pivot tables, filter and refresh them, and create a dashboard with charts. Additionally, it includes instructions for grouping options in pivot tables and inserting slicers to connect charts.

Uploaded by

Daredevil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 33

Normalise the Data Set

 Create two columns

 Apply the YEAR function in Column B and the MONTH function in Column C, and drag it
down.
 Format the table using (Control + T) or use Home -> Format a table
 Name the table as “ salestable” (without a space) and explore basic filtering options
Create Pivot table
 Create a Pivot table using the insert option

 Alternatively, we can create a Pivot Table using 'Summarize with Pivot Table' under the
Design tools.
 Copy and paste the Pivot Table four times, and name the first Pivot Table as 'SALESRAP'.

 Drop 'SALESRAP' in the Rows field and 'TOTAL' in the Values field for the first Pivot Table.
 Name the second pivot table as “PRODUCT”, drop product title in rows and “TOTAL” in
values filed
 Name the third pivot table as “ZONE” and drop Zone title in rows filed and Total in the
values field.

 Name the third pivot table as “MONTH” , drop Month title in the rows filed, Total in the
values field and also drop year title on the column field.
 Design each table
Format values in Pivot table

 Apply value field settings to change the activate the rupee symbol and apply the
same setting for other tables.
Expand and collapse pivot table in excel

 Select the first pivot table, drag the product title to the row’s fields.

 Select the second table, add product title to the row’s fields
 Select the third table, add sales rap title to the row’s fields

 Apply expand and collapse options


Filter and Refresh pivot table and refresh pivot table

 Select the first table, drop the year, month title in the filters field, demonstrate the filter
options
 Follow the same for other tables as well.
 To demonstrate the refresh option, move to “sales “ sheet and rename “AFRATH” as
“Mubeen” and then go to pivot table sheet and apply refresh options under analysis tools
Pivot Chart
 Before deploying charts, sort the values range of the table

 Use analyse tool and select pivot charts and select cluster chart
Dash board

 Create a new sheet “Dash Board”


 Cut and paste the created chart, apply the following for chart aesthetics
 Follow the same procedure for second table
 Follow the same procedure for third table
Grouping Options in Pivot

 Move to “sales” sheet, create pivot chart and select the existing sheet
 To apply grouping, remove 'TOTAL' from both the Rows and Values fields. Follow the
procedure to create groupings, then create a chart and move it to the dashboard.
 Create one more table by dropping year and month in row fields and total in value fields
Insert slicer

 Insert slicer and connect the charts to the slicer

You might also like