Course Content

Description: A short review of basic Excel for individuals who have prior experience but require a refresher.

Target Audience: For those who use Excel occasionally or need to refresh fundamentals before attending an intermediate course.

Note: Not for complete beginners; they should attend the two-day Level 1 course.

Duration: 1 full day (Onsite or Online).

Prerequisites: Knowledge of Windows and general PC usage.

Unit Standards: SAQA US ID 116937 (NQF Level 2, 4 Credits).

Module 1: Excel Essentials

• The Excel screen and Understanding the Ribbon.
• Contextual tabs and dialog box launchers.
• Customizing the Quick Access Bar.
• Using Help in Excel.
• Workbooks, Worksheets, and cell/range selection.
• Moving and scrolling through worksheets.

Module 2: Create a Spreadsheet

• Types of data and data entry.
• Using AutoFill and editing data.
• File management, compatibility, and Formulas.
• Absolute and Relative addressing.

Module 3: Edit a Spreadsheet

• Changing column/row widths and cell alignment.
• Centering headings over a group of cells.
• Inserting/deleting rows, columns, and worksheets.
• Renaming worksheets, moving, and copying.

Module 4: Format a Spreadsheet

• Changing number formats, fonts, sizes, and colors.
• Borders, Fill (shading), and copying formats.
• Freezing panes and zooming the display.

Module 5: Printing a Spreadsheet

• Using Page Layout View to create Headers & Footers.
• Print Preview and using the Page Layout ribbon.
• Manual Page Breaks, Orientation, and Scaling.
• Selecting a print area and repeating headings.

Module 6: Spelling and Grammar

• Setting defaults for Spellchecker and Dictionary Language.
• Checking Spelling, Grammar, and using Autocorrect.

Module 7: Introduction to Working with Data

• Sorting and Filtering lists.

Module 8: Charts

• Inserting and editing charts.
• Working with contextual tabs and chart options.
• Adding/deleting data series and changing chart types/formats.
• Adding text, drawing objects, and printing charts.
  

Course Content

Description: A foundation course for Excel covering spreadsheet creation, editing, formatting, printing, and basic formulas.

Who Should Attend: This course is designed for complete beginners who have never used Excel before.

Duration: 2 full days, available onsite or online.

Prerequisites: Basic knowledge of Windows and personal computer usage.

Unit Standards: SAQA US ID: 116937 (NQF level 2, 4 Credits).

Module 1: Excel Essentials

• Definition of spreadsheets and opening Excel.
• Navigating the Excel screen and understanding the Ribbon.
• Customizing the Quick Access Bar and using Help.
• Working with Workbooks, Worksheets, and selecting ranges.
• Moving and scrolling through workbooks.

Module 2: Creating a Spreadsheet

• Identifying types of data and performing data entry.
• Using AutoFill and editing existing data.
• Creating formulas and using absolute addressing.

Module 3: Editing a Spreadsheet

• Adjusting column and row widths.
• Alignment and centering headings over cells.
• Inserting or deleting rows, columns, and worksheets.
• Moving, copying, and using Find & Replace.

Module 4: Formatting a Spreadsheet

• Applying number formats, cell styles, borders, and fill (shading).
• Modifying character fonts, sizes, and colors.
• Freezing worksheet titles, splitting screens, and zooming.
• Copying formats to other cells.

Module 5: Printing a Spreadsheet

• Using Page Layout View for Headers and Footers.
• Using Print Preview and the Page Layout ribbon.
• Managing manual page breaks, orientation, and scaling.
• Defining a print area and repeating headings.

Module 6: Spelling and Grammar Check

• Setting defaults for spellchecker and dictionary language.
• Checking spelling, grammar, and using Autocorrect.

Module 7: Introduction to Working with Data

• Sorting and filtering lists.

Module 8: Charts

• Inserting, editing, and choosing chart options.
• Adding or deleting data series.
• Changing chart types and formats.
• Adding text, drawing objects, and printing charts.
• Addendum
• Formula checking and auditing.

Course Content

Description: This is a middle level or intermediate course for people who have done a beginners' course but need more skills to analyse and report on data. It is a pre-requisite for the advanced course.

Who Should Attend: Anyone who is working on Excel but struggles with formulas and functions, or handling large spreadsheets for analysis and reporting. It is ideal for self-taught users needing a refresher or wanting to work smarter with newer versions.

Duration: 2 full days onsite/public venue/online.

Prerequisites: You must have attended a beginner's course in Excel and/or work with Microsoft Excel on a daily basis.

Unit Standards: SAQA US ID: 116940: Use a graphical user interface (GUI)-based spreadsheet application to solve a given problem. NQF level 3. Credits 6.

Module 1: Excel Environment

• Plan, Prepare and Produce a Spreadsheet.
• Customising the View and Preferences.
• Working with Templates. Using Styles and themes.
• Excel Essentials.

Module 2: Essential Functions and formulas

• Basic Formulas and Functions.
• Statistical functions: Max, Average, Min, Count, CountA.
• Absolute, relative and mixed references in formulas.
• Naming ranges and cells.

Module 3: Creating multiple-worksheet workbooks

• Using Multiple Sheets: inserting, naming, deleting, grouping etc.
• Moving, copying & pasting data.
• Summing through the sheets.
• Link Formulas.

Module 4: More on Functions

• Getting help with functions: FX and Round.
• Logical functions: IF, SumIF, CountIF.
• Formula Auditing & show formula.

Module 5: Working with large spreadsheets

• Freeze Panes.
• Using split and side by side to view the spreadsheet.
• New Window.
• Text functions: Concatenate(Concat), Left, Right, Mid, Trim, Len, Text, TextJoin, Upper, Lower, Proper.
• Flash Fill and Text to Column wizard.
• Date functions: Now, Today, Weekday, Year, Month, Day.
• Hiding columns & rows, Paste Special, and Using Find & Replace.
• Cell comments and notes (365 only) and Conditional formatting.
• Finding duplicates by using conditional formatting.

Module 6: Working with Data, Lists and Tables

• Sorting and Filtering a list.
• Using Subtotals and Outlines.
• Using the create table feature, including modifying, formatting, and finding duplicates in a table.
• Functions and formulas in a table and Using Slicers with Tables.
• Display data trends using sparklines.

Course Content

Description: The advanced course helps you to automate spreadsheets, work with advanced functions and reports using pivot tables and pivot charts.

Who Should Attend: Only people who work daily on Excel with very large spreadsheets and who can do everything in the intermediate course should attend. This advanced course is a prerequisite for levels 4 and 5.

Duration: 2 full days onsite/public venue/online.

Prerequisites: Knowledge of Intermediate features is essential. The advanced course assumes that you are working with Excel on a daily basis and have used the features covered in the Beginners and Intermediate courses.

Unit Standards: SAQA US ID: 258878: Ensure spreadsheet integrity to enhance reliability. NQF level 4. Credits 3.

Customising Excel

Customise the Ribbon. Using Options in Excel.

Module 1: Understanding, creating a Database

• Databases & Correct Data Input.
• Using the Get & Transform Data.
• Importing from Text & a PDF.

Module 2: Correcting the Data

• Apply Data Validation, Circle Invalid Data, and Remove Validation Circles.
• Custom Number Formats.

Module 3: Managing Data

• Convert Text to Number Values & Numbers to Text.
• Using Find & Replace and Go to Special.
• Using Text functions: Trim, Clean, Text, Concatenate (Concat) and &, TextJoin, TextSplit, Left, Mid, Right, TextBefore, and TextAfter.

Module 4: Analysing data

• Sorting, Filtering & Using Subtotals.
• Advanced Conditional Formatting and Data Consolidation.
• Name Manager.
• Logical Functions: $IF$, Nested IF statements, $IFS$ (365 Only), IFERROR, $AND$, $OR$. $Sumif$, $Countif$, $Averageif$, $Sumifs$, $Countifs$, $Averageifs$, $Maxifs$, $Minifs$ (365 Only).
• Lookup & Reference Functions: $VLookup$ Function, $Index$, $Match$, and $XLookup$ Function (365 Only).
• Financial Functions: $PMT$, $FV$, $IPMT$.
• Date Functions: $DateValue$, $Network Days$, $Network.Intl$, $Workday$, and $YearFrac$.

Module 5: Pivot Table Reports

• Formatting Data as a Table and Creating a pivot table.
• Filter, Show and Hide Pivot Table data and Use Slicers & Timelines.
• Rearrange Pivot Tables and use fields, and Refresh Data.
• Create separate pivot table reports and Use Show Value As.
• Perform calculations within a pivot table and Add Subtotals and Grand Totals.
• Format field settings and Apply a pivot table style.
• Create a new custom Pivot table Style.
• Create and Modify a Pivot Chart.

Module 6: Macros

• Record a macro.
• Relative and Absolute References.
• Edit and Delete macros. Run a macro.
• Assign macros to objects and to a toolbar.

Addendums

• Arrays and Spill.
• Dynamic Array Functions: Unique Function, Sort Function, SortBy Function, and Filter Function.