Excel Pivot Table for Quick Reports

What a Pivot Table does

A Pivot Table turns a long list of rows into a short summary – totals, counts or averages by category – in a few clicks, without writing formulas. Questions like “sales of each item in each branch” or “students per class who paid fees” take seconds.

Example data (a sweet shop’s sales list):

Create the Pivot Table

1. Click any cell in the data and press Ctrl + T to make it a Table (new rows will be included automatically later).
2. Insert → PivotTable → New Worksheet → OK.
3. In the PivotTable Fields pane drag Item to Rows.
4. Drag Branch to Columns.
5. Drag Amount to Values – it shows “Sum of Amount”.

Result:

Kaju Katli is the best seller (₹13,500), and the Johari branch sells more overall (₹13,650 of ₹22,900).

Change the question in seconds

Refreshing after new data

A Pivot Table does not update by itself. After adding rows, right-click the pivot → Refresh (or Data → Refresh All). Because the source is an Excel Table (Ctrl + T), the new rows are included automatically.

School examples

• Fee register: Rows = Class, Columns = Month, Values = Sum of Amount → who paid how much each month.
• Attendance export: Rows = Student, Values = Count of “P” (filter Status = P).
• Result list: Rows = Class, Columns = Result (PASS/FAIL), Values = Count of Roll → pass count per class.

To show the summary as a chart, click inside the pivot → PivotTable Analyze → PivotChart.

Google Sheets and LibreOffice

• Google Sheets: select data → Insert → Pivot table → add Rows, Columns and Values in the editor on the right. It updates automatically when data changes.
• LibreOffice Calc: Insert → Pivot Table → Current selection → drag fields to Row Fields, Column Fields and Data Fields. Refresh with right-click → Refresh.