Why keeping your data sanitised, as well as your hands, should be the new normal
Over the past months we've become accustomed to maintaining a higher level of personal hygiene, to prevent any COVID-19 virus particles from penetrating our bodies.
But arguably, many people do not have the same standards when it comes to using spreadsheets.
Spreadsheets are used for many 'everyday' work applications; Many of those applications form a critical part of our organisations' data processing, even if that is only to help the user to save their own working time. I'm sure however, we can all think of examples of spreadsheets which are 'unofficially' part of our organisation's key data processes.
Therefore, now that 'hospital-grade' sanitisation has become a personal responsibility for the whole population, it's time to adopt the same approach when it comes to making sure that 'dodgy' data cannot enter our spreadsheets.
In last week's blog, I encouraged spreadsheet users to be more disciplined in using the 'Format as Table' feature to created structured blocks of data. This helps ensure that the risk of formula error is reduced.
But a fully-functioning formula will still not work if it tries to process the wrong type of data.Ideally, we need to validate data as it is entered.
We're used to seeing data validation in online applications and in our core Enterprise systems:
"Please enter your card's expiry date in the format DD/YYYY"
"Error - you entered text when a number was expected"
The good news is that spreadsheets do
have the ability to validate data. It's something I use all the time
and it's helped me avoid contamination from other users (and my own keyboard errors!)
I'm focusing on Excel here (but the same principles do apply to Google Sheets). I've recorded a short video (link at top of this blog) which walks through how to set this up, as well as including a guide below.
How to set up data Validation in Excel
Let's say you have a table of sales data (properly formatted, like I showed in this blog
) and you want to enter a new line of data
Like me, you sometimes have typing issues, and you enter the date with extra characters in.
even though you know that somewhere else you're going to add up the sales by date (month etc).The spreadsheet will by default allow this entry. Excel is cleaver enough to interpret "9Jan21" as a date, but not "9Jang21" - it's just text. Therefore,and your new line of data will be missing from January 2021's total.
But fear not - if you set up the 'Date' column to have data validation, we can tell Excel that any new lines that get added to the table in that column need to be in a specific format.
First, highlight the entire column of dates, then from the Ribbon <Data> menu select <Data Validation>
This will bring up a dialog box, asking you to define what sort of data validation you require.
For now, select 'Date. 'll talk about other options later and in other blogs.
You then get a more specific dialog request for ranges of dates that allowable. in this instance, I'm selecting anything for 1 January 2021 onwards, but it possible to define ranges or maximum values also.
After clicking 'Okay', if I now try to add a value which is not a date, Excel will not allow my dodgy typing; I get an error message and an opportunity to retry.
Excellent - my users and I have been prevented from infecting our spreadsheet with unwanted data!
I'm not going to reproduce the menu steps here (watch the video!) but the same would apply if we wanted to create validation for the quantity to only contain whole numbers. In this sheet this kind of error might be visible (see below), but entry would still be permitted and the 'Value' formula error would corrupt your output. If the 'Value' was in another part of the spreadsheet there would be no sign of error on input.
To fix this, select the relevant column and then set up data validation for whole numbers (assuming you only sell in whole numbered quantities!).
Of course, most of the time, we don't enter raw transaction data manually, but hopefully it's been useful to use this familiar example demonstrate the principle. I'm sure there are plenty of areas in spreadsheets where you do key in data!
In next week's blog, I'll be diving deeper to show you how to set up custom lists of inputs for your data validation. This is a much more common need - where you want a specific 'lookup' such as account/customer/product name.
Even if you think you now know how to do data validation and can work out that I've going to be using the 'list' feature, there's a secret tip about setting up custom lists that 99% of Excel users won't know, and tips on how to structure your lookups!
Until then - Adios Amigos!
