**Excel: Advanced Formulas and Functions**

English | MP4 | AVC 1280×720 | AAC 48KHz 2ch | 4h 24m | 673 MB

Follow along with Excel expert Dennis Taylor as he demystifies the hundreds of formulas and functions available in Excel. Dennis starts with a few critical formula shortcuts that will speed up your work, then covers a variety of functions, such as VLOOKUP, MATCH, and INDEX, statistical functions, text functions, and date and time, math, text, and information functions. Dennis provides practical examples to help viewers easily transition to using Excel’s most powerful formulas and functions in real-world scenarios. Note that this course is recorded in Excel for Office 365 but anyone using a recent version—including 2019, 2016, and 2013—will be able to follow along.

Topics include:

- Displaying and highlighting formulas
- Debugging formulas
- Creating 3D formulas
- Creating nested IF functions
- Exploring VLOOKUP, MATCH, and INDEX
- Tabulating data with COUNTIFS, SUMIFS, and AVERAGEIFS
- Finding values with statistical functions
- Adjusting results with rounding functions
- Converting values between measuring systems
- Calculating dates
- Returning reference data
- Manipulating text
- Extracting information

**Table of Contents**

1 Use the most powerful formulas and functions in Excel

2 Display and highlight formulas

3 Use the auditing tools

4 Use entire row column references

5 Change formulas to values and update values without formulas

6 Simplify debugging formulas with the F9 key

7 Enhance readability with range names

8 Create 3D formulas to tabulate data from multiple sheets

9 Explore IF logical tests and use relational operators

10 Create and expand the use of nested IF statements

11 Create compound logical tests with AND, OR, NOT, and IF

12 Use IFS for multiple conditions

13 Explore the VLOOKUP and HLOOKUP functions

14 Find approximate matches with VLOOKUP and HLOOKUP

15 Use VLOOKUP to find exact matches and search large tables

16 Find table-like data within a function using CHOOSE

17 Use the SWITCH function for formula-embedded selection

18 Locate data with the MATCH function

19 Retrieve information by location with the INDEX function

20 Use the MATCH and INDEX functions together

21 Document formulas with the FORMULATEXT function

22 Extract and count unique entries from a list with UNIQUE

23 Tabulate data using a single criterion with COUNTIF, SUMIF, and AVERAGEIF

24 Tabulate data using multiple criteria with COUNTIFS, SUMIFS, and AVERAGEIFS

25 Use MAXIFS and MINIFS

26 Use the SUBTOTAL function to prevent double counting

27 Find middle and most common values with MEDIAN and MODE

28 Rank data without sorting using RANK and RANK.EQ

29 Find the largest and smallest values with LARGE and SMALL

30 Tabulate blank cells with the COUNTBLANK function

31 Use COUNT, COUNTA, and the status bar

32 Work with the ROUND, ROUNDUP, and ROUNDDOWN functions

33 Use MROUND, CEILING, and FLOOR for specialized rounding

34 Use INT, TRUNC, ODD, and EVEN for specialized rounding

35 Use MOD to find remainders and apply conditional formatting

36 Explore practical uses for RAND, RANDARRAY, and RANDBETWEEN

37 Convert a value between measurement systems with CONVERT

38 Use the AGGREGATE function to bypass errors and hidden data

39 Use ROMAN and ARABIC to display different number systems

40 Understand Excel date and time capabilities in formulas

41 Use various date and time functions

42 Use the TODAY and NOW functions for date and time entry

43 Identify weekdays with the WEEKDAY function

44 Count working days and completion dates (NETWORKDAYS and WORKDAY)

45 Tabulate date differences with the DATEDIF function

46 Calculate dates with EDATE and EOMONTH

47 Get data from remote cells with the OFFSET function

48 Return references with the INDIRECT function

49 Use INDIRECT with Data Validation for multitiered pick lists

50 Locate and extract data with FIND, SEARCH, and MID

51 Extract data with the LEFT and RIGHT functions

52 Use the TRIM function to remove unwanted spaces in a cell

53 Combine data with symbols (&) and CONCATENATE

54 Use CONCAT and TEXTJOIN to combine data from different cells

55 Adjust alphabetic case with UPPER, LOWER, and PROPER

56 Adjust character content with REPLACE and SUBSTITUTE

57 Use utility text functions TEXT, REPT, VALUE, and LEN

58 Extract information with the CELL and INFO functions

59 Explore various information functions

60 Use several error-checking functions

61 Track and highlight formula cells with ISFORMULA

62 Next steps

Resolve the captcha to access the links!