For a limited time only, get ALL of the above training for USD$55. Purchase now!
The purchase of any training allows the installation of the Course to an unlimited number of PCs within your organization.
Excel Training Module 2
Lesson 1 - Naming Constants. Dates and Times
In this lesson, we review the basic concepts of Excel and go into details the concept of Naming Constants and how Excel interprets Dates and Times.
Lesson 2 - Worksheet Formulas
In this lesson we will look at the thing that Excel does best -- Calculations. Excel has over 300 built in functions that can perform simple additions to some very obscure engineering functions.
We will also look at how we can easily perform Nesting, Formula Auditing and Troubleshooting.
Lesson 3 - Specific Worksheet Formulas
We would like to show you some of Excel most useful and popular functions like COUNT, Address, COUNTIF, DATEVALUE, HLOOKUP, MATCH, MAX and
VLOOKUP.
Lesson 4 - Decision making and extracting data
This lesson provides a good overall look at deciding what functions to use and how to use them. There are no hard and fast rules as more often than not you will need to use a nested formula that will incorporate many types of functions. Extracting data from tables and databases is something that is done quite frequently from within Excel. This lesson is meant as a guide to extracting data from a database or table.
Lesson 5 - Validation and External References
One thing you can be certain of when allowing other users to input into a spreadsheet you have designed is that they undoubtedly will enter incorrect data at some time or another. This lesson shows you how Excel can help you perform validation.
Lesson 6 - AutoFilters, Advanced Filters and Range Names
This lesson shows you how you can use Excels Filters (Advanced and Auto) to display virtually any criteria we want, so long as we have set up our table or list is a sensible manner.
Lesson 7 - Introduction to Charts
Charts in Excel can range from the very simple to the very complex and can be used for a multitude of reasons. They can be used to keep track of spending, stock performance, statistics, employee details and much more. Most charts are used to show a comparison of past data in a highly visual style. A well set up chart should be able to inform the user at a glance exactly what the picture is that it is painting. We have all heard the saying “A picture paints a thousand words” and this should always hold true with charts in Excel. If an informed user looks at a chart and cannot tell what it is that the chart is representing, then it would be fair to say that the chart has not been set up very well. Excel is arguably the best of the Office Applications when it comes to creating and/or using charts. It allows us to create almost any kind of chart needed with minimal effort. As can be said with a lot of Excels features and capabilities, we often find ourselves using only a very small aspect of them, charts are no different in this respect.
Lesson 8 - FOUR NOT-SO-COMMON Handy Features
For this lesson we thought we would look at a few of Excel’s little known but very handy features. These are Custom Formats, Text to Columns, Save Workspace and Custom Lists.
Lesson 9 - Protecting and Hiding
There are times when using Excel that we do not want other people to make changes to our Workbooks, Formulas, Worksheets or Cells. We can achieve this in many ways, ranging from hiding data to preventing the Workbook from opening at all without a password to protecting the Workbook or Sheet.
Lesson 10 - Custom Views and Report Manager
Custom Views can be used on it’s own or in conjunction with the Report Manager. The purpose of Custom Views is to save the appearance of a chosen Workbook. This means we can have more than one person work on a Workbook and each person can save their own Custom View. It also saves a lot of time with Print settings that can be very time consuming and fiddly.
Excel Training Module 3
Lesson 1 - Advanced Formulas
In this lesson, we will show you how how to easily write those mega functions that you have seen elsewhere. This includes concepts like Nesting, Array Formulas and Dfunctions. Nesting means to use the result of one formula as the argument in another. Array formulas allow you to perform calculations and return a result. Dfunctions are known as Excel Database functions.
Lesson 2 - Advanced Filters
In this lesson, we will have a good look at one of Excel's arguably most useful features and that is the Advanced Filter.
Advanced Filter will allow us to nominate where we would like our filtered data to be placed. The choices are: Filter the list, in place or Copy to another location.
Advanced Filter has a built-in function that will allow us to filter by unique records.
Advanced Filter allows us to use a formula as our criteria.
Lesson 3 - Pivot Tables (Part 1)
In this lesson we will start to look at arguably one of Microsoft Excel most powerful and useful feature, that is Pivot Tables. In a nutshell a Pivot Table takes two dimensional data (your spreadsheet) and creates a three dimensional table (the Pivot Table itself). They are a great way to produce statistical information from a table of data.
We would use a Pivot Table to produce meaningful information from a table of information. You will recall in lesson 2 we looked at Excel's Advanced Filter feature and how it could be used to extract information from a table of data based on a set of criteria. A Pivot Table could be used on that same table to create a table that could tell us much statistical information about all the data contained within it.
Lesson 4 - Pivot Tables (Part 2)
In this lesson, we will look at constructing some more complicated pivot tables and extracting meaningful data from them. This includes
Grouping Fields options where we could, for example, group people(data) by the years(attributes) in which they were born(rules).
Lesson 5 - Pivot Tables (Part 3)
In the last lesson on Pivot Tables, we looked at setting up a complete pivot table and how to use the grouping and field options of Pivot Tables. We will also discuss some of the more advanced features like Calculated field and, more importantly, be fully aware of the pitfalls associated with them.
Lesson 6 - Scenarios and Goal Seek
In this lesson we are going to look at two of the tools that are specifically designed for use in "What-If analysis", and as such make up part of the "What-If Analysis Toolpak of Microsoft Excel". These tools are used to determine different outcomes of your data by changing different cells within a Worksheet model. The two tools we will discuss here are called Scenarios and Goal Seek. Both of these tools are used widely in financial, accounting and engineering businesses today, and once understood, can be a huge aid in determining various outcomes and projections.
Lesson 7 - Data Tables and Consolidation
In this lesson, we are going to look at two of the very handy, but little known features of Excel -- Data Tables and Consolidation. Data Tables are another one of the tools that are specifically designed for use in "What-If analysis". By now, you will be familiar with how "What-If Analysis" works and what it is used for. Data Tables are just a range of cells that are used for testing and analysing outcomes on a a larger scale. Consolidation is a powerful feature of Microsoft Excel that enables you to combine data from separate worksheets into one consolidated worksheet. It also enables you to perform many calculations on this data, including 3-D formulas, which are formulas which refer to cells on multiple Worksheets.
Lesson 8 - Excel on the Web
One extremely handy feature that Excel offers is its ability to allow you to publish a spreadsheet on the web, so you can communicate your data to anyone you like, irrespective of whether they use or even have Microsoft Excel on their computers. In other words, your Excel spreadsheet can be viewed in a web browser. This could be useful for such things as employers wanting their employees to access things such as sales data from different areas, cost calculations, time sheets and many other uses. Basically, if your Excel Workbook is placed on the web, people anywhere in the world can access it, with or without Excel.
Lesson 9 - MACROS (Part 1)
In our last two lessons in our Excel Level 3 course, we will be taking a look at recording, editing and running macros using Excels Macro Recorder. A macro is simply an action or a set of actions you can use to automate a particular task or tasks. You use the Macro recorder a bit like using a video camera. You switch it on, record what you want to record, then switch it off. In effect a macro is like a mini-program that performs the actions that you have recorded. Macros are a fantastic time-saving feature and are ideal for automating repetitive tasks. They are also useful if you are setting up an application for others to use as you can use macros to create buttons and dialog boxes to guide a user through your application as well as automating the processes involved.
Lesson 10 - MACROS (Part 2)
In this, our last lesson in our Excel Level 3 course, we will be delving further into the macro feature of Excel. You now know how to record and run both absolute and relative macros, and hopefully you now understand a little about the actual macro (VBA) language. In this lesson we are going to create a mini-application using macros and we will assign these macros to an object.
Excel VBA(Visual Basic for Applications) Training Module
Lesson 1 - Introduction
VBA is short for Visual Basics for Applications. This is the standard Macro language used in most Microsoft Office products. The word "Applications" can represent any one of the Office products it is used within eg; Excel, Access etc. The VBA language is a derivative of Visual Basic (VB), which in turn is a derivative of the language Basic. The fundamental difference with VBA from VB is that VBA is (as the name implies) used within an Application. By far the most mature of these Applications when it comes to VBA is Excel. You will find as we delve deeper into VBA for Excel that we can modify the Application so it will behave in almost any way possible.
Lesson 2 - Common Objects
In this lesson we will look at the 4 most common (and arguably useful) Objects in VBA, these are: Application ,Workbook, Worksheet and
Range. The most useful Properties and Methods of these objects and the relationships between these objects will be explored. Sample codes of these objects will also be provided.
Lesson 3 - Variables
A Variable is used to store temporary information that is used for execution within the Procedure, Module or Workbook.
You can name variables with any valid name you wish. For example, you could name a variable "David" and then declare it as any one of the data types supported by VBA. However, it is good practice to formalize some sort of naming convention. This lesson explore the details of Variables and also the various rules and naming conventions that you must know about.
Lesson 4 - Loops
This lesson we will focus on Loops. There are many varieties of these, but they are all basically the same in that they will repeat a line, or multiple lines, of code a set number times, or until a condition becomes True or False.
Lesson 5 - Effective Decision Making IF Else And Or Not If
The "If" Function in VBA for Excel is very similar to the "IF" function used in a Worksheet formula. It will return either True or False and it does no more or less than this. As with the "IF" used in the Worksheet formula the "If" in VBA can take up to two arguments, one for True and one for False. This lesson will guide you to the effective use of the "If" Function in VBA.
Lesson 6 - Workbook and Worksheet Events
There are times when you may like or want the macro to run whenever a particular Event happens. Let's say each time you open your Workbook you would like the current date inserted into a cell, or each time you activate a particular Worksheet you want all data on it to be cleared. With Excel we can do this and much more! Excel has what is known as Events. The two most common being either Workbook Events or Worksheet Events. The word Event in this context means something that occurs whenever a particular action takes place which effects the Workbook or Worksheet Object. This might be a Workbook opening or a Worksheet cell changing or a Workbook closing etc. This lesson will explore Workbook and Worksheet Events.
Lesson 7 - De-bugging
This lesson we will concentrate on De-bugging code and Error Handling. You will no doubt find that debugging is something you will be doing a lot in the early stages of learning. A good programmer will never think that their finished project doesn't contain any errors, if you do, you won't bother placing in any code to handle errors and that is a BIG mistake. There are many features in Excel that can make De-bugging code a reasonably easy task. To avoid confusion and overload we will discuss in detail what I believe to be the best method.
Lesson 8 - WorksheetFunctions
In this lesson, we will focus Worksheet Functions in VBA for Excel. Excel has a number of functions that can be used in the Visual basic environment only, but it also has, at it's disposal, a long list of the standard Worksheet Functions. When these are combined with VBA for Excel it adds even more power and flexibility to the Visual Basic environment. It is a great idea to use Worksheet Functions within your code as it can often means doing away with unnecessary filling up of ranges with formulas. This in turn will mean a faster Workbook.
Lesson 9 - User Defined Functions
In this lesson we will look at User defined functions (UDF's) or Custom functions as they are also called. While Excel already has over 300 Functions available to us, at times it doesn't quite have the one we need, or if it does, it requires nesting several of these to create the formula we want. It is in these situation where UDF's come in very handy.
Lesson 10 - Controls
Prior to Excel 97, the only Controls available to the user on the spreadsheet were Controls from the Forms Toolbar. While these Controls certainly served a good purpose, they have nowhere near the flexibility of the Controls that are now available in later versions of Excel. ActiveX Controls are available in later version of Excel. It is important to note that they carry a lot of overheads. This means if your Workbook contains a lot of Controls, it can have an adverse affect on the size and performance of your Workbook. This lesson explores Controls in details.
Excel VBA User Form Training Module
Lesson 1 - UserForms - Introduction
This lesson will introduce you to Excel VBA UserForms. UserForms were first introduced into Excel in Excel 97. It works like a Dialog box(sheet) and has superior Event handling that allows us to respond to a users actions in ways that were previously not possible. By this I mean we can have specific code run when the user clicks the control, enters, exits, double clicks, right clicks, types and much more. Basically we are able to capture any action that the user takes. If you want to give your projects a professional and consistent look and feel, then UserForms will certainly help you do this.
Lesson 2 - Filling UserForm Controls
The vast majority of UserForms that are designed within Excel are used so that users can easily select and input data. This also ensures that any entries that are entered into a spreadsheet are within the requirements needed. Excel has many Controls that can be placed on a UserForm that can make this not only easy for the user, but also for the designer of the UserForm. The two most useful Controls for this are the “ComboBox” and the “ListBox”. In this lesson, we will look at each of these Controls.
Lesson 3 - MultiPage Control
In this lesson we will look at the MultiPage control, arguably the most useful controls to use if your UserForm will be containing many different controls and/or you wish to have different controls associated with different aspects of your project. The other thing that we can do with a MultiPage is make our UserForm behave in the same manner as any one of Excel's standard Wizards. A Wizard by definition is an aid that steps you through a particular process. An example of this would be the Pivot Table Wizard or Chart Wizard.
Lesson 4 - Validating The Users Inputs
One of the biggest problems that is (or should be) faced by a developer (other than understanding just what they want) is developing a project that will only accept valid data. By far the quickest and easiest way to achieve this is to use a ListBox and/or a ComboBox that presents the user with only valid data. The approach is one that I will try to employ whenever possible. Unfortunately, it's not always possible to use a ComboBox or ListBox and we may need to allow the user to type in an entry. This means you will need to check whether the user has typed in a valid entry. In this lesson, we will explore how this can be achieved.
Lesson 5 - When to Apply Validation
What we shall look at in this lesson is when to reject or accept a users entries. By this I mean should we allow the user to completely fill out the UserForm first and then inform him/her that some of their entries are not valid, or would it be better to inform the user at each step.
Lesson 6 - Which Controls to Use and When
In this lesson, we will look at which controls should be use and for what purpose should we use them? Excel has basically a Control for every possible job type. It is important to know which Controls to use for which job. It is fair to say that there are no hard and fast rules for this as each project usually has something which makes it unique. So, we need to at times use a Control and modify it to suit our specific needs. However, there are certain guidelines that we can follow.
Lesson 7 - Passing Control Values back to a Spreadsheet, Passing range values to the UserForm Controls
In this lesson we will be looking at how we can pass values from the Worksheet to a Control or any number of Controls on a UserForm and also look at how we can do the opposite which is to pass the value from a Control back to the Worksheet.
Lesson 8 - Option Buttons and Checkboxes in Detail
In this lesson we will look at Option Buttons and Checkboxes in detail.
The rules of which one to use and when are really quite simple and boils down to basically if the user should be allowed to make one selection only from a choice of many, then it should be OptionButtons. If the user should be allowed to make one or more choices from many, then it should be CheckBoxes.
Lesson 9 - Creating Template Controls, Using the RefEdit Control
Excel allows us to easily create Template Controls which we can format and name appropriately so that each time the Control is needed, rather than repeat the process over and over again, we can simply select the Template Control that we have created and place it on to a UserForm. This is extremely easy to do and can save many hours of repetitious work in the long run. We will look at Template Controls in this lesson.
On top of that, we will look at the RefEdit Control. Normally used on a user form, the RefEdit control will display the address of a range, or single cell, that you've entered (typed in) or selected.
Lesson 10 - Finding the ActiveControl, Creating Controls at Runtime
At times when creating a project in Excel VBA which incorporates the use of a UserForm, you may wish to create actual Controls via the choice of a user. The advantage of actually creating the Control is the fact that it will require the need for less Controls on the UserForm at any one time, which in turn can lessen overheads. We will look at how this can be done in this lesson.
