Beginners Section.


Overview of Excel:

This covers Excel 2007, 2010 and Excel 2013. The various components, ribbons and features in Excel.
Shows 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.
This module shows various methods to access data from different worksheets & 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 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 at 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 orders, suppliers,employees etc.
This module starts of by stressing the importance of setting up your lists or tables property. Because  without this, you cannot use Excel’s powerful data analysis tools like Filters, Advanced Filters and the SubTotal Tool. We will master all of these in this module.  Later on in the advanced course, we will learn in detail  how to use Excel’s most powerful analytical tool-Pivot Tables.


Intermediate Excel Section:


 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 its 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 number in a paper-based phone book?  What would you do?

Firstly, you would notice that  the book was sorted alphabetically , you would then search for the name and when you found it , you would move your eyes over the relevant record until you found the telephone number.
Well that’s what the VLOOKUP Function does. It just automates that human process of searching for a value in a record. But it will do the job in  a fraction of a second.

Now imagine if all the thousands of pages of the phone book was mixed up. It could take you a week to find those100 phone  numbers but even that’s no problem to the VLOOKUP Function, it will still get you those 100 phone numbers in a fraction of  a second.

This module will show you how to understand and build powerful VLOOKUP Functions in various situations. It will also explain the common problems that occur with VLOOKUP Functions.


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.These can be very handy if you need to clean up your text in Excel to make it ready for further analysis. it give plenty of examples of these in use – like extracting a name or a city  from an address text string.


Data Validation:

A lot of heartache can be avoided by using data validation techniques in your spreadsheet.

For example, if a cell requires a date, you can program that cell only to take a date, with it also flashing  a warning notice to your users. Drop-down boxes should always be used in Excel if users have to input customer names, employees name etc as only those names on the list can be chosen. This ensures data integrity. This module explains all these techniques in detail.



Conditional Formatting:

Conditional formatting allows you to format a cell or  cells if a certain condition or conditions are met.
For example, If the stock level of  an item falls to  a particular level, you can get Excel to format that  cell  automatically in a certain way We will learn about the various conditional formatting methods including data bar, color scales and icon sets based on criteria like traffic lights.  In the intermediate level, we will study how to combine conditional formatting with formulas.



Protecting Your Worksheets & Workbooks.

Do not under any circumstances ,let others use a spreadsheet you have created unless it is protected.
Protecting your worksheets stops your users from deleting any of  your formulas or functions. A deleted formula or function  can made your spreadsheet unreliable, and finding the corrupted cell in  very large workbook can be a nightmare. In this module we will show you how to protect all your formulas and even make them invisible.



Charts in Excel:

Learn about the various chart types in Excel, when you use each one and how to create and format them.
In later modules, we will delve more deeply in to charts, particularly solutions to the most common problems you might met when creating and formatting  charts in Excel.


Data Analysis 2: 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.
We will show how to create proper lists or databases in Excel. What is an Named range and how to create them.
What is an Excel Table , how to create and use them and how they differ from ordinary lists and Named ranges.
The key points is that if you add data  to the end of an ordinary Excel lists, your Lookup Functions, your Named Ranges  or your Pivot Tables will not automatically pick them up. The module will explain the answers and solutions  to all these questions.



Advanced Excel:


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.

Unfortunately the VLOOKUP Function can only search from Left to Right from the first column in your defined table but the INDEX and MATCH Functions when combined can search from Right to Left or Left to Right from any column in your table. To be able to extract data from your table dynamically ,no matter where it is, you need to master these two Functions.

This module will show you how to create and use both Function separately and then how to combine them with plenty of real life examples and solutions to commonly met problems.



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.

Imagine you have a list of 100 shares,along with the quantity you own of each of them along with the current share price.

You want to find out the total value of your portfolio.

Share:                Price:    Quantity:
IBM                     10           20
APPLE                20          100
MICROSOFT      25           200


Well,one solution would be to create a formula which multiplies each share quantity by the price.You will need  to create 100 formulas and then another formula to sum up each share’s total value ,that 101 formulas or instead  you could just use one Function -the SUMPRODUCT Function. It does as it says , its sums the various products.

This Function will be different from all the other functions you have used before because its takes a range of cells(array). This module will show you how to use this functions with many examples but it will also  introduce a very important advanced technique on how to deal with arrays in Excel.Once you get your mind around this techniques you will have no problem dealing with any  array formula in Excel.




Study the module on the SUMPRODUCT Function before you start this module.
In this module we will learn  what Is an Array and an Array Formula with examples of them in use in practical commercial situations. We will show how to create and use an Array Formula and look again at the important concept of Conditional Evaluation In an Array Formula.



If you want to get 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.

The great practical use of the INDIRECT Function is that it can accept a text sting as a cell reference.This is great for creating dynamic ranges. We show many practical uses of this function in this module.



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 users helper columns, SUMPRODUCT and ARRAY Formulas.

Data Analysis 3:  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 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 pre-formated reports from our live Pivot Table.