1. Do not use Excel

Use R or Python instead

You can track your computations steps by step

Let me show some examples

Greece economy crashed because of Austerity

In 2010 Carmen Reinhart and Kenneth Rogoff (U. Maryland and Harvard) published a paper saying

rising levels of government debt are associated with much weaker rates of economic growth, indeed negative ones

Austerity

Smaller public sector, less hospitals, less schools/universities, less infrastructure

This policy was applied in many countries, and resulted in big crises in Greece and UK (among other places)

Many people lost they jobs, their savings, sometimes their lives

Some people think that austerity was one of the reasons for Brexit

Austerity was an Excel error

“Reinhart and Rogoff allowed researchers at the University of Massachusetts to look at their original spreadsheet – and the mystery of the irreproducible results was solved. First, they omitted some data; second, they used unusual and highly questionable statistical procedures; and finally, yes, they made an Excel coding error.

It is hard to find errors in Excel

Not enough testing

“The underlying problem with spreadsheets is how easy it is to use them without applying adequate scrutiny, oversight and validation. They can include many complex customised algorithms but are not typically built by software engineers who are trained to create reliable software.

“Instead, research shows, they are often built without sufficient planning, any control process or testing. This results in lurking data integrity problems that, given the right circumstances, can suddenly cause catastrophe.

Update

October 24th, 2023, 11:25AM ET:

Microsoft has updated Excel on Windows and macOS, adding a toggle to turn off automatic data conversion.

UK COVID data was lost

“the details of 16,000 positive test cases had been lost because of an error made with Microsoft Excel”

And there are many other stories like that

https://eusprig.org/research-info/horror-stories/

2. Use it only for small data

Excel works well with a few thousand rows

3. Data first, presentation later

Many people are tempted to draw nice tables

These are hard to use

Store data into tables, easy to analyze

Later, prepare reports from the tables
(automatically)

4. One sheet for each table

Data is organized in tables

• One row for each individual/case
• One column for each variable
• One sheet for each table
• Always start in cell A1

The columns are fixed. Data is inserted as new rows

5. One column, one datatype

This is like data frames in R or Python

Do not mix numbers and text

All values must be in the same units
(or use another column for the units)

One cell, one value

If you need multiple values, use more rows

6. Use Google Forms to insert data as a team

• It allows you to enter data directly into a spreadsheet

• Minimizes errors, using a controlled vocabulary

• You can distribute the work among several team members

7. You can make quick analysis

Learn to use

• Pivot tables
• Plots