Practical 16 – Mastering PivotTables for Data Analysis

1. Objective

The primary objective of this practical is to equip students with the ability to use Excel’s PivotTable feature to dynamically summarize, analyze, explore, and present complex data. Students will learn to:

·         Create a PivotTable from a raw data range or an Excel Table.

·         Add, arrange, and manage fields in the four PivotTable areas.

·         Change the summary calculation (e.g., Sum, Count, Average, Min, Max).

·         Group data within a PivotTable (e.g., by date, numeric ranges).

·         Filter and sort data within a PivotTable for focused analysis.

·         Create a PivotChart to visually represent PivotTable data.

·         Understand the power of PivotTables for interactive data reporting and decision support.

2. Introduction

PivotTables are arguably one of the most powerful and versatile features in Excel. They allow you to transform rows and columns of data into meaningful summaries, enabling you to quickly answer complex questions about your data without writing a single formula. Whether you need to see total sales by region, average scores by grade, or the count of students per course, PivotTables provide a flexible, interactive way to slice and dice your data. Combined with PivotCharts, they become an indispensable tool for dynamic data visualization and reporting. This practical will guide you through the process of harnessing PivotTables to gain deep insights from your academic marksheet and other sample data.

3. Pre-requisites

·         Successful completion of Practical 3.

·         Basic understanding of Microsoft Excel interface.

·         Familiarity with data entry and basic table concepts.

·         Understanding of basic statistical functions (from Practical 7).

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

Ensure you have your completed marksheet from Practical 3 open. We will primarily use this table as our dataset for creating PivotTables.

Step 5.1: Opening the Marksheet and Preparing Data

1.     Open the Marksheet: Open the Excel workbook containing your Marksheet from Practical 3.

2.     Ensure Data is Clean: Verify that your data has clear column headers and no blank rows or columns within the data range.

3.     Convert to Excel Table:

o    Select any cell within your marksheet data (e.g., A3).

o    Go to the Insert tab.

o    In the Tables group, click Table.

o    Ensure "My table has headers" is checked and click OK.

o    Observation: This makes your data an "Excel Table," which automatically expands if you add more data, making PivotTable updates easier. Excel Tables are named (e.g., Table1), which is useful for referencing.

Step 5.2: Creating a Basic PivotTable

Let’s create a PivotTable to summarize the total marks by student.

1.     Select Data: Click any cell within your marksheet data (or the Excel Table you just created).

2.     Insert PivotTable:

o    Go to the Insert tab on the Excel ribbon.

o    In the Tables group, click PivotTable.

3.     Create PivotTable Dialog Box:

o    Table/Range: Excel will usually auto-detect your data range (e.g., Marksheet!$A$3:$J$8 or Table1). Confirm it’s correct.

o    Choose where you want the PivotTable to be placed: Select New Worksheet. This is generally good practice to keep your raw data separate from your analysis.

o    Click OK.

4.     Observation: A new sheet will open, showing a blank PivotTable area on the left and the PivotTable Fields pane on the right. The PivotTable Fields pane lists all your column headers (Student Name, Subject 1, Total Marks, Percentage, Grade, Result) as fields.

Step 5.3: Adding Fields to PivotTable Areas

We want to see each student’s total marks.

1.     Drag Fields:

o    Drag Student Name from the PivotTable Fields list to the ROWS area.

o    Drag Total Marks from the PivotTable Fields list to the VALUES area.

2.     Observation: The PivotTable on the left will immediately update, showing each student’s name in rows and their Sum of Total Marks next to them.

Step 5.4: Changing Summary Calculation

By default, Excel often uses SUM for numeric fields. Let’s change it to AVERAGE.

1.     Access Value Field Settings: In the VALUES area of the PivotTable Fields pane, click the dropdown arrow next to Sum of Total Marks.

2.     Select Value Field Settings....

3.     Change Summary Function: In the Value Field Settings dialog box:

o    Under Summarize value field by, select Average.

o    You can also change the Custom Name (e.g., Average Total Marks).

o    Click Number Format... to format the result (e.g., Number with 2 decimal places).

o    Click OK twice.

4.     Observation: The PivotTable now displays the Average of Total Marks for each student (which is the same as their total marks in this specific case, but useful for understanding the function).

Step 5.5: Adding Multiple Fields and Using Columns Area

Let’s see the Average Percentage for each Grade.

1.     Clear PivotTable (or create a new one): You can remove fields from the PivotTable Fields pane by dragging them out of the areas, or simply create a new PivotTable on a different sheet if you prefer.

2.     Create New PivotTable: (Insert > PivotTable > New Worksheet > OK).

3.     Arrange Fields:

o    Drag Grade to the ROWS area.

o    Drag Result to the COLUMNS area.

o    Drag Percentage to the VALUES area. (Change Sum of Percentage to Average via Value Field Settings, and format as Percentage with 1 decimal place).

4.     Observation: The PivotTable now shows a matrix with Grades as rows, Results (Pass/Fail) as columns, and the Average Percentage for each combination.

Step 5.6: Grouping Data in a PivotTable

Grouping is particularly useful for dates or numbers. Let’s create a hypothetical scenario for grouping student scores into ranges.

1.     Create Sample Data for Grouping: On a new sheet, create a small table:

Student ID	Score
S001	75
S002	88
S003	42
S004	91
S005	55
S006	68
S007	79
S008	33
S009	82
S010	60

2.     Create PivotTable: Select this new data, then Insert > PivotTable > New Worksheet > OK.

3.     Add Fields: Drag Score to ROWS, and Score again to VALUES (change to Count of Score).

4.     Group Scores:

o    Right-click on any score in the ROWS area of the PivotTable (e.g., 75).

o    Select Group....

o    Grouping Dialog Box:

§  Starting at: Excel will suggest a minimum. Adjust if needed (e.g., 0).

§  Ending at: Excel will suggest a maximum. Adjust if needed (e.g., 100).

§  By: Enter the size of the interval (e.g., 10 for groups of 10 marks: 0-9, 10-19, etc.).

o    Click OK.

5.     Observation: The scores are now grouped into intervals (e.g., 30-39, 40-49, etc.), and you can see the count of students falling into each score range.

Step 5.7: Filtering and Sorting in a PivotTable

You can apply filters and sorting directly within the PivotTable.

1.     Use the Marksheet PivotTable (from Step 5.5, with Grade in Rows and Result in Columns).

2.     Filter by Label: In the ROWS area of the PivotTable, click the dropdown arrow next to Row Labels (which are your Grades).

o    Uncheck A to remove it from the display. Click OK.

o    Observation: Grade A rows are now hidden.

3.     Clear Filter: Click the dropdown again and Clear Filter from "Grade".

4.     Sort: Click the dropdown arrow next to Row Labels again.

o    Select More Sort Options....

o    Choose Descending (Z to A) for Grade. Click OK.

o    Observation: Grades are now sorted from C to A.

Step 5.8: Creating a PivotChart

A PivotChart is a dynamic chart directly linked to a PivotTable.

1.     Select PivotTable: Click any cell within your PivotTable (the one summarizing Average Percentage by Grade and Result from Step 5.5).

2.     Insert PivotChart:

o    Go to the Analyze tab (or PivotTable Analyze tab) on the Excel ribbon.

o    In the Tools group, click PivotChart.

3.     Insert Chart Dialog Box:

o    Choose your desired chart type (e.g., Clustered Column).

o    Click OK.

4.     Observation: A PivotChart appears on your worksheet, visualizing the data from your PivotTable. Notice the field buttons on the chart.

5.     Interact with PivotChart:

o    Click one of the field buttons on the chart (e.g., Grade or Result).

o    You can filter directly from these buttons (e.g., uncheck ‘Fail’ from the Result field button).

o    Observation: As you filter or change fields in the PivotTable, the PivotChart automatically updates.

Step 5.9: Refreshing a PivotTable

If your source data changes, your PivotTable needs to be refreshed to reflect those changes.

1.     Go to your Marksheet data.

2.     Modify Data: Change one of Alice’s subject marks (e.g., B4) and observe her Percentage.

3.     Go to your PivotTable sheet.

4.     Refresh PivotTable:

o    Click any cell inside the PivotTable.

o    Go to the Analyze tab (or PivotTable Analyze tab).

o    In the Data group, click Refresh.

5.     Observation: The PivotTable will update to reflect the changes in the source data.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks using your marksheet or new sample data:

1.     Student Performance Summary:

o    Create a PivotTable showing each Student Name in the ROWS area.

o    Display their Average Percentage and Highest Total Marks in the VALUES area. (You’ll need to drag Percentage and Total Marks twice and change their Value Field Settings).

o    Filter this PivotTable to show only students who Passed.

2.     Subject-wise Analysis:

o    Create a new PivotTable.

o    Place Subject 1, Subject 2, Subject 3, Subject 4, Subject 5 fields in the COLUMNS area.

o    Place Student Name in the ROWS area.

o    Place Subject 1 (and Subject 2, etc.) in the VALUES area, and change the summary function to Average for each.

o    What are the average marks for each subject across all students?

3.     Grade Distribution by Subject:

o    Create a PivotTable to count how many students received each Grade for Subject 1.

o    Place Grade in ROWS, Subject 1 (or any single subject) in COLUMNS (this will automatically show the "Count" as the default aggregation if it contains grades like A,B,C), and then Student Name in VALUES (summarize by Count).

o    This will give you a count of students by grade within each subject.

4.     Date-based Grouping (if dates are available):

o    If you have a column with Date of Entry (from Practical 13), create a PivotTable with Date of Entry in the ROWS area and Count of Student Name in VALUES.

o    Right-click on any date in the ROWS area and Group by Months and Years.

5.     Create a PivotChart:

o    From the PivotTable you created in Exercise 1 (Student Performance Summary), create a Bar Chart to visualize the Average Percentage for each student.

o    Ensure the chart has a clear title.

o    Demonstrate filtering the chart directly using the field buttons.

7. Assessment / Deliverables

·         Submit the completed Excel workbook (.xlsx file) with your Marksheet sheet and new sheets for each PivotTable and PivotChart created in the procedure and exercises.

·         Ensure all PivotTables correctly summarize the requested data, with appropriate summary functions and formatting.

·         Verify that PivotCharts accurately represent the PivotTable data and demonstrate dynamic filtering.

·         Be prepared to demonstrate how to create, modify, group, filter, sort, and refresh PivotTables and PivotCharts, and explain the insights derived from them.