CME02 - Comprehensive Microsoft Excel Course
v2
Version:
12 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
1.      EXCEL BASICS
1.1.   Navigating Excel
1.2.   Commands and Formula
1.3.   Basic Functions
1.4.   Custom Formatting
1.5.   Filtering, Sorting and Find and Replace
2.      PAGE LAYOUT AND PRINTING
3.      TIME-SAVING TOOLS
3.1.   Flash Fill
3.2.   Selecting a group of cells based on contents
3.3.   Replicating and clearing formats
4.      TYPES OF VALUE AND REFERENCE
4.1.   Constant, relative, and absolute reference
4.2.   Fixing cell reference from row and/or columns
4.3.   Assigning a name to cell reference or ranges
5.      DATABASE FUNCTION
5.1.   Ranking dates and values
5.2.   Working with the SUBTOTAL function
6.      CONCATENATION
6.1.   Joining cell reference, number, string, and formula
7.      EXCEL TABLES
7.1.   Features of Excel Tables
7.2.   Working with Structured Reference
8.      SUM with Criteria
8.1.   Summation and count with multiple criteria or condition
9.      VLOOKUP
9.1.   VLOOKUP in exact match
9.2.   VLOOKUP in approximate match
10.  LOGICAL FUNCTIONS
10.1.  Logical expression to test if value returns to true or false
10.2.  IS function to try the data type of value
10.3.  IF statement with a single condition
10.4.  Nested IF when more than two possible conditions
10.5.  Multiple conditions in IF statement
11.  DATE FUNCTION
11.1.  Dynamic Date
11.2.  Calculating differences in date and time
12.  CHARTS AND GRAPH
12.1.  Methods of inserting data into a chart
12.2.  Changing elements, filters, and format
13.  PIVOT TABLES AND PIVOT CHART
13.1.  Inserting PivotTable
13.2.  Insert Row and Column
13.3.  Compound labels
13.4.  Dynamic source data for PivotTable
13.5.  Inserting PivotChart
13.6.  Inserting Slicers
14.  CONDITIONAL FORMATTING
14.1.  Format entire rows/columns based on cell value
14.2.  Setting conditions for formatting cells
15.  DYNAMIC DASHBOARD
16.  AUTOMATION IN EXCEL USING MACRO
16.1.  Understanding automation in Excel
16.2.  Enable Developer tab
16.3.  Recording Macro
16.4.  Absolute vs Relative
16.5.  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.