Our Online Excel course covers Excel from absolute Beginners up to Expert Level.So you can begin the course at whatever level is suitable for you.Below we explain the course outline in detail, emphasizing which sections are the most important and why.
For four levels , you can sit an online test for that level. A certificate will be produced automatically with your name and details after you have achieved 75% or over in your test for each Level.
The exams comprise of 10 to 15 multiple choice questions which test your understanding of the modules you have studied. You can repeat these tests.
The Full Course Syllabus:
Level 1 of 5: The Beginner Course:
Overview of Excel:
This covers Excel 2007, 2010 ,2013 , 2016, 365 and even the latest version Excel 2019. This lesson explains the various components, ribbons and features in Excel. Shows you how to customize Excel to your needs.
Inputting & Formatting:
All types of Formatting,especially number and currency formatting Also shows you how to delete formats correctly and how Excel views Formatting.
Introduction To Excel Formulas:
Shows the difference between a Formula and a Function. This module’s emphasis is that Formulas ,like Functions simply automate a ‘Human Process’. Viewing it from this point of view makes Formulas and Functions must easier to understand. This module finishes by providing a simple but powerful Two step method to create even complex formulas.
Introduction to Excel Functions:
Explains what Functions are and how to use them to their full potential. Covers the SUM,AVERAGE,MIN, MAX and COUNT Functions.
Linking Worksheets & Workbooks:
An extremely important Excel skill as in real life workplace situations, data is kept in different worksheets and workbooks.
Controlling Your Worksheets & Workbooks:
This module will teach you how to freeze rows and columns, split your screen, size and hide your rows and columns. Also how to rename, move or copy your worksheets.
Printing your spreadsheets:
Everything you need to know about printing your worksheets and workbooks.
The Three Essential Excel Concepts:
Probably one of the most important module in the course. I am always amazed that so many courses only give a few minutes to these concepts. This module is over 30 minutes long as it explains the concepts of ‘Relative Reference’, ‘Absolute Reference’ and using ‘Cell references” in their full glory. There is no point going any further in the course until you have mastered these three concepts. They are the very oxygen of Excel.
Introduction to Data Analysis:
So much of your work in Excel will be spent dealing with lists or tables of information,whether it be a list of
Level 2 of 5: The Intermediate Level:
The IF Function: The First Work-Horse Function.
In most work-place spreadsheets, two Functions can often accounts for over 50-60% of the functions used. They are Excel’s two work-horse Functions, the IF Function and the VLOOKUP Function. The IF function allows you automate decision making processes in Excel.
You will learn how to use the IF Functions and see it’s usage in various workplace situations. Special attention will be given to maximize its use by incorporating the three Essential Excel concepts.
In the advanced section of the course, we build up the power of the IF Function by learning about nested IF functions and the AND & OR Functions.
The VLOOKUP Function: THE Second Work-Horse Function.
Imagine I asked you to look up 100 phone numbers in a paper-based phone book? What would you do?
The SUMIF & COUNTIF Functions: Two Useful Lieutenants
These two functions are very useful. They allow you to add or count items based on a condition. How many sales did Mary make and how much revenue occurred from these sales. The COUNTIF Function is also used a lot in combination type functions. We will show you how to use these Functions with plenty of examples of using them in real life workplace situations. In the advanced course, we will meet a new relation of these Functions, the SUMIFS and COUNTIFS Functions. They arrived in the Excel 2007 version and are Functions you need to master, as they allow you to add or count items for Not just one condition but up to 100 conditions.
Working with Text:
This module shows how to manipulate text in Excel. It looks at the’ &’ concatenate operator and the main Text Functions like LEFT, RIGHT, LEN,MID and FIND Functions.
A lot of heartache can be avoided by using data validation techniques in your spreadsheet.
Conditional formatting allows you to format a cell or cells if a certain condition or conditions are met.
Protecting Your Worksheets & Workbooks.
Do not under any circumstances ,let others use a spreadsheet you have created unless it is protected.
Charts in Excel:
Learn about the various chart types in Excel, when you use each one and how to create and format them.
Understanding Lists and Tables.
Like the module on the ‘Three Excel Concepts’ , this is another essential module you need to master, especially if your work in Excel involves analyzing large chunks of data.
Level 3 of 5: The Advanced level.
INDEX & MATCH Functions: Get Complete Dynamic control over your Data.
The INDEX and MATCH Functions along with SUMIFS and SUMPRODUCT are the most important advanced type functions.
The INDEX and MATCH Functions when combined do everything that the VLOOKUP Function can do but more.
The SUMIFS, AVERAGEIFS AND COUNTIFS Functions.
We have already met the SUMIF,AVERAGEIF AND COUNTIF Functions but these only allow you test for one condition .These new Functions, which came in the Excel 2007 version allow you to test for up to 100 conditions. Very powerful dynamic reports can be created from using these new functions. We will show you how to use them and also show their use when your data is in a Excel Table Format.
SUMPRODUCT Function: Your First Glimpse of Arrays.
This Function is different from all the other functions you have used before because its takes a range of cells(array) as an argument.
ARRAY FORMULAS IN EXCEL.
Study the module on the SUMPRODUCT Function before you start this module.
OFFSET & INDIRECT Functions.
If you want to ask Excel to move from a certain cell to another cell by itself and then grab some cells from this cell point, then call on the OFFSET Function. We will show practical examples of this Function in use and show how to build your own powerful OFFSET Functions.
Duplicates in Excel:
This module looks at solutions to the problem of duplicates in Excel. Neither VLOOKUP, INDEX or MATCH can handle duplicates.
They will just find the first instance only. We look at various solutions to this problem by using – helper columns, the SUMPRODUCT and ARRAY Formulas.
Level 4 of 5: The Pivot Table level.
The Power of Pivot Tables.
This major section of the course deals with Pivot Tables in great detail. Pivot Tables are a must know tool for analyzing data.
Overview of Pivot Tables:
Examples of what Pivot tables can do. Using a large data set, we will create various reports showing what you can achieve with Pivot tables. You will learn about the various ribbons and each Pivot table command button.
Your First Pivot tables:
This module will show in detail how to create and manipulate Pivot tables.
Pivot Table Formatting:
Master the techniques to format your pivot tables.
Pivot Table Values:
This section show how to manipulate the values in your Pivot Tables. By default, a pivot table sums values up. We show how to show these values in different ways. See your values as percentages, in relation to other subtotals, differences from other time periods etc
Grouping Pivot Table data.
This module explains how we can group data in to bespoken group. We will especially look at the very important technique of grouping dates as most data has a date field.
Understanding the Pivot table cache.
We will see in this module that Pivot tables are automatically created from the same internal memory. This can cause problems especially if you are grouping data, as changing the grouping of a set of values will change all the others groupings in your other pivot tables. We will sow you in this module how to create independent Pivot tables.
Calculated Fields & Items in Pivot Tables:
We can create new calculated fields and items in our Pivot Tables based on existing data. This modules explains all you need to know.
The GETPIVOTDATA Function in Pivot Table
Pivot data is quiet volatile and it is quite difficult to grab data from pivot tables for further analysis. We will show in this module a possible solution to this problem whereby we can populated per-formatted reports from our live Pivot Tables.
Level 5: The Power Excel Course
Introduction to Power Excel.
- Excel Versions and Compatibility
- Power Excel Course Objectives
- Power Excel and its Benefits
- Course Resources Page
- Power Query (Get & Transform)
- Important Note regarding your region.
- Load Options & the Power Query Editor
- Basic Power Query Operations
- Number Operations In Power Query
- Text Operations in Power Query
- Date Operations in Power Query
- Advanced Power Query Operations
- How to Pivot & unPivot in Power Query
- How to Merge & Append Queries
- The Folder Technique
The Excel Data Model
- What is the Data Model?
- The key Concept of Normalization
- Creating and Editing Table Relationships
- Active v Inactive Relationships
- The Concept of Cardinality
- Connecting Multiple Data Tables.
- Introduction to Filter direction
- What is a Power Pivot?
- Calculated Columns and How to Create them.
- What are DAX Measures
- Implicit and Explicit Measures
- More about Filter Context
DAX Formula Language
- DAX Syntax
- DAX Count Functions
- DAX Logical Functions
- Switch and Switch(true) Functions
- The Calculate Function
- The Filter Function
- The ALL Function
- The Related Function
- The Iterator Functions