Excel is very widely used to capture and display data, and its formulae can be used to implement calculations on data to generate insights and reports. Excel has the advantage of being accessible and familiar to a lot of people, and provides data processing without a complex infrastructure set up. For that reason, Excel is one of the most common tools used in data processing.

If you are creating and using Excel to capture, process and display data, you can increase its usability by following some best practice guidelines.

<aside> <img src="/icons/book-closed_green.svg" alt="/icons/book-closed_green.svg" width="40px" /> More Information

This detailed guidance has been created by the Government Analysis Function for producers of statistics who need to publish data tables in spreadsheet documents.

https://analysisfunction.civilservice.gov.uk/policy-store/releasing-statistics-in-spreadsheets/

</aside>

Contents

<aside> <img src="/icons/info-alternate_green.svg" alt="/icons/info-alternate_green.svg" width="40px" /> This advice does not cover VBA (Visual Basic for Applications) or Macros. Instead it focuses on simple layout, formatting and formulaic changes you can make to increase the usability and robustness of your spreadsheet.

</aside>

One Sheet, One Purpose

Imagine you are moving house. Do you find the biggest box you can possibly find and put all of your belongings in it all mixed up together? Or do you get a box for plates, a box for books, a box for bedding? Which system makes it easier to sort, protect and find your things?

In Excel you avoid the mega box chaos by creating one sheet for each task the workbook has to do, be that inputting of data, calculating an output, or displaying a report.

<aside> <img src="/icons/checkmark-square_green.svg" alt="/icons/checkmark-square_green.svg" width="40px" /> The golden rules:

1. Have only one table per sheet
2. Ensure each sheet is wholly used for input, calculation or reporting
3. Have the same formula for every row in a column

</aside>

What this gives you

• Simplicity: users know exactly what each sheet, and each column in each sheet, does
• Navigation: the sheet names point directly to the purpose of each sheet
• Relevance: if a user is just there to input data, they only need look at input sheets. Similarly, if a user is only there to view outputted reports, they only need to look at the reporting sheets.
• Compatibility: having regularly structured input tables makes the data much more machine readable, so it can be more easily ingested by code in the future
• Expandability: If you have tables next to or on top of each other, adding extra rows or columns can be a fiddle

Some signs you need to split your sheets up

• If there is both space for data input and calculation formulas in the same sheet
  • create a sheet for input and a sheet for calculation
• If you need to use different formulas inside the same column
  • split the rows that need different formulas into a new sheet
• If the table headers do not fit all rows of input data
  • create multiple input sheets, one for each type of data
• If you need to create titles for your tables so they are understandable
  • table titles should be sheet names, if the sheet name doesn’t describe all of the table then it is too complicated