Chapter 2
Spreadsheet – Ranges and Functions
1. Introduction to Spreadsheets
Spreadsheets are powerful tools used to store, compute, and organize all types of data.
Early versions like LANPAR and VisiCalc paved the way for today’s spreadsheet programs.
Spreadsheets are vital for accounting, data analysis, record keeping, and personal as well
as business applications.
2. Entering Formulae in Spreadsheets
Every formula must begin with an equals (=) sign.
Three methods to enter a formula:
Direct Typing: Type C5 D5 E5 in the target cell to add the contents of cells C5, D5,
and E5.
Mouse Selection: Click on the formula bar, type =, and then select each cell with the
mouse to include their references in the formula, such as C5 D5 E5.
Using Functions: Use the fx button or type SUM C5 F5) to add a series of cells.
Example: In a marks list, use =C5+D5+E5+F5 in G5 to calculate a studentʼs total score.
3. Copying Formulae and Using Ranges
Formulae can be copied to other cells using the fill handle (a small box at the bottom-right
corner of a selected cell).
When a formula is copied down a column, Excel updates the cell references automatically if
they are relative.
Ranges: A range (such as C5 F5) is a block of cells selected together. It is useful for
operations like sum, average, or formatting.
Naming a range: Select cells B2 B8 and give it a name in the Name Box (e.g.,
―SalesTotal‖), then use it in formulas like SUM SalesTotal).
Example: Copy =SUM(C5:F5) from row 5 to rows 6 10 to automatically calculate totals for all
rows.
4. Cell Referencing: Relative, Absolute, Mixed
Relative Referencing: Default style; references like C5 change to C6 when copied.
Example: =C6*D6 in E6 becomes =C7*D7 in E7 if copied down.
Absolute Referencing: Uses a $ to lock column or row, e.g., $D$2. Cell reference doesnʼt
change when formula is copied.
Example: =C6*$D$2 always refers to cell D2 for commission rate.
Mixed Referencing: Only column or row is locked, e.g., $D6 or D$6.
Example: In =C6*$D$2, copying to E7 changes the relative part but keeps the absolute
part fixed.
5. Working with Multiple Worksheets
Inserting a worksheet: Right-click the sheet tab > Insert, or press Shift+F11.
Renaming: Right-click the sheet tab and select Rename.
Deleting: Right-click the sheet tab and select Delete.
Clearing data: Select all cells Ctrl+A , then use Home > Clear > Clear All.
6. Commonly Used Functions (with Examples)
a. SUM
Purpose: Adds numbers in a range or list.
Syntax: SUM(number1, number2, ...)
Example: =SUM(A2:A6) adds numbers from cells A2 to A6. If those cells have values 10, 20,
15, 25, 30, result is 100.
Example: =SUM(3, 2, TRUE) returns 6 TRUE counts as 1 .
b. AVERAGE
Purpose: Finds the arithmetic mean of numbers.
Syntax: AVERAGE(number1, number2, ...)
Example: =AVERAGE(B2:B6) where B2 B6 10, 20, 30, 40, 50 returns 30.
c. COUNT
Purpose: Counts cells containing numeric values.
Syntax: COUNT(value1, value2, ...)
Example: If A2 A6 contains 10, ―apple‖, 30, 50, blank, then =COUNT(A2:A6) gives 3.
d. MAX
Purpose: Returns the highest value in a range.
Syntax: MAX(number1, number2, ...)
Example: For A1 A5 10, 7, 9, 27, 2, =MAX(A1:A5) gives 27.
e. MIN
Purpose: Returns the smallest value in a range.
Syntax: MIN(number1, number2, ...)
Example: For A1 A5 10, 7, 9, 27, 2, =MIN(A1:A5) gives 2.
Practical Example:
Class result table:
Roll Name Term1 Term2 Final
101 Anil 80 75 90
102 Beena 65 70 85
If F2 holds =0.25*C2+0.25*D2+0.5*E2 and is copied down, this computes the weighted score
for each student.
To get the highest score: =MAX(F2:F11)
To count students appearing for finals: =COUNT(E2:E11)
7. Formatting Data in Worksheets
a. Formatting Text:
Change font type, size, color, bold, italic, underline from the Home tab.
Example: Select cells B2 B8, apply bold and blue text for emphasis.
b. Formatting Numbers & Dates:
Select cell(s), choose to show as currency, percentage, decimal, short date, or time from the
Home tab > Number group.
Example: Format E2 E11 as percentage for scores or C2 D11 as currency for prices.
c. Aligning Cell Content:
Use Home tab > Alignment group to align text left/center/right and top/middle/bottom.
Example: Center names in column B for better appearance.
d. Adding Borders:
Select desired cells, go to Home > Borders, and select a style.
Example: Add a thick box border around the totals row.
8. Real-life Applied Spreadsheet Example
Restaurant Daily Sales Table:
Columns: Dish, Unit Price, Number Sold, Total Sales
Formula in Total Sales column: =Unit_Price*Number_Sold
Use =SUM(Total_Sales_Range) to find total takings.
Format entire table—bold headers, currency for sales, borders around table.
This note offers step-by-step explanations and practical examples for each key concept,
enabling clear understanding and actionable application of spreadsheet features and
functions.
Most commonly used Shortcut Key in Excel
BOLD – Ctrl + B
Italic – Ctrl + I
Underline – Ctrl + U
Copy - Ctrl + C
Paste - Ctrl + V
Undo - Ctrl + Z
Redo - Ctrl + Y