Nikki blog covers her thoughts on many (non-Excel) subjects but the post she brought to my attention was a cautionary tale about an Excel mishap that recently befell her.
This got me to thinking that it might be useful to look at what things we should avoid in Excel.
I am not talking about good and bad practice here, I am talking about the things that can lead you to irretrievably screw up your spreadsheet! Those that throw all of your formulae out, or better still, cause that wrench in the pit of your stomach, the second you press the Enter key.
I'll start with Nikki's, throw in a few of my own, and then throw it open to the floor in the comments.
Nikki's nightmare was caused by forgetting that she had multiple sheets selected, causing her to overwrite everything she had been working on in the other sheets.
You can read Nikki's full nightmare here, it's not for the faint-hearted!
One of my nightmares that I have written about before is the dangers of cutting and pasting, or dragging and dropping.
For more detail on this one, have a look at my full post on it.
In short, cut and paste, or drag and drop, alter any formulae that refer to the cells being moved. This can mess up your whole spreadsheet, if that's not what you intended. It can be very difficult to pick out what has happened too.
Another one to be wary of is compatibility issues. If you have pivot tables in an Excel 2003 workbook and decide to save this as a .xlsx file in a later version of Excel - don't expect the pivot tables to work if you then try to save it as a .xls file again.
And the last one from me - don't add protection to a workbook or worksheet and apply a password, without making sure you have made a note of it somewhere you will find it next time you need it. It sounds obvious, but we've all done it!
I'm sure you've all got your own, so don't be shy. Please share them in the comments, so that your pain has not been in vain and it can help others to avoid the same fate.
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".
Good blog.
ReplyDeleteExperience has taught me to save work often. I work with complex project finance spreadsheets (circa 5-20Mb). Ctrl + S (shortcut for save) is used every 10 mins or so. A new version of the file is also saved in the directory everytime significant changes are made to the spreadsheet. I had 49 versions of one spreadsheet on one project spanning 6 months or so - which became a handy reference when I was asked for a summary of how the project had morphed over time.
Password protecting a worksheet seems to be a waste of time as there are free utilities out there that can break them in a matter of seconds.
Do you know that Excel gives you the opotunity to make a back up of your file when you save. This is an option you can activate when you save As.
DeleteThere is a button menu called Tools which gives you different options such as "Web options", "General options". In that last one, where you can protect the file, there is checkbox you can tick to make a backup of your file.
So that, each time you save your file, a copy of the workbook before any changes is saved with a xlk extension.
This can be very usefull in your case.
regards