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.
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.
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
ETC………………………………………….ETC
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.
ARRAY FORMULAS 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.
OFFSET & INDIRECT Functions.
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.