Getting your Excel files under control

I've written many articles and posts about Data Governance and Data Quality, where I generally consider the triangle of people, process and technology, with my focus often being on the people side.

But sometimes we have to be reminded that for most people, data problems are spreadsheet problems, and usually Excel problems.

This week brought us news of another one:

And even if spreadsheet concerns aren't newsworthy, they still lead to significant risks of error.

We're not going to eliminate spreadsheets, so we're going to have to live with them.

So in a world that relies on spreadsheets, what steps can we take to mitigate some of the risks?

The first step is to decide how much spreadsheet management we want. We only want to apply effort to the most material spreadsheets to the business. Consider factors such as:

• What impacts could results from errors in the spreadsheet data?

Making a suboptimal decision is one things, but what if you had to restate your financial results, or even found yourself breaching a regulation or breaking a law?

• How much risk is associated with the spreadsheet file?

How often is it updated? How often is the structure of the file changed? Are there macros or VBA code used?

Beware of taking criteria from somewhere else. A spreadsheet error that causes a £10,000 loss could be critical to a small business, but trivial to a large multi-national. The risk and impact assessment needs to be focused on your organisation.

Once you've established the scope that you want to work with, here are some standards that will help you to trust your spreadsheet and its content.

• A cover sheet which describes what the spreadsheet is and what it's used for; who created it and updated it; usage instructions; and any special things to know such as very hidden worksheets or the use of VBA.

• Testing that external look ups and links work.

• Clearly stating, and sticking to, units of measure. Are those financial amounts in thousands or millions, and in what currency?

• Protecting the formula cells so that users can input values but not overwrite calculations.

If you search for "spreadsheet standards" online, you'll find many more. The problem I have with implementing too many standards is that people find it overwhelming and give up.

Do fewer things, and do them well.

What challenges do you find from your spreadsheets? Reply to this email or comment below the web version.

Datazed has helped multiple organisations to get more value from data governance. We can support you on your journey, or manage it all for you before handing over to your team.

Was this message forwarded to you, or are you reading it online? Click here to subscribe and get this newsletter sent directly to you. Unsubscribe instantly and at any time using the link in the footer.

Have a wonderful week,
Charles