Excel is a useful tool to effectively organise, analyse and share data.
What happens, though, when the default methods used in Excel to organise information do not align with the way we typically work with data? A recent article titled ‘Why Geneticists Really, Really Hate Microsoft Excel’ shows how frustrating Excel can be to use, especially without Excel training.
How geneticists use Excel
Geneticists work with genes – compounds in our bodies made up of DNA which make up our physical characteristics.
Apparently, each of us have between 20,000 and 25,000 genes inside our bodies, all of which need to be categorised by geneticists in their research. Geneticists use Excel to simplify the way they use and share very large datasets of information.
If Excel is so useful to geneticists in their research, why do they find it so frustrating and experience Excel troubles?
Why geneticists have problems with Excel
According to the National Institute of Health, because we have so many genes, geneticists give each gene a unique abbreviation. It’s these standardised gene symbols that Excel ‘messes’ with, often converting certain symbols to dates. One example is SEPT1. When entering SEPT1 into an Excel spreadsheet, Excel automatically interprets the data as the 1st of September of the current year. Another example is MARCH1. You can guess what Excel does with that one.
A consistent, standardised system of gene naming is crucial for clear communication and, as a result, a total of 27 genes have been renamed to prevent inconsistencies in data entry. SEPT1 is now officially known as SEPTIN1 and MARCH1 has been changed to MARCHF1.
Simple solutions to data entry problems
It is amazing that geneticists have gone to such lengths to prevent Excel from converting gene names. There is a much simpler solution – two in fact.
The first solution is to include an apostrophe (‘) before the gene code. It’s that simple. The second solution is to simply preformat the cells containing gene codes to ‘Text’. Either solution ensures Excel does not second guess what output you require and will simply leave the information as you typed it.
If all geneticists knew this simple Excel trick, they wouldn’t have to rename their genes or have any Excel troubles.
This trick can be applied to more than just gene symbols. Many Excel users have fallen victim to Excel trying to convert a ‘Text’ entry to a ‘Numerical Value’. Hijacking the leading zero of a phone number is a classic. Enter 0821234567 in a cell and you are left with 821234567. Once again, the solution is simple: either prefix the data with an apostrophe or preformat the cell as ‘Text’.
How to work with Excel
Understanding how Excel interprets the data we enter is key to working efficiently with Spreadsheets and knowing how to automate daily Excel routines will save you hours of your time. Unfortunately, most self-taught Excel users learn this the hard way.
An Excel training course will help! An easy place to start is our Excel Beginner Course where we cover in detail the topic of how Excel interprets data. At Summit Solutions, our Excel courses empower our clients to become more confident and efficient in Excel. We teach the skills and tools you need to apply solutions in a relevant way through ongoing support and consulting.
Enrol in our Excel training today if you are ready to get confident with Excel.
And to all the geneticists out there, if you happened to discover a new Gene and you want to name it DEC1, you now know the alternative to renaming it to DECIN1 and ill hopefully utilise this solution to your Excel troubles..