Vishal Computer Classes - Data Analysis Techniques using Microsoft Excel

Data Analysis Techniques using Microsoft Excel

Master Excel tools for organizing, analyzing, and interpreting data. Focus on practical applications like sorting, filtering, and charting.

Course Features:

Language: English
Track: Back Office
Duration: 30 hours
Level: Beginner
Learning Mode: Learn at ALC or Learn at Home
Jurisdiction: Maharashtra
Certificate of Completion
Fees - 2500/-

Eligibility

Learner should preferably a std. 10th Pass student (Not Compulsory)
It is desirable that Learner should have done MS-CIT Course (Not Compulsory)

Introduction

Understanding the fundamentals of Excel functions and formulas.
Applying various functions in formulas for data manipulation and analysis.
Utilizing mathematical functions for statistical analysis and calculations.
Implementing financial functions for financial analysis and calculations.
Leveraging data functions for data manipulation and extraction.
Utilizing useful functions for error checking and dynamic formula creation.
Performing lookup operations using different lookup functions.
Handling date and time data effectively using advanced date and time functions.
Manipulating text data using text functions for extraction, manipulation, and formatting.
Understanding and utilizing arrays for complex data analysis.
Using what-if analysis tools to analyze different scenarios and make informed decisions.
Creating and merging scenarios to evaluate various possibilities and outcomes.
Generating scenario summaries and pivot table reports for comprehensive analysis and presentation.

What you'll learn ?

Apply advanced Excel functions and formulas to manipulate and analyze data effectively.
Utilize mathematical functions for statistical analysis and calculations.
Perform financial analysis and calculations using financial functions.
Manipulate and extract data efficiently using data functions.
Implement error checking and dynamic formula creation using useful functions.
Conduct lookup operations to retrieve data from different sources.
Handle date and time data effectively using advanced date and time functions.
Manipulate text data for extraction, manipulation, and formatting using text functions.
Analyze scenarios and make informed decisions using what-if analysis tools.
Create and merge scenarios to evaluate various possibilities and outcomes.
Generate scenario summaries and pivot table reports for comprehensive analysis and presentation of results

Certification

KLiC courses are recognised by Yashwantrao Chavan Maharashtra Open University (YCMOU).
MKCL provides certificate to the KLiC learner after his/her successful course completion.
Yashwantrao Chavan Maharashtra Open University (YCMOU) provides mark sheet to successfully passed KLiC learners (Jurisdiction: Maharashtra).

Academic Approach

The academic approach of the courses focuses on the “work-centric” education i.e. begin with work (and not from a book!), derive knowledge from work and apply that knowledge to make the work more wholesome, useful and delightful. The ultimate objective is to empower the Learner to engage in socially useful and productive work. It aims at leading the learner to his/her rewarding career as an employee or entrepreneur as well as development of the community to which s/he belongs. Learning methodology:

Step -1: Learners are given an overview of the course and its connection to life and work.
Step -2: Learners are exposed to the specific tool(s) used in the course through the various real-life applications of the tool(s).
Step -3: Learners are acquainted with the careers and the hierarchy of roles they can perform at workplaces after attaining increasing levels of mastery over the tool(s).
Step -4: Learners are acquainted with the architecture of the tool or tool map so as to appreciate various parts of the tool, their functions, utility and inter-relations.
Step -5: Learners are exposed to simple application development methodology by using the tool at the beginner’s level.
Step -6: Learners perform the differential skills related to the use of the tool to improve the given ready-made industry-standard outputs.
Step -7: Learners are engaged in appreciation of real-life case studies developed by the experts.
Step -8: Learners are encouraged to proceed from appreciation to imitation of the experts.
Step -9: After the imitation experience, they are required to improve the expert’s outputs so that they proceed from mere imitation to emulation.
Step-10: Emulation is taken a level further from working with differential skills towards the visualization and creation of a complete output according to the requirements provided. (Long Assignments)
Step-11: Understanding the requirements, communicating one’s own thoughts and presenting are important skills required in facing an interview for securing a work order/job. For instilling these skills, learners are presented with various subject-specific technical as well as HR-oriented questions and encouraged to answer them.
Step-12: Finally, they develop the integral skills involving optimal methods and best practices to produce useful outputs right from scratch, publish them in their ePortfolio and thereby proceed from emulation to self-expression, from self-expression to self-confidence and from self-confidence to self-reliance and self-esteem!

Syllabus

Introduction to Functions, Common Types of Arguments, Parts of Functions
Advanced Aspects of Functions, Searching for a Function

Using the Functions
Using the SUM and AVERAGE Functions
Using the IF Function
Analysing the IF Function
Using the OR Function
Using the AND Function
Using the OR Operator Function
Nesting the IF Statement
Analysing the Nested IF Function
Using the NOT Function
Using the SUMIF Function
Using the SUMIFS Function

Using the AVERAGE and MEAN Functions
Using the MODE Function
Using the MEDIAN Function
Area and Volume Calculations
Rounding Decimal Places
MOD and INT Functions and Uses
Using the Random Function
Using the RANDBETWEEN Function

EMI Calculations
Calculating Monthly Instalment and Interest
Calculating Principal Amount
Calculating Balance Amount
Using PV Function
Using FV Function

The CHOOSE Function
Using the MATCH Function
Using the INDEX Function
Using the MATCH and INDEX Functions Working Together

The CELL Function and Determining File or Sheet Names
Using the ISBLANK Function
Error Checking Using the ISERROR Function
Error Checking Using the IFERROR Function
OFFSET Function: Display the Value in Cell
OFFSET Function: Calculating a SUM
OFFSET Function: Creating a Dynamic Named Range
Using the INDIRECT Function
INDIRECT Function to Build Dynamic Formulas

Using the LOOKUP Function
Using the VLOOKUP Function
Using the HLOOKUP Function
VLOOKUP in Live Action
Lookups Nested Within Lookups

Working with Date and Time in Excel
Using the YEAR, MONTH, DAY, HOUR, MINUTE AND SECOND Functions
Calculation Between Two Dates
Calculating Person’s Age Using Excel
Using Functions to Serialize Date
Using the TIMEVALUE Function

LEFT: Text Manipulation
RIGHT: Text Manipulation
LEN: String Extractions
TRIM: String Extractions
Changing Case Function
CONCATENATE: Building Strings From Multiple Cells
REPLACE and SUBSTITUTE: Two More String Manipulation Function
Using the FIND and MID Text Functions
FIND and MID: Text Functions Working Together
Formatting Numeric and Date Values Using TEXT

IF ARRAY Formulas
Transpose-Shift

What-If Analysis Tools
Using Goal Seek

What are Scenarios?
Possible Scenarios
Naming Cells
Creating a Scenario
Second Scenario
Third Scenario
Viewing a Scenario
Quick Access to Scenarios

Merging Scenarios
Other Scenarios
Merging Process
The Merge Scenarios Window

Types of Scenario Summaries
Creating Scenario Summary
Understanding Scenario Summary
Creating Scenario PivotTable Report
Modifying Scenario PivotTable Reportc