# Data analysis through Microsoft Excel

Teacher

Category

### Course Attendees

Still no participant

Still no reviews

# Code(Credit) : CUTM1185 (0-0-2)

## Course Objectives

• To build a strong understanding on the Basics of Microsoft Excel
• To understand data crunching and data presentation

## Learning Outcomes

• To create dynamic reports by mastering one of the most popular tools in excels - PivotTables, Pivot chart, If, Vlookup, Index, and Match etc.

## Course Syllabus

Module 1  - Introduction to Excel

Course objective, outcome, methodology and assessment, Excel Introduction, , Basic Navigation Tab, Concept of Cell and Cell address , row Column concept, Sum, SumIf, Sumifs, Count number, CountA (Text and Number), Count if, Countifs, Average, AverageA, Averageif, Averageifs

Module 2  - Understanding different Formula

Round, Roundup, Round Down, Concatenate, Min, Max, Trim, Lower,Upper, Proper,Left, Right, Mid,,Exact, Randbetwen, Rand, Len (Length of character) Paste special, SQRT, If function with Example of IF, More function like And, OR with their example, Conditional Formatting basic and advance level with OR, AND, Nested IF function, Index, Offset, Match

Module  3  - Data analysis and Data presentation

Vlookup, Hlookup, Trace Precedents, Trace Dependents, show formula, Text to Column, Data Validation, Filter,Duplicate Removal, Sort, Filter, Wrap Test, What if Analysis, with detail example Merge Cell, Shapes, Median, Mode, Slandered Deviation (S), Correlation, Large, Small, Pivot Table, Pivot Charts, Slicing, Sparkling

Practice -

Practical session - Students demonstration

Project -

Preparation of automated attendance sheet

Preparation of Dashboard

Case study  -  GTIDS csp payout

## Session 1

Course objective, outcome, methodology and assessment, Excel Introduction, , Basic Navigation Tab, Concept of Cell and Cell address , row Column concept

## Session 2

Sum, SumIf, Sumifs, Count number, CountA (Text and Number), Count if, Countifs, Average, AverageA, Averageif, Averageifs

## Session 3 (Practice)

Practice -

Students Demonstration

## Session 4

Round, Roundup, Round Down, Concatenate, Min, Max, Trim, Lower,Upper, Proper,Left, Right, Mid,,Exact, Randbetween, Rand, Len (Length of character) Paste special, SQRT, If function with Example of IF, More function like And, OR with their example

## Session 5 (Practice)

Practice -

Students Demonstration

## Session 6

Internal Test - 1st

## Session 7

Conditional Formatting basic and advance level with OR, AND, Nested IF function, Index, Offset, Match

## Session 8 (Practice)

Practice -

Students demonstration

## Session 9

Case Study Discussion

Case study details -

Case Study Gtids CSP payout

## Session 10

Vlookup, Hlookup, Trace Precedents, Trace Dependents, show formula

## Session 11

Text to Column, Data Validation, Filter,Duplicate Removal, Sort, Filter, Wrap Test, Merge Cell, Shapes,

## Session 12 (Practice)

Practice -

Students Demonstration

## Session  13

Project -

Preparation of automated attendance sheet (To be submitted in a week Time)

## Session 14

What if Analysis, with detail example

## Session 15

Median, Mode, Slandered Deviation (S), Correlation, Large, Small,

(Brief discussion on Dashboard presentation, and group allocation.)

## Session 16 (Practice)

Practice -

Students demonstration

## Session 17

Pivot Table, Pivot Charts, Slicing, Sparkling

Video for Pivot Table

Video for Pivot Chart

## Session 18

Excel Quiz-

Class will be divided into 3-4 broad groups and quiz will be conducted.

(Each group has to prepare 10 short practical question before the quiz day to play)

## Session 19

Internal Test - 2nd

## Session 20 - 25

Project-

Dashboard Presentation