Contents

File Management

Microsoft Word

Microsoft Excel

Microsoft PowerPoint

Worksheet & Functions

Exercise 1: Simple Mathematical Calculations

Exercise 2: Marksheet Version 1

Exercise 3: Marksheet Version 2

Exercise 4: Stock Register Version 1

Exercise 5: Stock register version 2

Exercise 6: Changing fonts

Exercise 7: Mathematical and Statistical functions

SUM Function

PRODUCT Function

AVERAGE Function

MAX Function

MIN Function

COUNT Function

Exercise 8: Effects of blank cells on AVERAGE function

Exercise 9: Mathematical functions (ROUND, ABS, INT & MOD)

ROUND Function

ABS Function

INT Function

MOD Function

Exercise 10: Stock register for items with 2 units

Exercise 11: Formatting numbers

Exercise 12: Bank OD Account passbook

Exercise 13: Date functions

TODAY Function

NOW Function

DATE Function

YEAR Function

MONTH Function

DAY Function

EOMONTH Function

NETWORKDAYS Function

NETWORKDAYS

Exercise 14: String functions

UPPER Function

LOWER Function

PROPER Function

REPLACE Function

CONCATENATE Function

LEFT Function

RIGHT Function

MID Function

LEN Function

FIND Function

Exercise 15: IF function

Exercise 16: Nested If Function

Exercise 17: Marksheet Version 4

Exercise 18: Salary Sheet

Exercise 19: Aging Analysis Sheet

Exercise 20: Sales register with GST calculations

Exercise 21: Data validation

Exercise 22: Logical functions (OR & AND)

Exercise 23: Name Manager

Exercise 24: Conditional formatting

Exercise 25: SUMIF & SUMIFS functions

SUMIF Function

SUMIFS Function

Exercise 26: COUNT(IF/IFS), AVERAGE(IF/IFS) & COUNTBLANK

COUNIF Function

COUNTIFS Function

AVERAGEIF Function

AVERAGEIFS Function

Exercise 27: Daily attendance sheet

Exercise 28: Daily wages sheet

Exercise 29: VLOOKUP function

Exercise 30: VLOOKUP with Approx

Exercise 31: MATCH and INDEX function

Exercise 32: Financial functions to calculate depreciation

SLN Function

DB Function

Exercise 33: Financial function to calculate Future value (FV)

Exercise 34: FV on interest compounded monthly on lumpsum

Exercise 35: FV on investment done monthly

Exercise 36: FV on monthly investment compounded quarterly

EFFECT Function

NOMINAL Function

Exercise 37: Financial functions (RATE, NPER, PV)

RATE Function

NPER Function

PV Function

Exercise 38: Financial function to calculate EMI (PMT, IPMT & PPMT)

PMT Function

IPMT Function

PPMT Function

Data Analysis

Exercise 39: Sorting data

Exercise 40: Filtering data

Exercise 41: Using Advanced filter

Exercise 42: Removing duplicates from records in a data

Exercise 43: Paste Special

Pasting Formulas

Pasting Formulas and Number Formats

Pasting Values

Pasting Values and Number Formats

Pasting Formats

Pasting Data Validation

Pasting all Without Borders

Pasting Column Widths

Exercise 44: Transpose and Paste Math Operations

Exercise 45: Find and Replace

Exercise 46: Converting text to column (Fixed width)

Exercise 47: Converting text to column (Coma delimited)

Exercise 48: Adding subtotals to data

Exercise 49: What if analysis - Goal seek

Exercise 50: What if analysis -Scenario manager

Exercise 51: What if analysis - Data table

Exercise 52: Protecting and Unprotecting Sheet

Protecting Sheet

Unprotecting Sheet

Unlock Cells

Exercise 53: Pivot Table

Exercise 54: Pivot Chart

Exercise 55: Insert Chart

Column chart

Bar Chart

Stacked Column Chart

Pie Chart

Line Chart

Exercise 56: Macros

Viewing & Printing

Exercise 57: Freeze panes

Freeze Rows

Unfreeze panes

Freeze Columns

Unfreeze panes

Exercise 58: Split Windows

Split Horizontally

Remove Split

Split Vertically

Remove Split

Split both Vertically and Horizontally

Exercise 59: Page Setup & Printing

Changing Margins

Changing Orientation

Set Print Area

Remove Print Area

Repeating Top Rows on Every Page

Inserting Page Break

Removing Page Break

Repeat column at left

Change the page order of printing

Page Break Preview

Normal View

Page Layout View

Inserting Header

Inserting Footer

Adjusting the Page to Fit

Center on Page

Contents

File Management

Microsoft Word

Microsoft Excel

Microsoft PowerPoint

Worksheet & Functions

Exercise 1: Simple Mathematical Calculations

Exercise 2: Marksheet Version 1

Exercise 3: Marksheet Version 2

Exercise 4: Stock Register Version 1

Exercise 5: Stock register version 2

Exercise 6: Changing fonts

Exercise 7: Mathematical and Statistical functions

SUM Function

PRODUCT Function

AVERAGE Function

MAX Function

MIN Function

COUNT Function

Exercise 8: Effects of blank cells on AVERAGE function

Exercise 9: Mathematical functions (ROUND, ABS, INT & MOD)

ROUND Function

ABS Function

INT Function

MOD Function

Exercise 10: Stock register for items with 2 units

Exercise 11: Formatting numbers

Exercise 12: Bank OD Account passbook

Exercise 13: Date functions

TODAY Function

NOW Function

DATE Function

YEAR Function

MONTH Function

DAY Function

EOMONTH Function

NETWORKDAYS Function

NETWORKDAYS

Exercise 14: String functions

UPPER Function

LOWER Function

PROPER Function

REPLACE Function

CONCATENATE Function

LEFT Function

RIGHT Function

MID Function

LEN Function

FIND Function

Exercise 15: IF function

Exercise 16: Nested If Function

Exercise 17: Marksheet Version 4

Exercise 18: Salary Sheet

Exercise 19: Aging Analysis Sheet

Exercise 20: Sales register with GST calculations

Exercise 21: Data validation

Exercise 22: Logical functions (OR & AND)

Exercise 23: Name Manager

Exercise 24: Conditional formatting

Exercise 25: SUMIF & SUMIFS functions

SUMIF Function

SUMIFS Function

Exercise 26: COUNT(IF/IFS), AVERAGE(IF/IFS) & COUNTBLANK

COUNIF Function

COUNTIFS Function

AVERAGEIF Function

AVERAGEIFS Function

Exercise 27: Daily attendance sheet

Exercise 28: Daily wages sheet

Exercise 29: VLOOKUP function

Exercise 30: VLOOKUP with Approx

Exercise 31: MATCH and INDEX function

Exercise 32: Financial functions to calculate depreciation

SLN Function

DB Function

Exercise 33: Financial function to calculate Future value (FV)

Exercise 34: FV on interest compounded monthly on lumpsum

Exercise 35: FV on investment done monthly

Exercise 36: FV on monthly investment compounded quarterly

EFFECT Function

NOMINAL Function

Exercise 37: Financial functions (RATE, NPER, PV)

RATE Function

NPER Function

PV Function

Exercise 38: Financial function to calculate EMI (PMT, IPMT & PPMT)

PMT Function

IPMT Function

PPMT Function

Data Analysis

Exercise 39: Sorting data

Exercise 40: Filtering data

Exercise 41: Using Advanced filter

Exercise 42: Removing duplicates from records in a data

Exercise 43: Paste Special

Pasting Formulas

Pasting Formulas and Number Formats

Pasting Values

Pasting Values and Number Formats

Pasting Formats

Pasting Data Validation

Pasting all Without Borders

Pasting Column Widths

Exercise 44: Transpose and Paste Math Operations

Exercise 45: Find and Replace

Exercise 46: Converting text to column (Fixed width)

Exercise 47: Converting text to column (Coma delimited)

Exercise 48: Adding subtotals to data

Exercise 49: What if analysis - Goal seek

Exercise 50: What if analysis -Scenario manager

Exercise 51: What if analysis - Data table

Exercise 52: Protecting and Unprotecting Sheet

Protecting Sheet

Unprotecting Sheet

Unlock Cells

Exercise 53: Pivot Table

Exercise 54: Pivot Chart

Exercise 55: Insert Chart

Column chart

Bar Chart

Stacked Column Chart

Pie Chart

Line Chart

Exercise 56: Macros

Viewing & Printing

Exercise 57: Freeze panes

Freeze Rows

Unfreeze panes

Freeze Columns

Unfreeze panes

Exercise 58: Split Windows

Split Horizontally

Remove Split

Split Vertically

Remove Split

Split both Vertically and Horizontally

Exercise 59: Page Setup & Printing

Changing Margins

Changing Orientation

Set Print Area

Remove Print Area

Repeating Top Rows on Every Page

Inserting Page Break

Removing Page Break

Repeat column at left

Change the page order of printing

Page Break Preview

Normal View

Page Layout View

Inserting Header

Inserting Footer

Adjusting the Page to Fit

Center on Page