11 Essential Tips for Effective Data Collection

We live in an increasingly rich world of data – the amount of data that currently exists doubles every 18 months. That’s a phenomenal rate of growth and we’re just at the beginning of an incredible journey creating awesome intelligent applications that can handle these unimaginable amounts of data automatically.

This Big Data movement is happening at one end of the scale.

At the other, there are millions of researchers around the globe collecting and working with Small Data – data that is small enough to fit in an Excel spreadsheet and store on a floppy disc (remember those?).

It doesn’t matter whether you’re a scientist or an entrepreneur, in academia or in business, if you’re collecting data to try to answer some questions then you need to understand the fundamentals.


11 Tips for Data Collection

You’ll likely spend a lot of time observing, measuring, counting, classifying and quantifying what you see, and once you’ve collected your data you’re going to have to analyse it.

But let’s not get too far ahead of ourselves.

Before you can get any answers you’re going to have to:

  • 1
  • 2
    Record & Store
  • 3
    Clean & Classify

​The textbooks tend not to dwell on the practical issues of these steps because, well, to be honest, it can get quite messy, but these are vitally important steps and you really do need to know how to do them properly if you’re going to get the most out of your data.

So let’s rewind to the beginning and see what we can do to get you off to a good start...

This blog post will deal with data collection methods and data management and will give you some great tips on how to get organised, how to get started and how to avoid making the kind of mistakes that will make you prematurely grey!

Over the next couple of weeks I will deal with other important issues such as data cleaning and data classification, but for the moment let’s focus on the most important – data collection. Mess this one up and there’s no point moving on to the others, so focus guys!

​Data Collection "mess this ​up and your dataset is going to the great Data Graveyard In The Sky!" @eelrekab #gooddata

Click to Tweet

​Here are 3 rules to start off with. Repeat after me:

  • 1
    ​Don’t Panic !!!
  • 2
    Start thinking about the data before you start collecting it
  • 3
    Make a personal vow to understand the basics of data


I've put together an infographic that might help you a little:

11 Essential Tips for Effective Data Collection

1. Record Data on Paper First…

So you’ve got your hypothesis (theory, idea or hunch). Once you’ve decided what data you need to collect, the first thing you should do is design a paper-based form to store all your data (assuming that at least some of your data is going to be recorded by hand).

Keep it simple, print it out, then manually record your data with pen and paper. One form per case/patient/customer/test-tube, etc..

Save Data on Paper

2. …Then Transfer it to an Electronic Medium (Excel is most common)

We may be living in an electronic world, but ultimately you need a system where you (or anyone else) can follow the data trail from beginning to end and – more crucially – from end to beginning.

From time to time you WILL make a mistake with the data, so it is vitally important that you design a method that will let you spot and rectify the mistake by going back through all the steps until you find the error.

So now you have your data recorded on paper you need to transfer it into an electronic system. More than likely this will be either Microsoft Excel or Access.

In general, Excel is more common and easier to use, and has the added advantage that you can manipulate the data and do some simple analyses right there without having to export your data.

Most data is stored in Excel (in 7 years as a medical statistician I was only once given data in Access – all the other times it was in Excel), so we’ll go with that from here on in…

Transfer Data to Excel

3. Enter Your Data on a Single Worksheet Whenever Possible

Trying to sort your data when it is spread across multiple worksheets can lead to all sorts of problems, so try to avoid it whenever you can – keep your data on a single worksheet.

Excel 2003 has limits of 65,536 rows by 256 columns. That’s large enough for most datasets, but if you need higher limits you can use Excel 2010 or 2013 (1,048,576 rows by 16,384 columns).

4. Use a Unique ID column

You’ll likely have to sort your data many times and by different columns, so you’re going to need a way of restoring the original order.

Use column A as a unique identifier to insert consecutive numbers starting from 1. It may be simple, but it’s very effective.

When you’ve put your Unique IDs into column A, go back to your original paper sheets and write the Unique ID there as well. Trust me – you’ll thank me for this tip later…

Unique ID column

5. One Column per Variable

Each variable should have… oh, hold on a minute, what’s a variable?

Well, simply put, these are the things that can change or can be changed as part of your study. In short, these are all the pieces of information that you are observing, measuring, counting and collecting, like age, gender, distance, temperature, etc..

You can find more information in my blog on different data types.

Where were we? Ah yes…

Each variable should have its own column, and each variable should correspond to just one piece of information.

One Column Per Variable

If you’re entering the age of a patient, then just enter their age, don’t enter their date of birth in the same column or cell. If you want to record their age and DOB, then use 2 separate columns.

If you’re recording a composite variable made up of 2 or more constituent parts, like Body Mass Index – made up of Height and Weight – then record them in separate columns. You can always combine them into a single variable later.

6. Row 1 is the Variable Name

Eventually you’ll need to analyse your data and you may need to export it to a statistical program.

The standard for pretty much all commercial stats programs is for the first row to be reserved for the name of the variable and all other rows for the data. So don’t be tempted to use rows 2, 3 and 4 as well as row 1 for the variable name. It might keep everything looking nice and tidy in Excel, but it will only create more work for you later.

Row 1 is the variable name

7. Every Cell Should Have Something In It

What do empty cells tell you?

  • ​waiting for more information?
  • data not recorded?
  • original data incorrect?

An empty cell is just a great big question mark and tells you nothing.

Worse still, incomplete datasets give reviewers a reason to whack you about the head with a metaphorical stick (and believe me they will – I’ve been there many times…).

So make sure that something is entered in every cell.

It is quite common to use ‘illegal’ numbers as codes to give you information, so where the entries for a variable can only be positive values (like age or height), we can use codes such as:

Variable Codes

If negative numbers aren’t useful, then use letters a, b, c, etc..

If you’re not comfortable entering something in cells that strictly shouldn’t be there (after all, you are going to have to clean them up later before you can analyse your data), then use Excel’s Comment feature. I tend to use this sparingly, but that’s just me.

8. Keep Great Notes

When using codes you’ll need to keep notes to tell you what the codes mean. Keep the codes and notes in a different spreadsheet.

Codes Sheet

While we’re on the subject, it’s really important to


You’re likely not the only person that will ever work with this dataset, so get used to writing stuff down.

Explain what the project is all about, the question you’re trying to answer, why you’re collecting this data and how you’re going to get the answers you’re looking for. Explain how you measured things and under what conditions. If more than one person is collecting data, then explain who, what, where, when, why and how.

Notes Sheet

This will be the document that explains all the important stuff about your dataset, so write it down.

If there’s too much information to comfortably put into an Excel spreadsheet, then a Microsoft Word document will be just fine – and keep it in the same folder as the dataset.

9. Be Consistent

There’s nothing worse than getting a dataset that takes a fortnight to clean because data entry has not been consistent.

By that I mean make sure that if the entry for a variable should be ‘Positive’, then make it ‘Positive’ and not some other variation:

Be consistent

It’s hard enough correcting speeling missteakes and typos without also having to correct things that were deliberately entered differently.

Restrict the number of people that can enter data to cut down on this kind of issue, and make it clear what your data entry standards are.

10 Don’t Guess

Data should be entered as accurately as possible.

Don’t guess, approximate, round up or down.

Enter the value exactly as registered on paper.

If you need the data to be rounded up or down you can use Excel’s functions to achieve this, but if you’re doing calculations in your head, on paper or in a calculator you’ll make mistakes which can be difficult – if not impossible – to spot later.

Don't Guess

11. Zero is a Real Number

Don’t enter the number Zero into a cell unless what has been measured, counted or calculated results in the answer Zero.

I’ve often received datasets with lots of zeros and when I asked, the zeros meant ‘I don’t have data for this’.

The problem is that if you want to calculate something, like the mean, then all the zeros will be used in the calculation and you will get an inaccurate answer – or one that is just plain wrong!

Zero is a Real Number

​Your Next Step

​Let's face it - you didn't visit this blog post because you're passionate about data collection, did you?

You didn't leave college or University saying "now that I'm free to forge my own path, I'm going to be a ... ​​a data collectionist!"

That sounds like a line straight out of a Monty Python sketch!

No, you're here because you need to collect data, or you've collected data and want to know what the next steps are. Well, we're here to help.

I hope you've found this blog post useful, and to help you take your career to the next level we've created a series of video courses dedicated to data collection, data cleaning and data preparation to get your data analysis-ready in double quick time.

You can get the first of these courses right here:


This blog post will deal with data collection methods and data management and will give you some great tips on how to get organised, how to get started and how to avoid making the kind of mistakes that will make you prematurely grey! #datacollection #dtamanagement #datatips

The textbooks tend not to dwell on the practical issue of data collection. Here you have the 11 vitally important steps you need to take for effective data collection. #datatips #datacollection #data #datamanagement

Do NOT follow this link or you will be banned from the site!