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
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 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 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
https://www.youtube.com/watch?v=4PWVFBiFVVU
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
Our Main Teachers