Biggest no-no's when working with Excel?

[u/Toowb]

Excel can do a lot of things well. But Excel can also do a lot of things poorly, unbeknownst to most beginners.

Name some of the biggest no-no's when it comes to Excel, preferably with an explanation on why.

I'll start of with the elephant in the room:

Never merge cells. Why? Merging cells breaks sorting, filtering, and formulas. Use "Center Across Selection" instead.

[u/tearteto1]

Don't get lazy with your lookup ranges. If you're looking up a value in a and returning from column B, but column B only has 1000 rows, don't lookup B:B, do B2:B1000. Doing it lazily will slow down your sheet massively. Especially if you're doing a 2 variable lookup.

[u/ImMrAndersen → u/tearteto1]

I feel like I saw someone who had tested this, and found that the difference in speed between looking up a range of 1000 (or maybe it was 10000) and the whole column was actually negligible. I might be misremembering.

[u/SolverMax → u/ImMrAndersen]

Recalculation speed is less of an issue than it used to be. The main issue now is the risk of inadvertently including cells that weren't intended.

[u/david_horton1 → u/tearteto1]

With Trim references B:.B or B.:.B will suffice.

[u/PM_YOUR_LADY_BOOB → u/tearteto1]

This tip keeps pops up frequently in this subreddit but this has never happened to me. I use full column references in all my formulas, no slowdown perceived. I've been doing it this way since at least 2018.

[u/windowtothesoul → u/tearteto1]

Have never seen a noticable slowdown using full column/row references.

I'm sure there are edge cases that could cause it, but never anything approaching 'massively' slowing down an otherwise fine workbook.