Unit 2: Digital Documentation & Office Tools
Chapter 2: Spreadsheet Calculations and Charts
1. Fundamentals of Electronic Spreadsheets
An electronic spreadsheet (e.g., Microsoft Excel, LibreOffice Calc) is an interactive grid of rows and columns designed to organize data, execute complex mathematical computations, and generate statistical charts.
+--------------------------------------------------------------+
| Formula Bar: [ =SUM(B2:B10) ] |
+---+------------------+------------------+--------------------+
| | A | B | C |
+---+------------------+------------------+--------------------+
| 1 | Student Name | Marks (Out of 100) | Grade |
+---+------------------+------------------+--------------------+
| 2 | Aarav Sharma | 92 | =IF(B2>=90,"A","B")|
+---+------------------+------------------+--------------------+
| 3 | Diya Patel | 84 | B |
+---+------------------+------------------+--------------------+
| 4 | Total | =SUM(B2:B3) | |
+---+------------------+------------------+--------------------+
Core Concepts:
- Cell: The intersection of a column and a row (e.g.,
B4). - Cell Range: A rectangular block of adjacent cells designated by the top-left cell and bottom-right cell separated by a colon (e.g.,
A1:C10). - Formulas: Mathematical expressions created by users that must always begin with an equal sign (
=).
2. Cell Referencing Techniques
Cell referencing governs how formulas behave when copied to other cells:
| Reference Type | Syntax Example | Behavior When Copied to Another Cell |
|---|---|---|
| Relative Reference | =A1 + B1 |
Both column letter and row number change automatically relative to the new position. |
| Absolute Reference | =$A$1 * B1 |
The dollar signs ($) lock both row and column; $A$1 remains strictly fixed regardless of where the formula is copied. |
| Mixed Reference | =$A1 + B$2 |
Only the coordinate with the $ prefix is locked ($A1 locks column A; B$2 locks row 2). |
3. Essential Built-in Spreadsheet Functions
A. Mathematical and Statistical Functions
=SUM(range): Adds all numbers in a range (e.g.,=SUM(B2:B20)).=AVERAGE(range): Calculates the arithmetic mean.=COUNT(range): Counts cells containing numbers only.=COUNTA(range): Counts all non-empty cells (text, numbers, booleans).=COUNTBLANK(range): Counts blank/empty cells.=MIN(range)/=MAX(range): Returns smallest / largest value.
B. Logical Functions
=IF(logical_test, value_if_true, value_if_false):excel =IF(B2 >= 33, "Pass", "Fail")- Nested IF (Multiple conditions):
excel =IF(B2 >= 90, "A+", IF(B2 >= 75, "A", IF(B2 >= 60, "B", "C"))) =COUNTIF(range, criteria): Counts cells matching a specific condition.=COUNTIF(C2:C50, "Pass")=SUMIF(range, criteria, [sum_range]): Adds values in cells that satisfy a criterion.=SUMIF(A2:A20, "Science", B2:B20)
C. Lookup Functions
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]): Searches vertically down the first column of a table and retrieves a value from the specified column in the matching row.
4. Data Visualization: Charts and Graphs
Column / Bar Chart Line Chart Pie Chart
+--------------------+ +--------------------+ +--------------------+
| || || || | | /\ | | /| |
| || || || | | \ / \ | | / | |
| || || || | | \/ \ | | /___| |
+--------------------+ +--------------------+ +--------------------+
Category Comparison Trends Over Time Parts of a Whole (100%)
- Column / Bar Chart: Comparing discrete categorical data (e.g., quarterly sales across departments).
- Line Chart: Displaying continuous trends over time (e.g., monthly temperature changes).
- Pie Chart: Showing relative proportions contributing to a whole ($100\%$).
- Scatter Plot (XY): Plotting relationships and correlations between two continuous numerical variables.
5. Board Exam Practice Questions & Solutions
Q1. Differentiate between Relative and Absolute cell referencing with an example of each.
Answer:
- In Relative Referencing (e.g., =A1 + B1), cell addresses adjust automatically relative to the destination cell when the formula is copied.
- In Absolute Referencing (e.g., =$A$1 * B1), the coordinate preceded by a dollar sign ($) is locked, ensuring the reference remains anchored to that exact cell regardless of copying.
Q2. Write Excel formulas for the following operations on data in cells B2:B30:
1. Find the highest mark obtained.
2. Count the number of students who scored 90 or more.
3. Calculate the average marks of all students.
Answer:
1. =MAX(B2:B30)
2. =COUNTIF(B2:B30, ">=90")
3. =AVERAGE(B2:B30)
Q3. Which type of chart is most suitable for showing the percentage breakdown of household expenses? Justify your choice.
Answer:
A Pie Chart is the most suitable chart type because it represents data as proportional slices of a circular whole, making it visually intuitive to understand how individual expense categories contribute to the total 100% household budget.