Program: B. Sc.
- Computer Science Semester: III
Course: Advanced Excel Course Code:
Teaching Scheme Evaluation Scheme
Lecture Continuous Assessment Term End Examinations
Credit
(Hours per week) and Evaluation (CAE) (TEE)
02 2 40% 60%
Learning Objectives: The objective of this course is to
• Analyze and understand the environment of MS Excel and also apply various
calculations techniques to the managed data.
• Explore the various ways of working with multiple worksheets in MS Excel and
implement the formulas and functions across the worksheets.
• Create and design various charts and graphs using the existing data and apply in-built
rules available in MS Excel
Course Outcomes:
After completion of the course, learners would be able to:
CO1: Analyze and understand the environment of MS Excel and implement various
calculations techniques to the managed data.
CO2: Apply and evaluate various tools and techniques to work with multiple worksheets,
Ranges and tables.
CO3: Apply, compare and evaluate various AI feature available in Excel and for Excel
CO4: Apply and Analyze, the data using the in-built formulas and functions to generate and
display summary of data
CO5: Compare, Classify and understand the basics of data analysis and use pivot tools and
techniques to summarize and present the data.
CO6: Display relationship between the data and solve the case studies based on large amount
of data.
Outline of Syllabus: (per session plan)
Module Description No of hours
1 Basic Calculation, Multiple Spreadsheets and ranges & Tables 10
2 Use of AI tools, Working with Multiple spreadsheets, Ranges and 10
Tables, Formulas and Functions, Basics of Data Analysis
3 PivotTables & PivotCharts, Working with Large Data, Case 10
Studies based on Data Modeling
Total 30
No. of
Module Advanced Excel Hours/Credits
30/2
1 Basic Calculation, Multiple Spreadsheets and ranges & 10
Tables
Basic Calculations: Simple arithmetic (+, -, *, /), Introduction 4
to formulas (structure and syntax), Basic functions: SUM,
AVERAGE, COUNT, MIN, MAX, Cell Formatting, Number
formats (currency, percentage, date), Fonts, text size, colors,
Alignment, cell borders, Printing Essentials, Print settings (page
orientation, margins), Print preview
Working with Multiple spreadsheet: Create and using
template, Creating and linking Multiple spreadsheet, Adding, 4
removing, hiding and renaming worksheet.
Working with Ranges and Tables: Cell references (relative vs.
absolute), Create named ranges, Convert data to Excel Tables, 2
Creating and using named ranges.
2 Use of AI tools, Working with Multiple spreadsheets, 10
Ranges and Tables, Formulas and Functions, Basics of Data
Analysis
Use of Analyze Data in built function to create analysis 3
report, use of AI tools using Add-ins in Excel
Formulas & Functions: Creating formula, inserting function,
cell reference, absolutes, relative (within a worksheet, other
worksheets and other workbooks), logical operator, Use of Paste 3
Special, More complex formulas, Popular functions: IF,
VLOOKUP, COUNTIF, SUMIF, XLOOKUP, INDEX,
MATCH, Nested functions
Basics of Data Analysis: What-if analysis (Goal Seek, Scenario
Manager), Removing Duplicates, Converting text to columns, 4
Grouping and subtotal, Advanced Functions, Logical functions
(AND, OR), Text functions (LEFT, RIGHT, MID,
CONCATENATE), Date functions (TODAY, WEEKDAY,
NETWORKDAYS)
3 PivotTables & PivotCharts, Working with Large Data, 10
Case Studies based on Data Modeling
PivotTables & PivotCharts: Create and understand 3
PivotTables, summarize data in different ways, Use slicers and
timelines to filter PivotTables, Create PivotCharts
Working with Large Data: Create data entry rules to ensure
data accuracy, Importing Data from other sources, Working with 3
comma delimited (csv) files, Creating in-cell drop down boxes
based on list and rules, Power Query and Power Pivot
Case Studies based on Data Modeling: Creating and
Generating 600 worksheets which are based on Customer Data 4
from 3 to 5 years, Working with RBI Data, applying data
validation and Generating Charts, Working on Online System
data (5 Lakhs Rows of Data) and applying sumif, sumifs, countif,
and countifs.
ESSENTIAL READING
1. Jain, R. (n.d.). A To Z of MS EXCEL : A book for learners and trainers. Kindle
Edition.
2. Lalwani, L. (2019). Excel 2019 All-in-one : Master the new features of Excel
2019/Office 365 paperback. BPB Publication.
3. Steph, L. (June 2022). EXCEL 2022: Become an Expert Quickly and Master
Microsoft Excel Formulas and Functions from Basic to Advanced, with the Latest
Tips and Tricks.
SUPLEMENTARY READING
1. Tillery, N. (June 2023). Excel Essentials: A Step-by-Step Guide with Pictures for
Absolute Beginners to Master the Basics and Start Using Excel with Confidence.
2. Wayne, L. (2019). MICROSOFT EXCEL 2019: DATA ANALYSIS &
BUSINESS MODEL. PHI Learning Pvt. Ltd.