Certificaton/3 Objectives
Level 1 A level 1 user can use a range of basic tools and techniques to produce, present and check spreadsheets that are straightforward or routine. Any aspect that is unfamiliar will require support and advice from others.
Calc software tools and techniques will be defined as ‘basic’ because:
- the range of data entry, manipulation, formatting and outputting techniques are straightforward;
- the tools, formulas and functions involved will be pre-determined or commonly used (for example, sum, divide, multiply, take away and fractions); and
- the structure and functionality of the spreadsheet will be pre-determined or familiar.
A level 2 user can select and use a wide range of spreadsheet software tools and techniques to produce, present and check spreadsheets that are at times non-routine or unfamiliar. Any aspect that is unfamiliar may require support and advice from others.
Calc software tools and techniques will be defined as ‘intermediate’ because:
- the range of data entry, manipulation and outputting techniques will be multi-step and at times non-routine or unfamiliar;
- the tools, formulas and functions need to analyse and interpret the data requires knowledge and understanding (for example, mathematical, logical, statistical or financial); and
- the user will take some responsibility for setting up or developing the structure and functionality of the spreadsheet.
A level 3 user can select and use a wide range of advanced spreadsheet software tools and techniques to produce, present and check spreadsheets that are complex and non-routine.
Calc tools and techniques will be defined as 'advanced' because:
- the range of data entry, manipulation and outputting techniques will be complex and non-routine;
- the tools, formulas and functions needed to analyse and interpret the required information requires complex and non-routine knowledge and **understanding (for example, data restrictions, data validation using formula, pivot tables, data maps); and
- the user will take full responsibility for setting up and developing the functionality of the spreadsheet.
LEVEL 1
At level 1, the competent person will
Use a spreadsheet to enter, edit and organise numerical and other data (learning outcome)
To demonstrate this competence they can
- 1.1 Identify what numerical and other data is needed in the spreadsheet and how it should be structured
- 1.2 Enter and edit numerical and other data accurately
- 1.3 Combine and link data across worksheets
- 1.3 Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions where available
- 1.1 Identify what numerical and other data is needed and how the spreadsheet should be structured to meet needs
- 1.2 Enter and edit numerical and other data accurately
- 1.3 Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions where available
Use appropriate formulas and tools to summarise and display spreadsheet information
- 2.1 Identify how to summarise and display the required information
- 2.2 Use functions and formulas to meet calculation requirements
- 2.3 Use spreadsheet tools and techniques to summarise and display information
Select and use appropriate tools and techniques to present spreadsheet information effectively
- 3.1 Select and use appropriate tools and techniques to format spreadsheet cells, rows and columns
- 3.2 Identify which chart or graph type to use to display information
- 3.3 Select and use appropriate tools and techniques to generate, develop and format charts and graphs
- 3.4 Select and use appropriate page layout to present and print spreadsheet information
- 3.5 Check spreadsheet information meets needs, using IT tools and making corrections as appropriate
Content examples
**Store and retrieve
Files (eg create, name, open, save, save as, print, close, find)
**Page layout
Size, portrait, landscape, margins, header and footer, page breaks, page numbering
* Analysis and interpretation of spreadsheet data
Automatic sub-totals and sorting a cell range, relevant common methods for simple data
* Check spreadsheet data
Accuracy of numbers and any text
* Formatting techniques for charts and graphs
Title, chart type, axis titles, legend
* Formatting techniques for rows and columns in spreadsheets and tables
Height, width, borders and shading
* Formatting techniques for spreadsheet cells
Numbers, currency, percentages, number of decimal places, font and alignment
* Functions and formulae
Formulas with a single operator (eg SUM function), percentages
* Enter and edit spreadsheet data
Insert data into single cells, replicate data, add and delete rows and columns, clear cells, cell data types
* Spreadsheet structure
Worksheet components (eg cells, rows, columns, tabs, pages, charts, workbooks, worksheets) and lay out
* Numerical and other information
Numbers, charts, graphs, text, images
LEVEL 2
At level 2, the competent person will
Use a spreadsheet to enter, edit and organise numerical and other data
- 1.1 Identify what numerical and other data is needed in the spreadsheet and how it should be structured
- 1.2 Enter and edit numerical and other data accurately
- 1.3 Combine and link data across worksheets
- 1.4 Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions where available
Select and use appropriate formulas and data analysis tools to meet requirements
- 2.1 Identify which tools and techniques to use to analyse and manipulate data to meet requirements
- 2.2 Select and use a range of appropriate functions and formulas to meet calculation requirements
- 2.3 Use a range of tools and techniques to analyse and manipulate data to meet requirements
Select and use tools and techniques to present and format spreadsheet information
- 3.1 Plan how to present and format Calc spreadsheet information effectively to meet needs
- 3.2 Select and use appropriate Calc tools and techniques to format cells, rows and columns of information
- 3.3 Select and format Calc charts and graphs as appropriate to display selected information
- 3.4 Select and use an appropriate Calc page layout to present and print spreadsheet information
- 3.5 Check Calc spreadsheet information meets needs, using IT tools and making corrections as appropriate
- 3.6 Describe how to find errors in spreadsheet formulas
- 3.7 Respond appropriately to any problems with Calc spreadsheets
Content examples
* Problems with spreadsheets
Using help; sorting out errors in formulas, circular references
* Store and retrieve
Files (eg create, name, open, save, save as, print, close, find, share); version control; import/export; file size; folders (eg create, name)
* Page layout
Size, portrait, landscape, margins, header and footer, page breaks, page numbering, columns, adjust page set up for printing
* Analysis and interpretion of spreadsheet data
Automatic sub-totals and sorting a cell range, filter rows and columns, relevant common methods for more complex data
* Check spreadsheet data
Accuracy of numbers and any text, accuracy of results, sorting out errors in formulas, validity of analysis, layout and formatting
* Formatting techniques for rows and columns in spreadsheets and tables
Height, width, borders and shading
* Formatting techniques for spreadsheet cells
Numbers, currency, percentages, number of decimal place, dates, font, alignment, colour, shading and borders
* Functions and formulae
Mathematical, statistical, financial, relational
* Enter and edit spreadsheet data
Insert data into multiple cells at once, replicate data, use absolute and relative cell references, add data and text to a chart
* Spreadsheet structure
Worksheet components (eg cells, rows, columns, tabs, pages, charts, workbooks, worksheets, panes) and lay out
* Numerical and other information
Numbers, charts, graphs, text, images
LEVEL 3
At level 3, the competent person will
Use a spreadsheet to enter, edit and organise numerical and other data
- 1.1 Identify what numerical and other data is needed in the spreadsheet and how it should be structured
- 1.2 Enter and edit numerical and other data data accurately
- 1.3 Store and retrieve spreadsheet files effectively, in line with local guidelines and conventions where available
Select and use appropriate formulas and data analysis tools and techniques to meet requirements
- 2.1 Explain what methods can be used to summarise, analyse and interpret spreadsheet data and when to use them
- 2.2 Select and use a wide range of functions and formulas to meet calculation requirements
- 2.3 Select and use a range of tools and techniques to analyse and interpret data to meet requirements
- 2.4 Select and use forecasting tools and techniques
Use tools and techniques to present, format and publish spreadsheet information
- 3.1 Explain how to present and format spreadsheet information effectively to meet needs
- 3.2 Select and use appropriate tools and techniques to format cells, rows and columns effectively
- 3.3 Select and use appropriate tools and techniques to generate, develop and format charts and graphs effectively
- 3.4 Select and use appropriate page layout to present, print and publish spreadsheet information
- 3.5 Explain how to find and sort out any errors in spreadsheet formulas
- 3.6 Check spreadsheet information meets needs, using IT tools and making corrections as appropriate
- 3.7 Use auditing tools to identify and respond appropriately to any problems with spreadsheets
Content examples
* Forecasting tools and techniques
What-if scenarios, goal seek; data tables; views
* Problems with spreadsheets
Using help; sorting out errors in formulas, circular references, calculations and results; data validation, locate invalid data
* Store and retrieve
Files (eg create, name, open, save, save as, print, close, find, share); version control; import/export; file size; file properties; folders (eg create, name); archive (backup, restore)
* Page layout
Size, portrait, landscape, margins, header and footer, page breaks, page numbering, columns, adjust page set up for printing
* Analysis and interpretion of spreadsheet data
Automatic sub-totals and sorting a cell range, filter rows and columns, data restrictions, data validation, adding messages to data, using formulae to determine valid entries for cells, displaying by interest, pivot tables, data maps
* Check spreadsheet data
Accuracy of numbers and any text, accuracy of results, sorting out errors in formulas, layout and formatting validity, relevance and accuracy of analysis, interpretation of calculations and results
* Formatting techniques for charts and graphs
Title, axis titles, legend, change chart type, move and resize chart, axis scale, annotation, layout, pivot table reports
* Formatting techniques for rows and columns in spreadsheets and tables
Height, width, borders and shading
* Formatting techniques for spreadsheet cells
Numbers, currency, percentages, number of decimal place, dates, font, alignment, colour, shading and borders; conditional formatting
* Functions and formulae
Mathematical, statistical, financial, relational, look-up, arguments, arrays and formulas for validating data
* Enter and edit spreadsheet data
Insert data into multiple cells at once, replicate data, use absolute and relative cell references, add data and text to a chart, hide and protect cells, create, modify and merge multiple copies of a shared workbook
* Spreadsheet structure
Worksheet components (eg cells, rows, columns, tabs, pages, charts, workbooks, worksheets, panes) and lay out
* Numerical and other information
Numbers, charts, graphs, text, images