Best Practices

This is our definitive list of guidelines, suggestions, recommendations, etc
Thanks to Patrick O'Beirne (sysmod.com) for his contribution.
Number 1 - 25 are general recommendations.
Numbers 26 - 30 are recommendations involving VBA.

1) Save your workbook with the right file extension to avoid compatibility mode.
Make sure that no files are using the old 97-2003 file format.
Use a descriptive file name containing underscores or dashes (no spaces or full stops).

2) Have clear versioning of your workbooks.
Use Camel Case, avoid abbreviated words and never include words like "final" or "draft".
Create a folder structure that is intuitive and easy to navigate.

3) Use a predefined sheet template when inserting new worksheets.
It is very easy to create a default worksheet template (sheet.xltx) as well as a default blank workbook template (book.xltx).
These should contain all your default settings, font, theme, headers/footers, margins etc.

4) Have a file size less than 20 MB for maximum performance.
Large file sizes cause lots of problems especially if the files are being shared between different teams.
You can try the following suggestions to try and reduce the file size of your workbooks.

5) Check your calculation settings after opening and before saving.
The calculation settings are taken from the first workbook you open and then ignored in subsequent workbooks.
Setting your calculation to manual will speed up opening and closing files, but don't forget to calculate when required.

6) Remove unnecessary external links to prevent annoying popups.
Check your formulas, named ranges, chart source data, conditional formatting, data validation and shapes.
Any links coming in (or data being exported), should be clearly indicated on dedicated worksheets.

7) Remove circular references to improve stability.
These make a workbook slow and unstable - precision vs accuracy
You can modify the formula logic or use VBA to solve the formula.

8) Check the last populated cell on every worksheet.
Use the shortcut key (Ctrl + End).
Delete any unused rows or columns that are part of your "usedrange".

9) Move repeated formulas to their own cells.
Never use the same formula more than once.
Do not link to another link (no daisy chains).

10) Use simple formulas that fit on one line, of the formula bar.
Add parentheses and spaces into your formulas to improve readability.
Do not write multi-line formulas, nested IFs. Do not include constant values or references to entire rows or columns.

11) Use the correct lookup and reference function.
To lookup values in any column using Office 365 use XLOOKUP.
To lookup values in the first column use VLOOKUP.
To lookup values in any column use INDEX/MATCH (never OFFSET/MATCH).

12) Avoid using volatile built-in functions in your formulas.
Volatile functions constantly update when your calculation is set to automatic
There are 9 functions that are always volatile and 3 functions (inc. SUMIF) that can be, depending on the arguments.

13) Add error-checking formulas to help identify problems.
Use the IFERROR function, to return something else when its an error.
Use the ISERROR function, to return True or False if there is an error (instead of ISERR).

14) Use the default alignment for numbers and text.
Make sure the horizontal alignment is always set to "General" (numbers default to the right, text defaults to the left).
Avoid using merge cells because it creates problems copying and pasting. Use centre across selection instead.

15) Use embedded charts instead of chart sheets.
Make all your charts objects on worksheets, never use dedicated chart sheets.

16) Use cell styles to enforce consistent cell formatting.
Use a custom theme throughout the workbook and never apply manual formatting to any cells.
You do not need to use more than 20 different styles in a workbook.

17) Create a table when you have a list that has one row for each record.
Tables allow you to quickly sort, filter, aggregate, resize, export and format your data.
Combine tables with structured references for more intuitive formulas.

18) Use named ranges to refer to cells that are not close by.
Try and keep your named ranges defined at the workbook level and always use a clear and consistent naming convention.
Always use named ranges for references on different worksheets and include the type and purpose in the name.
Try and use an underscore when combining multiple words.
Never have more than 10,000 named ranges in a workbook and always delete any unnecessary or unused names.

19) Use data validation to prevent invalid entries.
Should be used to control the type of data being entered and to prevent the wrong data being entered.
Always use named ranges.

20) Use conditional formatting to automatically apply cell based formatting.
Emphasise differences easily using data bars, color scales and icon sets.
Quickly identify cells with duplicate values or values above or below the average.

21) Use the dd-mmm-yyyy (or dd-mmmm) date format to cater for both US and UK conventions.
Never use the "dd/mm/yy" date format because it can be easily confused with "mm/dd/yy".

22) Avoid using Ctrl Shift Enter array formulas in multiple cells.
An array formula can occupy a range of cells and be used to produce multiple results in different cells.
These formulas are hard to understand (and find) and individual cells cannot be changed.

23) Avoid using Ctrl Shift Enter array formulas in individual cells.
Often used for conditonal summing and conditional counting.
Curly brackets are optional in Office 365 because a lot of the functions (eg SUM) now support dynamic array formulas.

24) Avoid creating any unnecessary custom number formats.
Remove any unused number formats

25) Avoid hiding zero values unless there is a very good reason.
If you really want to hide the zero values then use the Advanced, Display Options, for that worksheet.
Do not use the custom number format "0;-0;;@" or conditional formatting with the same colour as the background.

Excel VBA Best Practices

These are Excel specific VBA recommendations. There is a different page for general VBA Best Practices.

26) Use the macro recorder to start exploring the power of VBA.
Each of the commands you perform while recording, will be saved, to allow you to play the macro again.

27) Use your Personal.xlsb file to share macros between workbooks.
Storing your macros in the Personal Macro Workbook will mean that they are available every time Excel is open.

28) Run your macro from a shortcut key for fast access.
When you record a macro or write a macro, shortcut keys can be easily assigned.

29) Run your macro from a worksheet button on the worksheet.
Place a worksheet control on the worksheet next to the relevant cell range.

30) Run your macro from the Quick Access Toolbar at the top of your application.
The Quick Access Toolbar can be customised from the Excel Options dialog box.