Excel can do far more than add up numbers. A handful of tricks saves you hours every week: splitting data automatically, building a summary in seconds or spotting errors instantly. Here are 10 Excel tips everyone should know, from beginner to advanced user.
1. Format as Table (Ctrl + T)
Select your data and press Ctrl + T. You instantly get filters, banded rows and formulas that fill down automatically as you add rows. Tables are also the foundation for PivotTables and Power Automate.
2. Flash Fill (Ctrl + E)
In a column next to your data, type one example, such as just the first name from John Peters. Press Ctrl + E and Excel fills in the rest using the same pattern.
3. XLOOKUP instead of VLOOKUP
XLOOKUP searches in any direction and doesn't break when you insert columns: =XLOOKUP(A2,Customers[Number],Customers[Name],"Not found").
4. Freeze Panes
Go to View > Freeze Panes > Freeze Top Row. Your column headers stay visible as you scroll down.
5. Conditional formatting
With Home > Conditional Formatting, automatically colour cells above a target, duplicate values or the top 10. Data Bars show the largest numbers at a glance.
6. PivotTables
Click in your table and choose Insert > PivotTable. Drag fields to Rows, Columns and Values and you have a summary per month, customer or product without a single formula.
7. Remove duplicates
Data > Remove Duplicates cleans a list in one click. Just want to see them? Use =UNIQUE() or conditional formatting.
8. Dynamic array formulas
Formulas like FILTER, SORT and UNIQUE return a whole list that grows automatically: =FILTER(Sales,Sales[Region]="Ghent").
9. Absolute references with F4
Click a cell reference in a formula and press F4. A1 becomes $A$1, so the reference doesn't shift when you copy the formula.
10. Navigate faster
- Ctrl + arrow key: jump to the end of your data.
- Ctrl + Shift + arrow key: select to the end.
- Ctrl + ;: today's date.
- Alt + =: AutoSum.
Bonus: Copilot in Excel
Have a Microsoft 365 Copilot licence? Ask Copilot to write a formula, highlight trends or build a chart. Even without Copilot, Home > Analyze Data suggests charts and summaries automatically.
Related: password-protect an Excel file and recover unsaved Excel files.
Read also
How to password protect an Excel file or remove the password
Recover an unsaved Word or Excel file
Create a Power App from a SharePoint list
Nederlandstalige versie: 10 Excel-tips die iedereen zou moeten kennen