08 August, 2021

Workbook design: basic principles

Design your workbook for both EFFICIENCY and LOOK & FEEL.
  • First of all - plan it well for your specific purpose, in order to avoid complications down the road. In cases where there is complexity of data and calculations, use multiple worksheets within the workbook and label them properly (name them by their purpose) to separate clearly data input, assumptions, calculations, output, tables, charts, etc.
  • Use highlighting/shading to differentiate, in particular, data entry parts (input) from outcome (results, report).
  • If you design the workbook for multiple users, add instructions and documentation, record of changes, wherever needed.
  • Use Comments or Callouts features to add meaningful explanatory notes to some cells, e.g. to explain more complex calculations, especially for external users.
  • Break formulas involving complex calculations into multiple cells to make any troubleshooting much easier. Use cell references rather than numbers (constants) to avoid errors related to input. Any assumptions must be clearly visible in worksheet layout.
  • Organize your worksheets vertically rather than horizontally. This improves flow of calculations.
  • Plan for logical constraints on data entries to avoid results that don't make sense or appear false. Use =IF(logical test, value if true, value if false) formulas and highlight cells resulting in false for further review.
  • Use also Data Validation feature to create certain restrictions for user input. E.g. include some limits for entering dates, other numbers or text data). This will improve accuracy of data being entered.
  • Be clear and consistent about used units and number formats!
  • Avoid blank rows and columns within ranges of data. They can be treated as the end of your data in some cases.
  • Sort your data, where possible, in a logical order. This will make some calculations easier.
  • FORMATTING - keep it efficient and SIMPLE.
    • Use the Format Painter (available in the ribbon) to automate formatting where possible. Do not overuse formats!
    • Change the gridlines in your workbook to a light color, or even turn them off, for better look (File>Options>Advanced>Display options for this worksheet>Gridline color). Use Borders formatting tool where necessary.
    • Avoid merging cells; it causes all kinds of problems. Use "Center across selection" instead.
    • Avoid hiding data. This increases risk of errors.
    • Preferably, use 12-point Calibri font, because it is well readable for both numbers and text. Limit use of different fonts to minimum.
    • Pay attention to formatting Dates, especially if they will be used in formulas.
    • Bold the font of headings.
    • Keep Conditional Formatting simple, if you need to apply it, at all.
  • Always use Excel's solutions before resorting to VBA macros. Avoid the use of macros, if possible.
  • Protect (Lock) cells used for formulas, functions and calculation details. Usually the only unlocked cells should be those used for user input. Remember that cells protection takes effect only then when you protect the worksheet afterwards.
  • Protect also the whole workbook if you consider it necessary. Keep in mind, however,  that if you forget your password, its recovery in Excel may not be possible due to a very strong encryption.

 

No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.