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!)
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.
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.
So instead of something like this where ‘price’ and ‘mpg’ are in the same cell…
Split it out like this so that they’re in different columns…
This example dataset is one of the sample Stata datasets. You can access it by typing ‘sysuse auto’ into Stata.
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.
In this example, ‘id’ is the unique identifier because it is different for each row of data…
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.
It’s even better though if you can use dummy coding for categorical variables (ones where you have distinct groups) as this removes the option to misspell things completely. In this example, ‘foreign_n’ is dummy coding for ‘foreign’ where Domestic is coded as 1, and Foreign is coded as 2.
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.
So instead of something like this where a red row tells you that the data should be excluded…
Create a new column with this information like this…
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.
Looking for more support with your data?
If you found this post on simple tips for organising data in Excel useful, you might like these articles on similar topics.