# Data Analysis using Excel and Python

Teacher

Category

### Course Attendees

Still no participant

Still no reviews

# Code(Credit) : CUTM1241  (0+2+2)

## Course Objectives

• To build a strong understanding on the Basics of Excel and Python
• 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.
• To do different operation in python with set, dict. tuples, list 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

Module 4  - Python Basics

Data Types, Loops, Inbuilt functions,List, Tuples, Dictionary,IF, Else, Numpy one Dimensional, Numpy two Dimensional. Loading and working on data with Python

Project -

Preparation of automated attendance sheet

Preparation of Dashboard

Numpy One Dimensional and two Dimensional Array

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 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 -

Student’s demonstration

## Session 9

Case Study Discussion

GTIDS CSP Payout

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

Internal Test - 2nd

## Session 13

Excel Quiz-

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

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

## Session 14 (Practice)

Practice -

Student’s demonstration

## Session 15

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 16 (Practice)

Practice -

Student’s demonstration

## Session 17

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 18

What if Analysis, with detail example

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

## Session 19

Project -

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

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

## Session 20 (Practice)

Practice -

Student’s demonstration

## Session 21 - 25

Project-

Dashboard Presentation

## Session 26

Python

Environmental setup - Anaconad and Jupyter

Python Installation Guide

## Session 27

Data Types of Python - Int, String, Float, None, Boolean

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

## Session 30 (Practice)

Practice project-

List and Tuple

1559282529_lists

1559282514_tuples

## Session 32 (Practice)

Practice project-

Dict and Set

1559282567_sets

1559282593_dictionaries

## Session 34 (Practice)

Practice project-

Condition and Branching

1559282660_conditions

## Session 36 (Practice)

Practice project

Loops

1559282692_loops

## Session 38 (Practice)

Practice project-

Functions

1559282723_functions

## Session 40 (Practice)

Practice project-

Object and Class

1559282753_classes

## Session 41

Reading and Writing files

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

## Session 42 (Practice)

Practice project-

Reading and writing files

1559282799_readfile

1559282846_writefile

## Session 43

Loading and working on Data with Panda

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

## Session 44 (Practice)

Practice project-

Working on Data with Panda

1559282875_loaddata

## Session 45

Numpy One Dimensional Arrays

https://www.youtube.com/watch?v=119bNu4E8R4

## Session 46

Practice project-

Numpy One Dimensional Arrays

1559282909_numpy1d

## Session 47

Numpy Two Dimensional Arrays

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

## Session 48 (Practice)

Practice project-

Numpy Two Dimensional Arrays

1559282949_numpy2d

## Session 49 and 50

Project Presentation

## Case Studies

GTIDS CSP Pay

Case Study Gtids CSP payout

VIEW PROFILE