Skip to main content

Excel calculation settings guidance

As part of ongoing data quality reviews we have identified a small number of examples of data being submitted through Strategic Data Collection Service (SDCS) with the excel calculation function set to manual.  This affects auto calculated cells and validations and can result in submissions being accepted into SDCS with errors in the data.

To remove this potential for error a further validation has been introduced to SDCS which will read the status of the calculation and return an error if an attempt is made to upload a submission with the calculation function set to manual. 

You will need to set the Calculation status to Automatic before attempting to resubmit the template.

How to check and set the calculation setting

The easiest way to see if your template is set to Automatic is to check the grey bar at the bottom of the Excel window when entering data.  As you input data you will see the word ‘Calculate’ at the bottom left if Excel is set to Manual and you will then need to reset the setting.

excel navigation ribbon with the calculation function set to calculate

Resetting the Calculation status can be done as follows:

Excel 2010 onwards:

  1. Open your file in Excel
  2. Click the Formulas tab at the top of the window

excel navigation ribbon with the tab formula highlighted

3. Click Calculation Options towards the right of the navigation ribbon.

excel document heading with the word formulas highlighted in red

4.  Three options are presented Automatic, Automatic Except for Data Tables, and Manual.  If any option is ticked other than Automatic, please click Automatic.

excel navigation ribbon with the word calculation options highlighted

If you save a template for repeated use, please be aware that opening the template whilst your local Excel setting is anything other than Automatic will result in the local setting being applied to the template. You will need to reset the Calculation Options before submitting. 

Last edited: 6 July 2020 12:44 pm