How to Keep Your Data Clean & Accurate [5-Step Framework]

Read time: 4 mins

If you're spending too much time manipulating data to ensure accuracy, this edition is for you

I'm sure you've been here.

• You're stuck in a spreadsheet.
• You're spending hours trying to fill gaps.
• You're correcting incorrectly inputted data.

There's a saying in the media industry called "We'll fix it in post".

Which means fixing poorly recorded footage or audio AFTER it's been recorded.

But there's another problem with this.

Your data can get WORSE if you're at the point where you're trying to fix it.

Keeping Data Clean

We use the term 'Garbage in, Garbage out' all the time.

But it's true...

So, when we're working with data, it's crucial the quality of our input is as high as possible.

This means the amount of effort upfront needs to be disproportionate to the amount of effort afterwards.

This may seem counter-intuitive. "We just don't have the time to be capturing lots of data upfront..."

Well.

This framework is here to help.

The Framework

1. Capture

Using the simple example of capturing Customer & Supplier details.

You may have a document either the sales team of the purchasing complete before it's entered into your finance system.

• Do you have a way of making data points compulsory?
• Do you have an approval / validation process?
• Do you have a way of making it easy?

If you're working manually, here's some inspiration:

• Create a Microsoft Form with required fields
• Use a Teams Approval when a document is created
• Develop a Power Automate flow to connect the two

And if you're not using Microsoft, Jotforms, Slack and Make.com are all accessible tools to help you lock down your process.

2. Compulsory

So we've locked down our 'static' data at the point of entry (the data that doesn't change much)

But what about the 'dynamic' data we enter as part of our day to day?

• Keying journals
• Entering orders
• Processing payments

Most finance systems have the ability to set fields as compulsory / required.

It could just be a case of tweaking your setup.

Some systems will even use AI to detect errors in data entry.

3. Approval

I mentioned the concept of approvals when entering customer and supplier information.

But what about approvals at the point of transaction?

Some finance systems will allow you to build approvals into your workflow.

If not, third party solutions like ApprovalMax can help facilitate this.

If none of those work, no-code automation platforms like Zapier & Make.com will can allow you to do an 'if this, then that' workflow when transactions are created in a system.

For example:

"When invoice is created in [Finance Software], send notification to Slack Channel"

4. Export & Format

So, you've captured your required data points upfront.

You've approved day to day transactions to ensure accuracy.

Now you're ready to export your data for reporting.

THE most important part of this is using the minimum amount of steps to format your data correctly.

The more steps required to format. The more scope there is for the data to change and become inaccurate.

Some of the best ways to work with data include:

1. Power Query
2. SQL Queries

NOTE - Be careful when using SQL queries, as they can also be used to change data, as well as pull data out for reporting.

5. Tidy

Hopefully, you won't have to use this step.

But it's possible you've done everything you can to keep your data clean, but there may still be some gaps.

There are now tools that make it easier to fill gaps in data.

• LABS.GENERATIVEAI
• ChatGPT for Sheets

Just be careful, as AI isn't perfect.

You'll still need to do some validation, but efficient use of these tools will aim to get you 70-80% of the way there.

Putting it into Practice

1. Look at the help guides for your finance software to see whether you can add required fields and approvals
2. If not, have a look at workflows you could achieve with Slack or Teams
3. Re-read my newsletter on 'No-Code Automation' here
4. Check out Rick Der Groot's Power Query videos here
5. Watch Mynda Treacy's 'Excel: 8 AI Game-Changers!' here

Your data will be clean and accurate in no time!

Until next time.

Adam

2 Ways I can help

When you're ready here's how I can help you:

• Book a 1:1 coaching session - We'll turn your tech confusion into tech confidence. Start crushing it here
• Join the AI & Automation Academy Waitlist - Get early bird access to my up and coming course here.