From the Not Just Numbers blog:
Yesterday I had a conversation with a client that I have had, in various forms, many times before.
It relates to using information in a spreadsheet from an external source.
Almost every time I find a client using data from their accounting system, or provided by a supplier, customer or other third party, they approach it in a way that significantly multiplies the work involved and increases the risk of errors.
It’s a shame because all that is required is a different approach, that rarely requires greater Excel skills than they already have.
In most cases the information is received as a spreadsheet – either generated from another software package, or emailed from a third party. Typically this information then needs to be edited and re-formatted to produce the desired end product – which might also have some pivot tables or charts based on the data.
The usual response to this situation is to do just that – take the spreadsheet that has been received and then edit it until it look s how they want it.
Unless this is a truly one-off situation, it is almost certainly the worst route to take, as you will need to do all of this editing again when you receive the next version of the data, next month, next week, or tomorrow!
Applying a little thinking to the situation suggests a much more efficient approach.
You will always receive this data in the same format, and will always require the same output format, so why not use this knowledge.
DON’T EDIT THE SPREADSHEET YOU RECEIVE!
Instead of editing the spreadsheet you receive, paste it into a blank sheet in a new workbook and create the output you want on the other sheets referring to the sheet with the data on it, but not editing the data itself at all.
Doing it this way, means that next month/week/day you can just paste the new data in and your job is done!
You can spend your time getting it right once – reducing the risk of errors – and remove all of the work in future.
Incorrect spreadsheets have hit the headlines in the US this week, with Excel errors discovered in the calculations behind a report by Harvard Economists Reinhart and Rogoff that has been used by Republicans to support budget cuts. Another timely reminder of the potential consequences of not getting these things right.
Click here for our our exclusive offer on Online Excel Training
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report “The 5 Excel features that you NEED to know”.