A spreadsheet is a grid of cells used for calculations, lists, budgets, marks sheets and charts. Excel (1985) saves .xlsx files. A workbook contains sheets; each sheet has 1,048,576 rows × 16,384 columns (A to XFD) = over 17 billion cells. Hindi: स्प्रेडशीट / इलेक्ट्रॉनिक सारणी.
Basic ideas
| Term | Meaning | Example |
|---|---|---|
| Cell | Intersection of a column letter and row number | B3 = column B, row 3 |
| Range | Block of cells | A1:C10 (30 cells) |
| Formula | Starts with = ; calculates | =A1+B1 |
| Function | Built-in formula | =SUM(A1:A10) |
| Relative reference | Changes when copied | =A1 copied down becomes =A2 |
| Absolute reference | Fixed with $ (press F4) | =$A$1 stays =$A$1; mixed: $A1 or A$1 |
| Operators | + − * / ^ (power) & (join text) = <> < > <= >= | =2^3 → 8; ="Hi "&A1 |
| Order of operations | Brackets → Exponent → × ÷ → + − | =2+3*4 → 14 |
| Fill handle | Small square at the cell corner — drag to copy or make series | Mon, Tue, Wed… / 1, 2, 3… |
| Flash Fill (Ctrl + E) | Excel guesses the pattern | Split 'Ram Kumar' into first/last name |
| Errors | #DIV/0! divide by zero · #N/A not found · #REF! deleted cell · #VALUE! wrong type · #NAME? unknown function · ##### column too narrow |
40 formulas you must know
| Function | What it does | Example |
|---|---|---|
| SUM | Adds numbers | =SUM(A1:A10) |
| AVERAGE | Mean | =AVERAGE(B2:B20) |
| MIN / MAX | Smallest / largest | =MAX(C2:C50) |
| COUNT / COUNTA / COUNTBLANK | Counts numbers / non-empty / empty cells | =COUNTA(A:A) |
| ROUND / ROUNDUP / ROUNDDOWN | Round to n decimals | =ROUND(A1,2) |
| IF | Condition | =IF(A1>=33,"Pass","Fail") |
| IFS | Many conditions | =IFS(A1>=90,"A",A1>=75,"B",TRUE,"C") |
| AND / OR / NOT | Logic | =IF(AND(A1>=33,B1>=33),"Pass","Fail") |
| SUMIF / SUMIFS | Sum with condition(s) | =SUMIF(B:B,"Delhi",C:C) |
| COUNTIF / COUNTIFS | Count with condition(s) | =COUNTIF(A:A,">50") |
| AVERAGEIF | Average with condition | =AVERAGEIF(B:B,"Science",C:C) |
| VLOOKUP | Find a value in the first column, return from column n | =VLOOKUP(E2,A2:C100,3,FALSE) |
| HLOOKUP | Same, horizontal | =HLOOKUP("Jan",A1:M5,3,FALSE) |
| XLOOKUP | Modern lookup (Excel 2021+) | =XLOOKUP(E2,A:A,C:C,"Not found") |
| INDEX / MATCH | Flexible lookup | =INDEX(C:C,MATCH(E2,A:A,0)) |
| CONCAT / CONCATENATE / & | Join text | =CONCAT(A1," ",B1) |
| TEXTJOIN | Join with separator | =TEXTJOIN(", ",TRUE,A1:A5) |
| LEFT / RIGHT / MID | Extract characters | =LEFT(A1,3) |
| LEN | Length of text | =LEN(A1) |
| TRIM | Remove extra spaces | =TRIM(A1) |
| UPPER / LOWER / PROPER | Change case | =PROPER(A1) |
| TEXT | Format number as text | =TEXT(A1,"dd-mm-yyyy") |
| TODAY / NOW | Current date / date-time | =TODAY() |
| DATE / DAY / MONTH / YEAR | Build or split dates | =YEAR(A1) |
| DATEDIF | Difference between dates | =DATEDIF(A1,TODAY(),"Y") → age |
| NETWORKDAYS | Working days between dates | =NETWORKDAYS(A1,B1) |
| EOMONTH | End of month | =EOMONTH(A1,0) |
| RANK | Position in a list | =RANK(B2,$B$2:$B$50) |
| LARGE / SMALL | n-th largest / smallest | =LARGE(B:B,2) |
| ABS / MOD / INT / SQRT / POWER | Maths | =MOD(10,3) → 1 |
| RAND / RANDBETWEEN | Random numbers | =RANDBETWEEN(1,6) |
| PMT | Loan EMI | =PMT(9%/12,60,-500000) |
| FV / PV / NPV / IRR | Finance | =FV(8%/12,120,-5000) |
| IFERROR | Replace errors | =IFERROR(A1/B1,0) |
| UNIQUE / FILTER / SORT | Dynamic arrays (365) | =UNIQUE(A2:A100) |
| SUMPRODUCT | Multiply then add | =SUMPRODUCT(B2:B10,C2:C10) |
| TRANSPOSE | Rows ↔ columns | =TRANSPOSE(A1:C3) |
| HYPERLINK | Clickable link | =HYPERLINK("https://codekilla.com","Codekilla") |
| CHOOSE | Pick by index | =CHOOSE(2,"Red","Green","Blue") → Green |
| LET / LAMBDA | Variables & custom functions (365) | =LET(x,A1*2,x+1) |
Features
- ISort & Filter (Data tab) — A→Z, Z→A, custom; AutoFilter drop-downs (Ctrl + Shift + L).
- IIConditional Formatting (Home) — colour cells that meet a rule: highlight marks < 33 in red, data bars, colour scales, icon sets.
- IIICharts (Insert) — Column, Bar, Line, Pie, Area, Scatter, Combo; Alt + F1 makes an instant chart. Recommended Charts helps beginners.
- IVPivot Table (Insert → PivotTable) — summarise lakhs of rows by dragging fields: total sales by city by month in seconds.
- VData Validation (Data) — drop-down lists, allow only numbers 1–100, dates, custom rules.
- VIFreeze Panes (View) — keep headings visible while scrolling. Split, Hide/Unhide, Group.
- VIIText to Columns, Remove Duplicates, What-If Analysis (Goal Seek, Scenario Manager), Data Table, Protect Sheet, Macros/VBA, Power Query.
- VIIINumber formats — General, Number, Currency (₹), Accounting, Percentage, Date, Time, Fraction, Scientific, Text, Custom (e.g. 0.00 "kg").
- IXPrinting — Page Layout → Print Area, Print Titles (repeat header rows), Fit to 1 page wide; Ctrl + P.
Shortcuts
| Shortcut | Action |
|---|---|
| Ctrl+Arrow | Jump to edge of data |
| Ctrl+Shift+Arrow | Select to edge |
| Ctrl+Space/Shift+Space | Select column / row |
| Ctrl+A | Select table (twice = whole sheet) |
| F2 | Edit active cell |
| F4 | Toggle $ absolute reference / repeat last action |
| Alt+= | AutoSum |
| Ctrl+;/Ctrl+Shift+; | Insert today's date / current time |
| Ctrl+D/Ctrl+R | Fill down / fill right |
| Ctrl+Enter | Fill selection with the same entry |
| Alt+Enter | New line inside a cell |
| Ctrl+1 | Format Cells dialog |
| Ctrl + Shift + $ / % / # | Currency / percent / date format |
| Ctrl+Page Up/Down | Previous / next sheet |
| Shift+F11 | New sheet |
| Ctrl+T | Create table |
| Ctrl+Shift+L | Toggle filters |
| Alt+N V | Pivot table |
| Ctrl+9/Ctrl+0 | Hide row / column |
| Ctrl+F/H | Find / Replace |
| Ctrl+Z/Y | Undo / Redo |
| Ctrl+S/P/N/O | Save / Print / New / Open |
| Ctrl+` | Show formulas |
Exam facts: a formula always begins with = ; the Name Box shows the active cell address; the Formula Bar shows its content; an Excel 365 sheet has 1,048,576 rows and 16,384 columns; a workbook is .xlsx and a macro-enabled one is .xlsm; the first spreadsheet was VisiCalc (1979), then Lotus 1-2-3 (1983).
// Keep exploring
All chapters →