Complex Excel Formulas and VBA User Defined Functions | Grant Gamble | Skillshare

## Complex Excel Formulas and VBA User Defined Functions

#### Grant Gamble

Play Speed
• 0.5x
• 1x (Normal)
• 1.25x
• 1.5x
• 2x
61 Videos (8h 38m)
• 1.1. Welcome to Advanced Excel Formulas and VBA User Defined Functions

3:45
• 2.1. How this course works

3:32
• 2.2. Writing complex Excel formulas

7:23
• 3.1. IF statements

8:45
• 3.2. Nested IF statements

9:22
• 3.3. The IFS function

7:00
• 3.4. Using VLOOKUP instead of IF

3:58
• 3.5. The SWITCH function

7:57
• 3.6. IFERROR

5:52
• 4.1. Combining LEFT, SEARCH and VLOOKUP

9:35
• 4.2. Combining MID, SEARCH and IF

8:33
• 4.3. Combining RIGHT, LEN and SEARCH

5:39
• 4.4. Creating a sentence case formula

5:37
• 4.5. Combining LEN and SUBSTITUTE

7:48
• 4.6. Brute force substitution

18:17
• 4.7. Creating a title case formula

10:19
• 4.8. Conditional concatenation

6:25
• 4.9. Starts with a number

9:24
• 5.1. Nesting VLOOKUP functions

7:26
• 5.2. VLOOKUP multiple criteria - Basic

4:48
• 5.3. Using MATCH & INDEX

6:26
• 5.4. Advanced MATCH & INDEX

7:56
• 5.5. Optimizing Lookup Operations Part 1

10:20
• 5.5. Optimizing Lookup Operations Part 2

14:32
• 5.6. OFFSET Function

7:32
• 5.7. Using the INDIRECT function

4:37
• 5.8. VLOOKUP multiple criteria - Advanced

11:33
• 6.1. Controlling rounding sensitivity

12:53
• 6.2. Using SUMIF and INDIRECT

7:19
• 6.3. Using wildcards with SUMIFS, AVERAGEIFS, MINIFS and MAXIFS

14:37
• 6.4. Using the SUBTOTAL function

8:26
• 6.5. The RANK function variations

13:49
• 6.6. Using LARGE, SMALL and COUNTA Part 1

7:41
• 6.6. Using LARGE, SMALL and COUNTA Part 2

9:01
• 6.6. Using LARGE, SMALL and COUNTA Part 3

10:48
• 7.1. Array formula basics

8:54
• 7.2. Using conditionals in array formulas

11:18
• 7.3. Using concatenation in array formulas

6:37
• 7.4. Array formulas and data tables

6:04
• 7.5. The TRANSPOSE function

5:43
• 8.1. Using the WEBSERVICE function

10:05
• 8.2. XPath overview

4:46
• 8.3. Using FILTERXML and ENCODEURL

12:18
• 8.4. Using FILTERXML with local XML files

8:49
• 8.5. Using XPath predicates

8:13
• 8.6. The new CONCAT function

8:13
• 8.7. Descendant-or-self axis

3:06
• 9.1. Making the Developer tab visible

1:41
• 9.2. The Visual Basic Editor

5:49
• 9.3. Writing a UDF in a macro-enabled workbook

10:54
• 9.4. Using trusted locations

3:21
• 9.5. Writing a UDF in the Personal Workbook

10:25
• 10.1. VBA syntax

7:38
• 10.2. Excel object model

8:47
• 10.3. VBA object model

4:57
• 10.4. Displaying the user name

11:59
• 10.5. Displaying workbook properties

8:26
• 10.6. Displaying worksheet properties

14:20
• 11.1. Using IF statements

12:36
• 11.2. Using Select Case statements

7:13
• 11.3. VLOOKUP pro rata function

16:50

(Please note that this course is designed for experienced Excel users. If you are still shaky on the basics of Excel formulas, have a look at the SkillShare course "Essential Excel Formulas and Functions". )

The ability to write, understand and maintain complex formulas is the key to mastering Excel. Complex formulas make use of multiple functions to achieve a specific operation; and it is the need to nest one function inside another that can sometimes make Excel formulas seem so impenetrable.

This course will show you how to create, comprehend and maintain complex Excel formulas; those which use multiple Excel functions in combination.

A key feature of the course is the use of explanatory diagrams. The course features dozens of diagrams saved in PDF format. You will have access to all of these diagrams. You can study them at your leisure. You can print them out; make notes on them; whatever you find useful.

Another key feature of the course is that we write our formulas on multiple lines and make strategic use of indentation. This makes it easier to read complex formulas, since it helps to make it clear when one function is nested inside another.

We’ll also encounter examples of using formulas to build formulas; mega formulas, sometimes dozens of lines long, which would be very hard to write manually.

We begin the course by looking at conditional functions, since these are the most versatile of Excel’s functions.

Then we’ll move on to look at building formulas for manipulating text; for manipulating numbers; formulas for performing complex lookup operations. And we’ll also discuss the use of array formulas; as well as formulas for importing XML-based information into Excel, both from the web and from local XML documents.

And finally, we’ll move on to look at the creation of custom, user-defined functions using VBA. These functions can be included in any workbook and can greatly enhance your Excel solutions.

Many courses which teach Excel user-defined functions will waste your time by showing you how to create VBA functions which replicate functionality which could be created by regular Excel formulas. Not on our course!

We’ll focus on writing VBA functions which provide results that simply can’t be produced using Excel’s own built-in functions: for example displaying the user’s Windows login name, or the names of all the worksheet tabs; or displaying information from the folder in which a workbook is saved.

So, if you’re looking for a challenging course which will provide you with strategies for creating powerful and complex Excel formulas, then this course is exactly the course you need: “Complex Excel Formulas and VBA user-defined functions”.

16

Students

--

Projects

• --
• Beginner
• Intermediate
• All Levels
• Beg/Int

Level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

#### Grant Gamble

Grant Gamble is an experienced IT trainer, developer, consultant and author able to deliver a wide range of training courses. He has a vast experience of delivering public and on-site IT training content at different skill levels, to groups of varying sizes.

His UK company G Com Solutions Limited provide IT training courses and consultancy to a wide range of UK and international clients. His speciality is running week-long, intensive training workshops on topics like Microsoft Power BI...

See full profile

Report class