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!