top of page

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

 

Color Swatches

Instructor

Jaicon Ruedas

Jaicon Ruedas

Jaicon Ruedas is Microsoft Certified Excel Expert and Microsoft Certified Data Analyst. He taught thousands of professionals, executives and individuals.

LEARN MORE
bottom of page