Sorting and filtering are the first two things you do with any data set. Eleven chapters take you from a one-click A to Z sort to multi-level and colour sorts, AutoFilter with text, number and date conditions, the Advanced Filter, Excel Tables with slicers, removing duplicates, the SORT, SORTBY, FILTER and UNIQUE functions, SUBTOTAL and AGGREGATE for filtered totals, and a troubleshooting chapter for every time sort or filter refuses to work.
Course curriculum
Chapters 1 to 6 cover the commands on the Data tab and work in every version of Excel. Chapters 7 to 11 add Excel Tables, duplicate handling, the Excel 365 dynamic array functions, filter-aware totals and troubleshooting. Each lesson has a worked example, a practice exercise and answers to the questions people search for most.
-
Sort Data in Excel: Single, Multi-Level and Custom SortA to Z, Z to A, smallest to largest, the Sort dialog with up to 64 levels, custom lists and the fixes for sorts that scramble rows.
-
Sort by Colour: Cell Colour, Font Colour and Icon SetsBring one colour to the top from the filter drop-down or right-click menu, then rank several colours and icons in your own order.
-
Filter Data in Excel: Apply, Search and Clear AutoFilterCtrl+Shift+L, ticking and searching values with wildcards, clearing one or all filters, and totalling only the visible rows.
-
Text, Number and Date FiltersBegins With, Contains, Greater Than, Between, Top 10, Above Average, Last Month, Year to Date and the Custom AutoFilter dialog.
-
Filter by Colour: Cell Colour, Font Colour and IconsShow only highlighted rows, filter by icon, combine colour filters with value filters and count what is visible.
-
Advanced Filter: Criteria Range and Unique RecordsCriteria ranges with AND and OR logic, wildcards and formula criteria, copy results to another location, and extract unique records.
-
Sort and Filter an Excel Table: Slicers and Total RowCtrl+T, permanent filter buttons, slicers with Multi-Select, a Total Row that respects filters, and structured references.
-
Remove Duplicates and Highlight Duplicate RowsData > Remove Duplicates, Conditional Formatting for duplicates, COUNTIF helper columns, UNIQUE and Power Query.
-
SORT, SORTBY, FILTER and UNIQUE FunctionsDynamic sort and filter formulas in Excel 365 and 2021: syntax, AND and OR criteria, spill ranges, top N with TAKE and chaining.
-
SUBTOTAL and AGGREGATE: Total Filtered Data OnlyFunction numbers 9 vs 109, options that skip hidden rows and errors, a visible-row counter and the Table Total Row.
-
Sort and Filter Not Working: Fixes and Sort OptionsBlank rows, merged cells, numbers stored as text, greyed-out buttons, stale filters, sort left to right and case-sensitive sort.
What you will learn
Frequently asked questions
What is the difference between AutoFilter and Advanced Filter?
AutoFilter filters in place using drop-down buttons and allows two conditions per column. Advanced Filter uses a criteria range you write on the sheet, supports any number of conditions with OR logic across columns, can use formulas as criteria and can copy the result elsewhere. Chapter 6 covers it.
Why does my sort mix up my rows?
Usually because only one column was selected. Select a single cell inside the table and Excel will expand the selection to the whole table. Chapter 1 shows this and chapter 11 lists every other cause.
Can I sort by more than one column?
Yes. With Data > Sort you can add up to 64 levels, for example Region then Sales, and each level can sort by value, cell colour, font colour or icon.
Can Excel sort and filter automatically when the data changes?
The Sort and Filter commands are one-off actions. In Excel 365 and 2021 the SORT, SORTBY, FILTER and UNIQUE functions return a live sorted or filtered copy that recalculates whenever the source changes. Chapter 9 explains them.
How do I total only the filtered rows?
SUM includes hidden rows. Use SUBTOTAL(109, range) or AGGREGATE(9, 5, range), or switch on the Total Row of an Excel Table, which writes SUBTOTAL for you. Chapter 10 covers both functions.