Most people don’t lose hours in Excel because Excel is hard. They lose them to small habits nobody ever told them to drop. Here are 12 of them, why each one hurts, and the faster way to do it instead. Every fix below is shown step by step in the video above.
Download the free practice file: it has one sheet per habit with made-up data, so you can try each fix as you read.
1. Stop scrolling to find the end of your data
Dragging the scroll bar through 10,000 rows is slow, and it’s easy to overshoot. Instead:
- Ctrl + Down Arrow jumps to the last filled cell in the column; Ctrl + Right Arrow jumps to the last column.
- Add Shift to select everything on the way: Ctrl + Shift + Down selects the whole column of data.
- It stops at the first blank cell, so it also shows you gaps in your data.
- Lost? Ctrl + Backspace brings you back to the active cell.
2. Stop merging cells for your titles
Merge and Center looks tidy but breaks sorting and column selection. Unmerge, select the title across the columns, press Ctrl + 1, and on the Alignment tab set Horizontal to Center Across Selection. It looks identical, and every cell stays a normal cell.
3. Stop working in plain ranges
Click anywhere in your data and press Ctrl + T to make it a table. New rows are included automatically, a formula typed in one cell fills the whole column, and formulas read like English, for example =SUM(Sales[Amount]). Charts grow with the table, and pivot tables pick up new rows on refresh.
4. Stop typing to split or clean up text
Type the result you want for the first row (say, the first name from a full name), move to the next cell and press Ctrl + E. Flash Fill spots the pattern and fills the column. It works for last names, initials, codes and phone formats. Flash Fill doesn’t update when the source changes, so use a formula for data that keeps changing.
5. Stop repeating the same formatting clicks
F4 repeats your last action. Format one block, select the next and press F4. It works for fills, borders, inserting rows and deleting columns. On some laptops you need to hold Fn as well. Inside a formula, F4 does something different: it adds dollar signs to lock a cell reference.
6. Stop using VLOOKUP
VLOOKUP needs a column number (which breaks when someone inserts a column), can’t look left, and silently uses an approximate match if you skip the last argument. XLOOKUP fixes all three:
=XLOOKUP(F2, B:B, C:C, "Not found")
| Argument | Meaning |
|---|---|
| F2 | What you’re looking for |
| B:B | Where to search |
| C:C | What to return |
| “Not found” | Message instead of #N/A |
It’s an exact match by default and works in any direction. XLOOKUP needs Microsoft 365 or Excel 2021 or later.
7. Stop copying data between sheets every month
Put all the monthly files in one folder. Go to Data > Get Data > From File > From Folder, pick the folder and choose Combine & Transform. Power Query stacks every file into one table; remove unneeded columns, set data types, then Close & Load. Next month, drop the new file in the folder and click Refresh All. The files need the same columns for this to work.
8. Stop copy-pasting filtered rows to a new sheet
A pasted report goes out of date the moment the data changes. FILTER keeps it live:
=FILTER(Sales, Sales[Amount]>1000, "No matches")
Combine conditions with * for AND and + for OR, for example (Sales[Amount]>1000)*(Sales[Region]="North").
9. Stop removing duplicates by hand
A list made with Remove Duplicates is frozen. =SORT(UNIQUE(Sales[Customer])) gives every customer once, alphabetically, and updates when new customers appear. Point a dropdown at it and the dropdown stays current too.
10. Stop squinting at long formulas
Press Ctrl + Shift + U to expand the formula bar and Alt + Enter to put each part on its own line. Then use LET to name a repeated calculation so Excel works it out once:
=LET(total, SUM(Sales[Amount]), IF(total>Target, Bonus, 0))
11. Stop sending raw pivot tables
- Design tab > Report Layout > Show in Tabular Form, so each field gets a real header.
- Rename “Sum of Amount” to something clear like Total Sales.
- Value Field Settings > Number Format, so commas survive a refresh.
- Insert a slicer so anyone can filter by region in one click.
12. Stop searching the web for formulas
If your Microsoft 365 plan includes Copilot, describe what you want in plain words (for example, “total sales by region for last quarter”) and Copilot suggests the formula or summary. Always check the result against a few rows you can verify yourself.
Quick recap
| Stop doing this | Do this instead |
|---|---|
| Scrolling to the end | Ctrl + Arrow keys |
| Merging title cells | Center Across Selection |
| Plain ranges | Ctrl + T tables |
| Typing to split text | Flash Fill (Ctrl + E) |
| Repeating clicks | F4 |
| VLOOKUP | XLOOKUP |
| Monthly copy-paste | Power Query |
| Pasting filtered rows | FILTER |
| Removing duplicates by hand | SORT + UNIQUE |
| Long unreadable formulas | LET |
| Raw pivot tables | Tabular layout, formats, slicers |
| Googling formulas | Copilot (if your plan has it) |
XLOOKUP, FILTER, SORT, UNIQUE and LET need Microsoft 365 or Excel 2021 or later. Copilot needs a Microsoft 365 plan that includes it.
Sources
- Microsoft Support: XLOOKUP function
- Microsoft Support: FILTER function
- Microsoft Support: UNIQUE function
- Microsoft Support: LET function
- Microsoft Support: Using Flash Fill
- Microsoft Support: Import data from a folder (Power Query)
- Microsoft Support: What’s new in Excel 2021
Found this useful? Subscribe at YouTube @findthinklearn for more clear, practical explainers.