Making ‘good’ Excel workbooks

image

This article lists what I like to check in my own Excel workbooks to make sure they’re ready to share

Naming

  • The file itself is named with a version number (mandatory), date (optional), and initials (optional)

Structure

  • There are no merged cells
  • There are no hidden rows or columns
  • All table columns have meaningful and unique headings
  • The table headings are frozen for better scrolling through large lists
  • A workbook with many tabs must have a contents tab with links to the other tabs, and those tabs must have a back link to that contents tab
  • Avoid putting multiple tables on the one sheet e.g. you would give Suburbs and Shops tables separate tabs

Data

  • Mandatory columns have no blanks
  • Unique columns have no duplicates
  • Where possible, large data tables must be sorted to highlight and bring known issues to the top

Formulas

  • Complex formulas are split into more cells if needed to show the incremental logic and avoid overlong formulas being concentrated in too few cells
  • There are no unintentional #NA or #ERROR values
  • Formulas are converted to values wherever possible
  • The workbook has no cells or formulas that refer to external files that your audience may not have on their own machines

Graphs

  • Graph headings are full sentences that describe what key insight the graph is illustrating
  • Graph colours are set to greys, with only one accent colour used for drawing attention to the key insight in the graph
  • Graphs should have a key immediately under the heading
  • Graph data should be sorted logically i.e. by date, or in ascending/descending by amount
  • Graph gap width should be set to 20%
  • Relevant data points should be labeled or annotated

Smarts

  • VBA should be used as a last resort solution because it obscures the mechanics of the workbook
  • Conditional Formatting and Data Validation should apply simple/maintainable rules
  • I doubt the value of restricting Excel with these smart settings because too much time is spent anticipating potential issues and Excel can’t enforce these rules when edited in certain scenarios anyway e.g. SharePoint/MsTeams, computers that restrict VBA, when a user copies your whole template into an entirely new workbook to unblock editing of any column

Related Reading