Use macros and templates to automate spreadsheet software

URN: TECHDUSS3
Business Sectors (Suites): IT Users
Developed by: e-skills
Approved on: 2022

Overview

This standard is about using macros and templates to automate spreadsheet software.

This involves setting up macros to perform repetitive tasks and templates to enhance the user interaction and appearance. It includes using spreadsheet features and tools to visualise data using charts and graphs.

This standard is for those who need to create macros to automate spreadsheet functions to meet their own needs or as part of their duties.


Performance criteria

You must be able to:

  1. Identify the automation requirements of the spreadsheet application with stakeholders

  2. Design a spreadsheet model to automate the required functionality

  3. Produce a plan to deliver the required macro sequences 

  4. Implement templates to facilitate the macro processing of data in line with requirements

  5. Design the required formulae and functions to deliver the outputs required

  6. Develop spreadsheet macros in line with requirements

  7. Test macros deliver the required outcomes

  8. Use spreadsheet tools to implement macro-operation correctly

  9. Save macros for reuse in other worksheets in line with organisational requirements

  10. Implement chart and graph templates to automate chart production


Knowledge and Understanding

You need to know and understand:

  1. What automation steps are required to be implemented in spreadsheet operations
  2. The main principles of spreadsheet macro development
  3. How to design spreadsheet automation models
  4. How to design macros for use in spreadsheets
  5. How to build and record spreadsheet macros
  6. How to save and deploy spreadsheet macros
  7. How to sort and filter data as part of automating spreadsheet operations
  8. How to use spreadsheet chart templates
  9. How to integrate spreadsheet operations
  10. How to identify cell references for discrete use in spreadsheet macros
  11. How to work with multiple data series in spreadsheets
  12. The need to unit test macro steps to ensure correct operation
  13. How to implement usability testing of an integrated template and macro supported spreadsheet system
  14. The need to use representative data when testing spreadsheet automation procedures
  15. How to allocate macros to toolbars

Scope/range


Scope Performance


Scope Knowledge


Values


Behaviours


Skills


Glossary


Links To Other NOS


External Links


Version Number

1

Indicative Review Date

2025

Validity

Current

Status

Original

Originating Organisation

ODAG Consultants Ltd.

Original URN

TECHDUSS3

Relevant Occupations

ICT for Users

SOC Code

4159

Keywords

Data management, spreadsheet