Microsoft Excel - Working with an excel list

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:

1. Click any cell in the column you want to sort
2. Go to Data tab
3. 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:

1. Select the list
2. Data → Sort
3. Add first sort level (e.g., Department)
4. 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:

1. Data → Sort
2. Choose column
3. Under Order, select Custom List
4. 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

1. Select your data range (or click any cell inside your list)
2. Go to the Data tab
3. Click Filter (funnel icon)
   → Small drop-down arrows appear on each column header
4. Click the drop-down arrow on the column you want to filter
5. Choose your filter option:
   • Select / unselect values
   • Text Filters (Starts With, Contains…)
   • Number Filters (Greater Than, Between…)
   • Date Filters (This Month, Last Week…)
6. 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

1. Sort your data by the column you want to group
   (Example: sort by Region or Department)

2. Go to Data tab

3. Click Subtotal

4. 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)

5. 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

1. Select the data range
   Highlight the list you want to convert.

2. Go to Home tab → Styles group
   Click Format as Table.

3. Choose a table style
   Select a design you like.

4. Confirm the range
   Excel shows the selected range — verify it.

5. Check “My table has headers”
   Tick this box if your list already has headings.

6. 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

1. Select your data range
   (Example: A1:A50)

2. Go to Home tab → Conditional Formatting

3. Choose Highlight Cells Rules → Duplicate Values

4. In the popup:

   • Select Duplicate
   • Choose a formatting style (e.g., Light Red Fill)

5. 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

1. Select your data range
   (Example: A1:C100)

2. Go to Data tab

3. Click Remove Duplicates

4. In the dialog box:

   • Choose the columns to check for duplicates
     (Example: Select all columns to remove fully duplicate rows)

5. Click OK

6. 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.