Intermediate 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 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.
These can be very handy if you need to clean up your text in Excel to make it ready for further analysis. It gives 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 names 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 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.
|