Most advice about Excel skills is a list of functions, which is why so much of it fails to stick. The techniques that change how quickly you finish work are structural: laying data out so formulas do not need to be clever, letting the application manage ranges for you, summarising without writing anything, and turning a monthly cleanup into something that refreshes. This guide covers those, with the behaviour taken from Microsoft’s own documentation so you can check every claim and find the exact menu item.
Updated October 2026.
Structure first: the Excel skills that pay for themselves
A spreadsheet that is hard to work with is almost always laid out badly rather than missing a formula. The target is one flat table: one row per record, one column per field, a single header row, no blank rows and no merged cells. Microsoft states this requirement directly for PivotTables, advising that your data should be organised in columns with a single header row before you start.
You have more room than you think. A worksheet holds 1,048,576 rows by 16,384 columns, a single cell can contain up to 32,767 characters, and in 64-bit Excel workbook size is limited only by available memory and system resources, while a 32-bit installation shares a 2 GB address space in which the data model alone can take 500 to 700 MB. The practical point is that splitting data across sheets to keep things tidy almost never helps. One long table is easier for every tool in the application to work with.
Format as Table
Turning a range into a table is the highest return single action in Excel. Microsoft documents the route as Home then Format as Table, and lists Ctrl+L or Ctrl+T as the shortcut that displays the Create Table dialog. What you gain is not cosmetic. Formulas can use structured references that reference table names rather than cell addresses. Entering a formula in one cell creates a calculated column, and that formula is instantly applied to all other cells in the column. A total row gives you an AutoSum dropdown offering functions such as SUM and AVERAGE, converting your choice into a SUBTOTAL function so it respects filtering.
The reason this matters is maintenance. Ranges that grow break formulas that reference fixed addresses. A table grows with the data, and everything pointing at it follows.
XLOOKUP instead of VLOOKUP
XLOOKUP takes a lookup value, a lookup array and a return array, then three optional arguments that remove most of the frustration of its predecessor. The if_not_found argument returns text you supply where no valid match exists, instead of leaving an error on the sheet. match_mode accepts 0 for an exact match, which is the default, -1 to fall back to the next smaller item, 1 for the next larger and 2 for a wildcard match. search_mode accepts 1 to search forward from the first item, -1 to search in reverse from the last, and 2 or -2 for a binary search on sorted data.
Two differences matter in daily use. XLOOKUP searches regardless of which side the return column sits on, so you are no longer rearranging columns to satisfy a left to right rule, and it can return an array with multiple items. One caveat before you standardise on it: Microsoft lists availability in Excel for Microsoft 365, Excel 2024, Excel 2021 and the mobile apps, and states that it is not available in Excel 2016 and Excel 2019. If colleagues are on those versions, your file will not work for them.
PivotTables and Power Query
A PivotTable is, in Microsoft’s words, “a powerful tool to calculate, summarize, and analyze data”. You select your cells, choose Insert then PivotTable, pick a new or existing worksheet and confirm. The reason to learn it is that it replaces the work of writing many conditional sum formulas with dragging fields, and it rebuilds itself when the question changes.
Power Query is the other half of the answer and the one most people skip. Microsoft describes it as a data transformation and data preparation engine with a graphical interface for getting data and an editor for applying transformations, covering extract, transform and load work. The decisive feature is repeatability: it records your transformations as query steps and saves them as a query you can refresh, rather than leaving you to redo the same cleanup by hand each month. It does not modify the source data, it offers over 350 types of data transformation, and it writes the M code for each step so you can inspect or extend it in the Advanced Editor. Of all the Excel skills here, this is the one that compounds, because a task you automate once stays automated.
7 simple wins worth learning
- Ctrl+T. Displays the Create Table dialog, which is the gateway to structured references, calculated columns and a total row.
- Ctrl+Arrow key. Moves to the edge of the current data region, which is how you navigate a long sheet without scrolling.
- Ctrl+Shift+L. Toggles AutoFilter on and off, so you can interrogate a column without building anything.
- Ctrl+D. Fill Down copies the contents and formatting of the cell above into the selection.
- Ctrl+Alt+V. Opens Paste Special, which is how you paste values instead of carrying formulas and formatting into a clean sheet.
- Alt and the equals sign. Inserts the Sum formula, the one calculation you type most often.
- F4. Cycles through absolute and relative reference combinations while a reference is selected in a formula.
The habit that prevents the worst mistakes
Keep raw data, calculations and presentation separate, and never type over an imported figure. If a number needs correcting, correct it in a step you can see, which is exactly what Power Query records for you. Then check the result against something you already know before you circulate it. Date arithmetic is where this discipline pays off most often, and we cover it in detail in our guides to calculating age in Excel, days between dates and working days between dates. For a worked financial model, our amortisation schedule walkthrough puts these habits together.
Common questions
Which Excel skills actually matter at work? Laying data out as one flat table with a single header row, converting it to a table so references and formulas maintain themselves, summarising with PivotTables, looking values up with XLOOKUP, and automating repeat cleanup with Power Query.
Should I still use VLOOKUP? Only if you have to. XLOOKUP searches regardless of which side the return column is on, can return multiple items and handles missing matches through if_not_found. Microsoft states it is not available in Excel 2016 and Excel 2019.
What is the shortcut to create a table? Ctrl+L or Ctrl+T displays the Create Table dialog. The menu route is Home then Format as Table.
How big can an Excel worksheet get? 1,048,576 rows by 16,384 columns, with up to 32,767 characters in a single cell. In 64-bit Excel workbook size is limited only by available memory and system resources.
Is Power Query worth learning if I only use Excel? Yes, if any task repeats. It saves your transformation steps as a query you can refresh rather than redoing the cleanup, offers over 350 transformations and leaves the source data unchanged.
Sources and further reading
Where the figures and rules above come from, so you can check them:
- Excel tables: structured references, calculated columns and the total row: Microsoft Support
- XLOOKUP syntax, match_mode, search_mode and version availability: Microsoft Support
- Creating a PivotTable and the single header row requirement: Microsoft Support
- Keyboard shortcuts including Ctrl+T, Ctrl+Shift+L, Ctrl+Alt+V and F4: Microsoft Support
- Worksheet row, column and character limits and workbook size: Microsoft Support
- Power Query as a repeatable transformation engine and the M language: Microsoft Learn
Photo credit: Lab-notebook-spreadsheet-simulation by MikeRun, CC BY-SA 4.0, via Wikimedia Commons.
Join the discussion