10 simple tips for organising data in Excel

Making sure your data are well-organised is a key part of any literature review. Excel is a great tool for this because it’s widely available and easy to use.

But if your data is a mess then it’s pretty much unusable.

The good news is that with these 10 simple tips for organising data in Excel you can get yourself on the right track and save lots of time. (Psst, this post includes the Excel mistake I see most often!)

10 simple tips for organising data in Excel

1. Get organised

Create a sensible folder structure and keep your data where it can be found by everybody who needs it. Use version numbers and archive old copies so that everybody uses the most recent copy.

2. Keep notes

You’re busy. You’ll do something today then come back to it a few months later and forget what you did or why. We all do it. Or somebody else will pick up the project and need to figure out what you did. Jotting down notes as you go can save so much time later. You can even use the first tab of an Excel sheet for notes so that they stay with the data.

Man analysing data on laptop

3. Sense check

It’s amazing what you notice just from a quick look through your data. Are there gaps? Numbers that look really high or low? Does the number of entries look sensible?

4. Only 1 piece of information per cell

This is the mistake I see the most. You can’t analyse data as soon as there’s more than 1 piece of information in a cell (each little ‘block’ of the Excel spreadsheet is a cell).

If you find yourself putting more than one piece of information in a cell then split it out into separate columns. For example, if you’re entering a mean and a standard deviation into the same cell then create 2 separate columns: 1 for the mean and 1 for the standard deviation.

Get charity data clean

5. Create an identifier for each row

Each row of data (rows run horizontally) should represent something unique, such as an individual or an appointment. Ideally, each row should have an identifier that’s unique to that row. This could be something like a donor reference number. Having an identifier helps considerably if you want to join together data from different sources.

6. Limit text options

It’s usually best to limit text data, unless you’re working with a qualitative researcher. A few options where people can specify ‘other’ or note any further comments are fine. But generally you want to stick with pre-specified options because this will make your analysis a lot quicker and clearer.

7. Check spellings

Make sure data that tells you the same thing is spelt in the same way. It sounds obvious, I know. It’s so easy though to enter ‘and’ one time, then ‘&’ the next. Or to mix up lower and upper case. An easy way to check is to set the top row as a filter then check the filter options are spelt consistently.

Get charity data ready to analyse

8. Don’t use colour coding for information

The information will get lost if you move your data into a different program or save it as a csv file. Create a new column for the information that you are colour coding instead.

9. Data in the same column should be the same format

Make sure everything in a column is the same format, such as a number, text or date. Having some text, like “N/A”, in a number column can make analysis harder. It’s better to leave the cell blank (or use a missing number code like -999 for missing data so long as it’s outside of the range of possible options).

10. Sort with caution

Excel treats each column separately if you let it so it’s very easy for data to get muddled up. Highlight all columns to make sure they stay together when you are sorting, or use the ‘expand’ option.

Found this post on simple tips for organising data in Excel useful? Please share it now!

Leave a Reply

Your email address will not be published. Required fields are marked *