5 Basics of Computers · 5.1 Computer basics: number systems, codes, logic, algorithms and spreadsheets
5.1.3 Spreadsheets: cells, ranges, formulas, cell references, functions, editing and printing
A spreadsheet is a grid of rows and columns whose cells hold numbers, text or formulas; formulas recalculate automatically when any value they use changes.
- APPSC Paper I
- Advanced level
- 9 min read
- Free
Core facts
- Spreadsheet programs: MS Excel, LibreOffice Calc, Google Sheets; the first PC spreadsheet was VisiCalc (1979), followed by Lotus 1-2-3.
- Workbook = the file; worksheet (sheet) = one grid inside it. Excel file types: .xlsx (2007 onwards), .xls (older), .xlsm (with macros); Calc uses .ods; .csv is plain comma-separated text.
- Rows are numbered (1, 2, 3 …); columns are lettered (A … Z, AA … XFD).
- Excel 2007 onwards: 1,048,576 rows × 16,384 columns per sheet; last column XFD. (Excel 2003: 65,536 rows × 256 columns, last column IV.)
- Cell: the meeting point of a row and a column; its address is column letter + row number, e.g. C5.
- Active cell: the selected cell with a thick border; its address shows in the Name Box; its content shows in the Formula Bar.
- Range: a rectangular block written with a colon, e.g. A1:C4 (3 columns × 4 rows = 12 cells).
- Formula: starts with = (Lotus used +/@), e.g. =A1+B1. A function is a built-in formula, e.g. =SUM(A1:A10).
- Cell references:
- Relative A1: changes when copied.
- Absolute $A$1: never changes.
- Mixed $A1 (column fixed) or A$1 (row fixed).
- F4 cycles A1 → $A$1 → A$1 → $A1 → A1.
- Default alignment: numbers and dates right, text left.
- Fill handle: small square at the bottom-right of the selection; drag to copy or AutoFill a series (Mon, Tue …; 1, 2, 3 …).
- Chart, sort, filter, pivot table, conditional formatting are standard features.
Example 1. How many cells are in the range B2:E6? Solution. Columns B to E = 4; rows 2 to 6 = 5. 4 × 5. Answer: 20
Explained
1. Entering and editing.
- F2 edits the active cell; Esc cancels the entry; Enter confirms and moves down; Tab moves right.
- Ctrl+Z undo, Ctrl+Y redo, Ctrl+C / X / V copy, cut, paste; Ctrl+F find, Ctrl+H replace.
- Paste Special pastes only values, formats, formulas, or transposes rows and columns.
- Ctrl+; inserts today's date; Ctrl+Home goes to A1; Alt+Enter starts a new line inside a cell.
- Insert / Delete rows, columns, cells; Clear removes contents but keeps the cell; Delete removes the cell and shifts others.
- Merge & Center joins cells; Wrap Text fits long text in a cell; Freeze Panes keeps headings visible while scrolling.
- #### in a cell: the column is too narrow to show the number (widen it).
2. Arithmetic operators and precedence (Excel).
| Order | Operator | Meaning |
|---|---|---|
| 1 | : , (space) | Reference operators |
| 2 | − | Negation (−5) |
| 3 | % | Percent |
| 4 | ^ | Power |
| 5 | * / | Multiply, divide |
| 6 | + − | Add, subtract |
| 7 | & | Join text |
| 8 | = < > <= >= <> | Compare |
Brackets override the order. Same level: left to right.
Example 2. Evaluate =2+34^2/8. Solution.* 4^2 = 16; 3 × 16 = 48; 48 ÷ 8 = 6; 2 + 6. Answer: 8
Example 3. Evaluate =(5+3)2^2. Solution.* 8 × 4. Answer: 32
3. Copying formulas: relative vs absolute.
Example 4. C1 contains =A1+B1. It is copied to C2 and to D1. What do they contain? Solution. Down one row: =A2+B2. Right one column: =B1+C1. Answer: C2 =A2+B2; D1 =B1+C1
Example 5. C2 contains =$A2B$1. It is copied to D3. Result? (a) =$A3C$1 (b) =$A2C$1 (c) =$B3C$2 (d) =$A3B$1 Solution.* Move: one column right, one row down. $A2: column fixed, row 2 → 3. B$1: column B → C, row fixed. Answer: (a)
Use: =B2*$E$1 multiplies every price in column B by one rate held in E1; copying keeps pointing to E1.
4. Common functions. Data: A1 = 10, A2 = 20, A3 blank, A4 = "abc", A5 = 30, A6 = 0.
| Function | Does | Result for A1:A6 |
|---|---|---|
| SUM(A1:A6) | Adds numbers | 60 |
| COUNT(A1:A6) | Counts numbers | 4 |
| COUNTA(A1:A6) | Counts non-empty cells | 5 |
| COUNTBLANK(A1:A6) | Counts empty cells | 1 |
| AVERAGE(A1:A6) | SUM ÷ COUNT | 15 |
| MAX / MIN | Largest / smallest | 30 / 0 |
AVERAGE ignores blanks and text but includes zeros: 60 ÷ 4 = 15, not 60 ÷ 6.
Example 6. Marks in B2:B6: 35, 62, 48, 90, 27 (pass mark 40).
| Formula | Result |
|---|---|
| =AVERAGE(B2:B6) | 52.4 |
| =MEDIAN(B2:B6) | 48 |
| =COUNTIF(B2:B6,">=40") | 3 |
| =SUMIF(B2:B6,">=40") | 200 |
| =IF(B2>=40,"Pass","Fail") | Fail |
| =RANK(B4,B2:B6) | 3 |
5. Printing.
- Print Preview shows pages before printing; Page Setup sets orientation (portrait/landscape), paper size, margins, scaling ("Fit to 1 page wide").
- Print Area prints only a selected range; Print Titles ("Rows to repeat at top") repeats heading rows on every page.
- Header and footer carry page numbers, date, file name.
- Gridlines and row/column headings are not printed by default; turn them on in Page Setup → Sheet.
- Page Break Preview shows and moves page breaks. Shortcut: Ctrl+P.
Deeper
1. More functions used in statistics work.
| Function | Use | Example → result |
|---|---|---|
| ROUND(x, n) | Round to n decimals | ROUND(3.14159, 2) → 3.14 |
| ROUND(x, −2) | Round to hundreds | ROUND(1234.5, −2) → 1200 |
| INT(x) | Round down to integer | INT(−3.7) → −4 |
| MOD(a, b) | Remainder, sign of b | MOD(17, 5) → 2; MOD(−7, 3) → 2 |
Others: MEDIAN, MODE, STDEV (sample) / STDEV.P (population), VAR, CORREL, PERCENTILE, QUARTILE, GEOMEAN, HARMEAN, FREQUENCY (an array function for frequency tables), SUMPRODUCT (Σfx in one step), VLOOKUP / HLOOKUP (look up a value in the first column / row of a table), AND, OR, NOT, CONCATENATE or &, TODAY(), NOW().
Example 7. Frequencies in B2:B6 against values in A2:A6. Write the mean in one formula. Solution. Mean = Σfx ÷ Σf. Answer: =SUMPRODUCT(A2:A6,B2:B6)/SUM(B2:B6)
2. Error values.
| Error | Cause |
|---|---|
| #DIV/0! | Division by zero (or by an empty cell) |
| #VALUE! | Wrong type, e.g. text in arithmetic |
| #REF! | Reference to a deleted cell |
| #NAME? | Misspelt function or name |
| #N/A | Value not available (lookup failed) |
| #NUM! | Invalid number (e.g. square root of −1) |
| #NULL! | Two ranges that do not intersect |
A formula that refers to its own cell (directly or indirectly) is a circular reference.
3. Tricky precedence. In Excel, negation comes before power: =-2^2 gives 4, while =0-2^2 gives −4 (here − is subtraction). Excel rounds a half away from zero: ROUND(2.5, 0) = 3, ROUND(−2.5, 0) = −3.
4. Other cell-reference forms.
- Whole column A:A, whole row 5:5.
- Another sheet: Sheet2!B4; another workbook: [Book2.xlsx]Sheet1!A1.
- Union (comma): SUM(A1:A3, C1:C3); intersection (space): SUM(A1:C3 B2:D4) adds only the shared cells B2:C3.
- Named range: call B2:B6 "Marks" and write =AVERAGE(Marks).
5. Data tools. Sort (ascending/descending, multi-level), Filter / AutoFilter (shows only matching rows; others are hidden, not deleted), Data Validation (restrict entries, e.g. 0–100), Conditional Formatting (colour cells by rule), Pivot Table (cross-tabulation, the spreadsheet's tabulation tool), Goal Seek (find the input that gives a target result), charts (column, bar, line, pie, scatter/XY, histogram).
Common traps
- ✗ "Formulas start with +." ✓ In Excel and Calc they start with =.
- ✗ "COUNT counts all non-empty cells." ✓ COUNT counts numbers; COUNTA counts non-empty cells.
- ✗ "AVERAGE treats blank cells as zero." ✓ Blanks (and text) are ignored; a typed 0 is counted.
- ✗ "$A1 fixes the row." ✓ $A1 fixes the column; A$1 fixes the row.
- ✗ "#### means a formula error." ✓ The column is too narrow.
- ✗ "Gridlines print by default." ✓ They do not; switch them on in Page Setup.
- ✗ "A workbook and a worksheet are the same." ✓ A workbook (file) contains worksheets.
- ✗ "Excel 2007+ has 65,536 rows." ✓ 1,048,576 rows, 16,384 columns (to XFD); 65,536 was Excel 2003.
Quick revision
- Workbook = file; worksheet = grid; cell = column letter + row number.
- Range A1:C4 = 12 cells; Name Box shows the active cell.
- Formulas begin with =; functions like SUM, AVERAGE, COUNT, IF.
- Relative A1; absolute $A$1; mixed $A1 / A$1; F4 toggles.
- Precedence: negation, %, ^, * /, + −, &, comparisons.
- COUNT = numbers; COUNTA = non-empty; AVERAGE ignores blanks, counts zeros.
- #DIV/0!, #VALUE!, #REF!, #NAME?, #N/A; #### = narrow column.
- Excel 2007+: 1,048,576 rows × 16,384 columns (XFD).
- Printing: Print Area, Print Titles, Page Setup, Print Preview, Ctrl+P.
- Gridlines not printed by default.
Go deeper: Mains writing (beyond what APPSC Paper I usually asks)
Mains writing Mains writing
- Frame: the spreadsheet is the everyday tool of a statistical office: data entry from schedules, validation, tabulation and computing averages, dispersion and index numbers.
- Good practice: keep raw data on one sheet and results on another; use absolute references for constants (weights, base-year prices); validate entries; document formulas.
- Limits: easy to make silent errors (wrong range, overwritten formula); large datasets need a database or statistical package.
Practise this topic. Questions on Spreadsheets, with answers explained.
All Computer basics: number systems, codes, logic, algorithms and spreadsheets notes · APPSC Paper I syllabus · APPSC Paper I Mock 1 with answers · Start free
Medha AI writes its own notes and checks them against the sources listed. Medha AI is an independent preparation service. It is not affiliated with, endorsed by or connected to APPSC, TSPSC, MPSC, UPSC or any other commission or government body.