When creating spreadsheets think about the en product. Ask yourself if there are any calculations or formatting that you can add that would make data entry or manipulation easier.
A client I’ll call Jo, showed me a spreadsheet. They were calculating annual leave. When a person went on leave, Jo would tpe the start and end date into columns then count how many days using a calendar to input. This number of days column was added up and subtracted from amount entitled to.
It is necessary for Jo to look at a calendar to count the number of days? No, there is a function called Networkdays which will calculate how many weekdays between 2 dates. It is not in the default function list, but needs to be added in.
I added another column for number of Bank holidays, as the function does not automatically include those, then in the number of days column used Networkdays and subtracted number of bank holidays to create total days on leave.
Thinking ahead and working backwards to create a spreadsheet takes longer to set up, but can save hourse per day. If you feel your knowledge of excel is not quite as good as an excel constultants, or you need a fresh pair of eyes, hiring a consultant could save you time.
