LRN0238 - Advanced Excel Functions
Course Description
Advance your Excel skills and go beyond the basics. Learn powerful functions to efficiently analyze, organize, and manipulate data. Discover how to apply advanced tools in real-world scenarios to boost productivity, uncover insights, and work smarter with data.Course Outline
Unit 1
SUM & COUNT
• SUMIF/ SUMIFS. Conditional summation for complex data analysis. Advantage: Reduces manual calculations and filters data simultaneously.
• SUBTOTAL. Summarizing data within filtered lists. Advantage: Provides dynamic summaries that adjust with filtering.
• DSUM. Conditional summation with complex criteria. Advantage: Reduces manual calculations and filters data simultaneously.
• COUNT/ COUNTA. Explores the difference between counting numbers and counting non-blank cells. Advantage: Useful for understanding data completeness.
• COUNTIF/ COUNTIFS. Counting cells based on specific criteria. Advantage: Quickly identifies data patterns and frequencies.
• COUNTBLANK/ COUNTNUM. Identifying empty or numerical cells. Advantage: Essential for data cleaning and quality checks.
• DCOUNT. Counting cells based on complex criteria. Advantage: Quickly identifies data patterns and frequencies.
Unit 2
Error Checking & Dates
• ISERROR/IFERROR. ISNA/IFERR. Handling errors gracefully and providing alternative values. Advantage: Prevents formula errors from disrupting analysis.
• ISEVEN/ ISODD. Determine if a value is even or odd. Advantage: Makes formulas more readable and easier to understand than using the MOD function.
• YEAR/MONTH/DAY. Extracting specific date components. Advantage: Enables detailed analysis of time-series data.
• TODAY/NOW. Obtaining current date and time. Advantage: Essential for time-sensitive data analysis.
• NETWORKDAYS, Determining date intervals. Advantage: Enables time duration calculations.
Unit 3
Text
• RIGHT/LEFT/MID. Extracting parts of text strings. Advantage: Useful for data cleaning and formatting.
• UPPER/LOWER/PROPER. Converting text case. Advantage: Standardizes text for consistent analysis.
• LEN/TRIM. Determining text length or eliminating leading/trailing spaces. Advantage: Enables consistent formulas for text manipulation which can standardize text for consistent analysis.
• CONCAT/TEXTJOIN. Combining text strings. Advantage: Creates customized labels and summaries.
Unit 4
Lookup
• XLOOKUP. The latest lookup function offering improvements over previous methods. Advantage: Simplifies complex lookups.
• INDEX/MATCH/XMATCH. A more powerful alternative to VLOOKUP and HLOOKUP. Advantage: Enhanced flexibility and error handling.
• INDIRECT. Returns a reference to a cell or a range of cells based on a string of text: Advantage: Often used to find a Table or Range by name instead of cell address.
• OFFSET. Returns a reference to a cell or a range of cells that is a specified number of rows and columns from a starting cell. Advantage: Often used to find a Table or Range by location in a set of data rather than a specific cell address or range/table name.