SL No

Lead

Components

1

Manage Workbook and Worksheet

a. Manage Workbook: Save a Workbook as Template, Office Theme, Customize Ribbon, Quick Access Toolbar

b. Manage Worksheet: Create a New Sheet, Format Sheet, Move or Copy Sheet, Delete Sheet

c. Navigation: Cell Movement

2

Commands of Main Tabs

a. Home Tab: Paste, Paste Special, Format Painter, Pattern Fill

Orientation, New Line, Merge Across, Data Formatting, Format

Clear, Fill, Find and Select

b. Insert Tab: Hyperlink

c. Page Layout Tab: Page Set up and Print, Theme

d. Formula Tab: Name Manager, Formula Auditing, Watch Window

e. Data Tab: Filter, Text to Columns, Remove Duplicates, Data Validation, Goal Seek Group by Subtotal

f. View Tab: Navigation, New Window & Arrange All, Freez Panes, Hide and Unhide, Switch Windows

3

Tips and Notes on Formula, Cell Reference, and Formula Copy

a. Some Important Tips on Excel Formula

b. Some Important Tips on Excel Cell Reference

c. Other Important Notes

4

Basic Mathematical Calculation

a. Summation, Subtraction, Multiplication, Division, Percentage, Average

b. Simplification Rule

c. Round- Roundup and Rounddown

5

Let’s Start with Basic Formulas

a. Find Max and Min Value

b. Rank Value in a Range

c. Find Top Largest and Smallest Value

d. Difference between Count and Counta

e. Make SL/Index number using Row and Column Formula

6

Manipulating Text

a. Setting text to sentence case

b. Flash fill

c. Join text string

d. Removing space from a text string

e. Counting characters in a cell

f. Extracting parts of a text string

g. Finding a particular character

h. Substituting text string

i. Finding similar multiple characters

j. Make Extracting parts of a text string automatic

7

Conditional Analysis

a. Simple and multiple conditions

b. Logical condition with AND & OR

c. Summing and Counting that meet conditions

8

Matching and Lookups

a. Looking up on a left lookup column

b. Looking up horizontally

c. Hiding errors returned by lookup functions

d. Looking up on a two-way matrix

e. Looking up by changing the referencing column

f. Looking up from multiple tables

g. Looking up by returning a reference to a range

9

Array Formulas

a. Finding Large and Small value

b. Transposing in an array

c. Summing in a range

d. Summing Largest and Smallest value in a range

e. Summing in a range that meet conditions

f. Finding Max and Min in an array

10

Conditional Formatting

a. Highlight cells that meet criteria

b.  Manage rules

11

Dates and Times

a. Current date and time

b. Extracting parts of a date

c. Text Function

d. Finding Next month and End of Month

e. Make a date

f. Length of Service

g. Find deadline and Working Days

12

Sorting

a. Data Sorting

d. Row and column sorting

13

Data Validation

a. Different validation criteria

b. Input message and Alert criteria

c. Checking duplicate value

14

Protect Work

a.  Unlock cells

b. Sheet protection options

15

Making Formulas Error-Free

a. Finding and Correcting Formula Error

b. Formulas returning an error

16

VBA Macro

a. How to record Macro

b. How to run Macro

c. How to assign Macro

d. Dynamic Advance Filtering

17

VBE

a. Call Macro

b. Protect Coding

c. Unhide multiple sheets

18

Introducing Pivot Table

a. Creating a Pivot Table

b. Analysing Data with Pivot Table

19

Slicer

a.  Creating a Slicer

b. Report Connections

20

Creating Chart

a. Creating Chart

b. Modifying and designing chart using format task

21

Dashboard Project

Making a beautiful dashboard