Data analysis through Microsoft Excel

Teacher

Amit Kumar

Category

Core Courses

Course Attendees

Still no participant

Course Reviews

Still no reviews

Course Name : Data analysis through Microsoft Excel

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

Online Reference links - 

 

https://www.youtube.com/watch?v=L3-uVawPLKc

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

https://www.youtube.com/watch?v=rBuiBNZWjE4&t=1511s

Session Plan

Session 1

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

https://www.youtube.com/watch?v=L3-uVawPLKc

Session 2

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

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

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

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

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

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

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

 

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

Session 11

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

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

Session 12 (Practice)

Practice -

Students Demonstration

Session  13

Project -

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

 

https://www.youtube.com/watch?v=D4oqM2tOvTI

Session 14

What if Analysis, with detail example

 

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

Session 15

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

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

https://www.youtube.com/watch?v=RdTozKPY_OQ&t=192s

Session 16 (Practice)

Practice -

Students demonstration

Session 17

Pivot Table, Pivot Charts, Slicing, Sparkling

Video for Pivot Table

https://www.youtube.com/watch?v=4PWVFBiFVVU

Video for Pivot Chart

https://www.youtube.com/watch?v=mc7xO8F8Pj8

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

 

https://www.youtube.com/watch?v=rBuiBNZWjE4&t=1511s

Our Main Teachers