Overview
-
Learn how to create efficient formulas and use some of the 450+ functions in Excel to tabulate and analyze numerical, date, and text data.
Syllabus
-
Introduction
- Welcome
- Using the exercise files
- Displaying and highlighting formulas
- Using Auditing tools
- Using entire row and column references
- Copying column formulas instantly
- Converting formulas to values with a drag
- Updating values without formulas
- Debugging formulas with the F9 key
- Enhancing readability with range names
- Tabulating data from multiple sheets
- Hierarchy of operations in formulas
- Formulas tab for locating functions
- Insert Function for learning about unfamiliar functions
- Extending the capabilities of AutoSum
- Absolute and relative references
- Using mixed references in formulas
- Using autocalculate in the status bar
- IF logical tests
- Expanding nested IF statements
- AND, OR, and NOT functions with IF
- Using IFS for multiple conditions
- Looking up information with VLOOKUP and HLOOKUP
- Finding approximate matches with VLOOKUP and HLOOKUP
- Finding exact matches with VLOOKUP
- Nesting lookup functions
- Using VLOOKUP with large tables
- Finding table-like information within a function using the CHOOSE function
- Using the SWITCH function for formula-embedded selection
- Locating data with the MATCH function
- Retrieving information by location
- Using MATCH and INDEX functions together
- Tabulating data using a single criterion
- Tabulating data using multiple criteria
- Using MAXIFS and MINIFS
- Preventing double counting
- Finding the middle value with MEDIAN and most common value with MODE
- Ranking data without sorting with RANK and RANK.EQ
- Finding the largest and smallest values with the LARGE and SMALL functions
- Tabulating blank cells with the COUNTBLANK function
- Using COUNT, COUNTA, and the status bar
- Working with the ROUND, ROUNDUP, and ROUNDDOWN functions
- Working with MROUND, CEILING, and FLOOR for specialized rounding
- Using the INT and TRUNC functions to extract integer data
- Finding the remainder with MOD and using MOD with conditional formatting
- Exploring practical uses for the RAND and RANDBETWEEN functions
- Converting a value between measurement systems with CONVERT
- Using the powerful AGGREGATE function to bypass errors and hidden data
- Using the ROMAN and ARABIC functions to display different number systems
- Understanding Excel date and time capabilities in formulas
- Using the DATE, YEAR, MONTH, DAY, TIME, HOUR, MINUTE, and SECOND functions
- Using the TODAY and NOW functions for dynamic date and time entry
- Identifying the day of the week with the WEEKDAY function
- Counting working days with the NETWORKDAYS function
- Determining a completion date with the WORKDAY function
- Tabulating date differences with the DATEDIF function
- Calculating end-of-month and future and past dates with EDATE and EOMONTH
- Extending formula capabilities with array formulas
- Counting unique entries in a range with an array formula
- Determining frequency distributions with the FREQUENCY function
- Flipping row and column orientation with TRANSPOSE
- Building analysis via regression techniques with TREND and GROWTH
- Using array formulas and the MATCH function for complex lookups
- Getting data from remote cells with the OFFSET function
- Returning references with the INDIRECT function
- Using INDIRECT with Data Validation for two-tiered pick list scenarios
- Locating and extracting data with the FIND, SEARCH, and MID functions
- Extracting specific data with the LEFT and RIGHT functions
- Using the TRIM function to remove unwanted spaces in a cell
- Using ampersands and CONCATENATE to combine data from different cells
- Using the CONCAT and TEXTJOIN to combine data from different cells
- Adjusting alphabetic case with the UPPER, LOWER, and PROPER functions
- Adjusting character content with the REPLACE and SUBSTITUTE functions
- Using the utility text functions: TEXT, REPT, and LEN
- Extracting information with the CELL and INFO functions
- Using ISBLANK, ISODD, ISEVEN, ISTEXT, ISNONTEXT, and ISNUMBER
- Using the ISERR, ISERROR, IFERROR, and ISNA error-checking functions
- Tracking and highlighting formula cells with the ISFORMULA function
- Next steps
