Use macros and templates to automate spreadsheet software
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:
Identify the automation requirements of the spreadsheet application with stakeholders
Design a spreadsheet model to automate the required functionality
Produce a plan to deliver the required macro sequences
Implement templates to facilitate the macro processing of data in line with requirements
Design the required formulae and functions to deliver the outputs required
Develop spreadsheet macros in line with requirements
Test macros deliver the required outcomes
Use spreadsheet tools to implement macro-operation correctly
Save macros for reuse in other worksheets in line with organisational requirements
Implement chart and graph templates to automate chart production
Knowledge and Understanding
You need to know and understand:
- What automation steps are required to be implemented in spreadsheet operations
- The main principles of spreadsheet macro development
- How to design spreadsheet automation models
- How to design macros for use in spreadsheets
- How to build and record spreadsheet macros
- How to save and deploy spreadsheet macros
- How to sort and filter data as part of automating spreadsheet operations
- How to use spreadsheet chart templates
- How to integrate spreadsheet operations
- How to identify cell references for discrete use in spreadsheet macros
- How to work with multiple data series in spreadsheets
- The need to unit test macro steps to ensure correct operation
- How to implement usability testing of an integrated template and macro supported spreadsheet system
- The need to use representative data when testing spreadsheet automation procedures
- How to allocate macros to toolbars