Index for Practical File

Sr. Experiment Faculty Signature

1 Simple arithmetic

2 Extending an operation by clicking and

dragging
3 Absolute and Relative referencing

4 Simple statistical functions

5 Logical operations

6 Decision making using IF, SUMIF,

COUNTIF, etc.
7 Text functions

8 Lookup functions

9 Visualization using charts

10 Page referencing
 Q1. Simple arithmetic

Theory
In Excel, arithmetic operations are performed using basic mathematical operators.
Excel automatically evaluates formulas that begin with an equal sign (=). These
operations help in data cleaning, financial calculations, data preprocessing, and any
numeric analysis.
Basic Arithmetic Operators
Operation Symbol Example
Addition + =A1 + B1
Subtraction - =A1 - B1
Multiplication * =A1 * B1
Division / =A1 / B1
Exponent ^ =A1 ^ 2

Example Table
Suppose we have the following data:
A (Value 1) B (Value 2)
10 5
20 4
15 3

Excel Operations
1. Addition
Formula:
=A2 + B2
Result:
10 + 5 = 15
2. Subtraction
Formula:
=A3 - B3
Result:
20 - 4 = 16
3. Multiplication
Formula:
=A4 * B4
Result:
15 * 3 = 45
4. Division
Formula:
=A2 / B2
Result:
10 / 5 = 2
5. Exponentiation
Formula:
=A3 ^ 2
Result:
20² = 400

Final Output Table

Operation Excel Formula Output
Addition =A2 + B2 15
Subtraction =A3 - B3 16
Multiplication =A4 * B4 45
Division =A2 / B2 2
Exponent =A3 ^ 2 400
 Q2. Extending an operation by clicking and dragging

Theory
Excel provides a very powerful feature called AutoFill, which allows you to extend
formulas, patterns, or series by simply clicking and dragging the fill handle (a small
square at the bottom-right corner of a selected cell).
AutoFill helps in:
 Repeating formulas
 Continuing number sequences (1, 2, 3…)
 Extending date ranges
 Copying text patterns
 Performing bulk calculations without typing formula again

How AutoFill Works

When a cell containing a number, text, or formula is selected, Excel recognizes the
pattern and extends it automatically when you drag the fill handle.

Example 1: Extending a Formula (Addition)

Step 1: Input Data
A B
10 5
12 8
15 10
Step 2: Enter Formula in C2
Write:
=A2 + B2
Step 3: Drag the Fill Handle
Select cell C2 → move cursor to bottom-right corner → drag down to C4.
Excel Automatically Fills:
A B C (AutoFill Result)
10 5 =A2+B2 → 15
12 8 =A3+B3 → 20
15 10 =A4+B4 → 25

Example 2: Extending Number Series

If you enter:
A1 = 1
A2 = 2
Select both cells → drag the fill handle down.
Excel auto-detects the pattern and generates:
1, 2, 3, 4, 5, …

Example 3: Extending Days or Dates

If you type:
A1 = Monday
Drag the fill handle downward.
Excel automatically continues:
Tuesday, Wednesday, Thursday…
If you type a date like:
A1 = 01/01/2025
Dragging extends it as:
02/01/2025, 03/01/2025, 04/01/2025…

Example 4: Extending Multiplication Formula

Table:
AB
5 3
7 2
4 6
Enter in C2:
=A2 * B2
Drag down → Excel fills:
A B C (Result)
5 3 15
7 2 14
4 6 24

Final Summary
Feature Use
Fill Handle Drag down/right to extend pattern
AutoFill Automatically continues series & formulas
Feature Use
Pattern Detection Numbers, dates, formulas, weekdays
 Q3. Absolute and Relative referencing

Excel uses cell references inside formulas. When formulas are copied or dragged,
these references may change—or stay constant—depending on the type of reference
used.
Excel has two types of references:
1. Relative Reference → changes when copied
2. Absolute Reference → stays fixed even when copied

1. Relative Referencing
Definition
A relative reference automatically adjusts when the formula is copied to another
cell.
Syntax Example:
=A1 + B1
If you copy this from C1 to C2, it becomes:
=A2 + B2 (Excel adjusts row numbers)
Use Case
Useful for repeating calculations across rows.

Example of Relative Reference

Table:
ABC
4 3 (Formula)
6 2 (Formula)
5 7 (Formula)
In C2, enter:
=A2 + B2
Then drag down.
Excel automatically fills:
A B C (Result)
4 37
6 28
5 7 12
Because the cell reference changed with each row → relative referencing.
2. Absolute Referencing
Definition
An absolute reference never changes, even if you drag or copy the formula.
Symbol: $
 $A$1 → both column A and row 1 are fixed.
Use Case
Used when applying the same constant value everywhere:
✔ Tax rate
✔ Discount rate
✔ Fixed cost
✔ Constant multiplication factor

Example of Absolute Reference

Table:
A B
Price Tax Rate
100 5%
300 5%
150 5%
Let’s assume tax rate is in cell B1 = 5%
In C2, write:
=A2 * $B$1
Here $B$1 will never change when copied.
Drag C2 downward → Excel produces:
A (Price) B (Tax Rate) C (Tax Amount)
100 5% 5
300 5% 15
150 5% 7.5
Because B1 was kept constant using $B$1.

3. Mixed Referencing
Sometimes you want to lock either:
 Only the column OR
 Only the row
Forms:
 $A1 → lock column A
  A$1 → lock row 1

Example of Mixed Reference

Imagine a multiplication table.
If you enter:
=$A2 * B$1
This means:
 Column A is fixed
 Row 1 is fixed
 Other parts shift normally
This is used in matrix operations, charts, pivot formulas, and data tables.

Comparison Table
Changes When
Type Example Use Case
Copied?
Repeating formula for each
Relative A1 YES
row/column
Absolute $A$1 NO Fixed constant values
$A1 /
Mixed Partially Tables, matrix formulas
A$1

Summary
 Relative reference → adjusts automatically
 Absolute reference → stays fixed using $
 Mixed reference → locks either row or column
 Used for formulas, tables, repeating calculations, tax/discount formulas, and
large datasets
 Q4. Simple statistical functions

Excel provides built-in statistical functions to calculate basic descriptive statistics

such as sum, average, maximum, minimum, and count. These functions are
widely used in data analysis, data science, and business reports.
Below are the most commonly used statistical functions with clear examples.

1. SUM() Function
Definition
Adds all numbers in a given range.
Syntax:
=SUM(range)
Example
If cells A1 to A5 contain:
10, 20, 30, 40, 50
Formula:
=SUM(A1:A5)
Output:
150

2. AVERAGE() Function
Definition
Calculates the mean of a range.
Syntax:
=AVERAGE(range)
Example
=AVERAGE(A1:A5)
Output:
30

3. MAX() Function
Definition
Returns the largest value in a range.
Syntax:
=MAX(range)
Example
=MAX(A1:A5)
Output:
50

4. MIN() Function
Definition
Returns the smallest value in a range.
Syntax:
=MIN(range)
Example
=MIN(A1:A5)
Output:
10

5. COUNT() Function
Definition
Counts how many cells contain numbers.
Syntax:
=COUNT(range)
Example
If A1–A5 contain: 10, 20, hello, 40, 50
=COUNT(A1:A5)
Output:
4
(Because “hello” is text)

6. COUNTA() Function
Definition
Counts all non-empty cells (numbers + text).
Syntax:
=COUNTA(range)
Output Example:
=COUNTA(A1:A5) → 5

7. MEDIAN() Function
Definition
Returns the middle value after sorting data.
Syntax:
=MEDIAN(range)
Example:
List: 10, 20, 30, 40, 50
Median = 30

8. MODE() Function
Definition
Returns the most frequently occurring number.
Syntax:
=MODE(range)
Example:
List: 10, 20, 20, 30 → Output = 20

Sample Practical Table

A (Marks)
78
95
62
89
71
Formulas:
Task Formula Output
Total Marks =SUM(A2:A6) 395
Average Marks =AVERAGE(A2:A6) 79
Highest Marks =MAX(A2:A6) 95
Lowest Marks =MIN(A2:A6) 62
Number of Entries =COUNT(A2:A6) 5

Summary (For Practical File)

Function Purpose
SUM() Adds values
AVERAGE() Mean value
MAX() Largest value
MIN() Smallest value
COUNT() Counts numbers
COUNTA() Counts non-empty cells
MEDIAN() Middle value
Function Purpose
MODE() Most frequent value
 Q5. Logical operations

Logical functions in Excel help you make decisions based on conditions.

They return TRUE or FALSE, or allow you to perform actions depending on the
result.
The most commonly used logical functions are:
 IF()
 AND()
 OR()
 NOT()

1. IF() Function
Definition
Checks a condition and returns one value if TRUE, another if FALSE.
Syntax:
=IF(condition, value_if_true, value_if_false)

Example 1: Pass/Fail
A (Marks)
45
72
89
30
Formula in B2:
=IF(A2>=40, "Pass", "Fail")
Marks Result
45 Pass
72 Pass
89 Pass
30 Fail

2. AND() Function
Definition
Returns TRUE only if all conditions are TRUE.
Syntax:
=AND(condition1, condition2)

Example: Check if marks are between 40 and 90

Formula:
=AND(A2>=40, A2<=90)
Marks AND Output
45 TRUE
72 TRUE
95 FALSE

3. OR() Function
Definition
Returns TRUE if any one condition is TRUE.
Syntax:
=OR(condition1, condition2)

Example: Check if marks less than 40 OR greater than 90

Formula:
=OR(A2<40, A2>90)
Marks OR Output
45 FALSE
72 FALSE
95 TRUE

4. NOT() Function
Definition
Reverses the value of a logical result.
Syntax:
=NOT(logical_value)

Example: Reverse pass/fail logic

If C2 contains TRUE → =NOT(C2) → FALSE
If C2 contains FALSE → =NOT(C2) → TRUE

Combined Logical Example

Table:
A (Marks) B (Attendance %)
A (Marks) B (Attendance %)
85 92
45 60
78 30
59 80
Condition:
✔ Student passes if
 Marks ≥ 50 AND
 Attendance ≥ 75%
Formula:
=IF(AND(A2>=50, B2>=75), "Pass", "Fail")
Marks Attendance Result
85 92 Pass
45 60 Fail
78 30 Fail
59 80 Pass

Summary Table
Function Purpose
IF() Decision making
AND() All conditions must be true
OR() At least one condition must be true
NOT() Reverses logical value
Combined IF + AND/OR Powerful conditional decision formulas
 Q6. Decision making using IF, SUMIF, COUNTIF, etc.

Excel provides several conditional functions used in data science and analytics to
make decisions based on conditions.
These include:
 IF()
 SUMIF()
 COUNTIF()
 AVERAGEIF()
 VLOOKUP()
 HLOOKUP()
 IFS()
Let’s cover each with clear examples.

1. IF() – Basic Decision Making

Syntax:
=IF(condition, value_if_true, value_if_false)
Example:
Marks → Pass if marks ≥ 40
=IF(A2>=40, "Pass", "Fail")

2. SUMIF() – Conditional Summation

Definition
Adds values that meet a single condition.
Syntax:
=SUMIF(range, criteria, sum_range)
Example Table:
Item Amount
Apple 120
Mango 80
Apple 90
Sum of amounts where Item = “Apple”:
=SUMIF(A2:A4, "Apple", B2:B4)
Output: 120 + 90 = 210

3. COUNTIF() – Conditional Counting

Definition
Counts cells that meet a condition.
Syntax:
=COUNTIF(range, criteria)
Example:
Count how many “Apple” entries exist:
=COUNTIF(A2:A4, "Apple")
Output: 2

4. AVERAGEIF() – Conditional Average

Definition
Calculates average of values that satisfy a condition.
Syntax:
=AVERAGEIF(range, criteria, average_range)
Example:
Average of Mango entries:
=AVERAGEIF(A2:A4, "Mango", B2:B4)
Output: 80

5. VLOOKUP() – Vertical Lookup

Definition
Searches for a value in the first column and returns a value from another column.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

Example Table:
ID Name Marks
1 Ali 85
2 Simran 74
3 Rahul 91
Find marks of ID 2:
=VLOOKUP(2, A2:C4, 3, FALSE)
Output: 74

6. HLOOKUP() – Horizontal Lookup

Definition
Same as VLOOKUP but searches horizontally across rows.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index, [range_lookup])
Example Table:
A B C
1 ID 1 2
2 Marks 80 95
Formula to get Marks of ID 2:
=HLOOKUP("Marks", A1:C2, 2, FALSE)
Output: 95

7. IFS() – Multiple IF Conditions

Definition
Simplifies multiple conditions without nesting.
Syntax:
=IFS(condition1, value1, condition2, value2, ...)

Example: Grade System

=IFS(A2>=90,"A", A2>=75,"B", A2>=60,"C", A2<60,"D")

Full Combined Example

Given the dataset:
Product Sales
A 1200
B 900
A 1500
C 400
Task 1: Total sales of Product A
=SUMIF(A2:A5,"A",B2:B5) → 2700
Task 2: Count sales of Product B
=COUNTIF(A2:A5,"B") → 1
Task 3: Average sales of Product C
=AVERAGEIF(A2:A5,"C",B2:B5) → 400
Task 4: Grade based on sales
In C2:
=IF(B2>=1000,"High","Low")

Summary Table
Function Meaning Example
IF Decision making =IF(A2>50,"Pass","Fail")
Function Meaning Example
SUMIF Conditional sum =SUMIF(A:A,"A",B:B)
COUNTIF Conditional count =COUNTIF(A:A,"A")
AVERAGEIF Conditional average =AVERAGEIF(A:A,"A",B:B)
VLOOKUP Vertical lookup =VLOOKUP("Ali",A2:C4,3,FALSE)
HLOOKUP Horizontal lookup =HLOOKUP("Marks",A1:C2,2,FALSE)
IFS Multiple conditions =IFS(A2>=90,"A",...)
 Q7. Text functions

Text functions help you clean, modify, extract, combine and analyze text data in
Excel.
Below are the most important text functions with formula + example + output.

✅ 1. CONCATENATE / CONCAT / TEXTJOIN

Used to combine multiple text values.
Example Data
A B
Yug Rathi
Data Science
Formula
=CONCAT(A2," ",B2)
Output
Yug Rathi

✅ 2. UPPER, LOWER, PROPER

Used to change text case.
Example
Cell A2 = "data science"
Formulas
=UPPER(A2)
=LOWER(A2)
=PROPER(A2)
Outputs
DATA SCIENCE
data science
Data Science

✅ 3. LEFT, RIGHT, MID

Used to extract specific characters.
Example
Cell A2 = "Excel2025"
Formulas
=LEFT(A2,5)
=RIGHT(A2,4)
=MID(A2,3,4)
Outputs
Excel
2025
cel2

✅ 4. LEN
Returns number of characters.
Formula
=LEN("Hello World")
Output
11

✅ 5. FIND / SEARCH
Used to find character position in text.
Example
Cell A2 = "Data Science"
Formula
=FIND("S",A2)
Output
6

✅ 6. SUBSTITUTE
Replaces specific text.
Example
Cell A2 = "I love Excel"
Formula
=SUBSTITUTE(A2,"Excel","Python")
Output
I love Python

✅ 7. TRIM
Removes extra spaces from text.
Example
Cell A2 = " Hello World "
Formula
=TRIM(A2)
Output
Hello World
 Q8. Lookup functions

Lookup functions help you search for a value in a table and return related
information.
The most important lookup functions are:
� VLOOKUP
� HLOOKUP
� XLOOKUP
� LOOKUP

✅ 1. VLOOKUP (Vertical Lookup)

Searches a value in the first column and returns data from another column.
Example Table
Roll No Name Marks
101 Yug 88
102 Riya 92
103 Aman 76
Formula
=VLOOKUP(102, A2:C4, 2, FALSE)
Output
Riya

✅ 2. HLOOKUP (Horizontal Lookup)

Searches in the top row and returns value from another row.
Example Table
A B C
1 ID 101 102
2 Name Yug Riya
Formula
=HLOOKUP(102, A1:C2, 2, FALSE)
Output
Riya

✅ 3. XLOOKUP (Advanced Lookup)

New function that replaces VLOOKUP & HLOOKUP.
Searches in any direction.
Example Table
Product Price
Pen 10
Book 40
Bag 350
Formula
=XLOOKUP("Book", A2:A4, B2:B4)
Output
40

✅ 4. LOOKUP
Basic lookup function (older version).
Example Table
A B
10 Low
20 Medium
30 High
Formula
=LOOKUP(20, A1:A3, B1:B3)
Output
Medium
 Q9. Visualization using charts

Charts in Excel help you visually understand data patterns, comparisons, and trends.
Common chart types include:
 Column Chart
 Bar Chart
 Line Chart
 Pie Chart
 Scatter Plot
Below is an example dataset and chart creation steps.

✅ Example Dataset
Month Sales
Jan 12000
Feb 15000
Mar 18000
Apr 22000
May 20000

A. Column Chart (Most Common)

Steps / Code-like Description
1. Select data range: A1:B6
2. Go to Insert → Charts
3. Choose "Column Chart"
4. A chart will appear showing Month vs Sales
Output (Conceptual Visualization)
Sales
│
22k│ █
20k│ █ █
18k│ █ █
15k│ █ █
12k│ █ █ █
───────────────────────────
Jan Feb Mar Apr May
B. Line Chart (Used for Trends)
Steps / “Code”
1. Select A1:B6
2. Insert → Line Chart
3. Excel draws a line showing the trend of sales across months.
Output (Conceptual)
Sales
│ *
│ *
│ *
│ *
│*
└─────────────────────────
Jan Feb Mar Apr May

C. Pie Chart (Percentage Contribution)

Useful to show how each month contributes to total sales.
Steps
1. Select B1:B6
2. Insert → Pie Chart
3. Chart shows slice sizes based on sales values.
Output (Conceptual)
(Pie Chart)
▓ Jan (15%)
█ Feb (18%)
▒ Mar (21%)
▤ Apr (25%)
▧ May (21%)

D. Bar Chart
Same as column chart but horizontal.
Output Example
Jan ██████
Feb █████████
Mar ███████████
Apr ██████████████
May ████████████
 Q10. Page referencing

Page Referencing means adjusting your worksheet for printing, page setup, and
navigation so that the sheet looks clean and professional when printed.
It includes:
 Setting page layout
 Adding headers & footers
 Adjusting print area
 Adding page breaks
 Using page numbers

A. Page Layout Settings

Steps:
1. Go to: Page Layout Tab
2. Set Orientation → Portrait / Landscape
3. Set Size → A4 (most common)
4. Set Margins → Normal / Narrow / Custom

B. Header and Footer (Page Referencing)

Add things like page number, date, filename.
Steps:
1. Insert → Header & Footer
2. Choose:
- Page Number
- Current Date
- Sheet Name
- File Path
3. Header/Footer applies to all printed pages.
Output Example (How it appears on printed page):
------------------------------------------
Sales Report 2025
------------------------------------------

(Excel Content Here)

------------------------------------------
Page 1 of 3
------------------------------------------

C. Setting Print Area

Steps:
1. Select the area you want to print
2. Go to Page Layout → Print Area → Set Print Area
Output (Effect):
Only selected rows/columns will be printed.

D. Page Breaks
Used to control where a new page starts.
Steps:
1. Click the row where new page should start
2. Page Layout → Breaks → Insert Page Break
Output Example:
Page 1: Rows 1–30
------------------------- (page break)
Page 2: Rows 31–60

E. Print Titles (Repeating Header Row)

Useful for long tables.
Steps:
1. Page Layout → Print Titles
2. In "Rows to repeat at top": select row (example: $A$1:$E$1)
Output:
Every printed page will show the header row.

F. Scaling to Fit on One Page

Steps:
1. Page Layout → Scale to Fit
2. Fit Sheet on One Page → Yes
Output:
Excel shrinks content so the whole sheet fits on 1 page without cutting.