From the course: Excel 2016: Advanced Formulas and Functions
Welcome
From the course: Excel 2016: Advanced Formulas and Functions
Welcome
- Hi, I'm Dennis Taylor, and welcome to Excel 2016: Advanced Formulas and Functions. For many Excel users, the most important thing they do in Excel is work with formulas and functions. If you really want to take advantage of Excel's power, you need exposure to these features. I'll start off by showing you some great shortcuts for building formulas and functions, numerous variations on the powerful IF and VLOOKUP functions, vital statistical and math functions, and some date functions that could prove to be indispensable. I'll show you how text functions can help you clean up flawed data, how powerful array formulas and functions give you even more analytical possibilities, and I'll show you how to use functions in combination with one another to achieve even greater power. Welcome to Excel 2016: Advanced Formulas and Functions.
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.
Contents
-
-
Displaying and highlighting formulas4m 43s
-
Using Auditing tools6m 15s
-
Using entire row and column references4m 4s
-
Copying column formulas instantly2m 45s
-
Converting formulas to values with a drag4m 59s
-
Updating values without formulas3m 26s
-
Debugging formulas with the F9 key4m 2s
-
(Locked)
Enhancing readability with range names5m 23s
-
(Locked)
Tabulating data from multiple sheets8m 20s
-
-
-
(Locked)
Hierarchy of operations in formulas6m 28s
-
(Locked)
Formulas tab for locating functions5m 34s
-
(Locked)
Insert Function for learning about unfamiliar functions5m 20s
-
(Locked)
Extending the capabilities of AutoSum6m 10s
-
(Locked)
Absolute and relative references5m 4s
-
(Locked)
Using mixed references in formulas6m
-
(Locked)
Using autocalculate in the status bar5m 45s
-
(Locked)
-
-
(Locked)
Looking up information with VLOOKUP and HLOOKUP4m 30s
-
(Locked)
Finding approximate matches with VLOOKUP and HLOOKUP7m 16s
-
(Locked)
Finding exact matches with VLOOKUP5m 30s
-
(Locked)
Nesting lookup functions4m 12s
-
(Locked)
Using VLOOKUP with large tables4m 21s
-
(Locked)
Finding table-like information within a function using the CHOOSE function2m 44s
-
(Locked)
Using the SWITCH function for formula-embedded selection5m 40s
-
(Locked)
Locating data with the MATCH function3m 43s
-
(Locked)
Retrieving information by location2m 53s
-
(Locked)
Using MATCH and INDEX functions together4m 14s
-
(Locked)
-
-
(Locked)
Finding the middle value with MEDIAN and most common value with MODE2m 21s
-
(Locked)
Ranking data without sorting with RANK and RANK.EQ4m 8s
-
(Locked)
Finding the largest and smallest values with the LARGE and SMALL functions1m 47s
-
(Locked)
Tabulating blank cells with the COUNTBLANK function5m 29s
-
(Locked)
Using COUNT, COUNTA, and the status bar3m 32s
-
(Locked)
-
-
(Locked)
Working with the ROUND, ROUNDUP, and ROUNDDOWN functions6m 13s
-
(Locked)
Working with MROUND, CEILING, and FLOOR for specialized rounding3m 20s
-
(Locked)
Using the INT and TRUNC functions to extract integer data3m 58s
-
(Locked)
Finding the remainder with MOD and using MOD with conditional formatting5m 26s
-
(Locked)
Exploring practical uses for the RAND and RANDBETWEEN functions4m 57s
-
(Locked)
Converting a value between measurement systems with CONVERT2m 55s
-
(Locked)
Using the powerful AGGREGATE function to bypass errors and hidden data6m 32s
-
(Locked)
Using the ROMAN and ARABIC functions to display different number systems3m 25s
-
(Locked)
-
-
(Locked)
Understanding Excel date and time capabilities in formulas9m 52s
-
(Locked)
Using the DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, and SECOND functions4m 4s
-
(Locked)
Using the TODAY and NOW functions for dynamic date and time entry6m 49s
-
(Locked)
Identifying the day of the week with the WEEKDAY function4m 40s
-
(Locked)
Counting working days with the NETWORKDAYS function5m 26s
-
(Locked)
Determining a completion date with the WORKDAY function4m 10s
-
(Locked)
Tabulating date differences with the DATEDIF function6m 15s
-
(Locked)
Calculating end-of-month and future and past dates with EDATE and EOMONTH4m 58s
-
(Locked)
-
-
(Locked)
Extending formula capabilities with array formulas5m 7s
-
(Locked)
Counting unique entries in a range with an array formula5m 51s
-
(Locked)
Determining frequency distributions with the FREQUENCY function3m 53s
-
(Locked)
Flipping row and column orientation with TRANSPOSE5m 10s
-
(Locked)
Building analysis via regression techniques with TREND and GROWTH2m 59s
-
(Locked)
Using array formulas and the MATCH function for complex lookups5m 30s
-
(Locked)
-
-
(Locked)
Locating and extracting data with the FIND, SEARCH, and MID functions6m 20s
-
(Locked)
Extracting specific data with the LEFT and RIGHT functions3m 22s
-
(Locked)
Using the TRIM function to remove unwanted spaces in a cell5m 43s
-
(Locked)
Using ampersands and CONCATENATE to combine data from different cells5m 51s
-
(Locked)
Using the CONCAT and TEXTJOIN to combine data from different cells6m 8s
-
(Locked)
Adjusting alphabetic case with the UPPER, LOWER, and PROPER functions3m 48s
-
(Locked)
Adjusting character content with the REPLACE and SUBSTITUTE functions5m 41s
-
(Locked)
Using the utility text functions: TEXT, REPT, and LEN5m 7s
-
(Locked)
-
-
(Locked)
Extracting information with the CELL and INFO functions4m 10s
-
(Locked)
Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER5m 9s
-
(Locked)
Using the ISERR, ISERROR, IFERROR, and ISNA error-checking functions7m 53s
-
(Locked)
Tracking and highlighting formula cells with the ISFORMULA function3m 2s
-
(Locked)