How do I number rows in Excel with the fill handle?
This is the fastest method for a one-off list, per Microsoft's Automatically number rows support page:
- Type 1 in the first cell of the range you want to number, and 2 in the cell directly below it.
- Select both cells.
- Drag the small square fill handle in the bottom-right corner of the selection down the column, as far as you need the numbering to go.
- Excel detects the pattern from your two seed numbers and continues it — release the mouse and the whole range fills in.
Dragging down or right fills in increasing order; dragging up or left fills in decreasing order. The one thing to know: these are plain values, not a live formula, so Microsoft's guidance notes they are not automatically updated when you later add, move, or remove rows — you'd need to redo the fill, or turn the range into an Excel table, which numbers new rows added at the end automatically.
How do I number rows so they stay correct after sorting or filtering?
For a list you plan to sort or reorder, a formula holds up better than typed values. Microsoft's ROW function returns the row number of whatever cell you point it at, so typing =ROW(A1) in the first data row and filling it down the column gives you 1, 2, 3, and so on, recalculating live. Sorting the data re-sorts the formula along with each row, and since ROW() recalculates based on each row's new position, the sequence stays intact — unlike static typed numbers, which travel with their row and end up out of order after a sort. Filtering is a separate case: filtered rows are hidden rather than moved, so a straight ROW() count will show gaps for any hidden rows rather than renumbering around them.
What is the syntax for XLOOKUP in Excel?
Per Microsoft's XLOOKUP function reference, the full syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Only the first three arguments are required. lookup_value is what you're searching for, lookup_array is the range to search in, and return_array is the range to pull the matching result from — it does not have to be positioned to the right of the lookup range. The optional if_not_found argument lets you supply your own text or value to show instead of the default #N/A error when nothing matches, which is useful for keeping reports free of error codes.
How does XLOOKUP improve on VLOOKUP?
Three practical differences stand out. First, XLOOKUP can search in either direction — the return range can sit to the left or right of the lookup range, whereas VLOOKUP only ever looks to the right. Second, XLOOKUP takes an actual return-range reference instead of a counted column-index number, so inserting or deleting a column between the lookup and return ranges doesn't silently break the formula the way it can with VLOOKUP's column-index approach. Third, XLOOKUP has that built-in if_not_found argument, so you don't need to wrap it in a separate IFERROR() just to hide a missing-match error.
A worked XLOOKUP example
Say column A lists staff ID numbers, and column B lists each staff member's name, running from row 2 to row 50 (row 1 is the header). To find the name that matches ID "EMP014" typed into cell D2, the formula in E2 would be:
=XLOOKUP(D2, A2:A50, B2:B50, "ID not found")
Excel searches column A for the value in D2, and once it finds the matching row, returns the corresponding name from column B in that same row. If no ID matches, the fourth argument means the cell shows "ID not found" instead of #N/A. The same formula works even if the ID column were placed to the right of the name column — you would simply point lookup_array and return_array at the correct ranges either way, without recounting columns.
What Excel version do I need for XLOOKUP?
XLOOKUP requires a current version of Excel: Microsoft 365, Excel 2021, or Excel for the web. It is not available in Excel 2019, Excel 2016, or older standalone versions of Excel, and a workbook using XLOOKUP will show an error in those versions instead of a result. If you're not sure which version you or a colleague has, checking whether XLOOKUP resolves at all in a test cell is a quick way to confirm — if it isn't recognised, VLOOKUP or INDEX/MATCH remain the fallback options on older installs.
People also ask
Why do my row numbers change when I sort or filter in Excel?
Fill-handle numbers are static values and don't move correctly with a sort. The ROW() formula recalculates and stays in sequence after sorting, though filtering still leaves gaps for hidden rows.
What is the syntax for XLOOKUP in Excel?
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) — only the first three arguments are required.
What Excel version do I need for XLOOKUP?
Microsoft 365, Excel 2021 or later, or Excel for the web. It is not available in Excel 2019 or earlier.
How is XLOOKUP better than VLOOKUP?
It searches left or right, uses a direct return-range reference instead of a counted column index, and has a built-in if_not_found argument to replace #N/A errors.