Here are my six top tips. You can download them as a useful checklist.
- Ensure users know which file to use (version control)
Before
After
- Name files clearly including date or version number.
- Move old files to sub-folders so that the main folder contains only the final version e.g., move prior versions sent out or used in meetings to “Archive” and back-ups to “zzz old”
-
Bonus tip: save space! In Windows explorer search for “zzz old”. Any folders that are older than (say) 6 months can be deleted as content is unlikely to be needed.
- Restrict access to authorised users only
- Save files in server location with restricted access e.g., only finance department.
- Add password to open or change, if appropriate: File, Save As, More options (under file name and type), Tools (next to Save button), General options…
- Develop workbooks using best practice rules for clarity and ease of use
- Add a cover sheet showing contact person, workbook status and purpose, a list of sheets with short descriptions --> so users get a good overview and know who to contact in case of questions.
- Use a clear workbook structure with separate sections for inputs, calculations, and outputs --> so users can understand the structure and easily find their way around.
- Add an instructions sheet --> so users know how to use the workbook.
- See my separate blog on 'Gary's Golden Ground-Rules' for details.
- Clearly mark input cells so users know what they may change
- Use consistent formatting e.g., light grey with white border, protection turned off.
- Define and use cell styles (on the Home ribbon) for ease of use.
- Define: Click the drop-down arrow at the lower right and select "'New Cell Style' to define.
- Use: Select cells to be formatted, then click on the relevant cell style in the ribbon.
Bonus tip: If you didn’t format your input cells as you created them, you can easily do that later. Here’s how to find and select all input cells on a sheet, to format them using your input cell style.
- Select all cells using the mouse or shortcut Ctrl A (select all).
- Press F5 --> the “Go To” dialogue box appears.
- Click “Special…”, Constants and ensure only “Numbers” is selected, then click OK.
- Now all input cells are selected – click on your input cell style to format them all in one go.
- Use data validation to restrict inputs to valid entries
- Restrict inputs e.g., to
- integers, e.g. between 1 and 12 for months (see screenshot above)
- decimals (incl. percentages) e.g. only positive (>=0) or only negative numbers (<=0)
- a list (dropdown)
- custom e.g., date is a Monday.
- See my blog or my video for details.
- Protect worksheets, so only input cells can be changed
- Ensure formula cells are locked and unlock input cells - define and use cells styles for efficiency.
- Turn protection on for each worksheet, if desired with password
- Use macros for efficiency:
- Macro #1: Protect all sheets upon opening file.
- Macro #2: Remove all sheet protection by entering a password, to enable changes.
- See my separate blog for details.
Useful links
- Here you can view my one-hour webinar video explaining all six tips.
- Or use this link to Plum Solutions (who kindly organised the webinar) to also get access to the webinar downloads.
- For more details on how to avoid errors of all kinds, please read my book “Avoid Excel Horror Stories”, available on Amazon.
