98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsExcel Advanced Formulas and Functions Training Course
Designed for Excel 365, 2016, 2013, 2010, 2007
 1 day Instructorled
 Courses never cancelled
 Restaurant lunch
Syllabus
Who is this course for?
Our Microsoft Excel Formulas course is suitable for those with an advanced working knowledge of Excel who want a greater understanding of more sophisticated formulas & functions within Excel.
Prerequisites
Familiarity with creating functions including IF, VLOOKUPs and HLOOKUPs or our Excel Advanced part 1 course.
Benefits
At the end of this course you will have a greater understanding of building complex formulas using nested functions (such as IF statements).You will find quicker ways to customise formulas by taking advantage of the builtin functions available in Excel. Save time by learning more sophisticated search & auditing techniques when working with dataheavy spreadsheets.
Also available is the Excel Advanced  For Power Users course.
Course Syllabus
Building complex formulas
Using nested IF statements
Creating compound logical tests using AND, OR, NOT functions with IF statements
Nesting LOOKUP functions
Source table structure information using CHOOSE function
Using MATCH function to locate data
The INDEX function for retrieving information by location
Using a nested formula containing INDEX, MATCH and MATCH (twoway lookup)
Advanced functions in formulas
Using COUNTIFS,SUMIFS, & AVERAGEIFS for tabulating data based on single/multiple criteria
Statistical functions; MEDIAN, MODE, RANK, LARGE, SMALL, ROUND, MOD
AGGREGATE function to sum data in ranges with errors
Use a variety of Financial functions such PMT, FV, IRR
Data & Text Functions
Smarter ways to calculate date & time; TODAY
NETWORKDAYS, WORKDAY, DATEDIF
Use TYPE to identify data type of existing cell contents
Text functions; UPPER, PROPER, FIND, MID, SEARCH, LEFT, RIGHT, LEN
TRIM excess space in cells
Auditing formulas
Tracing formula precedents, dependents, and errors
Correcting errors in formulas
Combining IF with VLOOKUP to suppress error messages
Using the IS information function
Error checking functions; ISERR, ISERROR, IFERROR
Prices & Dates
What you get
Training is held in our modern, comfortable, airconditioned suites
Modernspec IT, fully networked with internet access
"What do I get on the day?"
Lunch
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)
Refreshments
Available throughout the day:
 Hot beverages
 Clean, filtered water
 Biscuits
Learning tools
Incourse handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with stepbystep instructions
24 months access to Microsoft trainers
Your questions answered by Microsoft qualified trainers online.
Training formats & Services

Testimonials
(408 reviews) 
Her Majesty's Inspectorate Of Constabulary
Christopher Williams,
Business Analyst
The course was professionally run and extremely open and welcoming. The content was well covered and the exercises extremely helpful.
Excel Advanced  Formulas & Functions
United Biscuits
Stephanie Jensen,
Fats & Oils Buyer
Great, very clear, good teacher
Excel Advanced  Formulas & Functions
Shore Capital
Tony Throp,
Manager
Jens was very good and entertaining.
Excel Advanced  Formulas & Functions
Example training manuals
Below are some extracts from our free Excel training manuals.
Unit 4: Array formulas
In this unit, you will learn how to:
 Work with arrays in formulas
 Use the SUMPRODUCT function
 Create elegant formulas that appear to perform spreadsheet magic
 Compare SUMPRODUCT, SUMIFS with SUM arrays
 Use the TRANSPOSE function to switch row and column data
Understanding Array Formulas
If you do any computer programming, you've probably been exposed to the concept of an array. An array is simply a collection of items operated on collectively or individually. In Excel, an array can be one dimensional or two dimensional. These dimensions correspond to rows and columns. For example, a onedimensional array can be stored in a range that consists of one row (a horizontal array) or one column (a vertical array). A twodimensional array can be stored in a rectangular range of cells.
As you'll see, arrays need not be stored in cells. You can also work with arrays that exist only in Excel's memory which are known as array constants. You can then use an array formula to manipulate this information and return a result. An array formula can occupy multiple cells or reside in a single cell.
This section presents two array formula examples: an array formula that occupies multiple cells and another array formula that occupies only one cell.
A multicell array formula
The following table shows a simple worksheet set up to calculate product sales. Normally, you'd calculate the value in column D (total sales per product) with a formula such as the one that follows, and then you'd copy this formula down the column.
=B2*C2
After copying the formula down, the worksheet contains sixteen formulas in column D with a sales total formula in D18.
An alternative method uses one formula (an array formula) to calculate all sixteen values in D2:D17. This single formula occupies sixteen cells and returns an array of sixteen values. This is also known as a two dimensional array.
To create a multicell array formula to perform the calculations, follow these steps:
1. Select a range to hold the results. In this case, the range is D2:D17. Because you can't display more than one value in a single cell, sixteen cells are required to display the resulting array — so you select sixteen cells to make this array work.
2. Type the following formula:
=B2:B17*C2:C17
3. At this point you would normally press 'Enter' however, because this is an array formula you must press CTRL+SHIFT and then press Enter to activate the formula. This type of formula is also known as a CSE formula (CTRL+SHIFT+ENTER).
Note: You can't insert a multicell array formula into a range that has been designated a table (using Insert > Tables > Table). In addition, you can't convert a range that contains a multicell array formula to a table.
The formula is entered into all sixteen selected cells. If you examine the Formula bar, you see the following:
{=B2:B17*C2:C17}
Excel places curly brackets around the formula to indicate that it's an array formula.
This formula performs its calculations and returns a sixteenitem array. The array formula actually works with two other arrays, both of which happen to be stored in ranges. The values for the first array are stored in B2:B17 and the values for the second array are stored in C2:C17.
This array formula returns exactly the same values as these sixteen normal formulas entered into individual cells in D2:D17:
=B2*C2
=B3*C3
=B4*C4
=B5*C5
=B6*C6
=B7*C7
=B8*C8
=B9*C9
=B10*C10
=B11*C11
=B12*C12
=B13*C13
=B14*C14
=B15*C15
=B16*C16
=B17*C17
Using a single array formula rather than individual formulas does offer a few advantages:
 It's a good way to ensure that all formulas in a range are identical.
 Using a multicell array formula makes it less likely that you'll overwrite a formula accidentally. You can't change one cell in a multicell array formula. Excel displays an error message if you attempt to do so.
 Using a multicell array formula will almost certainly prevent novices from tampering with your formulas.
Using a multicell array formula as described in the preceding list also has some potential disadvantages:
 It's impossible to insert a new row into the range. But in some cases, the inability to insert a row is a positive feature. For example, you might not want users to add rows because it would affect other parts of the worksheet.
 If you add new data to the bottom of the range, you need to modify the array formula to accommodate the new data.
Multicell array block formula
Here is another example of a muticell array that creates the array in a block rather than a single column. The array formula in range D17:I25 calculates the quantity values in range D6:I14 multiplied by a Unit Price of 200 for Product Codes starting with the letter A. For the other Product Codes the array multiplies by 230.
To create the array formula first highlight the range D17:I25.
Then type the formula:
=IF(LEFT(C6:C14)="a",D6:I14*K1,D6:I14*K2)
Remember to press CSE after typing the formula and make it into an array:
{=IF(LEFT(C6:C14)="a",D6:I14*K1,D6:I14*K2)}
The advantage of using an array here is that there is no need for any absolute referencing of cells because there is no copying involved. To achieve the same calculation without using arrays the formula would read:
=IF(LEFT($C6)="a",D6*$K$1,D6*$K$2)
A singlecell array formula
Now let's take a look at a singlecell array formula, also known as a one dimensional array. The following example which is similar to the previous one does not use column D. The objective is to calculate the sum of the total product sales without using the individual calculations that were in column D.
When you enter this formula, make sure that you use Ctrl+Shift+Enter (and don't type the curly brackets because Excel automatically adds them for you).
This formula works with two arrays, both of which are stored in cells. The first array is stored in B2:B7 and the second array is stored in C2:C7. The formula multiplies the corresponding values in these two arrays and creates a new array (which exists only in memory). The SUM function then operates on this new array and returns the sum of its values.
Note: In this example, you can use the SUMPRODUCT function to obtain the same result without using an array formula: =SUMPRODUCT(B2:B17,C2:C17)
As you can see, array formulas allow many other types of calculations that are otherwise not possible.
Creating an array constant
The examples in the preceding section used arrays stored in worksheet ranges. The examples in this section demonstrate an important concept: An array need not be stored in a range of cells. This type of array, which is stored in memory, is referred to as an array constant.
To create an array constant, list its items (separated by commas) into a cell and surround the items with curly brackets as per the following example of a fiveitem horizontal array constant:
{2,5,0,0,7}
The following formula uses the SUM function, with the preceding array constant as its argument.
The formula returns the sum of the values in the array (which is 14):
=SUM({2,5,0,0,7})
Note: This formula uses an array, but the formula itself isn't an array formula. Therefore, you don't use Ctrl+Shift+Enter to enter the formula. Although, entering the formula as an array formula will also work producing the same result.
Note: When you specify an array directly (as shown above), you must type in the curly brackets around the array elements. When you enter an array formula, on the other hand, you do not supply the brackets.
Array constant elements
An array constant can contain numbers, text, logical values (TRUE or FALSE), and even error values, such as #N/A. Numbers can be in integer, decimal, or scientific format. You must enclose text in double quotation marks. You can use different types of values in the same array constant, as in this example:
{1,2,3,TRUE,FALSE,TRUE,”Bob”,”John”,”Sam”}
An array constant can't contain formulas, functions, or other arrays. Numeric values can't contain dollar signs, commas, parentheses, or percent signs. For example, the following is an invalid array constant:
{SQRT(32),$56.32,12.5%}
Understanding the Dimensions of an Array
As stated previously, an array can be one dimensional or two dimensional. A onedimensional array's orientation can be horizontal (corresponding to a single row) or vertical (corresponding to a single column).
Onedimensional horizontal arrays
The elements in a onedimensional horizontal array are separated by commas, and the array can be displayed in a row of cells. The following example is a onedimensional horizontal array constant:
{1,2,3,4,5}
Displaying this array in a range requires five consecutive cells in a row. To enter this array into a range, select a range of cells that consists of one row and five columns. Then enter ={1,2,3,4,5} and press Ctrl+Shift+Enter.
Note: If you enter this array into a horizontal range that consists of more than five cells, the extra cells will contain #N/A (which denotes unavailable values). If you enter this array into a vertical range of cells, only the first item (1) will appear in each cell.
The following example is another horizontal array; it has seven elements and is made up of text strings:
{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}
To enter this array, select seven cells in a row and type the following (followed by Ctrl+Shift+Enter):
={"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}
Onedimensional vertical arrays
The elements in a onedimensional vertical array are separated by semicolons, and the array can be displayed in a column of cells. The following is a sevenelement vertical array constant:
{10;20;30;40;50;60;70}
Displaying this array in a range requires seven cells in a column. To enter this array into a range, select a range of cells that consists of seven rows and one column. Then enter the following formula, followed by Ctrl+Shift+Enter:
={10;20;30;40;50;60;70}
The following is another example of a vertical array; this one has four elements:
{"Bell Housing";"Flange";"Gasket";"Electric Pump 750 amps"}
Twodimensional arrays
A twodimensional array uses commas to separate its horizontal elements and semicolons to separate its vertical elements. The following example shows a 3 × 4 array constant:
{1,2,3,4;5,6,7,8;9,10,11,12}
Displaying this array in a range requires 12 cells. To enter this array into a range, select a range of cells that consists of three rows and four columns. Then type the following formula, followed by Ctrl+Shift+Enter:
={1,2,3,4;5,6,7,8;9,10,11,12}
The following figure shows how this array appears when entered into a range (in this case, B3:E5).
If you enter an array into a range that has more cells than array elements, Excel displays #N/A in the extra cells. The next figure shows a 3 × 4 array entered into a 6 × 5 cell range.
Each row of a twodimensional array must contain the same number of items. The array that follows, for example, isn't valid, because the third row contains only three items:
{1,2,3,4;5,6,7,8;9,10,11}
Excel doesn't allow you to enter a formula that contains an invalid array.
Naming Array Constants
You can create an array constant, give it a name, and then use this named array in a formula. Technically, a named array is a named formula.
The following figure shows a named array being created from the New Name dialog box. (Access this dialog box by choosing Formulas > Defined Names > Define Name.) The name of the array is Wdays, and it refers to the following array constant:
{"Sun","Mon","Tue","Wed","Thu","Fri","Sat"}
Note: In the New Name dialog box, the array is defined (in the Refers To field) using a leading equal sign (=). Without this equal sign, the array is interpreted as a text string rather than an array. Also, you must type the curly brackets when defining a named array constant; Excel does not enter them for you.
After creating this named array, you can use it in a formula. The next figure shows a worksheet that contains a single array formula entered into the range A1:G1. The formula is:
{=Wdays}
Using TRANPOSE to insert a horizontal array into a vertical range of cells
Because commas separate the array elements, the array has a horizontal orientation. Use semicolons to create a vertical array. Or you can use the Excel TRANSPOSE function to insert a horizontal array into a vertical range of cells. The following array formula, which is entered into a sevencell vertical range, uses the TRANSPOSE function:
{=TRANSPOSE(Wdays)}
Using INDEX to access individual elements from an array
You also can access individual elements from the array by using the Excel INDEX function. The following formula, for example, returns Wed, the fourth item in the Wdays array:
=INDEX(Wdays,4)
Working with Array Formulas
This section deals with the mechanics of selecting cells that contain arrays and entering and editing array formulas. These procedures differ a bit from working with ordinary ranges and formulas.
Entering an array formula
When you enter an array formula into a cell or range, you must follow a special procedure so that Excel knows that you want an array formula rather than a normal formula. You enter a normal formula into a cell by pressing Enter. You enter an array formula into one or more cells by pressing Ctrl+Shift+Enter.
Don't enter the curly brackets when you create an array formula; Excel inserts them for you. If the result of an array formula consists of more than one value, you must select all the cells in the results range before you enter the formula. If you fail to do so, only the first element of the result is returned.
Selecting an array formula range
You can select the cells that contain a multicell array formula manually by using the normal cell selection procedures. Or you can use either of the following methods:
 Activate any cell in the array formula range. Display the Go To dialog box (choose Home > Editing > Find & Select > Go To, or just press F5). In the Go To dialog box, click the Special button and then choose the Current Array option. Click OK to close the dialog box.
 Activate any cell in the array formula range and press Ctrl+/ to select the entire array.
Editing an array formula
If an array formula occupies multiple cells, you must edit the entire range as though it were a single cell. The key point to remember is that you can't change just one element of a multicell array formula. If you attempt to do so, Excel displays the message shown in the following figure.
The following rules apply to multicell array formulas. If you try to do any of these things, Excel lets you know about it:
 You can't change the contents of any individual cell that makes up an array formula.
 You can't move cells that make up part of an array formula (but you can move an entire array formula).
 You can't delete cells that form part of an array formula (but you can delete an entire array).
 You can't insert new cells into an array range. This rule includes inserting rows or columns that would add new cells to an array range.
 You can't use multicell array formulas inside of a table that was created by choosing Insert > Tables > Table. Similarly, you can't convert a range to a table if the range contains a multicell array formula.
To edit an array formula, select all the cells in the array range and activate the Formula bar as usual (click it or press F2). Excel removes the brackets from the formula while you edit it. Edit the formula and then press Ctrl+Shift+Enter to enter the changes. All the cells in the array now reflect your editing changes.
Note: If you accidentally press Ctrl+Enter (instead of Ctrl+Shift+Enter) after editing an array formula, the formula will be entered into each selected cell, but it will no longer be an array formula. And it will probably return an incorrect result. Just reselect the cells, press F2, and then press Ctrl+Shift+Enter.
Thanks. Your download will begin shortly.
Please help us
Share or create a link to this manual today!
Just follow these simple instructions...