Microsoft Excel Advanced Course

Overview

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.

The qualifying learner will be capable of:

Training Outcomes

  • 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.

Add These Skills To Your Skill Set