Data analysis – help needed.

I’ve got a slight problem. My work involves a fair bit of data analysis, mainly in Excel. My problem is though that I’ve been caught out for a couple of embarrassing numerical typos in reports. Nothing too serious, but still embarrassing. Does anyone have any tips?

Get someone else to review.

Put error checking in.

Don’t use excel.

Just the obvious, check, then check again, and then get someone else to check.
May help if you print it out. Depending on what calcs you’re doing, check some by hand with a calculator.
If possible, work out a result two different ways, result should be the same, but clearly depends on what the data is.
Use graphs to spot anything clearly wrong.

Someone is presumably picking up your errors. So are they spending more time than you in checking the data ?

Can you add some conditional formatting into the reports i.e. if a key value exceeds normal parameters then highlight in red.

What causes the errors? Wrong input data? If so, use drop down lists to prevent entering the wrong data.

Thanks for the quick responses everyone. I think for me, in situations where I can’t use the other methods printing out might be the best thing. I think that after spending some time staring into excel it becomes very difficult to spot a stray number in the wrong place.

Yeah, the problems are at the input stage

Graph it then, much easier to spot abnormal data visually.

if the issue is data entry then a printout is easier to check – I always print to proof-read or I miss totally obvious mistakes

as well as graphing data, try and graphically predict* what you expect first.

*old skool, back of envelope, biro.

Your prediction will rarely be 100% accurate, but you should be able to describe discrepancies between your observed and predicted graphs. If you cant, go back to the data and work it out from first principles.

If you catch yourself saying things like: “oh thats just the way the function calculates that”, or “there’s bound to be outliers”, or “the inputs aren’t all clean”…then double check, because you’re making excuses for variation, not describing their causes.

I built my career on data analysis and no matter how hard you try, you will always make mistakes. But they should be so minor that they are obscure and de minimis in their effect on output and any deductions drawn.

However, I found the older and less interested in my work I got the less obscure and the less de minimis my mistakes became. So I chose to change my role away from heavy analytical lifting. I still do analysis but I do so in far simpler models and keep clear of complex projects. Im lucky to have the luxury of that choice now.

Good, accurate analysis is not easy and it takes concentration and focus. I can’t do it like I used to any more.

As Stoner said, graph the data (if it is possible). Errors are usually glaringly obvious on the correct type of chart/graph.

If no-one is checking your work then it is inevitable that mistakes will occur.

You can set limits in cells so you can only enter values between a certain range (might help) or do what I did for some project and that is to in a way chart it so basically one sense checking tab that said if the number was 20% higher or lower than the previous month then big red text formatttinf. Good for seeing potential errors but also highlight things mgt should be aware of whatnot.

Another option is to duoble enter the data onto two seperate worksheets and get excel to check you’ve entered the same data, time consuming but belt and braces approach.

Data Entry Issues? First question why are you manually entering data? Whats the source, unless it’s hand written then there are automated ways of importing it. If it is hand written ask why! Work out how much time you spend entering data and then that is the business case to improve data recording and import.

I don’t do as much data work these days as I did but I try and automate everything, it’s more efficient and it removes human error.

As just mentioned it sounds like a classic case of using the wrong tools.. Excel is for amateur’s first thing you need to to do is uninstall ms office. Then look into a proper bi solution that can be fully automated.

As just mentioned it sounds like a classic case of using the wrong tools.. Excel is for amateur’s first thing you need to to do is uninstall ms office. Then look into a proper bi solution that can be fully automated.

Excel is an excellent tool – nothing wrong with it when used correctly. But I do agree that automation would be an excellent solution if available, and I do wonder why the OP needs to enter the data by hand – what format is it in? If it’s electronic you should be able to either import it directly, or write a script to clean it up and then import.

If it’s on paper and you really have no way to import it correctly, one trick is to add checksums – for example the sum of your weekly hours should be the same as the sum of daily hours over a month, subtract one from the other and the result should be zero. If it isn’t, something’s gone wrong. (Just make sure you either label the cell as a checksum, or delete it before sending…)

do you have ‘expected’ values?

you could set a colour filter on the cells to highlight any values above or below a certain figure

eg. flag anything below 5 in red

Then look into a proper bi solution that can be fully automated.

Speaking as someone who uses and now sells a BI tool they are not always the best solution. Excel is a very powerful tool that can do a lot. It also has a lot of dangers with it.
A BI tool still needs data entry which looks like the main issue.

If you know what range your data is likely to fall in, can’t you just set conditional formatting on the column to highlight anything suspicious. You can then just check the coloured cells to see if there is a genuine error or an outlier.
Or just graph the data with a line graph and look for the blips?