Understanding Excel List Structures
An Excel list (table) is a structured set of rows and columns used to store related data (like employee info, sales records).
- Each column has a heading
- Each row is a record
- Helps in sorting, filtering, and analyzing data easily
Sorting a List Using Single-Level Sort
Sort based on one column only.
Steps:
- Click any cell in the column you want to sort
- Go to Data tab
- Choose Sort A→Z (ascending) or Z→A (descending)
Example: Sort employees by Name alphabetically.
Sorting a List Using Multi-Level Sorts
Sort based on more than one column.
Steps:
- Select the list
- Data → Sort
- Add first sort level (e.g., Department)
- Click Add Level and add second sort level (e.g., Salary)
Example: First sort by Department, then sort each department by Salary.
Using Custom Sorts in an Excel List
Use custom order instead of A-Z or numbers.
Steps:
- Data → Sort
- Choose column
- Under Order, select Custom List
- Pick preset lists (Mon, Tue, Wed…) or create your own
Example: Sort months as Jan, Feb, Mar… instead of alphabetical order.
Filter an Excel List Using the AutoFilter Tool
AutoFilter helps you quickly show only the rows that match specific criteria while hiding the rest.
Steps
- Select your data range (or click any cell inside your list)
- Go to the Data tab
- Click Filter (funnel icon)
→ Small drop-down arrows appear on each column header
- Click the drop-down arrow on the column you want to filter
- Choose your filter option:
- Select / unselect values
- Text Filters (Starts With, Contains…)
- Number Filters (Greater Than, Between…)
- Date Filters (This Month, Last Week…)
- Click OK
→ Only matching rows are shown
Example
Filter a sales list to show only “East Region” records.
Steps:
Data → Filter → Region column drop-down → tick East → OK
Tip
To clear filters:
Data → Clear (or remove filter icon again)
Creating Subtotals in a List in Excel
The Subtotal tool automatically inserts group totals and summaries into a sorted list.
When to Use
Use Subtotal when you need totals for each group/category in a column (e.g., total sales by region, department, or product).
Steps
-
Sort your data by the column you want to group
(Example: sort by Region or Department)
-
Go to Data tab
-
Click Subtotal
-
Set the options:
- At each change in: choose the column to group by
(e.g., Region)
- Use function: select summary function
(Sum, Count, Average, etc.)
- Add subtotal to: choose the column to total
(e.g., Sales Amount)
-
Click OK
Excel inserts subtotal rows for each group and a grand total at the bottom.
Example
To get total sales by Region:
- Sort by Region
- Subtotal → At each change in: Region
- Function: Sum
- Add subtotal to: Sales
Useful Tips
- Expand/Collapse groups using the outline buttons (1, 2, 3) on the left
- To remove subtotals:
Data → Subtotal → Remove All
Format a List as a Table in Excel
Purpose:
Convert your data range into a structured Excel Table for easier sorting, filtering, and formatting.
Steps
-
Select the data range
Highlight the list you want to convert.
-
Go to Home tab → Styles group
Click Format as Table.
-
Choose a table style
Select a design you like.
-
Confirm the range
Excel shows the selected range — verify it.
-
Check “My table has headers”
Tick this box if your list already has headings.
-
Click OK.
Result
✅ Data becomes a Table with filters
✅ Easy sorting & formatting
✅ Table styles applied automatically
✅ Auto-expands when new data is added
Using Conditional Formatting to Find Duplicates in Excel
Purpose:
Highlight duplicate values in a range to easily identify repeated data.
Steps
-
Select your data range
(Example: A1:A50)
-
Go to Home tab → Conditional Formatting
-
Choose Highlight Cells Rules → Duplicate Values
-
In the popup:
- Select Duplicate
- Choose a formatting style (e.g., Light Red Fill)
-
Click OK
Result
✅ All duplicate values in the selected range are highlighted
✅ Very useful for data cleaning and checking repeated entries
Tip
To highlight unique values instead, choose Unique in step 4.
Removing Duplicates in Excel
Purpose:
Delete duplicate rows from data and keep only unique records.
Steps
-
Select your data range
(Example: A1:C100)
-
Go to Data tab
-
Click Remove Duplicates
-
In the dialog box:
- Choose the columns to check for duplicates
(Example: Select all columns to remove fully duplicate rows)
-
Click OK
-
Excel shows how many duplicates were removed and how many unique values remain
Result
✅ Duplicate records removed
✅ Only unique rows stay in the sheet
Tip
If your data has headers, ensure "My data has headers" is checked in the dialog box.
