Seven Excel mistakes to avoid

Want to master Excel?

Then STOP doing these 7 things:

#1 Spending more than 15 minutes on ANYTHING

It’s a mistake not to use Excel’s numerous ways to automate your work. But knowing where to start learning about advanced features is tough. I use the 15-minute rule. Whenever I do one thing in Excel that takes more than 15 minutes, I stop and Google if there is a faster way. Often, there is a formula I didn’t know about that does the trick.

#2 Copy / pasting

The best way to ensure you make a mistake in Excel is by copy/pasting. The issue is that this process is (seemingly) so simple that our brains stop paying attention. But often, that’s where mistakes creep in. So always use a formula or a macro to reference data elsewhere in Excel.

#3 Manually turning one column into two

Data doesn’t always come neatly formatted in the way you need it. Sometimes, you have, say, account number and account name in the same column. To separate the two data points into separate columns, you should NEVER do this manually. Google how to combine LEFT(), RIGHT(), FIND(), and LEN() formulas to automate this.

#4 Vlookup

Whenever I see someone use Vlookup to do anything, I cringe and coach them on how to use Index / Match. Vlookup is terrible because it invites mistakes. Conversely, Index/Match dynamically adjusts to changes in the source table and is fully transparent about what it does, which avoids mistakes.

You may wonder if the new Xlookup formula is any better. It is, but it’s still inferior compared to Index / Match. I recently wrote an in-depth comparison about it. The link is in the comments.

#5 Merge & Center

If you merge and center cells in Excel, you cause multiple features to stop working. Referencing cells, filters, or simply highlighting a column may become impossible and cause frustration.

Instead, use “Center Across Selection” (CTR +1 → Alignment —> Horizontal drop-down). It looks the same but avoids all issues.

#6 Formulas and hard-coded cells on the same tab

Did you ever override a formula with a hard-coded cell because you were in a hurry? Later, you may discover your model doesn’t make sense, and you don’t know why. To avoid that, keep all your formulas on one tab (”Calculations”) and all your hard-coded cells on another (”Inputs”). That way, it’s fast to double-check everything still works as intended.

#7 Clicking on buttons

Last but not least, to truly level up to Excel mastery, you should stop using your mouse. Every button and menu item in Excel is accessible via keyboard shortcuts. The best thing about it is that Microsoft did a great job organizing shortcuts into a system that is easy to learn and remember. My absolute favorite shortcut is F4. In addition to turning a dynamic cell reference into a static one, it repeats whatever formatting you just applied. Like magic.

If you’d like to learn more about FP&A, I offer help in three ways:

1️⃣ Subscribe to my free newsletter “FP&A Tuesday” here.

2️⃣ Listen to 75 bite-sized mini lessons and get FP&A advice on the go.

3️⃣ Join my live online course FP&A Bootcamp to master FP&A in two weeks.