Preface

 

This workbook is a general instruction guide for analysts on how to make their Excel worksheets update automatically and contain other dynamic elements. This will prevent you from having to copy and paste data, formulas, and update charts. It will also reduce the number of errors and the time it takes to update data in Excel.

There are four chapters, each for a method of automating the spreadsheet with specific examples and how-to steps outlined in each:

  1. Chart auto update
  2. Set constant baseline period in chart
  3. Add a record to a table when there is new data in the data sheet
  4. Automatically update the table or chart title when the table has been updated

Example Excel workbooks have also been created and are linked to in the individual chapters when they are used.

  1. HSMR_TableA1a_Automated
  2. HSMR_TableA1b_Automated

Tips

  • If R is used in the data wrangling/analysis process then the openxlsx package can be used to automatically populate the data tab, again decreasing manual steps in the workflow.
  • Before starting to design/re-design an Excel table, consider all the data requirements to populate the content. This will include not just standard data items such as figures and labels, but also items required for titles and footnotes. This many require additions to, or re-structuring of, the underlying data.
  • The ‘dynamic ranges’ formula, offset(), in Excel will be key when creating charts which update automatically, so it will be useful to be familiar with this.
  • VLOOKUP will be used to search data and create cell content.
  • In order to create drop-down selection, we recommend using an alternative method to macros by clicking: Developer > Insert > Form Controls > Combo Box (see ‘HSMR_TableA1a_automated.xlsm’ for examples.)