EXCEL LEARNING MODULES
hemapriyavinayagam0210@[Link]
List of Modules
1. Excel Fundamentals
2. Cell Referencing
3. Data Analysis
4. Arithmetic Operators
5. logical Operators and Conditional Operators
6. Text Functions
7. Date and Time Functions
8. Counting, Conditional, and Error Handling Functions
9. Lookup Functions
10. Pivot Tables and Data Summarization
11. Data Visualization with Charts
12. Dashboard Creation
13. Power Query
14. Macros
15. VBA
16. Projects
Excel Fundamentals
1. What is Excel?
2. Excel Interface
3. Workbook vs Worksheet
4. Rows, Columns, Cells
5. Creating, Saving, Opening Files
6. Basic Data Entry
7. Practice Exercise
WHAT IS EXCEL ?
Microsoft Excel is a spreadsheet software used
to store, organize, calculate, analyse, and
visualize data.
Excel helps us store records digitally and
perform calculations automatically.
1,048,576 ROWS AND 16,384 COLUMNS
EXCEL INTERFACE ?
The Excel Interface is the excel screen , what we
visualize
Main Parts:
Title Bar ,Ribbon ,Formula Bar ,Worksheet Area
,Rows ,Columns ,Sheet Tabs
Workbook vs Worksheet ?
A Workbook is an Excel
file that contains one or
more worksheets.
This entire file is called a
Workbook.
A Worksheet is a single
sheet inside a workbook.
Each sheet is called a
Worksheet.
Rows, Columns, Cells ?
ROW : A ROW IS A HORIZONTAL LINE OF CELLS. ROWS ARE IDENTIFIED BY NUMBERS. (LEFT TO RIGHT)
COLUMN : A COLUMN IS A VERTICAL LINE OF CELLS. COLUMNS ARE IDENTIFIED BY LETTERS.
(TOP TO BOTTOM)
CELL : THE INTERSECTION OF A ROW AND COLUMN IS CALLED A CELL. A1 IS A CELL ADDRESS.
COLUMN A + ROW 1 = A1
CREATING, SAVING, OPENING FILES
BASIC DATA ENTRY
Column = Attribute / Field
DATA TYPES
Row = Record / Tuple
Practice Problem 1:
Mobile Store Sales Tracker
You have been hired to maintain sales records for a mobile phone shop.
Apply Excel Features Product ID Mobile Brand Price (₹) Quantity Sold Sale Date
1. Format Price as Currency (₹).
2. Format Sale Date as Date.
3. Make the headings bold.
4. Adjust column widths so all data is visible.
5. Add borders to the table.
Analyze the Data
Answer the following using Excel:
1. Which mobile generated the highest total sales?
2. What is the total quantity of phones sold?
3. What is the grand total revenue?
Practice Problem 2:
Supermarket Inventory & Sales Tracker
You have been hired as a data entry operator for a
supermarket. Maintain daily sales records and
analyze sales performance.
Tasks
[Link] Unit Price as Currency (₹).
[Link] Sale Date as Date.
[Link] a Total Sales column.
[Link] borders and bold headings.
Find:
[Link] with highest total sales.
[Link] quantity sold.
[Link] total revenue.
Cell Referencing & Data Analysis
2 . Cell Referencing
1. Relative
2. Absolute
3. Mixed
4. Practice Exercise
3 .Data Analysis
1. Sorting Data
2. Filtering Data
3. Conditional Formatting
4. Data Validation
CELL REFERENCING – RELATIVE , ABSOLUTE , MIXED ?
Cell Referencing means using a cell address in a formula instead of typing the actual value.
1. RELATIVE CELL REFERENCE
A relative reference changes automatically when a
formula is copied to another cell.
Example: Same formula iteratively
applies for all rows
Cell D6 = 10
Cell E6 = 20
In F6, enter:
= D6 +E6 = 30
In F7 ,
= D7 +E7 = F7 value
2. ABSOLUTE CELL REFERENCE
An absolute reference remains fixed when copied.
Use the $ symbol before the column and row.
Use case: Tax rates, commission
percentages, constants
$ Symbol used to lock value
3. MIXED CELL REFERENCE
Locks either the row or the column, but not both.
Row fixed, column
$A1 or A$1
changes.
Column fixed, row changes.
Practice Exercise
Student Marks Sheet Student Maths Science English
Create the following table:
Ravi 80 75 90
Tasks:
Save the file as Student_Marks.xlsx. Priya 85 88 92
Calculate Total Marks.
Add a bonus mark = 5 stored in one cell
Calculate Total Marks after adding bonus Kiran 70 78 85
points.
Analyze the student got lowest mark
Practice Exercise: Cafe Billing and Discount Calculator
You have been hired as an Excel Assistant for a cafe. The manager
wants you to calculate sales, discounts, and final amounts
Task
[Link] Total Amount
[Link] Final Bill Item ID Item Name Price (₹) Quantity Total Amount
Amount C101 Coffee 120 5
C102 Sandwich 150 3
Discount = 10%
C103 Burger 180 4
C104 Pizza 250 2
C105 Juice 90 6
Data Analysis in Excel ?
Data Analysis in Excel helps users organize, examine, and understand data to make better decisions.
Excel provides several tools to analyze large amounts of information quickly and accurately.
1. SORTING DATA
Sorting arranges data in a specific order, such as
ascending or descending.
Types of Sorting
Ascending Order: A → Z, Smallest → Largest
Descending Order: Z → A, Largest → Smallest
2. FILTERING DATA
Filtering displays only the data that meets specific criteria while hiding the rest.
Filtering does not delete data; it only hides non-matching records
Example
If a student table contains Classes 8A, 8B, and 9A,
a filter can show only students from Class 8A.
3. CONDITIONAL FORMATTING
Conditional Formatting automatically changes the appearance of cells based on specified conditions.
Example
Highlight marks greater than 80:
Marks > 80 → Green
Marks < 40 → Red
4. DATA VALIDATION
Data Validation restricts the type of data users can enter into a cell.
Example
Allow only numbers between 1 and 100 in a marks column.
Or
create a dropdown list:
Present
Absent
Leave
Feature Purpose
Sorting Arranges data in a specific order
Filtering Displays only required data
Conditional Formatting Highlights data based on conditions
Data Validation Controls what data can be entered