1. Setting standards and best practice

The FAST Standard Organisation

Is Excel the right tool for the job?

Excel worksheets has 1048576 rows and 16384 columns. There is no reason for Microsoft to give us all these rows and columns because Excel cannot handle this amount of cells. Excel is a spreadsheet for analysis and calculations not a database.

4 Do’s and Don’ts

1.      Do plan your spreadsheet

Spending time planning a new spreadsheet is a very good investment. Too many waste a huge amount of time because of bad or no planning when it comes to spreadsheet design. Spreadsheets must be structured right to be usable.

2.     Do focus on Output before Input

Don’t focus on the input creating spreadsheets but the output. Too many want it to be easy to enter the data in the spreadsheet and make the spreadsheets for the eye not for output. Do we need to use PivotTables to analyse the date we must have the source data in a list not in a tabular table. If we want to query the data from an external workbook the source data must be in a list starting from A1.

3.     Don’t Merge cells

Merged cells in Excel can cause limitations in the spreadsheets. You have tools in Excel to provide you with the same effect as merge cells can do, without causing any problems.

4     Don't Leave Blank Rows or Columns When Entering Related Data

KEEP RELATED DATA TOGETHER

Leaving blank rows or columns in data tables or related ranges of data can make it very difficult to properly use a number of Excel's features such as charts, pivot tables, and certain functions.

2. Planning a Workbook

Identifying the audience and co-users

Make sure that you identify the audience and co-users when planning a new workbook. If the workbook needs to visualise data in a report/dashboard make sure that your audience commit to the expectations of what they expect to be able to see in the report. 

Recognise inputs, workings and outputs

Keep inputs, workings and outputs strictly separated.

Creating consistency and durability

3. Designing a Workbook

Include a welcome and introduction sheet (see below)

Creating clear organisation of worksheets

Clear organising of the worksheets within your workbook can make your Excel workbooks easier to maintain, manage and to understand for co-users.

The Input Sheet:

 The input sheet should only contain the source data and nothing else. The input data should be organised in a list and not a tabular table. The list should be formatted as an Excel table which will save you a lot of time when new data is added. 

Organising source data

It is recommended to organise your data in list format as follows:

Best practice when writing functions and formulas

·       Limited use of nested functions

You can nest 60 functions in Excel. Nested functions make it much more difficult to understand Excel and increases the chances of making mistakes. Sometimes you have to nest functions to get the output you need, but many times it is not necessary.

·       Double check functions and formulas to make sure they return the right answer

In estimated 80% of all workbooks there are errors which cost companies a huge amount of money. Avoid this by testing the calculation before you start using the workbook.

·       Use the right functions

Many Excel users use Vlookup. This can slow down the computer’s memory and can take several minutes to open the workbook. The combination of Index and Match (and indeed the Xlookup)  can do the same as Vlookup and  is much faster.

Conditional formatting best practice

Conditional formatting is very useful to spot trends, useful information and errors.

Use of tables and lists

Only use tabular tables on the Output sheet (the report or the dashboard) and never on the Input sheet. Always have the source data in lists!

Use range names

Best Practice is to assign a descriptive name to a cell range. This makes it easy to remember the contents of the range. Your functions and formulas will be much easier to understand =Vlookup(StaffId,StaffList,2,False). The function looks up the staff id in the staff list.

5. Making tables dynamic

Converting data ranges to dynamic tables.

This tool is probably the most important tool in Excel and should be used for all source data in workbooks. This will save you a lot of time and your formulas and functions will be much easier to read and understand. All tools used in your worksheets will automatically update when you add or change the source data.

What is a Table?

In Excel, a table is a specially designated range of numbers. This special range of numbers has added functionality that other cell ranges do not have. You can have more than one table in a workbook or worksheet if you want, and tables can be as large or small as the amount of data you want to work with. Here is an example of a Table below:

6. Working with Links

Links Between Workbooks

Avoid inter-workbook links wherever possible: they are slow, easily broken, and not always easy to find and fix.

If you cannot avoid using linked workbooks, try to have them all open rather than closed and open the workbooks that are linked to, before you open the workbooks that are linked from.

 Links Between Worksheets

Using many worksheets can make your workbook easier to use, but generally it is slower to calculate references to other worksheets than references within worksheets.