Modern Excel for Excels
Modern Excel refers to newer features: dynamic array functions such as FILTER, SORT, UNIQUE and XLOOKUP that return many values and spill into neighbouring cells, and Power Query, a tool that imports data from files and databases, cleans it through recorded steps and loads it into the workbook.
Stage: Reporting · Part of the Excel roadmap · Last reviewed 19 November 2026
What to learn in Modern Excel
- Use FILTER, SORT and UNIQUE to produce a live, spilled list of results
- Explain spill ranges and fix a #SPILL! error
- Import a CSV file and a folder of files with Power Query
- Apply Power Query steps such as changing types, unpivoting and merging queries
- Refresh a query to repeat the same cleaning on new data
Modern Excel interview questions
What causes a #SPILL! error?
A dynamic array formula needs a block of empty cells to return its results, and something blocks it, such as existing data, a merged cell or a table. Clearing the obstruction or moving the formula removes the error.
Why use Power Query instead of cleaning data by hand?
Power Query records each transformation as a step, so the same cleaning reruns automatically when the source changes. This is repeatable and auditable, unlike manual edits, and it can combine many files or tables without copy and paste.
Where Modern Excel fits in the Excel roadmap
Modern Excel belongs to the Reporting stage (beginner level). Show insights.
Other units in this stage
Back to the full Excel roadmap