- Data entry is quicker and easier
- Your spreadsheet is much more future-proof
- Everything will work better if the data is consistent
- You only have to set these parameters once
Some good practice
If we all created our spreadsheets as if they were going to become fundamental tools for our whole organisation to use, they probably would end up being more understandable, more interactive and more readily used. The key thing is to keep it simple.
- Name your columns. Name your columns clearly, so your grandma could understand what’s in them.
- Consider a tab which explains the workings of the spreadsheet, or a tab at the beginning which explains how the sheet is used. A few notes can make everything easier.
- Mark any new copies of the spreadsheet with a date that they can be deleted, so that it's easy to see immediately when you open a folder. Not only will this make it clear in the future what can be deleted, but it will also discourage people from making too many copies of a spreadsheet.
- Don't merge cells or hide data. Merged cells and hidden data can cause all sorts of problems. Be cautious of things like this which sacrifice usability for looks. If you have to do it, make sure you mention it in your help tab.
- Create reference boxes for fixed values that can change (such as VAT). Create a box that your formula then refers to, rather than just writing the amount into a formula. That way you can change all of your formulae in one go, rather than needing to edit every one.
Pro tip: the dollar signs lock the reference cell, so you could drag to copy this reference and C4 would stay the same whilst B2 would cycle to B3, B4, B5 etc.
- Limit the number of tabs within one spreadsheet. No more than what is visible along the bottom of the screen at one time. Too many more and they’ll just get missed, and your file will take ages to open.
Finally, one of the tricks I use the most in Excel is paste as values. This marvellous tool is vital when you’re pulling calculated data out for another purpose. If you just click paste (or CTRL V), hidden formulae will break, and you’ll get a N/A or error, but if you right click and choose paste as values, only the final value will paste, leaving your data lovely and clean.
Further Reading & Training
Treehouse has a course on excel
Microsoft has an extensive website on excel
Business Insider’s top ten things you can do with excel
“You can paint as long as you have excel!” This, on the Michaelangelo of Microsoft Excel is amazing
Or how about the awe-inspiring Spreadsheet Architecture of Emma Stevens
Last but not least, a song about the wonder that is pivot tables
Katie Cagney, Database & Research Manager, Tyne and Wear Archives and Museums, TWAM