Don’t Do This in Excel — Part 2

Pivot tables, formatting, and manual data input. These were the practices I slightly criticized in the previous post. And honestly, that was only the beginning of a fairly long list of things it’s better to avoid in Excel. Without further ado — let’s continue.

1. Pasting data into areas with formulas A similar topic to last time. You download data from SAP, copy it, paste it into your report, and possibly extend the formulas. You might think it can’t get any more complicated. But it can — especially when you paste the data in multiple steps.

Quite often there’s a column with formulas somewhere in the middle of the dataset. In that case, you need to paste values separately before and after that column. It can be partially justified, for example because of column order (important columns at the beginning), but the risk of errors simply increases.

The advice is simple: if you use formulas, keep them in one consistent area — ideally in a table.

2. Solutions based on fixed column positions

We don’t need to look far. A VLOOKUP that searches for a value in the fifth column based on the first one is enough. Read that again. The fifth column. Based on the first.

That’s quite fragile. When the data structure changes even slightly, the formula will still return a result. And that’s the bigger problem — it returns a value even when it no longer makes sense. The function doesn’t care what the column represents, only where it is.

A better approach? VLOOKUP combined with MATCH, or even better, XLOOKUP used together with tables.

That’s enough for today. Hopefully, I didn’t make you hate Excel too much. Next time, we’ll look at a few more practices that are better left out.