7 Excel Tricks Everyone Wishes They Learned Sooner
Excel tricks: Let’s switch things up today and talk about the tool we all spend countless hours in: Excel.
We all have our favorite tips and tricks, and if you’re a true nerd like me (it’s okay to admit it), you get excited when Excel releases new features and/or you get an opportunity to show your coworkers something new.
Today I’ll touch on 7 tricks/shortcuts/features that I definitely wish I knew sooner…
7 Excel Tricks Every Finance Professional Wishes They Learned Sooner1. INDEX/MATCH
Multi-directional lookup function that frees you from the constraints of VLOOKUP.
(Who’s going to shout “XLOOKUP!”?)
The real beauty of this function combo comes with you use MATCH twice, like this:
=INDEX([where I want to look],MATCH([row]),MATCH([column]))
This effectively allows you to find data across an entire worksheet in your model without having to worry about changing ranges. I find this extremely helpful when I want to extract specific information from a more detailed Three Statement Model.
Btw, VLOOKUP is perfectly fine in a lot of situations, but INDEX/MATCH (and fine, XLOOKUP) is much more scalable.
2. USING TABLES
Formulas update automatically and everything sits in a named range. A much safer way to organize tabular data.
In truth I don’t use them as much as I should, but I’m starting to integrate them more (at least for smaller data sets).
I find they get buggy/laggy if you have too much data, so I still prefer building “tables” in a traditional range and applying filters, but that’s just me.
Tables will become commonplace if you’re a PowerQuery user, so it’s important to at least understand them.
3. DYNAMIC ARRAYS
UNIQUE, FILTER, and SORT are some of the most powerful functions you can use for data analysis.
I’d say Dynamic Arrays are one of the biggest game changers to Excel in the last decade.
My current use cases right now:
- Weekly flash reports where I want things to update automatically based on new data
- Filtering new bank data to update journal entries
- Filtering time worked by client, by month, to create invoices
- Filtering hours worked by client, by day, to enter hours
There are a million others here that I’m not listing.
Only thing to watch for: they require some extra computing power, so as you start to reference larger data sets they can slow things down. That said, I still think the benefits definitely outweigh the costs.
Big picture, if you’re not using Dynamic Arrays yet, I suggest you start.
4. CAMERA TOOL
The first hidden gem of this list: you can “watch” other parts of your model without needing to build direct links.
So when it comes to Financial Modeling, I find the real benefit is it allows you to make dashboards without being confined to cell dimensions on the current sheet.
This makes everything much more print-friendly and easy to organize (yes, I still print stuff from time to time).
The reason it’s a “hidden gem” is because you need to customize your Quick Access Toolbar to find it.
5. CTRL + [
An OG Shortcut I’ve been using for nearly two decades.
Quickly trace precedent cells to see where data comes from.
🦸♂️ Superpower: Combine with F5+Enter to jump back-and-forth.
This makes your model considerably easier to navigate and audit, which is so important as your model grows in complexity.
I probably use this shortcut combo more than anything else.
6. GO TO SPECIAL
The other “hidden gem” of this list: select only constants, blanks, or other unique parts of your data.
Blanks is a great one because you can quickly fill in holes within your data.
However, I use this most often to “select constants” and then format them blue: A cornerstone formatting convention when building Financial Models.
7. CTRL + ENTER
This allows you to complete the same action in multiple cells at the same time.
So you can update formulas at scale and simplify bulk operations.
I’ll use it to carry a formula through multiple rows or columns at once.
Like I mentioned above: when combined with Go To Special, this works great for filling in blanks in a list.
Can You Stop by my Office?
I love Excel and all the amazing things it can do, but it’s time for this reminder us modelers often forget:
⏰Reminder: Your boss doesn’t care what function you use or how many Excel shortcuts you know. Your boss wants to know, “does the Financial Model give us enough information to figure out what to do next with our company?”
So I’d say get the job done first, by whatever means necessary, and optimize with Excel tricks later.
(Just don’t skimp on error checking)
That’s it for today. See you next time.
— Chris
p.s., if you enjoyed this post, then please consider checking out my Financial Modeling Courses. As featured by Wharton Online, Wall Street Prep, and LinkedIn Learning, you'll learn to build the exact models I use with Private Equity and FP&A teams around the world. 👉 Click here to learn more.