One of the topics I cover in my new book Your Excel Survival Kit: A guide to surviving and thriving in an Excel World (ooh, still get a thrill when I say that!) which will be out in June 2016 is how to set up your data to make it easy to work with.
If you set up your data as I suggest you will feel like…
Excel is a very powerful tool but it does assume certain things. One of the main things it assumes is that your data will (in most cases) be set up using the following guidelines
- Side by Side – no blank columns
- Every entry directly under the previous – no blank rows
- One cell headings: Headings to be in one cell and preferably bold (so Excel knows they are a heading and doesn’t try to sort them)
- Consistent data entry: For example – “The Big Shop” should always be written as “The Big Shop”, not “Big Shop”, “Big Shop, The”, “The Big Shop”. While Excel is generally not case-sensitive about stuff like this e.g. “The Big Shop” will be seen as the same as The big shop” , it is fussy about having extra spaces. As you develop your Excel expertise, you should look at the implementation of data validation.
- Dates should be entered the same way every time.g. 1/1/2016
- If you need to add a comment to a cell, do it as a comment (right click on the cell, Add Comment, then type in your comment. The cells with comments with have a red triangle in the corner.
- Feels like the first time : (Apologies to Foreigner here) Even if you are entering data about the same item but it’s a different occasion e.g. same customer, different invoice. Same patient, different visit. Same learner, different course. You must enter all the details as if you are entering it for the first time.
- One big dataset: Users often tend to separate their data out over months – then at the end of the year they are asked for annual figures and it requires the skills of a seamstress to put it all back together – just keep it all in one place.
You can download an example of a Excel_novice_data_entry_sheet
If you don’t set up your data as I suggest…you will probably end up feeling like..
So why?
- At some stage you will want to sort and filter your data – if your data has lots of blank rows and columns, you are nearly guaranteed to omit entries….
- The day will come when someone will say to you or you want to know…”how many people of X did Y in the course of the previous month/year” So you may need to create a pivot table. That’s just a couple of steps if your data is set up correctly. If not, it will require the use of formulas and strategies like Power Query that novice users are generally not familiar with.