Log In
Register
Home
New Member
October 22, 2022
Offline
Greetings,
I am new to this forum, but from scanning different posts I think you can probably give me good information on risks of data entry using Excel.
We have a wide range of natural history data on paper, entered into Excel and then exported to GIS or other software for analysis, storage, archive etc. The appeal of Excel for data entry is familiarity, ease of use, and ability to view multiple rows of previous data while entering a new record. I.e. a form where one record shows on a screen at a time will not work (at least for some of the projects).
I am concerned with how easy it is to make an error in excel w/o realizing it. Of special interest is the risk of Excel changing date and numerical data types (with no one noticing) and accidentally deleting contents of a cell.
I have set data types, used formulae, locking and sheet protection, data validations, drop down lists, etc. So I am familiar with (maybe not all) tools in excel to 'protect' the data and decrease the risk of error. I also have used a python script to clean data.
My question is, for people with experience with Excel, just really how protected are the data using the tools I mentioned?
I realize no software is perfect - I'm not looking for assurance of perfection. But reasonably speaking, how well can I trust the data entry and the validations, locks, domains, etc.? What about deleting the value of a cell when not all cells in the column need a value?
Before I put the time into it, I would like to get a better idea of just how likely it is that I would be able to use Excel or if I need to look for a different software that would allow a similar user interface in a tabular form - where multiple rows are visible. I don't have a working example with locks, protections, data validations, lists, etc, to post as an example. However I did attach a screenshot of a sample data entry spreadsheet showing the straightforward rows and columns with field headings and multiple rows of data visible. This is the format that we would like (using data validation, locks, etc). I would appreciate your comments or suggestions.
We use primarily Dell PCs, Excel 365, version 2207, build 15427.20194
Thank you,
Peg
See Answer
July 16, 2010
Offline
Hi Peg,
Welcome to our forum!
It sounds like you're using all the relevant tools for protecting your data integrity. Obviously, you can't prevent people who know their way around Excel from undoing those protections, but I would question their motives for wanting to do something like this.
In Excel online and Microsoft 365 you have track changes, which will show you who has edited a cell, what they changed it from and to, and when. You can see an example of it here: https://www.myonlinetraininghu.....e-in-excel
At least with this you have an audit trail.
Hope that helps.
Mynda
Answers Post
New Member
October 22, 2022
Offline
Hi Mynda-
And thank you - I'm glad to have found the forum.
That's a great suggestion - I hadn't thought about tracking something to see if there were changes where there should not have been - like on the first day a new person is entering data-as you say, at least there would be a trail.... I don't yet know where the spreadsheet will 'live' - it's early stages yet, but I will add that to my bag of tricks.
So it sounds like if I use the mentioned tools appropriately, and to the maximum extent possible, I should be in good shape as far as accidental changes. Great food for thought!
Thank you,
Peg
1 Guest(s)
