Cooking and data capture - what do they have in common?

Quite a bit actually. Unless cooking is your vocation and livelihood, it is quite likely that when you cook everyday, it becomes a bit of a chore. Boring. Sure, you may try spice it up every now and again with new recipes, new ingredients, but basically, you do it because you need to prevent unrest and strikes in your home. You would probably prefer to spend your cooking time doing something you enjoy, like exercising, playing outside with the kids, or drinking a bottle of wine with a good book.

Data capture is also not something too many people enjoy, especially when it becomes tedious, boring and, lets be frank, mind numbing. You would probably prefer to be doing something more productive with your time, like actually analysing the data you have input. Or reporting on your data. Or drinking a bottle of wine with a good book...

But there is hope. Just like a "Meals in 30 minutes" recipe book can revolutionise the way you prepare the family grub, a couple of changes in the way you use Excel for data capture can revolutionise your working day.

Here are a couple of closely guarded family recipes for data capture success. Use them, enjoy them, the bottle of wine is optional:

1) Use a formula wherever possible.

This may seem like a ridiculously simple suggestion, but bear with me. Imagine you have a sales price list of products, which is simply the cost price of the product, plus a 30% markup.

You could easily (but slowly) take each product, line by line, and calculate the sales price. An even worse approach would be to hardcode each price. OR, you could do something like this (I've shown the formula for the first line):

Note that I 'locked' the cell with the markup (G3) into the formula. When you do this, you can autofill your list, and the formula will continue to look up cell G3. To do this, highlight the cell coordinate in your formula and press F4 on your keyboard. The little $ signs will appear next to the column and row coordinates. To lock just the row or just the column, continue to push F4 until the right coordinate is locked.

For more on autofill, please read my prior blog: http://excelangel.blogspot.com/2012/04/autofill-your-lifewell-as-least-your.html



The joy of this is that if you change your mark up in the future, it is literally a 10 second job to update your pricelist, not a hour's worth of work.

You can make this step even easier by 'naming' your markup cell...but more on this in our subscribed newsletter. Sign up here.

http://www.excelangels.ie/faqs_and_weekly_tips_8.html

2) Break your data down into its simplest form

Taking our data example above, imagine your boss now tells you that all the blue and green products need to have 35% markup instead. Easy enough on 8 lines. Not so easy on 100. But if you just break your data up into its simplest form, it is easy to filter or sort and then adjust the formula. Like so:

3) Use Autocomplete if you need to type repeated words in a list

Autocomplete is a massive time saver. If you need to type in the full list of products from scratch, the first time you type in 'Gadgets', Excel logs the spelling and remembers it. If you then start typing in 'Gadgets' again a few cells down in your list, Excel will offer you 'Gadgets' after you type in a couple of letters. You can then hit enter to select it, or type over it if you want a different word.

This not only saves you time, but also ensures that all your 'Gadgets' are exactly the same, making sorting, vlookups, counting etc much more effective. One thing to note though, you can't have gaps in your list for this to work. Any gaps, and Excel loses it's memory.

If you find your Autocomplete is not working, ensure it is selected by going to File - Options - Advanced, and make sure the box next to "Enable Autocomplete for Cell Values" is ticked.

So these are 3 little time savers. Hope they help. For more info, or for one to one training, check out our website at www.excelangels.ie

Happy Spreadsheeting. I'm off to find that bottle of wine...