One of the key things that Excel is used for is storing data but there are some pitfalls to avoid. Here are my top 5 tips when working with data.
1. Arrange data in a table format
If you are using Excel to store information then it’s a really good idea to always set it up in the following way. This will give you the greatest flexibility on the analysis options you can use and make your life so much easier. It’s also a structure that most will be familiar with.
Always put headings in the top row of the data with your record row by row below this and avoid blank rows and columns.
I have seen many spreadsheet where data is grouped in column headings and then grouped in row headings. This is sometimes referred to as a crosstab structure. It may be the result you require but a pivot table would easily change the data from normal table structure to this whilst keeping the full capability of the original table too.
2. Consistency of Data
Make sure your data has consistency. For example don’t enter Ltd in one place and Limited somewhere else. Excel doesn’t understand this is NOT the same thing and if you analyse your data it will view them as totally different. Similarly, be careful with misspelling or extra addition of spaces after your information. With the spaces the entries will look identical but they are not.
3. Don’t enter dates with dots
If you have dates in Excel most standard ways to enter dates will work fine but one that often catches people out is entering dates using dots. For example, 25.12.2021 is not a valid date format and will not sort or filter like a date. It will be treated as text. Similarly entering a date with st, nd, rd etc treats it as text too. e.g. 25th December 2021 is not a valid date. I tend to enter my dates in the following format but there are plenty of others that will work too – 25/12/21.
4. Don’t type in £ sign and commas when entering amounts.
Although this isn’t wrong it will save you time if the column where the data is going to entered is formatted in currency or accounting style. Then you can type your numbers in as plain numbers like 1000 instead of £1,000. Imagine doing this hundreds or thousands of times. That’s a lot of extra typing and waste of time that could be better spent elsewhere.
5. Protect Sheet
Have you ever spent ages setting up loads of great calculations that automatically update when information is added, only to find someone has overtyped them with numbers or text? If you have you will have found it really frustrating. If you have any calculations or data you don’t want anyone to accidentally change then protecting the sheet is a good idea. You may need to unprotect the cells where you want to have data entered first but once that is done the risk of important formulas or information accidently being removed is gone.
I hope these tips will help you to get the most out of the data you have and explain why sometimes you may not get the results you expect.
If you’ve enjoyed reading my top 5 Excel data tips, there’s some other blogs below that you might find useful: