CME01 - Comprehensive Microsoft Excel Course
v1
Version:
24 hours
Duration:
Overview
Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android, and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro.
Course Outline
EXCEL ESSENTIALS
Navigating Excel
Commands and Formula
Basic Functions
Formatting
Filtering, Sorting and Find and Replace
Printing and Techniques in printing worksheets
TIME-SAVING TOOLS
Flash Fill
Selecting a group of cells based on contents
Replicating and clearing formats
TYPES OF VALUE AND REFERENCE
Constant, relative, and absolute reference
Fixing cell reference from row and/or columns
Assigning a name to cell reference or ranges
STATISTICAL / DATABASE FUNCTION
SUMPRODUCT, MAX, MIN, LARGE and SMALL
Working with the SUBTOTAL function
Working with AGGREGATE Function
Summation and count with criteria or condition
Summation and count with multiple criteria or condition
CONCATENATION
Joining cell reference, number, string, and formula
EXCEL TABLES
Features of Excel Tables
Working with Structured Reference
LOOKUPS
VLOOKUP in exact match
VLOOKUP in approximate match
HLOOKUP
INDEX-MATCH
Two-Way Lookup: VLOOKUP vs INDEX-MATCH
Introduction to XLOOKUP (Excel 2021/365)
LOGICAL FUNCTIONS
Logical expression to test if value returns to true or false
IS function to try the data type of value
IF statement with a single condition
Nested IF when more than two possible conditions
Multiple conditions in IF statement
TEXT FUNCTIONS
Format when combining date and numbers
Extract characters from the text
Get the number of characters from the text
Test the content of cell data
DATE FUNCTION
Extracting value from a date
Dynamic Date
A function that returns to the same date with a different month
A function that returns to the end of the month
REFERENCE FUNCTIONS
INDIRECT – converting text to a reference.
CONDITIONAL FORMATTING
Format based on scale, rank, and content
Format if duplicate or unique values
CHARTS AND GRAPH
Methods of inserting data into a chart
Changing elements, filters, and format
PIVOT TABLES AND PIVOT CHART
Inserting PivotTable
Insert Row and Column
Compound labels
Dynamic source data for PivotTable
Inserting PivotChart
Inserting Slicers
FORMULA-BASED DATA VALIDATION
Restrict data if number or text only
Restrict data if data from another cell is blank
FORMULA-BASED CONDITIONAL FORMATTING
Format entire rows/columns based on cell value
Format cell based on value from another cell
POWER QUERY
Import and consolidate external data source
Combining different tables
AUDITING FORMULA
Identify the dependent and precedent cells
Resolving circular reference
Evaluating formula to check errors
DYNAMIC DASHBOARD
AUTOMATION IN EXCEL USING MACRO
Understanding automation in Excel
Enable Developer tab
Recording Macro
Absolute vs Relative
Assigning a clickable button for automation
Â
Instructor
Jaicon Ruedas
Jaicon Ruedas is Microsoft Certified Excel Expert and Microsoft Certified Data Analyst. He taught thousands of professionals, executives and individuals.