Best Excel Skills for Office Jobs in 2026

Excel remains the undisputed lingua franca of office work. Across industries, continents, and job titles, it sits at the centre of how organisations manage data, track performance, and make decisions. Yet despite being decades old, Excel continues to evolve — and the gap between candidates who truly know it and those who merely claim to has never mattered more.

This guide covers every Excel skill that employers actually look for in 2026, from the foundational functions that appear on every job description to the modern tools that mark you out as a serious candidate. Whether you're applying for your first admin role, pivoting into finance, or trying to level up in operations, this is the complete picture.

No function has appeared on more job descriptions, in more industries, across more decades than VLOOKUP. It has outlasted trends, survived software revisions, and still serves as the single most common proxy hiring managers use to gauge whether a candidate actually knows Excel. Knowing it isn't optional. Understanding it deeply — and knowing when to move beyond it — is what separates candidates.

VLOOKUP: the classic

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP searches the leftmost column of a range for a value and returns something from a specified column to the right. It's the function most interviewers expect you to walk through from memory.

Example: You have an employee ID in cell A2 and a staff directory in columns F through I. To return the department:

=VLOOKUP(A2, F:I, 3, FALSE)

The FALSE at the end forces an exact match — almost always what you want. Forgetting it, or leaving it as TRUE, is one of the most common beginner mistakes and can silently return wrong results.

Its limitations — know these

VLOOKUP only searches left-to-right. If the column you want to return is to the left of your search column, it fails. It also breaks the moment you insert a column in the middle of your table, because the column index number (3 in the example above) is hardcoded. In large workbooks maintained by multiple people, this is a real and frequent problem.

XLOOKUP: the modern replacement

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP, introduced in Microsoft 365, resolves every major VLOOKUP limitation. You specify the lookup column and the return column separately, so direction doesn't matter. You can search both horizontally and vertically. You can return an entire row or column, not just a single cell. And you can define exactly what happens when no match is found — no more ugly #N/A errors without extra IFERROR wrapping.

Same example in XLOOKUP:

=XLOOKUP(A2, F:F, H:H, "Not found")

Cleaner, more readable, and won't break if columns shift.

When companies still use VLOOKUP

If the organisation uses Excel 2019 or earlier — which is still common in the public sector, legal, and some finance environments — XLOOKUP won't be available. Always check which version of Excel the role uses. Being fluent in both marks you as adaptable.

Interview tip: Be ready to explain the differences between VLOOKUP, XLOOKUP, and INDEX-MATCH (covered in section 4), and articulate when you'd reach for each. Interviewers who know Excel will probe this specifically.

If you could only master one Excel feature, PivotTables would be the most defensible choice. They allow you to summarise hundreds of thousands of rows of data in seconds, without writing a single formula. They're interactive, reconfigurable, and used daily in every sector from retail to investment banking.

The basics — what every user should know

Creating a PivotTable is straightforward: select your data, go to Insert → PivotTable, choose where to place it. The PivotTable field list appears on the right with four zones — Filters, Columns, Rows, and Values. Drag fields between them and the table restructures instantly.

The deeper skill is knowing what question you're trying to answer before you build it. Employers don't just want people who can create PivotTables mechanically — they want people who can look at raw data and instinctively know how to summarise it usefully.

Intermediate techniques that distinguish good users

Grouping: You can group date fields by week, month, quarter, or year without creating new columns in your source data. Right-click any date value in the PivotTable and select Group. This is how you turn a transaction log into a monthly revenue summary in about four clicks.

Calculated fields: When the value you need isn't in your source data — for example, profit margin as a percentage of revenue — you can create it inside the PivotTable using Insert → Calculated Field. This keeps your source data clean while giving you derived metrics.

Show values as: The dropdown under "Show Values As" is criminally underused. It lets you show values as a percentage of the column total, running totals, differences from a previous period, or rank. Most of the standard reporting work that people write complex formulas for can be done here.

Slicers and timelines: Slicers are visual filter buttons that sit next to your PivotTable. Timelines do the same for date fields, letting you drag a slider to select time periods. Both can be connected to multiple PivotTables at once, making it straightforward to build simple dashboards without any advanced knowledge.

Refreshing data — a common mistake

PivotTables don't update automatically when source data changes. You need to right-click and Refresh, or set the workbook to refresh on open. New rows added below your source range won't be captured unless your source is defined as a Table (Ctrl+T) — which automatically expands. Converting source data to a Table before building PivotTables is a best practice that saves significant frustration.

PivotCharts

A PivotChart is a chart directly linked to a PivotTable. Filter the PivotTable and the chart updates. This is how most professional management dashboards are built — not with static charts, but with PivotCharts driven by slicers.

Real-world use cases: Monthly sales reports, budget vs. actuals summaries, headcount analysis by department, customer segmentation, stock movement reports.

The purpose of conditional formatting is to make patterns, anomalies, and status visible at a glance — without the reader having to scan numbers manually. Used well, it replaces verbal explanations. Used poorly (everything highlighted, clashing colours everywhere), it creates visual noise that obscures the very thing it was meant to show.

The fundamentals

Access conditional formatting via Home → Conditional Formatting. The built-in options cover most situations: highlight cells greater/less than a value, top/bottom N items, above or below average, duplicates, and date-based rules. These require no formula knowledge and are immediately useful.

Data bars, colour scales, and icon sets

These three tools visualise the relative magnitude of numbers without creating a chart. Data bars display a small bar inside the cell proportional to the cell's value. Colour scales apply a gradient — typically red through green — across a range. Icon sets add traffic lights, arrows, or flag symbols.

Practical example: A weekly KPI report for a sales team with 12 reps. Apply a three-colour icon set to the "Target vs. Actual %" column. At a glance, the manager sees green arrows for overperformers, yellow dashes for those on track, and red arrows for those behind. No chart needed. No manual formatting each week.

Custom formula rules — the real power

The most flexible conditional formatting uses custom formulas. This lets you highlight an entire row based on a value in one column, which is a common business need.

Example: Highlight all rows in a project tracker where the status column (column D) says "Overdue":

=$D2="Overdue"

The $ locks the column reference but not the row, so the rule evaluates correctly for every row in the range.

Alternating row colours without a Table:

=MOD(ROW(),2)=0

Apply this to your entire data range for clean alternating shading — useful when you can't convert data to a Table.

Important caveats

Conditional formatting rules stack and evaluate in priority order. If two rules conflict, the one higher in the list wins. Managing the rules list (Home → Conditional Formatting → Manage Rules) becomes important in shared workbooks where rules accumulate over time. Excessive conditional formatting can also slow workbooks down significantly on large datasets.

INDEX-MATCH has existed longer than XLOOKUP and remains essential knowledge for 2026, both because many organisations use older Excel versions and because it demonstrates a level of formula thinking that basic VLOOKUP knowledge doesn't.

Understanding the components separately first

INDEX returns the value at a specific position in a range:

=INDEX(A1:A100, 5)

Returns whatever is in the 5th cell of the range.

MATCH returns the position of a value within a range:

=MATCH("Finance", B1:B100, 0)

Returns the row number where "Finance" first appears. The 0 specifies exact match.

Combining them

The genius of INDEX-MATCH is using MATCH to supply the position argument that INDEX needs:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Concrete example: You want the salary for employee "Sarah Chen". Employees are in column B, salaries in column E — and column E is to the right, which VLOOKUP would handle, but let's say the lookup column is actually to the right of the salary column. VLOOKUP fails. INDEX-MATCH doesn't care:

=INDEX(E:E, MATCH("Sarah Chen", B:B, 0))

Why it still matters in 2026

Beyond compatibility with older Excel versions, INDEX-MATCH is faster on very large datasets because MATCH can work with sorted data using binary search (using 1 or -1 as the third argument instead of 0). It also handles two-dimensional lookups more naturally than XLOOKUP when you need to match both a row and a column:

=INDEX(B2:F20, MATCH("Q3", A2:A20, 0), MATCH("Revenue", B1:F1, 0))

This finds the intersection of the "Q3" row and "Revenue" column — a genuinely common reporting task that's clunky to solve any other way.

Garbage in, garbage out. Data validation is the discipline of making it structurally difficult to enter bad data in the first place. It's a skill that reflects professionalism and an understanding of how shared workbooks actually fail in practice.

Basic list validation

Select a cell or range, go to Data → Data Validation, choose "List" as the validation type, and either type options separated by commas or point to a range. The cell gets a dropdown arrow and only allows listed values.

This sounds simple — and it is — but it's the difference between a clean dataset and one full of "N/a", "N/A", "n/a", and "Not Applicable" all meaning the same thing, which you'll spend an hour cleaning before analysis.

Error alerts and input messages

Data validation includes two underused elements. Input messages appear as a tooltip when a user selects the cell — use these to give brief instructions ("Enter date in DD/MM/YYYY format"). Error alerts appear when invalid data is entered and can be set to Stop (prevent entry entirely), Warning (allow with a warning), or Information (just inform the user). Stop is appropriate for critical fields; Warning is better when occasional exceptions are legitimate.

Dependent drop-downs with INDIRECT

This is an intermediate technique that impresses interviewers. A dependent drop-down changes its options based on a selection in another cell. If you choose "Europe" in one dropdown, the next only shows European countries; choose "Asia" and it shows Asian countries.

The technique uses named ranges (name a range "Europe" containing European countries, another "Asia" for Asian countries) and the INDIRECT function:

=INDIRECT(A2)

Where A2 contains the selection from the first dropdown. Excel looks for a named range matching whatever is in A2. When the first dropdown shows "Europe", the second dropdown pulls from the range named "Europe". Elegant and powerful for data entry forms.

Validation for numbers, dates, and text length

Beyond lists, validation can restrict input to whole numbers within a range, decimals, dates between two limits, or text of a specific length. These are invaluable in expense forms, booking sheets, and any workbook where structured input matters.

Logical functions are how formulas think. The ability to write a formula that produces different outputs based on conditions is fundamental to almost every non-trivial Excel use case — from payroll calculations to project status flags to commission structures.

IF: the foundation

=IF(logical_test, value_if_true, value_if_false)

Example: Flag invoices that are overdue. Due dates are in column C, today is today:

=IF(C2<TODAY(), "Overdue", "OK")

Nested IF: use sparingly

You can nest IF statements for multiple conditions:

=IF(A2>=90, "Excellent", IF(A2>=70, "Good", IF(A2>=50, "Pass", "Fail")))

This works but becomes unreadable beyond three levels. Excel limits nesting to 64 levels, but you should impose your own limit of about three for maintainability.

IFS: the clean alternative

=IFS(A2>=90, "Excellent", A2>=70, "Good", A2>=50, "Pass", A2<50, "Fail")

Each condition-result pair is stated clearly. No nesting, no closing brackets to count. IFS is available in Excel 2019 and Microsoft 365.

AND, OR, NOT — compound conditions

=IF(AND(B2="Approved", C2>1000), "High Priority", "Standard")

This flags a row as High Priority only if both conditions are true. OR returns true if any condition is true. These are the building blocks of most real-world business logic.

IFERROR and IFNA

These handle formula errors gracefully — critical for any workbook that will be used by others:

=IFERROR(VLOOKUP(A2, Data!B:D, 2, FALSE), "Not found")

IFNA is more precise — it only catches #N/A errors, leaving other errors (like #DIV/0!) visible, which is often the right choice because those errors indicate real problems.

SWITCH: the cleaner multiple-match

=SWITCH(A2, "Mon", "Monday", "Tue", "Tuesday", "Wed", "Wednesday", "Other")

Where IFS evaluates conditions, SWITCH matches a value against a list of possibilities. Cleaner than nested IFs for code-like category mapping.

The ability to build charts that are clear, honest, and appropriately formatted is increasingly important as Excel is used for stakeholder reporting and executive presentations. Most people can insert a chart. Far fewer can build one that actually communicates.

Chart selection — matching the chart to the question

This is the skill that matters most and is rarely taught. The question you're answering should determine the chart type:

Question	Chart type
How does a value change over time?	Line chart
How do categories compare in size?	Bar or column chart
What's the part-whole relationship?	Stacked bar (not pie — unless you have 2-3 slices)
What's the distribution?	Histogram
Is there a relationship between two variables?	Scatter plot
How are multiple metrics performing against targets?	Combo chart (bar + line)

Pie charts are widely used and almost always the wrong choice for more than three categories. The human eye is poor at comparing angles. A sorted bar chart is almost always more readable.

Formatting that communicates

Professional charts have these characteristics: a clear, specific title (not "Sales Chart" but "Monthly UK Sales — Jan to Jun 2026"); axis labels with units; no 3D effects (they distort perception); gridlines that are light grey rather than prominent; and a minimal colour palette that serves the data rather than decorating it.

Remove chart borders. Use direct labels on data series rather than a legend when there are only two or three series — it reduces eye travel. Align your chart to the cell grid (hold Alt while resizing) for a clean, professional layout.

Dynamic charts linked to dropdowns

An intermediate-to-advanced skill: connect a chart to a dropdown menu so the user can switch what's displayed without touching the chart itself. This typically involves using INDIRECT, named ranges, or OFFSET to create a dynamic data range that responds to a cell reference. The result looks polished and is genuinely useful in reporting templates used by non-Excel users.

Sparklines — micro-charts in cells

Sparklines are tiny charts that live inside a single cell. They show trends at a glance without taking up report space. Insert via Insert → Sparklines. Use the Win/Loss type for binary outcomes (above/below target) and Line for trends. They pair extremely well with PivotTables.

Power Query, accessible via Data → Get & Transform, is the most significant advancement in Excel's analytical capability since PivotTables. It allows you to connect to data sources, clean and reshape the data, and load it into Excel — and then repeat that entire process with one click the next time, because the steps are recorded.

Until recently, this kind of data preparation was either done manually (tedious and error-prone), with complex array formulas (fragile and hard to maintain), or required VBA (beyond most users). Power Query democratises it.

What it actually does

Connect to a CSV file, a folder of CSV files, a SQL database, a SharePoint list, a web page, or an Excel table. The Query Editor opens, showing a preview of the data and a panel on the right called Applied Steps. Every action you take is recorded as a step — remove a column, filter rows, split a column by delimiter, unpivot a table. When you click Close & Load, the cleaned data appears in Excel.

The next time the source data updates, you right-click the query and hit Refresh. All the steps run again automatically on the new data.

The most useful transformations

Unpivoting: Perhaps the single most valuable Power Query operation. Data in "wide" format (dates as column headers, for example) is common from exports but useless for PivotTables. Select the identifier columns, right-click the rest, and click Unpivot Columns. Instantly transformed.

Merging queries: The Power Query equivalent of VLOOKUP. Two tables, matched on a common key, combined into one. Unlike VLOOKUP, it doesn't slow down with large data, and it handles many-to-many relationships cleanly.

Appending queries: Stack multiple tables — say, twelve monthly exports — into one. If the folder containing those files is the source, new monthly files added to the folder appear automatically on next refresh.

Custom columns: Add calculated columns using Power Query's M language. Basic operations (combining text columns, simple arithmetic) are accessible through the Add Column menu without knowing M. For more complex logic, the formula bar in the editor accepts M expressions — a different but learnable syntax.

Why this differentiates you

Most Excel users still do data preparation manually — copying and pasting, deleting rows, using Text to Columns. Mentioning Power Query on a CV, and being able to describe a real use case, immediately signals to a hiring manager in a data-adjacent role that you've moved past the basics. In finance, operations, and data analysis roles, it's rapidly becoming an expected skill rather than a differentiator.

Dynamic arrays, introduced in Microsoft 365, changed how Excel formulas fundamentally work. Before them, a formula returned exactly one value to exactly one cell. Dynamic array formulas can return hundreds of values that automatically spill into adjacent cells — and they update when the data changes.

FILTER — the most immediately useful

=FILTER(array, include, [if_empty])

Example: Return all rows from a sales table where the region is "North":

=FILTER(A2:D200, B2:B200="North", "No results")

The results spill automatically into as many rows as needed. Previously, achieving this required complex array formulas or VBA. Now it's one formula.

SORT and SORTBY

=SORT(array, [sort_index], [sort_order], [by_col])
=SORTBY(array, by_array1, sort_order1, ...)

Return a sorted version of a range without modifying the original data. SORTBY lets you sort by a column that isn't in the output range, which is extremely useful for reports.

UNIQUE

=UNIQUE(array, [by_col], [exactly_once])

Returns a list of distinct values from a range. Combine with SORT for an automatically updating dropdown source or summary list.

SEQUENCE

=SEQUENCE(rows, [columns], [start], [step])

Generates a sequence of numbers. Useful for building dynamic date ranges, row numbers in templates, or testing formulas.

The spill range operator (#)

When a dynamic array formula spills into multiple cells, you can reference the entire spill range using #:

=SORT(UNIQUE(A2:A200))

If you put this formula in E2, you can reference the entire result as E2# in other formulas. This is how modern Excel workbooks chain dynamic calculations.

The difference between a workbook written by someone who learned Excel informally and one written by a professional is often visible in the formulas themselves. Professional Excel users write formulas that are readable and maintainable.

Named ranges

Select a range, click the Name Box (top-left of the formula bar), and type a name. Now you can use that name in any formula in the workbook:

=SUM(Sales_Q1)
=VLOOKUP(A2, Staff_Directory, 3, FALSE)

These formulas explain themselves. =SUM(B2:B47) requires you to go look at what's in column B. =SUM(Sales_Q1) doesn't.

Manage named ranges via Formulas → Name Manager. Delete unused names (which accumulate in copied workbooks), check for errors, and document what each name covers.

Tables and structured references

Converting a range to a Table (Ctrl+T) is one of the most underused and impactful habits in Excel. Tables automatically expand when new rows are added. They come with built-in filter dropdowns. Formulas automatically fill down when a new row is added. And they enable structured references:

=SUM(Table1[Revenue])
=Table1[@Revenue] - Table1[@Cost]

The @ means "this row" — so in a calculated column, every row gets the right values automatically. Structured references are also how Power Query, PivotTables, and XLOOKUP integrate most cleanly with your data. Make Tables a default habit, not an afterthought.

Efficiency in Excel isn't just about knowing functions — it's about working fluidly enough that the tool doesn't slow your thinking. Hiring managers and colleagues notice when someone works quickly and confidently. They also notice when someone reaches for the mouse to do things that take two keystrokes.

Navigation

Shortcut	Action
Ctrl+End	Jump to last used cell
Ctrl+Home	Jump to cell A1
Ctrl+Arrow	Jump to edge of data region
Ctrl+Shift+Arrow	Select to edge of data region
Ctrl+Shift+End	Select from current cell to last used cell
F5 (then Enter)	Return to last position

Data entry and editing

Shortcut	Action
Ctrl+D	Fill down from cell above
Ctrl+R	Fill right from cell to left
Ctrl+;	Insert today's date
Ctrl+Shift+:	Insert current time
F4	Repeat last action OR toggle absolute/relative references in a formula
Ctrl+Enter	Confirm entry and stay in same cell
Alt+Enter	Insert line break within a cell

Formatting and structure

Shortcut	Action
Ctrl+T	Convert selection to Table
Ctrl+Shift+L	Toggle AutoFilter
Ctrl+1	Open Format Cells dialog
Ctrl+Shift+$	Apply currency format
Ctrl+Shift+%	Apply percentage format
Alt+H+O+I	AutoFit column width

The one habit that changes everything

Use Ctrl+Shift+Arrow constantly. In a dataset of 50,000 rows, it selects the entire column of data in one keystroke. Combine it with Ctrl+Shift+End to select the full data range in two keystrokes. This alone makes routine tasks dramatically faster and signals to anyone watching that you know what you're doing.

VBA (Visual Basic for Applications) is not a skill every office job requires. But understanding it at a basic level — enough to record macros, read them, modify them slightly, and run them — is increasingly listed as a preference in more senior admin, finance, and operations roles.

Recording macros

The simplest entry point: View → Macros → Record Macro. Perform your repetitive action. Stop recording. Excel has written VBA code capturing every step. Run it again with one click or a keyboard shortcut.

This is valuable without ever writing a line of code yourself. The kind of task it solves: formatting a report every Monday morning, copying data between sheets in a specific pattern, applying a standard set of filters and then exporting.

Reading and modifying recorded code

Recorded macros are verbose and often inefficient, but they're readable. Opening the VBA editor (Alt+F11) and looking at recorded code teaches you the basic structure:

Sub FormatWeeklyReport()
    Range("A1:F1").Font.Bold = True
    Range("A1:F1").Interior.Color = RGB(68, 114, 196)
    Columns("A:F").AutoFit
End Sub

Even without learning VBA properly, being able to adjust a colour value, change a range reference, or add an AutoFit line is within reach after a few hours of exploration.

When VBA is genuinely necessary

Automating email sending from Excel (via Outlook), building custom user forms for data entry, creating functions that can't be expressed as worksheet formulas, or automating multi-step processes across multiple workbooks. These go beyond what macros alone can achieve and represent genuine advanced territory — but knowing they're possible, and approximately how, is itself valuable in a role that involves process improvement.

On a CV

The most common mistake is listing Excel as a single bullet point: "Proficient in Microsoft Excel." This tells a hiring manager nothing they can evaluate. What you want instead is specificity, context, and — wherever possible — a quantified outcome.

Instead of: "Proficient in Microsoft Excel"

Write: "Built automated weekly sales report using PivotTables and VLOOKUP, reducing manual preparation from 4 hours to 20 minutes"

Or: "Developed Power Query pipeline connecting five monthly data exports, eliminating manual data preparation step and reducing errors"

Or: "Created dynamic Excel dashboard with slicers and conditional formatting used by 12-person finance team for weekly KPI reviews"

Each of these tells the hiring manager what you built, how you built it, and what it achieved. That's the formula.

Matching the job description

Job descriptions often list specific Excel skills. Mirror their language precisely. If the posting says "advanced Excel including PivotTables and VLOOKUP", use those exact terms. Applicant tracking systems (ATS) scan for keyword matches, and humans reading shortlisted CVs will look for direct evidence that you have what they asked for.

If the job requires skills you have but haven't labelled clearly on your CV — for example, you've used Power Query extensively but haven't named it — add it. Many people have skills they've forgotten to claim.

In an interview

Excel skills are unusual among CV claims because they're immediately testable. Some roles include Excel assessments as part of the process. Even when they don't, an interviewer who knows Excel well will ask questions designed to probe depth:

• "Walk me through how you'd use VLOOKUP to match customer records from two different spreadsheets."
• "You've received a dataset with 50,000 rows and need to produce a regional breakdown by month. How do you approach it?"
• "What's the difference between a VLOOKUP and an INDEX-MATCH, and when would you use each?"

Prepare to answer these conversationally and concisely. The best answers describe a real situation where you used the skill, what you built, and what the outcome was.

If you're asked to do an Excel test, slow down, read the task carefully, and work methodically. Use the functions you know rather than reaching for unfamiliar ones. Clean, readable output — labelled clearly, formatted professionally — signals good judgement even if you haven't used the most elegant formula.

The portfolio approach

For roles with a significant analytical component — data analyst, finance business partner, operations manager — consider having a sample Excel workbook you've built that demonstrates your capabilities. A clean dashboard, an automated report, a Power Query pipeline. Offer to share it or walk through it. Few candidates do this, and it's memorable.

Level	Characteristic capabilities
Basic	Format cells, SUM/AVERAGE/COUNT, sort and filter, basic charts, simple IF statements
Intermediate	VLOOKUP/XLOOKUP, PivotTables, conditional formatting, data validation, nested IFs, chart formatting
Advanced	INDEX-MATCH, Power Query, dynamic arrays (FILTER/SORT/UNIQUE), named ranges, combo charts, intermediate VBA
Expert	Custom dashboards, Power BI integration, complex M language, advanced VBA automation, DAX for data modelling

Most office jobs require Intermediate. Finance, data, and operations roles increasingly expect Advanced. Expert-level skills position you for specialist analytical and BI roles.

Excel proficiency in 2026 isn't binary — it's a spectrum, and the distance between "I know Excel" and "I can demonstrate Excel at an advanced level" is measurable, learnable, and career-relevant. The skills covered here aren't abstract or academic. They're the ones that appear on job descriptions, come up in interviews, and get used on a Monday morning in real organisations.

The most effective approach is also the simplest: find real problems, apply real tools, build real things. The capability compounds. And in a market where most CVs list "proficient in Excel" without being able to back it up, genuine fluency is a genuine differentiator.