Don’t Do This in Excel
There is no getting around it. Controlling is still very much about Excel. Sure, it’s just a tool — but a tool that to a large extent affects how we do things. You could say that Excel is already almost like a low-code tool. You can create formulas, the structure of tables hardly limits you, and when you miss something, you can program it yourself. Great interface, right?
Get Martin Saly’s stories in your inbox
Join Medium for free to get updates from this writer.
As they say in England — it depends.
With this freedom comes responsibility, because just because a program allows you to do something doesn’t mean it’s right. In today’s post, I would like to look at a few practices in data processing in Excel that you shouldn’t get used to.
- A Pivot Table as a data source I ask — why? Even if it makes a little sense: in the final formula, you save one function that the pivot table does for you. But is it worth it? A pivot table was always intended as a display tool. As an output, not a middleman.
- Colors — and even formats Sure, we mark a manually entered value in red. Grey for data that is no longer relevant. Yellow for some important change. On top of that, conditional formatting to color things automatically. And also borders — a lot and thick, because otherwise I won’t find it in two seconds.
In these cases, it might not be a bad idea to go the opposite way: instead of adding, let’s try to remove format. - Manual data insertion You know it. You download from SAP and then copy-paste into the report. So far, so good.
But you have formulas linked to the data — except this time they have a different number of rows, so you have to drag them down. Quite a few steps, right?
Try turning on Power Query. The formulas can easily stay there, but have the data at least as an Excel table.
Enough for today. Next time we will look at other proven practices. How about you? Do you do it similarly too? 🙂
This article was originally published on my LinkedIn profile.
