---

Lecture Notes Of Class 8 
PivotTables and PivotCharts

Objective

How  to create and  manipulate PivotTables and PivotCharts in Excel. These tools help you summarize, analyze, and visualize large datasets effectively.

---

1. Creating PivotTables

A PivotTable allows you to extract significant patterns and insights from a large dataset by summarizing the data in a table format.

A Pivot Table is a data summarization tool available in spreadsheet applications (like Microsoft Excel) that is used to organize, analyze, and summarize large datasets. It dynamically rearranges and aggregates data, allowing users to view it in a more meaningful and structured way, such as totals, averages, or counts, without altering the original dataset.

---

Key Characteristics of a Pivot Table:

• Summarizes Data: Aggregates data like sums, averages, or counts.
• Dynamic: Allows users to easily change rows and columns to view data from different perspectives.
• Filterable: Users can filter or sort data to focus on specific insights.

---

 Key Components:

• Data Source: The set of data you want to analyze (it can be a range of cells in Excel).
• Fields: These are the columns in your data, which can be placed in different areas of the PivotTable:
• Rows: Categories that will form the rows of your PivotTable (e.g., Products, Dates).
• Columns: Categories that will form the columns of your PivotTable.
• Values: The numerical data that you want to summarize (e.g., sales totals).

Steps to Create a PivotTable:

1.   Select the Data Source:

o    Highlight the range of data that you want to use for your PivotTable, including the headers (e.g., Product names, Sales data).

2.   Insert PivotTable:

o    Go to the Insert tab in the Excel Ribbon.

o    Click on PivotTable.

o    In the dialog box that appears, choose where to place the PivotTable (New Worksheet or Existing Worksheet) and click OK.

3.   Add Fields to the PivotTable:

o    A PivotTable Field List will appear.

o    Drag fields into the different areas:

§  Place Product in the Rows area to see the products listed vertically.

§  Place Date in the Columns area to see dates listed horizontally.

§  Place Sales Amount in the Values area to see total sales.

2. Customizing PivotTables

You can customize your PivotTable to better analyze your data.

Key Customization Options:

• Filters: Limit the data displayed in your PivotTable based on specific criteria.
• Sorting: Arrange the data in either ascending or descending order.
• Grouping: Combine data into categories or time frames (like grouping dates by month).

Steps to Customize a PivotTable:

1.   Apply Filters:

o    Click the dropdown arrow next to a Row or Column label in your PivotTable.

o    Select specific items to display or hide.

2.   Sort Data:

o    Right-click on any Row or Column label.

o    Select Sort > Sort A to Z (ascending) or Sort Z to A (descending) to arrange the data.

3.   Group Data:

o    Right-click on a Row label (e.g., a date).

o    Choose Group to combine data into categories (e.g., group by month).

Example:

Imagine you have the following sales data:

Product	Region	Salesperson	Sales Amount
Laptop	North	Alice	50,000
Mobile	South	Bob	30,000
Tablet	East	Alice	20,000
Laptop	West	Charlie	40,000
Mobile	North	Alice	25,000
Tablet	South	Bob	15,000

Now, you want to answer questions like:

• What is the total sales amount by region?
• Who sold the most?
• What are the sales by product category?

---

Steps to Create a Pivot Table in Excel:

1.   Select the Data: Highlight the range containing the data (A1:D7 in this case).

2.   Insert Pivot Table:

o    Go to the Insert tab and click on PivotTable.

o    Select whether you want the Pivot Table in a new worksheet or the existing one.

3.   Set up the Pivot Table:

o    Drag Region to the Rows section.

o    Drag Sales Amount to the Values section.

o    The table now shows the total sales for each region.

4.   Explore More:

o    To see the sales by product, drag Product to the Rows section.

o    To check sales by salesperson, drag Salesperson instead.

---

Example Output:

Total Sales by Region:

Region	Sales Amount
East	20,000
North	75,000
South	45,000
West	40,000

Sales by Product:

Product	Sales Amount
Laptop	90,000
Mobile	55,000
Tablet	35,000

---

Why Use Pivot Tables?

• Quick Summaries: Aggregate data without formulas.
• Dynamic Analysis: Change views instantly to answer different questions.
• Easy to Learn: Drag and drop fields into sections like Rows, Columns, and Values.

By practicing, you'll see how a Pivot Table can turn complex datasets into clear and actionable

Creating PivotCharts from PivotTables

A PivotChart is a visual representation of the data in your PivotTable, making it easier to understand trends and patterns.

Steps to Create a PivotChart:

1.   Select the PivotTable:

o    Click anywhere within your existing PivotTable.

2.   Insert PivotChart:

o    Go to the Insert tab on the Ribbon.

o    Click on PivotChart.

o    Choose a chart type (e.g., Column, Line, Pie) and click OK.

3.   Customize the PivotChart:

o    Use the Chart Tools on the Ribbon to change the design, layout, and format (like adding titles or legends).

---

Exercise

Exercise 1: Create a PivotTable from a Dataset

1.   Given Dataset:

Date	Product	Sales Amount
2024-01-01	A	200
2024-01-01	B	150
2024-01-02	A	300
2024-01-02	B	200
2024-01-03	A	250
2024-01-03	B	100

2.   Steps:

o    Select the Data:

§  Highlight the entire table including headers.

o    Insert PivotTable:

§  Click on the Insert tab > PivotTable.

§  Choose New Worksheet and click OK.

o    Add Fields:

§  In the PivotTable Field List:

§  Drag Product to the Rows area.

§  Drag Date to the Columns area.

§  Drag Sales Amount to the Values area.

Exercise 2: Analyze Data Using PivotCharts

1.   Steps:

o    Select the PivotTable:

§  Click anywhere on your created PivotTable.

o    Insert PivotChart:

§  Click Insert > PivotChart.

§  Select Column Chart and click OK.

o    Customize the Chart:

§  Click on the chart title to edit it to "Sales Analysis".

---

Conclusion

PivotTables and PivotCharts are powerful tools in Excel for analyzing data. They allow users to quickly summarize and visualize large datasets, making it easier to identify trends and insights. Mastering these tools will significantly enhance your data analysis skills.

---