I’ve spent most of my career working with data, but not in the way people often expect. It hasn’t been about complex systems or highly technical tools. It’s been about taking whatever information is available, often messy and inconsistent, and turning it into something useful. Across banking, retail, and property, the common thread has always been the same. If you can understand your data, you can make better decisions. If you can’t, you’re often guessing.
Watch the whole recording here.
One of the questions I get asked a lot is why we still spend time in Excel when there are more advanced tools available. The honest answer is that Excel is still running a lot of businesses. Not everyone has access to more sophisticated platforms, and even when they do, the same underlying concepts apply. What I wanted to do in this session was strip things back and focus on the fundamentals. The building blocks that, once you understand them, carry across into any system you end up using.
What I see time and time again is that the real challenge is not analysis. It’s preparation. Most of the issues people face start long before they attempt to extract insights. They start with the quality of the data itself. If your data is inconsistent, incomplete, or incorrectly formatted, then whatever comes out the other end is unreliable. And that’s a dangerous place to be, because decisions get made on top of it.
There are a few recurring problems that show up in almost every dataset I come across. Numbers stored as text is one of the most frustrating. On the surface everything looks fine, but Excel won’t recognise those values properly. Calculations don’t behave as expected, and unless you know what to look for, it can go unnoticed. I’ve seen entire reports built on top of this kind of issue. The fix itself is simple, but the impact of not fixing it can be significant.
Gaps in data are another one. Blank fields might seem harmless, but they create ambiguity. Is the value missing, or does it not apply? When you start aggregating or filtering, those blanks distort the results. Over time, I’ve found it far more effective to replace gaps with a consistent placeholder. It gives you something to track, something to report on, and most importantly, it forces you to acknowledge where the data isn’t complete.
Then there are the subtle ones. Extra spaces in text fields, inconsistent date formats, small inconsistencies that are easy to overlook but cause problems later. These are the kinds of issues that slow you down when you’re trying to analyse anything at scale. You spend more time fixing problems than actually learning from the data. Once you start recognising these patterns, you realise that cleaning your data is not a one-off task. It’s part of the process every time.
I worked with a dataset recently that looked perfectly usable at first glance. It had customer names, transaction values, and dates. But once we started digging in, the cracks showed quickly. Some of the numbers weren’t being included in totals because they were stored incorrectly. Dates were inconsistent, making it difficult to track trends over time. And there were gaps that meant we couldn’t confidently segment the data. Nothing dramatic on its own, but together it made the analysis unreliable. Once we cleaned it properly, the same dataset started telling a very different story.
That’s usually the turning point. When your data is clean, you can start asking better questions. Instead of looking at everything in aggregate, you can break it down in ways that reflect how your business actually operates. Rather than asking what your total sales are, you start looking at when those sales happen. Which days perform better. Which months show patterns. Where things are consistent and where they aren’t.
I often encourage people to start with what they already suspect. Your intuition about your business is usually a good starting point. If you think sales are stronger at certain times of the year, test it. If you believe certain days are underperforming, validate it. The difference is that now you’re not relying on gut feel alone. You’re using data to confirm or challenge what you think you know.
This is where more structured analysis comes in. Functions like INDEX and MATCH might seem technical at first, but what they really do is give you flexibility. They allow you to extract exactly what you need from your dataset without breaking when things change. And things always change. New data gets added, columns shift, formats evolve. If your approach can’t handle that, it becomes fragile very quickly.
What I like about these methods is that they scale. Once you’ve set them up properly, you can apply the same logic across different parts of your data without rebuilding everything from scratch. That consistency is what builds confidence. You’re not just getting answers, you’re getting answers you can rely on.
From there, you can start to describe your data in a more meaningful way. Most people are familiar with averages, but averages on their own can be misleading. I like to think about a simple example. If most people in a group earn a low income and one person earns significantly more, the average shifts upwards. But that doesn’t reflect the reality for most people in that group. That’s where measures like the median and mode become useful. They give you a clearer sense of what’s typical, not just what’s mathematically central.
Another concept that becomes important is understanding how spread out your data is. Two datasets can have the same average but behave very differently. If your values are tightly grouped, things are predictable. If they’re widely spread, there’s more variability. That variability matters, especially when you’re trying to spot problems.
I’ve seen businesses use this in very practical ways. For example, tracking the average value of a sale and understanding the normal range around it. Once you know what “normal” looks like, anything outside that range becomes a signal. It might indicate a pricing issue, a process breakdown, or something else worth investigating. The key is that you’re not waiting days or weeks to notice it. You can pick it up as it happens.
That’s really the shift. Moving from looking at data after the fact to using it as a tool in real time. Even simple datasets can support that if they’re structured properly. You don’t need perfect systems to start doing this. You just need a clear approach and a bit of discipline in how you handle your data.
What I wanted to show is that none of this is out of reach. These are not advanced concepts reserved for specialists. They are practical techniques that anyone working with data can apply straight away. Clean your data, structure it properly, and ask better questions of it. The tools are already there. It’s about using them with intent.
If this struck a chord, take a look at your own data this week. Start small. Clean one dataset, test one assumption, build one simple analysis. You might be surprised at what it reveals. And if you want to go further with it, I’m always happy to continue the conversation over a coffee or at the next session.