Course

Unit 1

Introduction to Spreadsheets
In this module, you will be introduced to the use of Excel spreadsheets and various basic data functions of Excel. Topics covered include the following:
Reading data into Excel using various formats – Basic functions in Excel, arithmetic as well as various logical functions – Formatting rows and columns – Using formulas in Excel and their copy and paste using absolute and relative referencing function on individual cells, functions on set of numbers – Functions on Ordered Pairs of Data.

Unit 2

Spreadsheet Functions to Organize Data
This module introduces various Excel functions to organize and query data. Learners are introduced to the IF, nested IF, VLOOKUP and the HLOOKUP functions of Excel. Topics covered include the following:
Sorting data in excel, IF and the nestedIF functions, VLOOKUP and HLOOKUP, The RANDBETWEEN, function

Unit 3

Introduction to Filtering, Pivot Tables, and Charts
This module introduces various data filtering capabilities of Excel. You’ll learn how to set filters in data to selectively access data. A very powerful data summarizing tool, the Pivot Table, is also explained and we begin to introduce the charting feature of Excel. Topics covered include the following:
VLOOKUP across worksheets, Data filtering in Excel, Use of Pivot tables with categorical as well as numerical data, Introduction to the charting capability of Excel, introduction to Binary Classification.

Unit 4

Advanced Graphing and Charting
This module explores various advanced graphing and charting techniques available in Excel. Starting with various line, bar and pie charts we introduce pivot charts, scatter plots and histograms. You will get to understand these various charts and get to build them on your own. Topics covered include the following:
Line, Bar and Pie charts, Pivot charts, Scatter plots, Histograms, Describing Histograms and Probability
Distributions Functions, Introduction to data analysis tool pack plug in in excel.

Unit 5

Advanced Filters and Sorting
This module explores various date / time functions, Text functions using, Validations, Advanced Filters, Advanced Sorting, Hyper / Data Linking, Summarizing Data. Topics covered include the following:
Using The Today, Now and Date Functions, Using The Dated if / Network days / Eomonth Functions, Using The Weeknum Functions, Using The Edate / Network days. Intl / Weekdays. Intl Functions, The Mid / Search / Left / Right Functions, Using The Trim / Clean / Upper/ Lower Functions, Using The Substitute / Text Functions, Using The Trim / Clean / Proper / Dollar Function, Input Messages / Error Alerts / Drop-Down Lists, Conditional Formatting, Extracting Records with Advanced Filter, Using Formulas in Criteria. Sorting by Top to Bottom / Left to Right, Creating / Deleting Custom List, Sort by using Custom List, Creating Subtotals / Nested Subtotals, SUBTOTALS Formula.

Course Objective:
The course takes you from basic operations such as reading data into excel using various data formats, organizing and manipulating data, to some of the more advanced functionality of Excel.

Course Outcomes:

Reference Books:

1. Ritu Arora – Mastering Advanced Excel – BPB Publications
2. Web Tech Sol. – Mastering Microsoft Excel Functions and Formulas – Khanna Book Publishing Co.
3. Alan Murray – Advanced Excel Success: A Practical Guide to Mastering Excel – Apress Publication
4. Naveen Mishra – Excel with Microsoft Excel: Comprehensive & Easy Guide to Learn Advanced MS Excel – Penman Books