This Microsoft Excel Advanced Course is intended for people who need to plan, produce, use and spreadsheets to solve problems using a Graphical User Interface (GUI)-based spreadsheet application either as a user of computers or as basic knowledge for a career needing this competency, like the ICT industry.
An outline solution is developed to meet the requirements of a given brief.
The spreadsheet produced addresses the given problem.
Toolbar menus are switched off and on.
The view of the spreadsheet is enlarged and made smaller.
The way that the spreadsheet is viewed is changed.
Cells are frozen to prevent scrolling.
The default file location is changed.
A username is added to the file.
The purpose of using multiple worksheets within one spreadsheet file are explained with examples.
New worksheets are opened.
Worksheets are renamed in terms of their purpose within the solution to the given problem.
Each worksheet to have a different name, with a purpose within the solution.
Cells are manipulated between worksheets.
A worksheet within a workbook (or spreadsheet) is deleted.
Formulae are entered taking into consideration the natural order of operation.
Formulae are entered to deliberately change the natural order of operation.
Formula error messages are correctly interpreted, and the formula corrected accordingly.
The difference between relative and absolute cell addressing is demonstrated by using it in a formula.
Data are changed to test possible solutions to the given problem without having to change formulae.
Scenarios are created that demonstrate different possible outcomes.
Functions are explained in terms of their purpose, use and construct.
Simple Mathematical functions are applied to the given problem.
Statistical functions are applied that achieve the anticipated result.
Methods of automatically formatting a spreadsheet such as templates, styles and manuals are explained.
A spreadsheet is created using a template.
Cells are formatted using styles based on number, date, currency, percentage.
Formats are copied between cells.
Text is centred across a cell range.
The orientation of text within a cell is changed.
A border is applied to cells and removed from cells.
Fill (shading) is applied to cells and removed from cells.
The spreadsheet is evaluated for compliance with a given problem, appropriate formatting, readability, legibility, presentation, accuracy, and data integrity.
The spreadsheet is modified if required and compliance with the brief is confirmed.
Data is imported from an external text file to a spreadsheet. External text file includes but not limited to delimited text file (.txt), Comma-separated values text file (.csv).
Data is converted according to user requirements. Conversion includes but not limited to convert text to numbers, convert text to dates.
Data is exported to a text file from a spreadsheet. Text files include but not limited to at least two (2) of Formatted text (.prn), Text (.txt), Comma-separated values (.csv), Data Interchange Format (.dif), Symbolic Link (.slk).
The uses of formulae are analysed to determine their impact on linking and consolidating spreadsheets.
Data from a single worksheet is replicated across multiple worksheets and spreadsheets.
Data from multiple spreadsheets are consolidated and linked using a SUM function into one (1) worksheet.
Single/simple filter criteria are applied to data in a spreadsheet. Filter criteria include but not limited to at least two (2) of Filter for the smallest or largest number, Filter for blank or nonblank cells, Filter by specific text.
Complex filter criteria are applied to data in a spreadsheet.
Filters are removed to deselect the filtered information.
The use of forms on a spreadsheet is analysed in terms of their role in the presentation of information.
A data form is created to capture data.
New records are added, edited and deleted to update the spreadsheet for data currency.
A filtered list is sorted to organise and access information.
A filtered list is printed to provide records of a query.
The use of macros on a spreadsheet is analysed in terms of their role in the presentation of information.
Macros are created, edited and run-in accordance with user requirements.
Macros are used to automate repetitive tasks to facilitate data capture.
Macros are created to set required filters to locate records.
Macros are assigned to toolbar buttons to facilitate ease of access to information.
Macros are deleted in accordance with user requirements.
A report is created by using the application’s data analysis tools to combine and compare large sets of data.
The detail in a report is shown and/or hidden to focus attention on specific information required.
Totals in a report are shown to facilitate summary analysis.
Data in a report is updated to reflect changing user requirements.
Items in a report are grouped and ungrouped per user requirements.
Formatting in a report is changed per audience and user requirements.
The layout of a report is edited to reflect the required information in a given situation.
A chart is created from report data for the graphic representation of information.
A report is printed and deleted per organisation specific requirements.
