Spreadsheet Solutions

20 features for better spreadsheets

Part 18 of 22 - Locked worksheets and workbook

If you are using a spreadsheet, rather than making a spreadsheet, it should be locked. Unlocking data entry cells, and the protecting the workbook, is a basic requirement. Yes, this means you can’t over-write formulas, but that is the point. I even lock the spreadsheets I make which only I use. It is so easy to over-write something you shouldn’t, so lock your spreadsheets.

Please visit https://spreadsheetsolutions.biz/contact-us if you wish to get in touch with us for a new spreadsheet.

20 features for better spreadsheets

Part 17 of 22 - Adjustable 12 month periods

I so often see spreadsheets created for a 12-month period, which makes complete sense. But then come the end of that period, and people are trying to make a new template for the new year, adjusting all the headers and dates. At very least you should be able to open a new template of the spreadsheet, change the old year to the new year in ONE place, and have the rest update. If you really want to be adventurous, why not have a rolling 12-month period, so that you always have 12 months of data to view. Both are possible, and I usually use whichever is best for each case.

Please visit https://spreadsheetsolutions.biz/contact-us if you wish to get in touch with us for a new spreadsheet.

20 features for better spreadsheets

Part 16 of 22 - Hidden calculations

I often see spreadsheets with 32 columns, when only 5 of them are data entry, and 5 of them are useful to know. The other 22 columns are simply helper columns for workings. No harm in that whatsoever. The issue comes in when they are all in one messy lump. I’ve spoken about separating the data entry and formulated cells, but then keep the workings separate and hide them. Showing those only confuses the user. These columns don’t need to be in a logical order to follow the flow of the formula, but rather a logical order for the data entry and viewing.

Please visit https://spreadsheetsolutions.biz/contact-us if you wish to get in touch with us for a new spreadsheet.

20 features for better spreadsheets

Part 14 of 22 - Editable cells rather than hard-coded formulas

Hard coding means to type a number directly into a formula. For example, VAT on £100. At the time of writing this, VAT is at 20%. So, 100*20% would give you the VAT on £100. However, it may not stay at 20%. So, instead of then having to go through your entire spreadsheet trying to find all the VAT references, make a VAT Rate cell (on another tab even). Then reference all formulas to that cell. Then if it changes, simply change one cell and all the references to that cell update.

Please visit https://spreadsheetsolutions.biz/contact-us if you wish to get in touch with us for a new spreadsheet.