Excel · Lesson 7 of 8

Sorting, filtering & pivot tables

Turning raw data into summaries with tables, filters and PivotTables.

Clean data first: one header row, one record per row, no blank rows or merged cells. Press Ctrl+T to convert it into an Excel Table — formulas fill automatically and ranges grow as you add rows.

Sort (Data → Sort) by one or more columns.
Filter (Ctrl+Shift+L) to show only matching rows.
Remove Duplicates under the Data tab.

PivotTables

Insert → PivotTable summarises thousands of rows in seconds. Drag fields into Rows (group by), Columns, Values (Sum/Count/Average) and Filters. Right-click → Refresh after the data changes. Add a Slicer for clickable filters on dashboards.

Sample sheet & formulas

Type these in Excel or Google Sheets

Raw data (Table "Sales"):
     Date        Region   Executive   Amount
     01-Oct-26   North    Anu         12000
     03-Oct-26   South    Rahul       8000
     07-Oct-26   North    Meera       15000
     12-Oct-26   South    Rahul       11000
     18-Oct-26   North    Anu         9000

PivotTable setup:
  Rows:    Region
  Values:  Sum of Amount, Count of Amount

Results

Region   Sum of Amount   Count of Amount
North    36000           3
South    19000           2
Grand    55000           5

Works in Microsoft Excel 365/2021 and, for almost every formula, in Google Sheets. Data is fictional.

Key points

Ctrl+T tables keep ranges and formulas tidy.
PivotTables group and summarise without formulas.
Refresh the pivot when source data changes.

Quick check

Where do you drag a field to group the summary by it?

Values Rows Filters Slicers only

Practice task

Create a pivot from the sample data showing each executive's total and average sale, sorted from highest total.

Interview corner

Why convert data to an Excel Table before making a pivot?

The pivot's source then grows automatically with new rows, so refreshing picks up all data without changing the range.

How would you show sales by month in a pivot?

Put the Date field in Rows and group it by Months (and Years) via right-click → Group.