Excel against science, what would you not have imagined?

It is normal, when talking about scientific research, to think about Excel docs bursting with numbers, formulas, more numbers, some more formulas and, if we are very lucky, some format adjustment that makes the information contained in the document easier to read. Graphics? They are only for the faint of spirit. Illustrations? Graphic elements in Word Art? Texts understandable to ordinary mortals? Of course, it starts like this and ends up making a Mortadelo and Filemón comic …

Well, kidding aside, it is true that Excel, and calculus books, are a uniquely valuable tool for scientific research, since not only allow to collect a large amount of information, adjusting its organization to the specific needs of each case, but rather also allow to relate it to each other and, thanks to the formulas, make these relationships reflect and update automatically when making any changes. It is not surprising that spreadsheets were “responsible” for the infiltration of computing in companies and offices of all kinds, in the distant and endearing eighties of the last century.

However, and although as I say, spreadsheets are an excellent tool, they also have some negative points, and today I read in Übergizmo an example of this that, at the same time, I recognize immediately and it surprises me greatly. And it is that, from what I see, the habit of Excel to try to interpret the information that is entered in each cell, in order to give it the appropriate format, could be posing a problem for researchers.

And what is the problem with Excel, and what it was already approached by a team of researchers back in 2016? Well let’s take a basic example: imagine that you are taking data from, for example, several samples of experiments carried out during the month of March. We are not going to ask the researcher to also be a literary pro, so it is possible that the names he uses to identify the results are something like this:

• March 1
• March-2
• March-3
• March-N

The problem is that Excel, when viewing these entries, it will probably interpret them as dates, and when the person who has added the data, or anyone else with access to the document, starts to review it, what they will find is something like:

• March 1, 2020
• 03/01/2020

That is, data that is text, is interpreted by Excel as dates and, therefore, it applies the format it deems appropriate. This can also occur with Numeric data that appear to be dates, but are not actually dates. For example, anyone who has a book in which they keep track of their invoices, and names them with a number and the year, something like 04/2020, what you can find is that Excel replaces it with «April 2020 ».

And we must bear in mind, of course, that we are talking about documents in which, in general, a few dozen or hundreds of entries are not saved. No, we generally speak of a much higher volume, so a manual review of these can be time consuming and also leaves room for errorEspecially if the review is done by the same person who added the data in the Excel workbook.

And it is not a minor problem, it is more, it gets to the point that entities like HUGO, the committee that works on naming human genes, has had to publish a set of guidelines for this task. Rules that are born, precisely, as a result of the possible negative effects that the Excel date auto-format function can have. A set of rules that should not only be used in the future, but should also be applied retroactively to all genes that have already received a name that can be misinterpreted by Excel.

It would be interesting if Excel could be configured for, by default and in all documents, Excel does not apply formats to the data that is entered in a sheet and that can be misinterpreted. But, until that option arrives, if it does (and hopefully so, we already know that Excel is Nadella’s darling), lthe best possibility is to stop the automatic number formatting and its change by dates. Microsoft explains how to do it here. And if you prefer to wait for Microsoft to activate this function, you can always spend time playing some X-COM games in Excel. So, even if it takes time to arrive, at least you will be entertained.