Excel That Actually Makes You Faster
This article will count 0.25 units (15 minutes) of unverifiable CPD. Remember to log these units under your membership profile.
Smart tricks for the middle-level user
Most people learn Excel the slow way. They click around, drag cells, and copy things by hand. That works, but it eats up time. Once you know a few smart tricks, the same job that took 30 minutes can take 5. This article shows you tricks that sit between basic and advanced. They are not hard to learn, but they will save you hours every week.
1. Flash Fill: Let Excel Read Your Mind
Flash Fill is one of the best tools many people miss. It looks at what you are typing and guesses the pattern, then fills the rest for you.
Say you have a list of full names in column A, like “John Smith”. You want just the first names in column B. Type “John” in the first cell of column B. Then press Ctrl + E. Excel fills the whole column with first names from every row.
It works for many things: splitting names, joining text, fixing capital letters, pulling numbers out of mixed text, or making email addresses from names. As long as you give Excel one or two clear examples, it figures out the rest. No formulas needed.
2. Freeze Panes: Stop Losing Your Headers
When you scroll down a long sheet, the top row with your column titles disappears. That makes it hard to know what each column means.
To fix this, click the View tab, then click Freeze Panes, and pick “Freeze Top Row”. Now your header stays in place no matter how far you scroll. You can also freeze the first column the same way, or freeze both at once by clicking a cell first and choosing “Freeze Panes”.
3. Tables: The Best Trick Nobody Talks About
Most people just type data into cells. But if you turn that data into an Excel Table, magic happens. Click anywhere in your data and press Ctrl + T. Make sure “My table has headers” is ticked, then click OK.
Now your data is a Table, and you get many free benefits:
• Filter buttons appear on every column.
• New rows you add are styled and pulled into formulas right away.
• Formulas use names like [@Price] instead of cell numbers, which is easier to read.
• Charts and PivotTables grow with your data on their own.
Once you start using Tables, you will not go back to plain ranges.
4. XLOOKUP: The Better Way to Find Things
You may have heard of VLOOKUP. It looks up a value in one column and pulls data from another. It works, but it is fussy. XLOOKUP is the new and better version.
The pattern is simple: =XLOOKUP(what you want to find, where to look, what to return).
For example, if you have a list of products in column A and prices in column B, and you want to find the price of “Apple”, you type: =XLOOKUP(“Apple”, A:A, B:B). It will give you the price.
XLOOKUP works left to right, right to left, top to bottom, anywhere. It also lets you set a default if nothing is found, like “Not in stock”, by adding a fourth part: =XLOOKUP(“Apple”, A:A, B:B, “Not in stock”).
5. Conditional Formatting: Make Numbers Talk
A wall of numbers is hard to read. Conditional Formatting paints cells based on rules, so important things stand out.
Pick the cells you want to style. Go to the Home tab and click Conditional Formatting. You can:
• Show all sales above 1,000 in green.
• Mark late dates in red.
• Add color bars that grow with the value.
• Highlight duplicate names in a list.
Your eyes will spot trouble in seconds without reading every number.
6. Paste Special: More Than Just Paste
When you copy a cell and paste it somewhere new, Excel pastes everything: the formula, the format, the color. Often you only want one part. That is what Paste Special is for.
Copy your cell, then press Ctrl + Alt + V. A box opens with options:
• Values: paste only the numbers, not the formula. Great for freezing results.
• Formats: paste only the colors and fonts.
• Transpose: turn rows into columns and columns into rows.
The Transpose trick alone is worth learning. No more typing the same data sideways by hand.
7. Keyboard Shortcuts That Save Real Time
Mouse clicks slow you down. Here are a few shortcuts that pay back fast:
• Ctrl + Arrow keys: jump to the edge of your data.
• Ctrl + Shift + Arrow keys: pick everything from here to the edge.
• Ctrl + ; : add today's date to a cell.
• Alt + = : insert AutoSum.
• Ctrl + D : copy the cell above into the cell below.
• F4 : repeat your last action, or lock a cell in a formula (like $A$1).
Pick two or three of these and use them every day for a week. They will become normal fast.
8. Remove Duplicates in One Click
Long lists often have repeated rows. To clean them, pick your data, go to the Data tab, and click Remove Duplicates. Tick the columns to check, click OK, and Excel deletes the extras. It even tells you how many it removed.
This used to take a long formula. Now it is one button.
9. Drop-Down Lists: Stop Typos
If you fill in the same words a lot, like “Yes”, “No”, or city names, make a drop-down. Pick the cells, go to the Data tab, and click Data Validation. Choose “List” and type your options, like Yes,No,Maybe.
Now those cells show a small arrow. Click it and pick from the list. No more typos, no more cleaning up later.
Final Tip: Practice One Trick at a Time
Do not try all of these at once. Pick one trick from this list and use it on your real work this week. Once it feels easy, pick the next one. In a month or two, you will be the person at work who finishes Excel jobs in half the time, and people will start asking how you do it.
The truth is, you do not need to be an Excel master to be fast. You just need to know the right small tricks and use them often. Start today, and your next spreadsheet will feel a lot lighter.
