Error-prevention strategies when using Excel

[Peter Bartholomew]

@chirayu
True, but my reason for opening up the discussion was that not all errors are at the detailed implementation level. In fact the most damaging errors can be at the planning stage which draws on both domain knowledge and Excel solution logic.

Not that I would fault anything you have suggested.

One further check that I would propose is to count the rows of input data and then test whether every downstream calculation is adequately sized to accommodate it in its entirety. Much of the testing I have seen has the content of the data as its focus rather than the structure (if any) of the proposed solution.

Peter

 

[chirayu]

@PP3321 for point 5 I mean for example if you use SQL, then using a specific SQL statement that extracts/ manipulates the data the way you need, that way each time you run the query you know the extract will generate with the same calculations/filters etc.

In my company we have in-house front end applications that link to the databases so we can preconfigure how we want the data to be extracted and then I assign the reports to people so they get it at a set time each day etc. & they'll run a macro to generate their report from the data.

@Peter Bartholomew I rarely work with extremely large datasets in Excel. Excel gets laggy if I do as its forever calculating. For any data I do work with I generally tend to reference columns rather than ranges so A:D instead of A2:D50 etc. So rows isn't really an issue. It usually data quality rather than quantity that becomes the issue.

Also 1 issue you can't account for no matter what you do is users managing to screw up a predefined process. No matter how well you plan, some idiot who doesn't know what they're doing can manage to mess up everything you've set up. I know that whenever I set up something I write a little bullet point list saying how to run it etc. But for some people I still sit with them personally due to their ineptitude to follow instructions.

There is no way to prevent errors. Fact of life. All you can do is minimize the number of them through teaching people good practices, by showing them the correct procedures for any automation they use and by releasing newer versions as & when an error is raised (where applicable).

I say where applicable because sometimes you actually want an error to be raised so the macro can be stopped & the user can physically check what caused the error & update accordingly.

I have a few macros where I haven't built in error handling on certain parts because they act as a fail safe, if macro errors then the user knows that certain important details have changed between master file & source file. This way they can investigate what has changed & update it before running the code.

 

[PP3321]

@chirayu
Thank you...

This discussion reminded me of difference between strategic and tactical.

For example, you can choose how to get from point A to point B.
(Airplanes, ships, walk, cars)

But then in big scheme of things,
is it really necessary to go to point B?

What if we go to point C?

What direction do we really need to go when it comes to delivering delivables with best accuracy?

One direction is work on these techniques but another direction we can also take is look off Excel (team-work, project set-up) etc.

You may not even use Excel for some projects

But because if you have hammer, you can only see nails, so we tend to suggest Excel Solution...

 

[edwards142]

Well it's quite common to get error during the usage of excel application but the important of all is to have infromation about the error which you got. as most of the time we can't fix some error just becuase we don't know why it's occuring and how we can fix it. So i have listed down some most commonly encountred Excel error along with it's fixes here in this post.

You guys can check this out, i am damn sure that you will find this helpful.

source: Excel errors and fixes