SESSION 1

TIME-SAVING TOOLS

·         Useful Shortcuts

·         Flash Fill

·         Selecting a group of cells based on contents

·         Replicating and clearing formats

MANAGING WORKSHEET DATA 1

·         Custom number formatting

·         Advanced Filter

·         Changing positions of cell values

SESSION 2

MANAGING WORKSHEET DATA 2

·         Grouping of column or row data

·         Auto outline

·         Nested Subtotal

·         Techniques in printing worksheets

·         Set up restrictions in encoding data

·         Creating dropdown list

·         Protecting the workbook and worksheet

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

CONCATENATION

·         Joining cell reference, number, string, and formula

SESSION 3

STATISTICAL / DATABASE FUNCTION

·         AVERAGE, 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

EXCEL TABLES

·         Features of Excel Tables

·         Working with Structured Reference

·         Insert Slicers

SESSION 4

LOOKUPS

·         VLOOKUP and HLOOKUP

·         INDEX-MATCH as dynamic lookup

·         Two-way Lookup

·         Lookups with multiple criteria

·         XLOOKUP

SESSION 5

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

·         IFS Function

·         Multiple conditions in IF statement

SESSION 6

TEXT FUNCTIONS

·         Format when combining date and numbers

·         Extract characters from the text

·         Separate values into columns

·         Get the number of characters from the text

·         Test the content of cell data

DATE FUNCTION

·         Functions that return to the current date and time

·         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

SESSION 7

REFERENCE FUNCTIONS

·         CHOOSE – refer to values based on the number of choices

·         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 element, filter, and format

SESSION 8

PIVOT TABLES AND PIVOT CHART

·         Inserting PivotTable

·         Insert Row and Column

·         Compound labels

·         Dynamic source data for PivotTable

·         Inserting PivotChart

·         Inserting Slicers

SESSION 9

POWER QUERY

·         Import and consolidate external data source

POWER PIVOT

·         Combining different tables

AUDITING FORMULA

·         Identify the dependent and precedent cells

·         Resolving circular reference

·         Evaluating formula to check errors

SESSION 10

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

APPLICATION OF  EXCEL TOOLS

·         Applying INDIRECT in structured reference

·         Dynamic dropdown list

·         Assigning name for the formula

SESSION 11

·         Preparing raw data for Dashboard

·         Inserting charts and visuals

·         Creating navigation action buttons

·         Techniques in Dashboards

SESSION 12

MAIL MERGE - automatically produces multiple documents

BASIC MACRO

·         Understanding automation in Excel

·         Enable Developer tab

·         Recording Macro

·         Absolute vs Relative

·         Assigning Macro

·         Macro Security

·         Editing Macro