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
Face to face / Virtual public schedule & onsite training. Restaurant lunch included at STL venues.
Designed for Excel 365
(658 reviews, see all 98,708 testimonials) |
From £230 List price £350
- 1 day Instructor-led
- 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.
You may also wish to consider one of our finance for non financial managers courses.
Prerequisites
Familiarity with creating functions including IFs, lookup functions, COUNTIFS, SUMIFS and nested functions, or our Excel Advanced part 1 course.
Benefits
At the end of this course, you will have a greater understanding of building complex formulas and functions. You will be able to create advanced nested functions to produce solutions and outcomes in your data sets.You will find quicker ways to customise formulas by taking advantage of the built-in functions available in Excel. Save time by learning more sophisticated search & auditing techniques when working with data-heavy spreadsheets.
You will be introduced to the latest functions available in Excel 365, as well as less well-known but powerful existing functions.
In addition to learning a wide range of new formulas, you will also discover best-practice for setting up Excel files so they are flexible, appropriate, well-structured, and transparent.
Also available is the Excel Advanced - For Power Users course.
Course Syllabus
Building complex formulas
Nested functions best practice
Writing functions more efficiently using Formatted Tables
Get an understanding of how nested functions get executed
Statistical and forecasting functions
LARGE, SMALL, ROUND
CORREL & SLOPE
FORECAST.LINEAR
FORECAST.ETS
Date, Time & Text Functions
Smarter ways to calculate date & time
WORKDAY, DATEDIF, EDATE, WEEKNUM
Text functions:
UPPER, PROPER, FIND, MID, SEARCH, LEFT, RIGHT, LEN
TRIM excess space in cells
TEXTJOIN & CONCAT
Introduction to Array formulas
Using embedded Excel Array formulas
TREND, GROWTH, FREQUENCY
UNIQUE, SPILL, SORT (365)
SORTBY, FILTER (365)
Creating bespoke Array formulas
Advanced Lookup & Reference
XLOOKUP & XMATCH
ADDRESS
INDIRECT
OFFSET
CHOOSE
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
"What do I get on the day?"
Arguably, the most experienced and highest motivated trainers.
Face-to-face training
Training is held in our modern, comfortable, air-conditioned suites.
Modern-spec IT, fully networked with internet access
Lunch, breaks and timing
A hot lunch is provided at local restaurants near our venues:
- Bloomsbury
- Limehouse
Courses start at 9:30am.
Please aim to be with us for 9:15am.
Browse the sample menus and view joining information (how to get to our venues).
Refreshments
Available throughout the day:
- Hot beverages
- Clean, filtered water
- Biscuits
Virtual training
Regular breaks throughout the day.
Learning tools
In-course handbook
Contains unit objectives, exercises and space to write notes
Reference material
Available online. 100+ pages with step-by-step instructions
24 months access to Microsoft trainers
Your questions answered on our support forum.
Training formats & Services
Training Formats & Services
|
Testimonials
Globo International London Ltd
Suellen Juvenato,
ACCOUNTS ANALYST
Even tough the content of the course did not meet my expectations, the trainer was wonderful. Patience and knowledge can be his other surnames.
Excel Advanced - Formulas & Functions
BMG Chrysalis
Mai Yang,
Assistant Management Accountant
Found the course very useful, though I may need to do another course that relates to my work some more
Excel Advanced - Formulas & Functions
Duracell
Andreia Martins,
Order Management Analyst
Jens is amazing. He is very enthusiastic and he's an expert on Excel. He provides real-world situations and he gives up a sneak peek to other courses. I can't wait to learn more. Well done STL!
Excel Advanced - Formulas & Functions
Learning & Development Resources
Blog
- Streamline Data Analysis with Excel Dynamic Arrays Feature
- Be more productive with Excel’s UNIQUE function (3 of 4)
- Using the Concatenate Function in Excel
- Be More Productive With Excel’s FILTER function (1 of 4)
- Be More Productive With Excel’s SORT function (2 of 4)
- Use the Error checker tool in Excel
Training manual sample
Below are some extracts from our 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 one-dimensional array can be stored in a range that consists of one row (a horizontal array) or one column (a vertical array). A two-dimensional 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 sixteen-item 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.
Multi-cell array block formula
Here is another example of a muti-cell 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 single-cell array formula
Now let's take a look at a single-cell 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 five-item 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 one-dimensional array's orientation can be horizontal (corresponding to a single row) or vertical (corresponding to a single column).
One-dimensional horizontal arrays
The elements in a one-dimensional horizontal array are separated by commas, and the array can be displayed in a row of cells. The following example is a one-dimensional 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"}
One-dimensional vertical arrays
The elements in a one-dimensional vertical array are separated by semicolons, and the array can be displayed in a column of cells. The following is a seven-element 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"}
Two-dimensional arrays
A two-dimensional 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 two-dimensional 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 seven-cell 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...