PRE Scriptum: Share your favorite Excel tips in the comments
I watched my experienced finance colleague struggle with Excel for a whole hour. He's an Excel guru, has been working with it for 20 years. But when I saw how he was typing formulas, I couldn't help but show him a couple of simple tricks that blew his mind.
It was a problem of ignorance—ignorance of features that have been in Excel for years, saving hours of work, but which no one ever talks about.
That's why I'm writing this article, to gather the best life hacks and save precious seconds of your life.
The Competence Paradox
Here's the surprising thing: the longer someone works in Excel, the less likely they are to learn new features. If your method has worked for 20 years, why change it? You're an expert. Your formulas are complex, but reliable.
The problem is that over those 20 years, Excel has changed radically. Functions that required nested IFs spanning three screens can now be solved with a single line. Tasks that took an hour are now done in a minute.
But Microsoft doesn't send out notifications about new features. YouTube is full of tutorials, but you're not looking for a solution to a problem you've already solved your own way. But you will learn about them in this article, which you stumbled upon by chance.
Most Excel experts work less efficiently than properly trained interns could.
Here, test yourself:
Do you know what Ctrl + E does?
Do you use F4 when writing formulas?
Have you heard of the Camera Tool?
If the answer to even one of these is 'no,' you're leaving hours of productivity on the table every week.
Flash Fill: The Machine Learning No One Knows About
Imagine this task: you have 5000 rows of emails like 'ivan.petrov@company.ru', and you need to extract the names and companies separately.
The traditional approach (used by 95% of people):
=ЛЕВСИМВ(A2;НАЙТИ(".";A2)-1)
Another formula for the last name. A third for the domain. If the structure changes slightly (someone included a middle name, someone has a hyphen in their last name), you have to redo everything.
The approach of someone who knows about Flash Fill:
In the adjacent cell, you manually type 'ivan' (for the first email)
Press Ctrl + E
That's it
Excel instantly understands the pattern and fills all 5000 rows.
This isn't a formula. It's machine learning, built into Excel since 2013. But if you don't know about Ctrl + E, this technology simply doesn't exist for you.
F4: Two Seconds That Add Up to Weeks
I watch people type a formula with an absolute reference:
=A1*$B$1
They type it character by character: equals, A, 1, asterisk, Shift+4, B, Shift+4, 1.
'Do you know about F4?'
The F4 key, when editing a formula, toggles the reference type:
A1 → $A1 → A$1 → $A$1 → back to A1
One keystroke instead of four characters. Quick and convenient. Sound like a small thing?
But it's not about the keystrokes. It's about the fact that when you type manually, you sometimes make mistakes. You forget the dollar sign. You put it in the wrong place. The formula gets copied, the results are wrong, and you spend tens of minutes searching for the error.
F4 eliminates this entire class of problems. With one button.
Camera Tool: The Secret Weapon for Dashboards
How many times have you created a 'beautiful report' in Excel by copying tables and charts from different sheets? And how many times did you have to redo everything when the data was updated?
There's a feature that solves this completely: the Camera Tool.
This isn't a screenshot. It's a live link to the source data. You 'photograph' any range of cells, and this photograph:
Updates automatically when the data changes
Can be scaled like an image
Can be placed anywhere
Can be layered on top of other elements
For dashboards, this is a revolution. Instead of complex formulas and links between sheets, you simply assemble 'photographs' of the necessary tables on a single sheet like building blocks.
The problem: The Camera Tool isn't on the ribbon, and nobody knows about it.
To find it, you need to:
1. Know that it exists (you know now)
2. Go to File → Options → Customize Ribbon
3. In the dropdown list, select 'All Commands'
4. Find 'Camera' in the alphabetical list
5. Add it to the Quick Access Toolbar
It's easier to Google it and watch a one-minute video :-)
Your Minimum Survival Kit
Here are the features you should start using right now. Not tomorrow. Now.
1. Alt + =
Instantly sums the selected cells. Forget about typing =СУММ(.
2. Ctrl + T
Turns a data range into a 'smart table' with auto-filters, formatting, and structured references. After this, instead of =A2*B2 you write =[@Цена]*[@Количество]. Readable. Understandable. Doesn't break when you insert rows.
3. Ctrl + E
Flash Fill. You show it a pattern—Excel continues it.
4. F4
Toggling reference types in formulas.
5. Alt + ;
Select only visible cells. A lifesaver when copying filtered data (otherwise, hidden data gets copied too).
6. Double-click the fill handle
Instead of dragging a formula down 5000 rows, double-click the small square in the bottom-right corner of the cell. Excel will automatically fill it down to the end of the data.
7. Ctrl + Arrow
Instantly jump to the edge of a data range. Navigate large tables without scrolling.
8. Ctrl + F3
Name Manager. Instead of using A1:A100 in all your formulas, you name the range 'Sales' and use that. Formulas become readable: =СУММ(Продажи) instead of =СУММ(1:100).
Each of these features saves seconds. But seconds add up to minutes, minutes to hours, and hours to days.
Why You Won't Change Your Habits
You'll read about Ctrl + E, think 'I should remember that,' close the tab, and continue working as you always have.
Because changing habits requires effort. And your current way of working, no matter how inefficient, works.
This is called personal technical debt. You accumulate inefficient practices, and over time, the cost of fixing them seems higher than the benefit.
But here's the thing: that cost is growing. Every day you lose a little more time. Every week, the gap between you and those who work efficiently widens.
At some point, a young analyst who knows about dynamic arrays will come along and do in an hour what takes you a day.
What to Do Right Now
Don't try to learn everything at once. Start with one feature. Today.
The challenge: Open Excel right now. Find a formula with an absolute reference. Click on the reference in the formula bar. Press F4. See how it changes.
That's it. Nothing more is needed.
Tomorrow, try Flash Fill.
The day after tomorrow—Ctrl + T on a data range.
In a week, these features will become automatic. In a month, you'll wonder how you ever worked without them.
Share in the comments what cool tricks you use in Excel. Let's gather the best life hacks in one place.
If after reading this article you opened Excel and pressed F4 in a formula, my work is done. If you tried Flash Fill, I'm happy. If you opened Name Manager (Ctrl + F3) and named a range 'Sales' instead of using A1:A100, my day has not been wasted :-)
Upd. from the comments, the gurus added more:
F12 — the classic 'Save As...' dialog box (without the backstage view)
Tab — autocomplete the function name while typing
Ctrl + ; (semicolon) — insert the current date
Ctrl + Shift + ; — insert the current time
F2 — toggle the arrow key mode: move through characters in the formula ↔ move between cells on the sheet. A lifesaver when editing complex formulas without a mouse.
F9 — calculate the selected part of a formula directly while editing (for debugging)
Ctrl + [ and Ctrl + ] — go to precedent/dependent cells (even in other workbooks)