A while back I had a message from a colleague. She had got a training contract but she wanted to track it on Excel. She needed to be able to see what days her courses were being delivered and by whom and the topic. She had made the – oh so common – mistake of setting it up across multiple worksheets – which of course meant that at the end when she wanted to get a summary of how many courses she had run etc – it would require the Excel equivalent of putting Humpty Dumpty back together again. Ah, hours of fun. Excel works best and is most fun when it is being fed good food i.e. clean, properly organised data i.e. in tables, side by side and using dropdown lists to ensure consistent data entry.
This is a very common mistake so in this blog post I am outlining how you need to set up your data so that it will be easy for you summarize and analyse later on.
Begin by identifying the bits of data you need e.g. names of trainers, dates etc. Some of these will be repeating e.g. staff names, course names. Others will not e.g. dates of delivery
So what I suggested was the following process
Step One – Create lists of your repeating entries
- In this case we began by creating a list of what would be repeated e.g. course names, trainer names, time slots. Add them to a sheet called List. You can download the sample file here and you can watch the video of how I have prepared those lists here.
Step Two – Create a table for your with the dropdown lists
- In another sheet – called Data Entry – set up a table with the headings you need and create dropdown lists for the repeating entries using data validation. You can download a file with some entries in it here and you can watch a video here of how you would set up your data entry sheet. I have also added a quick demo on how you would add a duplicate check to ensure that you don’t inadvertently double book a trainer for the same date and time slot.