**Microsoft Excel – Advanced Excel Formulas & Functions**

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 8 Hours | 2.65 GB

Master 75+ Excel formulas with hands-on demos from a best-selling Microsoft Excel instructor (Excel ’07 – Excel 2019)

It’s time to show Excel who’s boss. Whether you’re starting from square one or aspiring to become an absolute Excel power user, you’ve come to the right place.

This course will give you a deep understanding of the advanced Excel formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. While most Excel courses focus on simply what each formula does, I teach through hands-on, contextual examples designed to showcase why these formulas are awesome and how they can be applied in a number of ways. I will not train you to regurgitate functions and formula syntax; I will teach you how to THINK like Excel.

By the end of the course you’ll be writing robust, elegant formulas and functions from scratch, allowing you to:

- Easily build dynamic tools & Excel dashboards to filter, display and analyze your data
- Go rogue and design your own formula-based Excel formatting rules
- Join datasets from multiple sources with Excel’s LOOKUP, INDEX & MATCH functions
- Pull real-time data from APIs directly into Excel (weather, stock quotes, directions, etc.)
- Manipulate dates, times, text, and arrays
- Automate tedious and time-consuming tasks using cell formulas and functions in Excel (no VBA required!)

We’ll dive into a broad range of Excel formulas & functions, including:

- Lookup/Reference functions
- Statistical functions
- Formula-based formatting
- Date & Time functions
- Logical operators
- Array formulas
- Text functions
- INDIRECT & HYPERLINK
- Web scraping with WEBSERVICE & FILTERXML

What you’ll learn

- Learn how to THINK like Excel, and write powerful and dynamic Excel formulas from scratch
- Automate, streamline, and completely revolutionize your workflow with Excel
- Master unique tips, tools and case studies that you won’t find in ANY other course, guaranteed
- Explore fun, interactive, and highly effective lessons from a best-selling Excel instructor
- Get LIFETIME access to project files, quizzes, homework exercises, and 1-on-1 expert support
- Build Excel formulas to analyze dates, text fields, values and arrays

**Table of Contents**

**Getting Started**

1 Course Structure Outline

2 READ ME Important Notes for New Students

3 DOWNLOAD Course Resources

4 Setting Expectations

5 Looking to become an Excel MASTER

**Excel Formulas 101**

6 Introduction Excel Formulas 101

7 Excel Formula Syntax

8 Writing Efficient Formulas with Fixed Relative References

9 Common Excel Error Types

10 Formula Auditing Trace Precedents Dependents

11 Formula Auditing Evaluate Formula Error Checking (PC Only)

12 Navigating Excel Worksheets with Ctrl Shortcuts

13 Saving Time with Function Shortcuts

14 Accessing Tools with Alt Key Tips

15 PRO TIP Creating Drop-Down Menus with Data Validation

16 Congrats Youre a Developer

17 HOMEWORK Excel Formulas 101

**Conditional Statements Logical Operators**

18 Introduction

19 Anatomy of the IF Statement

20 Nesting Multiple IF Statements

21 Adding Conditional ANDOR Operators

22 Using NOT Conditionals

23 Fixing Errors with IFERROR

24 Common IS Statements

25 HOMEWORK Excel Logical Operators

**Common Excel Statistical Functions**

26 Introduction

27 Basic Excel Statistical Functions

28 Extracting Values with SMALLLARGE RANKPERCENTRANK

29 Randomization with RAND() RANDBETWEEN

30 Row-Level Calculation with SUMPRODUCT

31 DEMO Calculating Shipping Costs with SUMPRODUCT

32 Conditional Aggregation with COUNTIFS SUMIFS AVERAGEIFS

33 DEMO Building a Basic Dashboard with COUNTIFS SUMIFS

34 DEMO Counting Duplicates with COUNTIF SUMPRODUCT

35 PRO TIP Data Profiling with COUNT SUMPRODUCT

36 HOMEWORK Excel Stats Functions

**Lookup Reference Functions**

37 Introduction

38 Working with Named Ranges

39 Counting Rows Columns with ROWROWS COLUMNCOLUMNS

40 Introduction to VLOOKUPHLOOKUP

41 Joining Data with VLOOKUP

42 Fixing Errors with IFERROR VLOOKUP

43 VLOOKUP Reference Array Options

44 Approximate Match Lookups

45 Navigating Cell Ranges with INDEX

46 Matching Text Values with MATCH

47 Combining INDEX MATCH to Dynamically Search Ranges

48 Combining MATCH VLOOKUP for More Flexible Lookups

49 TROUBLESHOOTING VLOOKUP with Duplicate Keys

50 SNEAK PEEK XLOOKUP (Office 365 Only)

51 DEMO Building Dashboards with XLOOKUP (Office 365 Only)

52 Selecting List Items with CHOOSE

53 Defining Ranges with OFFSET

54 Combining OFFSET with COUNTA to Create a Flexible Range

55 PRO TIP Using OFFSET to Create Interactive Charts

56 HOMEWORK Excel Lookup Reference Functions

**Text Functions**

57 Introduction

58 Capitalization with UPPER LOWER PROPER TRIM

59 Combining Text with CONCATENATE ()

60 Extracting Strings with LEFT MID RIGHT LEN

61 Converting Text to Values with TEXT VALUE

62 Searching Text String with SEARCH FIND

63 Categorizing Data with IF(ISNUMBER(SEARCH))

64 Combining RIGHT LEN and SEARCH

65 Replacing Text with SUBSTITUTE

66 HOMEWORK Excel Text Functions

**Date Time Functions**

67 Introduction

68 Understanding Excel Date Syntax with DATEVALUE

69 Formatting Dates Filling Date Series

70 Creating Real-Time Functions with TODAY NOW

71 Extracting Time Periods with YEAR MONTH DAY HOUR MINUTE SECOND

72 Calculating the Month Start or End with EOMONTH

73 Calculating of Year with YEARFRAC

74 Defining Time Periods with WEEKDAY WORKDAY NETWORKDAYS

75 Calculating Differences Between Dates with DATEDIF (Legacy Function)

76 DEMO Building a Simple Budget Pacing Tool

77 HOMEWORK Excel Date Time Functions

**Formula-Based Formatting**

78 Introduction

79 Creating Editing Managing Formula-Based Rules

80 Highlighting Rows Using the MOD Function

81 Formatting Based on the Value of Another Cell

82 Formatting Cells Using Stats Functions

83 Formatting Cells Using Text Functions Logical Operators

84 HOMEWORK Excel Formula-Based Formatting

**Array Formulas**

85 Introduction

86 READ ME Array Formulas are Changing

87 Rules of Excel Array Functions

88 Pros Cons of Array Functions

89 Defining Vertical Horizontal and 2-Dimensional Arrays

90 Using Array Constants in Formulas

91 Defining Referencing Named Array Constants

92 Restructuring Data with TRANSPOSE

93 Linking Data Between Sheets (Array vs. Non-Array Comparison)

94 Returning the Largest Values in a Range

95 Counting Characters Across Cells

96 Creating a MAX IF Array Formula

97 Creating a MAX IF Array Formula with Multiple Criteria

98 Converting Boolean Values Using the Double Unary (–)

99 HOMEWORK Excel Array Functions

**Extra Bonus Functions**

100 Introduction

101 Creating Custom References with INDIRECT

102 Linking to Worksheet Locations with HYPERLINK

103 Real-Time Weather Conditions with WEBSERVICE FILTERXML (Part 1)

104 Real-Time Weather Conditions with WEBSERVICE FILTERXML (Part 2)

**Wrapping Up**

105 Conclusion Next Steps

106 More from Maven Analytics

Resolve the captcha to access the links!