99% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
Excel Advanced Training Course
Designed for Excel 365, 2016, 2013, 2010, 2007
Who is this course for?
Our Microsoft Excel Advanced course is suitable for those with a sound working knowledge of Excel who wish to progress to the most complicated functions and features.
A working knowledge of Excel or our Excel Intermediate course.
- At the end of this course you will have a complete understanding of the higher functions of Excel.
- You will be able to perform the most advanced calculations and functions, manipulate data using pivot tables and 'advanced' analysis, record macros and integrate with other office applications.
Using logical functions
- Creating named ranges
- Using named ranges in formulas
The IF function (recap)
Creating nested IF functions
AND and OR functions
Combining logical functions
Working with lookups
The VLOOKUP function
The HLOOKUP function
Advanced list management
Using data validation
- Setting criteria for entry of text, values and dates
- Creating a dropdown list
Using database functions
- Setting up criteria
PivotTables and PivotCharts
Using the PivotTable Wizard
Using the PivotTable toolbar
Using Slicers to manipulate PivotTables
Using analytical options
Using Goal Seek
Using the Solver add-in
Working with scenarios
- Creating and saving scenarios
- Viewing scenarios
Using the macro recorder
Viewing and editing macro VBA code
Prices & Dates
What you get
Training is held in our modern, comfortable, air-conditioned suites
Modern-spec IT, fully networked with internet access
"What do I get on the day?"
Lunch is provided at a local restaurant or pub. Browse the sample menus:
Breaks and timing
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Joining information (how to get to our venues)
Available throughout the day:
- Hot beverages
- Clean, filtered water
Training formats & Services
Axon Solutions Ltd
HR Business Partner
The pace was generally ok, just sometimes, I would have liked to practice examples more to understand the reasoning behind the tasks.
There was a lot to take in one day so I do not think I absorbed everything - maybe less topics should be covered in a day so we can spend more time on each one.
Really helpful and I actually enjoyed doing today's course after the intermediate one yesterday.
Please add in some more practice exercises for us to do to test our understanding - these are great and very useful.
My second course with STL. I would recommend both courses.
Example training manuals
Below are some extracts from our free Excel training manuals.
Module 3: Advanced List Management
This module will explore applying data validation settings to your worksheet to control data entry. You will also identify and use database functions to perform calculations using multiple database fields.
Creating Data Validation Rules
Data validation allows you to create restrictions on what can be entered into a cell. You can have Excel display a warning message or prevent invalid entry if a user of the worksheet does not enter the correct type of value.
Use the following procedure to create a data validation rule.
- Select the cells where you want to apply the data validation rule.
- Select the Data tab from the Ribbon.
- Select Data Validation. Select Data Validation.
- On the Settings tab of the Data Validation dialog box, set up the Validation Criteria. Use the drop down lists to help you build your criteria. In this example, we are requiring a three-digit number.
- Select the Input Message tab.
- Enter a Title and Message that the user will see when he or she selects the cell.
- Select the Error Alert tab.
- Select the Style of error from the drop down list. Enter a Title and Error message to display if the user enters invalid data.
- Select OK.
If you select the Settings tab and choose List from the Allow drop menu, you will see a data box (labeled Source) where you can enter permissible source data for the cell.
You can type the values that are permissible for the cell directly, or specify a range that contains the values.
If you specify a cell range, make sure there is an equals sign = in front of it.
For example, cell E2 may be being set up so that items in cells A2 to A9 (surrounded by a dashed border) will be available as choices for data entry.
Now, a user can choose from a validation drop list that is available for cell E2.
Using a Formula to Validate Entries
If you select Custom from the Allow drop menu, you can enter a formula to validate your data. You can base the formula on data from another cell if you wish.
Here the custom type has been chosen from the Allow drop list and a formula containing an IF function has been entered. The formula reads: IF(Not(G4=100),TRUE,FALSE).
This means that if the value entered into cell G4 (the cell that is being validated) is not 100, the function will return TRUE. If it is 100 the function will return FALSE. If the validation formula returns true, the data is permitted. If FALSE, the data is invalid. Basically, this formula allows any number other than 100 to be entered.
There are many possibilities for setting data validation rules. For example, you can select a range of cells, click within the range, and click the Data Validation button on the Data Ribbon to set up validation rules for the entire range. You can also AutoFill a cell with validation rules into other cells in a row or column. These cells will all have the same validation properties.
Don't forget only newer versions of Excel such as 2016, 2013, 2010 and 2007 have the new ribbon at the top. Older versions such as 2003, 2002/XP, 2000 and 97 us the Menu system, such as File, Edit etc.
Using Database Functions
Database functions allow you to perform operations on an Excel database that involve multiple fields. In a sense, they offer some of the power of array formulas, but generally make worksheets faster because they do not require the same amount of recalculation.
With database functions you can get counts, averages, or sums of your database on selection criteria involving multiple fields. Implementing database functions is a little like implementing advanced filters: you have to establish a criteria range first before you use the function itself.
Some useful database functions are:
- DSUM Used to create total values in a field based on specified criteria
- DAVERAGE Used to average values in a field based on specified criteria
- DCOUNT Counts the cells that contain numbers and meet the specified criteria
- DMAX and DMIN Return the largest and smallest values respectively from records that meet the specified conditions.
- DPRODUCT Multiplies values in a field according to specific conditions
- DGET Returns a single record value from a record that meets the specified conditions.
All of the database functions use the same argument format:
Function Name (Database range, Field to be returned or calculated, Criteria range).
Take a the data sample table as an example.
We will use the DSUM database function to calculate the total profit for all Tuesdays and Thursdays with Sales greater than 2200 and Profit less than 1900.
Creating a Criteria Range
The first step in using a database function is to set up a criteria range. We do this by entering the field headings that we want to use exactly as they are in the original data list. For this example, we will need the fields Day, Sales, and Profit for our criteria range. If we enter these field headings in cells F1, G1, and H21 respectively, we can then move on to setting up our criteria.
This is the criteria range for the DSUM database function.
In the row under the field headings, we have T*, >2200, <1900. This means that records with a Day starting with T, Sales greater than 2200, and Profit less than 1900 will be selected for summation.
If you wanted to join the criteria with a logical Or operator, you would offset the criteria on separate rows (like an advanced filter).
Entering the Database Function
The next step is to choose a cell (H4) to place the function in. To enter the function, activate the cell and click the function (fx) button on the formula bar. In the Insert Function dialogue box, choose DSUM from the database category to reveal the Function Arguments box.
First, select your original list with your mouse, or enter the range directly into the Database data area. Press Tab or use your mouse to move to the Field data area and type Profit (this is the field that will be summed).
Then, put your cursor in the Criteria data area, and select the entire criteria range (F1:H2) with your mouse. To finish, click the OK button.
You can see the sum of the Profit fields for records that meet the criteria in cell H4.
You could have also activated cell H4, and entered the function directly into the formula bar:
The database range is A1:D25
The field that is being summed is Profit
The criteria range is F1:H2.
If you enter a database function directly into the formula bar, remember to keep your cell ranges accurate, and put the field that will be summed, averaged, or otherwise operated on, in quotation marks.