Intermediate Excel Skills: The List Employers Expect
By ExcelReady TeamUpdated 5 min read
The short answer
Intermediate Excel skills are the ones that turn a list into answers: lookups with VLOOKUP or XLOOKUP, totals and counts with SUMIFS and COUNTIFS, logic with IF, AND and OR, absolute references, pivot tables, sorting and filtering, conditional formatting, data validation dropdowns and basic text and date formulas. Most office jobs that list "intermediate Excel" mean this set.
Key takeaways
- Intermediate Excel means you can answer questions from a table, not just enter and total data.
- The core functions are VLOOKUP or XLOOKUP, SUMIFS, COUNTIFS, IF with AND or OR, and IFERROR.
- Pivot tables, conditional formatting and dropdown lists are tools you're expected to use without help.
- Correct $ references are what make one formula work across a whole summary table.
On this page+
Intermediate Excel skills are what employers usually mean when a job ad asks for "good" or "intermediate" Excel. You go beyond typing and totalling: you look up values, total and count with conditions, apply simple logic, and summarise a table with a pivot. This guide lists the skills, shows each one on the same small sales table and explains how to check that you really have them.
What counts as intermediate Excel#
Intermediate means you can answer business questions from a table without help. A beginner can enter data and use SUM. An intermediate user can say "North sold 1630 this month, and most of it was one laptop order" and show the formula behind it.
There's no official standard for the levels, so job ads vary. The list below covers what most office, finance, marketing and analyst roles expect. For a role-by-role view, see Excel skills for finance jobs and Excel skills for marketing jobs.
All examples use this sales table in A1:E7:
| Order | Region | Rep | Product | Amount |
|---|---|---|---|---|
| 1001 | North | Lee | Laptop | 1200 |
| 1002 | South | Kim | Monitor | 300 |
| 1003 | North | Lee | Monitor | 350 |
| 1004 | East | Ravi | Laptop | 1100 |
| 1005 | South | Kim | Laptop | 1250 |
| 1006 | North | Ana | Keyboard | 80 |
The intermediate Excel skills list#
Here is the full list in one place. The sections after it show the most tested ones in action.
| Skill | What it lets you do |
|---|---|
| VLOOKUP or XLOOKUP | Find a value by ID or name |
| SUMIFS, COUNTIFS, AVERAGEIFS | Total, count or average with one or more conditions |
| IF with AND and OR | Turn a rule into a label or flag |
| IFERROR | Show a friendly message instead of an error |
| Absolute and mixed references | Fill one formula across a table without breaking it |
| Pivot tables | Summarise a table by group in a few clicks |
| Sort and filter | Put data in order and show only the rows you need |
| Conditional formatting | Highlight values over a limit, duplicates or the top 3 |
| Data validation | Limit entries to a dropdown list |
| Excel tables (Ctrl+T) | Keep ranges growing as you add rows |
| Text and date basics | TRIM, LEFT, TEXT, DATEDIF, NETWORKDAYS |
Lookups: VLOOKUP and XLOOKUP#
A lookup finds one row and returns a value from it. To get the rep for order 1004:
=VLOOKUP(1004,A2:E7,3,FALSE)The result is Ravi. The 3 means "return the third column of the table", and FALSE asks for an exact match. In Excel 2021 or Microsoft 365, =XLOOKUP(1004,A2:A7,C2:C7) does the same and doesn't need a column number. The VLOOKUP function guide covers the arguments and the common errors.
Conditional totals and counts: SUMIFS and COUNTIFS#
SUMIFS adds the numbers that meet every condition you give it. The range to add comes first:
=SUMIFS(E2:E7,B2:B7,"North")returns 1630 (1200 + 350 + 80). Add a second condition to narrow it:
=SUMIFS(E2:E7,B2:B7,"North",D2:D7,"Laptop")returns 1200. COUNTIFS counts instead of adding: =COUNTIFS(B2:B7,"South",E2:E7,">1000") returns 1, because only order 1005 is a South sale over 1000. See the SUMIFS function guide for the argument order.
To build a full summary grid, put regions down a column and products across a row, then use one SUMIFS with mixed references such as $G2 and H$1. The $ keeps the formula pointing at the right header as you fill it across and down. Getting those references right is one of the clearest signs of an intermediate user.
Logic: IF, AND, OR and IFERROR#
IF turns a rule into a result. To flag big orders:
=IF(E2>=1000,"Large","Standard")Order 1001 shows Large. Combine it with AND when two things must be true: =IF(AND(B2="North",E2>=1000),"Yes","No") returns Yes for order 1001 and No for 1003. Wrap a lookup in IFERROR so a missing ID shows a message instead of #N/A: =IFERROR(VLOOKUP(2000,A2:E7,3,FALSE),"Not found").
Pivot tables#
A pivot table summarises a table by group without any formulas. Click inside the data, choose Insert > PivotTable, then drag Region to Rows and Amount to Values. Excel shows North 1630, South 1550 and East 1100, with a grand total of 4280. Drag Product to Columns to split each region by product.
Intermediate users are expected to build one like this, switch Sum to Count or Average, and refresh it when the data changes (Data > Refresh All, or Ctrl+Alt+F5 on Windows).
Data tools: sorting, filtering, formatting and dropdowns#
Intermediate Excel also covers the tools that keep data clean and readable:
- Filter: turn on filter buttons with Ctrl+Shift+L (Mac: ⌘+Shift+F) and show only North rows.
- Conditional formatting: highlight orders over 1000 so they stand out.
- Data validation: restrict the Region column to a dropdown of North, South and East, so nobody types "Nrth". The guide to creating a dropdown list with data validation shows how.
- Tables: press Ctrl+T (Mac: ⌘T) so formulas and pivots pick up new rows.
How to check your level and show it#
The honest test is simple: can you do each skill above on a fresh table, without looking anything up, in a few minutes? If yes, you're intermediate. If a few items are shaky, practise those first.
When you apply, name the skills in context, such as "built a monthly sales summary with SUMIFS and a pivot table". If you're starting your career, see Excel skills for fresh graduates. If you're heading towards analysis work, Excel for business analysts shows the next set of skills to learn.
Practise it#
Lists don't build skill, but practice does. Practise VLOOKUP in a live spreadsheet, then practise COUNTIFS and SUMIFS with several conditions: you type the formula, and it checks it instantly. Then build your first summary in Pivot tables 1. To test yourself against the clock, try the intermediate Excel mock test.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
What is the total Monitor revenue in the North region? Click G2 and use SUMIFS on the Amount column (E2:E7) with two conditions: Region is North and Product is Monitor.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Amount | |||||||
| 2 | ||||||||
| 3 | ||||||||
| 4 | ||||||||
| 5 | ||||||||
| 6 | ||||||||
| 7 | ||||||||
| 8 | ||||||||
| 9 | ||||||||
| 10 |
Tip: SUMIFS puts the range to add first, then each criteria range followed by its condition.
Practise this in a lesson
Frequently asked questions
What are intermediate Excel skills?
Intermediate Excel skills include VLOOKUP or XLOOKUP, SUMIFS and COUNTIFS, IF with AND and OR, absolute references, pivot tables, sorting and filtering, conditional formatting and dropdown lists. They let you summarise and check data, not only type it in.
Is VLOOKUP an intermediate skill?
Yes. VLOOKUP is usually the first function people think of as intermediate. Employers often treat INDEX MATCH and XLOOKUP as the next step up.
Are pivot tables intermediate or advanced?
Building a simple pivot table is usually counted as intermediate. Features such as calculated fields, % of total, grouping and slicers sit on the line between intermediate and advanced.
How long does it take to reach intermediate Excel?
It depends on how often you practise. If you already know the basics, working through lookups, conditional totals and pivot tables with hands-on exercises a few times a week is a realistic way to get there.