ExcelBN
Beginner
Intermediate
Advance
Dashboards NEW

Advance Microsoft Excel Syllabus

(2 days) Do you want even more Excel? The Advance syllabus introduces powerful Excel features that are not widely used but still very sophisticated. Use these features and everyone will think you are an Excel Master!

Completing the Advance syllabus will put you in the perfect position to enrol in our course on Building Interactive Dashboards.

Features

  • 2 days hands-on learning structure.
  • Group-based environment where learning from each other is highly encouraged.
  • Chapter Review exercises to strengthen understanding.
  • Fun open-ended questions to assist knowledge recall, promote deeper thought and encourage group interaction.
  • Supplementary exercises to add more challenge.
  • Identify and practice your unique strengths to support everyone’s learning journey.
  • Support for Individual and Group Well-Being.

Learning Goals

Participants will be able to:

  1. Understand and use Power Query to retrieve, clean and shape data from external sources.
  2. Use Excel Data Tools commands to split a column into one or more columns
  3. Use Excel Data Tools commands to automatically populate columns based on a recognized pattern.
  4. Understand and use Excel’s Data Validation command.
  5. Use Conditional Formatting to highlight data that meets specific requirements.
  6. Use PivotTables to perform data analysis.
  7. Understand and take advantage of how Excel stores Dates and Times.
  8. Use Date and Time Functions: DATE(), DATEVALUE(), DAY(), MONTH(), YEAR(), WEEKDAY(), DAYS(), NETWORKDAYS.INTL(), EDATE(), EOMONTH(), WORKDAY.INTL(), TODAY(), TIME(), TIMEVALUE(), HOUR(), MINUTE(), SECOND(), NOW().
  9. Use Excel’s Formula Auditing commands to find and fix errors.
  10. Create Nested Functions.
  11. Understand and use the Goal Seek command.
  12. Understand and use the Forecast Sheet command.

Prerequisites

Participants are expected to meet the following requirements with regards to using computers:

  • know how to switch on and shut down a PC.
  • comfortable using a Windows PC with common hardware including a keyboard, monitor, mouse and USB ports.
  • know how to use Windows to launch applications, navigate directory structure and search for files.

Participants should also demonstrate competence with the following topics from the Beginner and Intermediate Syllabuses:

  • Understand what spreadsheets are and what they are used for.
  • Navigate the default MS Excel interface including the Ribbon, Tabs and Commands.
  • Create new Workbooks and Worksheets.
  • Enter data into Worksheets.
  • Understand what Cell References are.
  • Apply Cell Formatting and Number Formatting
  • Create formulas to perform mathematical calculations in Excel.
  • Locate and insert Excel Functions including using Function Arguments.
  • Understand and use Autosum functions - SUM(), AVERAGE(), COUNT(), MAX(), MIN().
  • Sort data on one or more columns including according to a custom order.
  • Filter data on one or more columns that meet specific criteria.
  • Understand the difference between Relative and Absolute Cell References and when to use each one.
  • Understand what PivotTables are used for.
  • Build basic PivotTables and PivotCharts from data.
  • Use PivotTable Filters, Slicers and Timelines to filter data.