Overview of Excel:
This covers Excel 2007, 2010 ,2013 and Excel 2016. Explaining 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
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?
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 those 100 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.
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 names etc as only those names on the list can be chosen. This ensures data integrity. This
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 Advanced 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 workbooks 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 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 important questions.
INDEX & MATCH Functions: Get Complete Dynamic control over