Prevent Excel user errors

Here are my six top tips. You can download them as a useful checklist.

  1. 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.

 

  1. 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…

 

  1. 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.

 

  1. 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.

 

  1. 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.

 

  1. 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.