Excel Skills Checklist: What to Know for a Job
By ExcelReady TeamUpdated 6 min read
The short answer
For most office jobs, you should be able to format data, write formulas with SUM, AVERAGE and IF, use $ references, sort and filter, total with SUMIFS and COUNTIFS, look up values with VLOOKUP or XLOOKUP, and build a pivot table and a chart. Advanced roles add INDEX MATCH, dynamic arrays and Power Query.
Key takeaways
- Tick a skill only when you can do it on a blank sheet, without searching for the steps.
- Basics: data entry, formatting, SUM and AVERAGE, relative and $ references, sort and filter.
- Intermediate is what most job tests check: IF, SUMIFS, COUNTIFS, VLOOKUP or XLOOKUP, pivot tables and charts.
- Advanced: INDEX MATCH, dynamic arrays like FILTER and UNIQUE, Power Query and clear model layout.
- Speed matters in tests. Practise each skill until it takes a minute or less.
On this page+
This Excel skills checklist lists what employers usually expect, from basic data entry to pivot tables, with a quick task for each skill so you can test yourself honestly. Most office jobs need the basic and intermediate levels; analyst and finance roles go further. Work down the list, tick only what you can do on a blank sheet without help, and practise the gaps before your interview or Excel test.
How to use this checklist#
Treat each line as a task, not a topic. "I know VLOOKUP" is not enough; "I can pull a salary into a new table by employee ID, with an exact match, in under a minute" is. For each skill:
- Open a blank sheet and type a few rows of sample data.
- Do the task in the "Prove it" column without looking anything up.
- Tick it only if you got the right result first time and could explain each step.
Job tests are timed, so speed counts as much as knowing the steps. If you'd like a faster starting point, the Excel level test shows in 12 questions which level you're at.
Level 1: Basic Excel skills#
These are the skills almost every office role takes for granted. If any are missing, start here.
| Skill | Prove it |
|---|---|
| Enter and edit data | Type a 10-row list, fix a typo inside a cell with F2 (Mac: Ctrl+U) |
| Format cells | Show numbers as currency with 2 decimals, dates as 12-Mar-2026, and a bold header row |
| Basic formulas | Calculate a total price as =B2*C2 and fill it down |
| SUM, AVERAGE, MIN, MAX | Total and average a column with AutoSum (Alt+=; Mac: ⌘+Shift+T) |
| Relative and $ references | Work out each row's share of a total with =B2/$B$10 and fill down without errors |
| Sort and filter | Sort by two columns and filter to one region |
| Select and move fast | Select a full column of data with Ctrl+Shift+↓ (Mac: ⌘+Shift+↓) |
| Freeze panes and print | Keep the header row visible while scrolling |
The basics skill check covers this level in one go and checks each answer.
Level 2: Intermediate Excel skills#
Intermediate skills are what most Excel job tests check. They let you answer business questions, not just enter data.
| Skill | Prove it |
|---|---|
| IF, with AND and OR | Mark a row "Pass" if both scores are 50 or more |
| COUNTIF and COUNTIFS | Count open tickets with High priority |
| SUMIF and SUMIFS | Total North region laptop revenue |
| VLOOKUP or XLOOKUP | Bring a price from a product table into an order list by product code |
| IFERROR | Show "Not found" instead of #N/A |
| Pivot tables | Summarise sales by region and month, then filter by product |
| Charts | Turn a monthly total into a clear column or line chart with a title |
| Text functions | Split "Smith, Anna" into two columns, and clean extra spaces with TRIM |
| Date functions | Find the days between two dates and the month of an order date |
| Data validation | Add a drop-down list to a Status column |
Two functions do much of the heavy lifting here. SUMIFS adds numbers that meet one or more conditions, for example:
=SUMIFS(E2:E9,A2:A9,"North",C2:C9,"Laptop")The SUMIFS function guide explains every argument. VLOOKUP finds a value in the first column of a table and returns a value from another column in the same row:
=VLOOKUP(F2,A2:D6,4,FALSE)With an employee table in A2:D6 and the ID 104 in F2, this returns Wei's salary, 61,000. The 4 is the column to return and FALSE means exact match. The VLOOKUP function guide covers the common errors. For more detail on this level, see the intermediate Excel skills list.
Level 3: Advanced Excel skills#
Advanced skills matter for analyst, finance and reporting roles. Many other jobs never need them, so check the job ads before you invest the time.
| Skill | Prove it |
|---|---|
| INDEX and MATCH | Look up a value to the left, or by both row and column |
| Dynamic arrays | Use FILTER, UNIQUE and SORT to build a live report (Excel 2021 or Microsoft 365) |
| Advanced pivot tables | Group dates by month, add a calculated field, show values as % of total |
| Power Query | Import a folder of monthly files and combine them into one table |
| Model layout | Separate inputs, calculations and outputs, with no hard-coded numbers in formulas |
| Error checking | Trace precedents to find why a total is wrong |
| Macros (some roles) | Record a macro that formats a weekly report |
Which skills does your target job need?#
The core is the same, but each field leans on different parts of the checklist:
- Finance and accounting: SUMIFS, lookups, $ references, rounding, date functions and model layout. See Excel skills for finance jobs.
- Marketing: pivot tables, charts, percentages, COUNTIFS and cleaning exported data. See Excel skills for marketing jobs.
- First jobs after university: formulas, IF, SUMIF, VLOOKUP, pivot tables and charts. See Excel skills for fresh graduates.
- Admin and operations: sorting, filtering, data validation, COUNTIF and tidy formatting.
If a job ad names a test, look at how it works. For example, the Indeed Excel assessment guide explains that format.
How to turn the checklist into evidence#
Ticked skills are only useful if you can show them. Three ways to do that:
- On your resume, name specific tasks, not "advanced Excel". The guide to showing Excel skills on your resume has example bullets.
- In the interview, be ready to describe a real task: what data you had, which function you used and what the result was.
- In the test, rely on practice, not memory. Try a timed run with the basic Excel test or the intermediate Excel test before the real one.
Practise it#
Pick the first unticked line on your checklist and practise it now. Try the VLOOKUP exercise on this page, then practise VLOOKUP exact match in a live spreadsheet, build your first pivot table, and run the basics skill check to confirm Level 1 is solid. Each lesson checks your formula instantly, so you know a skill is really ticked.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
A core checklist skill: in G2, use VLOOKUP to return the Salary for the employee ID typed in F2. Look in A2:D6 and use an exact match.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Salary | Salary | ||||||
| 2 | ||||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 |
Tip: VLOOKUP needs the value to find, the table, the column number (Salary is column 4) and FALSE for an exact match.
Practise this in a lesson
Frequently asked questions
What Excel skills do employers look for?
Most employers expect clean data entry and formatting, basic formulas, $ references, sorting and filtering, SUMIFS and COUNTIFS, VLOOKUP or XLOOKUP, pivot tables and simple charts. Finance and analyst roles add more modelling and data tools.
What is considered basic Excel skills?
Entering and formatting data, writing simple formulas with + - * /, using SUM, AVERAGE, MIN and MAX, copying formulas with relative and $ references, and sorting and filtering a list.
What counts as advanced Excel skills?
Usually INDEX MATCH or XLOOKUP in tricky layouts, dynamic array functions like FILTER and UNIQUE, Power Query for cleaning and combining data, and building clear, checkable models. Macros and VBA count for some roles but are not required for most.
How do I know my Excel level?
Try each task in a checklist on a blank sheet without help, and time yourself. A short level test is a quicker way to see which skills you have and which to practise next.
How long does it take to learn Excel for a job?
It depends on your starting point and how often you practise. Most of the intermediate skills on this list are single functions or features, so steady daily practice on real tasks builds them faster than reading about them.