Overview

Framework:
RQF
Level:
Level 2
Unit No:
H/618/3695
Credits:
3
Guided learning hours:
24 hours

Aim

Learners will develop an understanding of spreadsheet software tools and techniques to be able to produce spreadsheets.. They will learn how to apply advanced formulae and functions to process data and use automated tools to aid productivity.

Unit Learning Outcomes

1

Understand the features of spreadsheet software and how spreadsheets are used.

  • A spreadsheet stores, sorts, manipulates and analyses data and presents it in tabular form, most common uses, to create budgets, to forecast future performance, calculate tax, completing basic payroll, producing charts and calculating revenues.
  • Uses: support decision making, presenting/analysing/manipulating data, perform calculations, cost modelling, stock control, repetitively and accurately performing calculations e.g. payroll, statistics, list management e.g. searching large datasets, interpreting data using sorting and filtering.
  • Features to improve productivity, accuracy and usability (aid the presentation and output of data): cell replication and formatting, functions, page setup, graphs and charts etc, automated features (macros to automate tasks).

Assessment Criteria

  • 1.1

    Explain how spreadsheets are used for different purposes.

  • 1.2

    Explain how features in spreadsheet software can be used to improve productivity, accuracy and usability.


2

Be able to create a spreadsheet to improve productivity.

AC 2.1, 2.2:

  • Advanced formulae and functions, automated features: worksheets (headers, page breaks, links), cell manipulation (entering and editing data, auto filling, replication, conditional formatting (to highlight outcomes), cell formatting (colours, shading, merging cells, alignment), data manipulation (filters, sorts, pivot tables), formulae (add, subtract, divide, multiply), functions (sum, average, min, max, count, countIF, lookup, index), logical functions (IF, AND, OR, NOT), data validation, relative and absolute cell referencing, boxes (lists, drop-down), lookup tables, nested IF functions, cell/worksheet protection, conditional formatting, named ranges, relative and absolute cell referencing, macros, alternative file formats (PDF, HTML, CSV, XML, TXT).

AC 2.3:

  • Tools and techniques: insert and delete rows and columns, page layout, e.g. margins, orientation, header and footer, format numerical data, e.g. integer, real, decimal, currency, date, autofill, copy and paste, move, replicate formulae, formatting tools, e.g. height/width, wrap text, merge cells, styles, e.g. bold, underline, italics, borders/shading, colours.

AC 2.4:

  • Graphical format: types of charts and graphs (bar, pie, line etc), chart and graph formatting (titles, axis, labels, legend, resizing).

Assessment Criteria

  • 2.1

    Create a spreadsheet using advanced formulae and functions to process data.

  • 2.2

    Use automated features to aid productivity.

  • 2.3

    Use tools and techniques to edit and format spreadsheet data.

  • 2.4

    Display spreadsheet data in a graphical format.


3

Be able to review and refine a spreadsheet to improve usability.

AC 3.1:

  • Check for: functionality, accuracy and usability in relation to accuracy of numbers, formulas and any text; suitability of charts and graphs; layout and formatting; validity and accuracy of analysis; sorting out errors; use of reveal formulae; checking that user requirements met.

AC 3.2:

  • Review spreadsheet in relation to user requirements, fitness for purpose, strengths and improvements.

Assessment Criteria

  • 3.1

    Check spreadsheet for accuracy making refinements as necessary.

  • 3.2

    Review how the spreadsheet meets requirements in relation to productivity and usability.