Advanced Excel

Lookups, dropdowns, duplicates & PivotTables — with examples.

A free advanced Excel tutorial for the features that level you up: create a dropdown list with Data Validation, find and highlight duplicate names, remove duplicate rows, VLOOKUP / XLOOKUP / INDEX-MATCH lookups, nested IF / IFS, COUNTIFS & SUMIFS, conditional formatting and a first PivotTable — every topic with a worked, step-by-step example. Runs fully in your browser, no sign-up.

Frequently asked questions

How do I create a dropdown list in Excel?

Select the cells, go to Data ▸ Data Validation, choose Allow: List, and in the Source box type your options separated by commas (e.g. High,Medium,Low) or select a range of cells that holds the list. Click OK and a dropdown arrow appears in each cell. You can also build dependent (cascading) dropdowns with INDIRECT.

How do I find duplicate names in Excel?

Select the column, then Home ▸ Conditional Formatting ▸ Highlight Cells Rules ▸ Duplicate Values to colour every repeated name. To flag them with a formula, use =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique") in a helper column.

How do I remove duplicate rows in Excel?

Click a cell in your data, go to Data ▸ Remove Duplicates, tick the column(s) that define a duplicate, and click OK — Excel keeps the first of each and reports how many it removed. To keep the original intact, use Data ▸ Advanced ▸ Unique records only and copy to a new location.

What is the difference between VLOOKUP and XLOOKUP?

VLOOKUP searches the first column of a table and returns a value to its right using a column-index number (always end it with FALSE for an exact match). XLOOKUP (Excel 365/2021) is more flexible: it looks left or right, takes a separate return range so there's no counting, and has a built-in not-found message. INDEX+MATCH works in every Excel version.

What is a PivotTable in Excel?

A PivotTable summarises thousands of rows into a compact report without formulas. Click your data, choose Insert ▸ PivotTable, then drag a category into Rows and a number into Values to get instant totals, averages or counts — and change the breakdown just by dragging fields.

Related tools — Guides & Tutorials