Whether you’re a beginner or have been using Microsoft Excel for years, chances are you’re only scratching the surface of what it can do. Excel isn’t just about rows and columns — it’s a powerhouse for productivity, data analysis, and time-saving automation.
Here are 10 surprisingly useful Excel tricks that even seasoned users often miss:
1. Flash Fill (Ctrl + E)
Want Excel to recognize patterns and fill data for you? Flash Fill is your best friend.
Just type a few examples, press Ctrl + E, and watch Excel do the rest — great for formatting names, dates, or extracting parts of text.
2. Quick Analysis Tool
Highlight a table and click the lightning bolt icon in the bottom-right corner. You’ll get instant options for:
- Charts
- Conditional formatting
- Totals
Perfect for fast visual insights without digging into the Ribbon.
3. Remove Duplicates in Seconds
Cleaning up messy data? Just go to:
Data > Remove Duplicates
Select the columns to check — Microsoft Excel will instantly filter out duplicate entries.
4. Paste Special – Multiply, Divide, Add, or Subtract
Need to increase prices by 10%?
Copy your percentage (e.g., 1.1), select your data range, right-click > Paste Special > choose Multiply.
No formulas needed!
5. IFERROR to Clean Up Formulas
Tired of ugly #DIV/0! or #N/A errors?
Wrap your formulas with =IFERROR(formula, "Custom Message").
Example:=IFERROR(A1/B1, "Check your data")
6. CTRL + Arrow Keys to Navigate Fast
Instead of scrolling forever, use:
- Ctrl + ↓ to jump to the bottom of your data
- Ctrl + Shift + → to highlight a full row of data
It’s a small shortcut with big time-saving impact.
7. Name Ranges to Make Formulas Easier
Highlight a cell or range > Go to Formulas > Define Name
Now, instead of =SUM(A1:A20) you can write =SUM(Sales) — much clearer and easier to manage.
8. Dynamic Drop-Down Lists
Use Data Validation to create drop-downs from a list (great for order forms or surveys).
Bonus: Link it to a named range so it updates automatically.
9. Ctrl + ; and Ctrl + Shift + : for Date & Time
Quickly insert the current date with Ctrl + ;
Insert the current time with Ctrl + Shift + :
Fast, simple, and great for logging tasks or timestamps.
10. Pivot Tables: Data’s Secret Weapon
They sound scary, but they’re not. PivotTables let you instantly summarize large data sets without formulas.
Go to Insert > PivotTable, select your data, and drag/drop fields. You’ll feel like a data wizard in minutes.
Ready to Master in Microsoft Excel?
These tricks are just the beginning. If you want personalized Excel training — whether you’re a beginner or looking to go advanced — check out our flexible 1-on-1 courses at MicrosoftCourses.au.
Weekend and online sessions available!