Comprehensive Microsoft Excel

Title: CME01 - Comprehensive Microsoft Excel Course

Number of sessions: 6

Duration per session: 4 hours

Course 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.

EXCEL BASICS

• 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