The qualifying learner is capable of:
Validating and sorting data in a spreadsheet.
Applying security settings to a spreadsheet.
Using a range of functions within a spreadsheet.
Using formula, mixed references and named ranges to provide solutions to a problem.
Auditing formula to ensure data integrity.
Creating and edit scenarios.
Course Content
Validating and sorting data in a spreadsheet
- Data validation is applied to data in a spreadsheet to ensure consistency and integrity. Data
validation includes but not limited to text length (must be limited to at least ten [10]
characters). - Validated data properties are edited in accordance with user requirements.
- Data validation is removed from data in accordance with changing spreadsheet requirements.
- A simple data sort is performed in order to organise information for ease access. Sorting
includes but is not limited to ascending order, descending order. - Data is sorted by columns to organise information in accordance with user requirement.
- Data is sorted by months and weekdays in order to provide breakdown summaries.
– See fewer details
Apply security settings to a spreadsheet
- Security settings are explained in terms of their use.
- Security settings are applied to restrict and/or limit access to information.
- Security settings are changed in accordance with user requirements.
- Security settings are removed in accordance with changing requirements.
+ See more details
Use a range of functions within a spreadsheet
- Date and time functions are used to manipulate chronological data.
- Mathematical functions are used to facilitate the efficiency of calculations. Mathematical
functions include but are not limited to SumIf, RoundDown, RoundUp, Even, Odd. - Statistical functions are used to analyse statistics. Statistical functions include but are not
limited to CountIf, Counta, Trend, Large, Small. - Financial functions are used to facilitate the efficiency of monetary calculations. Financial
functions include but are not limited to FV, NPV, PMT, IPMT, NPER, PV, Rate. - Subtotalling features are inserted and removed to provide data breakdown for analyses.
Subtotaling includes but is not limited to Individual subtotals, Nested subtotals. - Text functions are used to facilitate the formatting of data for reporting purposes. Text
functions include but are not limited to Upper, Lower, Proper, Concatenate. - Lookup and Reference functions are used for retrieving data. Functions include but are not
limited to Lookup VLookUp, HLookUp. - Logical functions are used to select and organise data. Logical functions include but are not
limited to IF, AND, OR, NOT, TRUE, FALSE, nested IF function. - A mixed reference is used in a formula in order to analyse data.
- A named range is used in a formula to select a data source or sub-source.
+ See more details
Use formula, mixed references and named ranges to provide solutions to a problem
- The input to a result is found in a formula by adjusting a value.
- Scenarios are created to demonstrate different possible outcomes.
- Scenarios are edited to establish different outcomes.
- A scenario summary report is created to provide an overview of the implications of the data.
- Ranges are create and used to select specific data.
- Named ranges in a spreadsheet are analysed in terms of their use and impact. Cells, formula,
labels. - A named range is created to facilitate referencing of data. Range includes but is not limited to
cells, formula, and labels. - A named range is deleted and/or edited in accordance with user requirements.
- A named range is navigated in order to access named data.
+ See more details
Audit formula to ensure data integrity
- An error in a formula is traced in order to identify the correct data to be required.
- An error in a formula is edited to rectify data in accordance with data integrity requirements.
- Precedent and dependent cells are traced for a selected cell in order to determine the value of
other cells. - Trace precedent and dependent arrows are removed in accordance with user requirements.
- Formulae are displayed and viewed in order to ensure that the value required is accurately
determined in a worksheet - Formula are hidden from view in a worksheet to facilitate focus on overall data presented.
+ See more details