FORMULA AND FUNCTION TIPS AND SHORTCUTS
- Using the entire row/column references
- Copying column formulas instantly
- Converting formulas to values with a simple drag
- 3D formulas to gather data from multiple sheets
- Updating values without formulas
- Displaying and highlighting formulas
- Simplifying debugging formulas
- Enhancing readability with range names
FORMULA AND FUNCTION TOOLS
- Reviewing function basics
- Using and extending AutoSum
- Absolute and relative references
- Mixed references
IF AND RELATED FUNCTIONS
- IF logical tests and using operators
- Nested IF statements
- Using the AND, OR, and NOT functions with IF
POWER FUNCTIONS
- Tabulating information on a single criterion – COUNTIF, SUMIF, and AVERAGEIF
- Tabulating information on multiple criteria – COUNTIFS, SUMIFS, and AVERAGEIFS
STATISTICAL FUNCTIONS
- Finding the middle value – MEDIAN
- Ranking data without sorting – RANK
- Finding the magnitude data – LARGE and SMALL
- Tabulating blank cells – COUNTBLANK
DATE FUNCTIONS
- Excel date/time capabilities in formulas
- Identifying the day of the week – WEEKDAY
- Counting working days – NETWORKDAYS
- Determining a completion date – WORKDAY
- Tabulating date/time differences – DATEDIF
MATH FUNCTIONS
- Working with rounding functions
MOD
- Random number generation – RAND and RANDBETWEEN
- Converting a value between systems – CONVERT
- Using the powerful new AGGREGATE function to bypass errors and hidden data
LOOKUP AND REFERENCE FUNCTIONS (OPTIONAL)
- Looking up information – VLOOKUP and HLOOKUP
- Finding approximate or exact matches – VLOOKUP
- Nesting LOOKUP functions
- Finding table-like information within a function – CHOOSE
- Locating data – MATCH
- Retrieving information by location – INDEX
- Using MATCH and INDEX together