Advanced Excel Formulas and Functions Training

Locating and extracting data with FIND, SEARCH, and MID; Extracting specific data with LEFT and RIGHT; Removing extra spaces with TRIM and removing hidden characters with CLEAN; Using ampersands and CONCATENATE to combine data from different cells; Adjusting the case within cells with PROPER, UPPER, and LOWER; Adjusting character content with REPLACE and SUBSTITUTE; Using other utility text functions: LEN, REPT, VALUE, TEXT

Finding the middle value with MEDIAN; Ranking data without sorting with RANK; Finding the largest and smallest values with LARGE and SMALL; Tabulating blank cells with COUNTBLANK; Using COUNT, COUNTA, and the status bar

Working with ROUND, ROUNDUP, and ROUNDDOWN; 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; Practical uses for the random number functions RAND and RANDBETWEEN; 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 numeral systems

Understanding Excel date/time capabilities in formulas; Using TODAY and NOW functions for dynamic date/time entry; Identifying the day of the week with WEEKDAY; Counting working days with NETWORKDAYS; Determining a completion date with WORKDAY; Tabulating date differences with DATEDIF; Calculating end-of-month and future/past dates with EDATE and EOMONTH; Converting text entries into dates and times with DATEVALUE and TIMEVALUE

Extending formula capabilities with arrays; Counting unique entries in a range with an array formula; Determining frequency distributions with FREQUENCY; Flipping row/column orientation with TRANSPOSE; Building analysis via regression techniques with TREND and GROWTH; Using array formula techniques with the MATCH function for complex lookups

Getting data from remote cells with OFFSET; Returning references with INDIRECT; Using INDIRECT with data validation for two-tiered pick list scenarios; 11. Text Functions; Locating and extracting data with FIND, SEARCH, and MID; Extracting specific data with LEFT and RIGHT; Removing extra spaces with TRIM and removing hidden characters with CLEAN; Using ampersands and CONCATENATE to combine data from different cells; Adjusting the case within cells with PROPER, UPPER, and LOWER; Adjusting character content with REPLACE and SUBSTITUTE; Using other utility text functions: LEN, REPT, VALUE, TEXT

Extracting information with the CELL and INFO functions; Using ISBLANK, ISODD, ISEVEN, ISTEXT, and ISNUMBER; Using error-checking functions ISERR, ISERROR, IFERROR, ISNA, and IFNA; Using the ISFORMULA function with conditional formatting