Last reviewed 16 Sept 2026 · 11 min read
Spreadsheet basics
A spreadsheet organises data in rows and columns for calculation, analysis and visualisation. Microsoft Excel is widely used for estimates, bills of quantities, bar bending schedules, levelling computations, material test results, cash flows and project tracking. Alternatives: Google Sheets, LibreOffice Calc.
| Term | Meaning |
|---|---|
| Workbook | An Excel file (.xlsx) containing one or more worksheets |
| Worksheet (sheet) | A grid of rows and columns (tabs at the bottom) |
| Column | Vertical, labelled A, B, C … Z, AA … XFD |
| Row | Horizontal, numbered 1, 2, 3 … |
| Cell | Intersection of a row and column, identified by its address (e.g. C5) |
| Active cell | Currently selected cell (shown in the Name Box) |
| Range | Group of cells, e.g. A1:D10 |
| Formula bar | Shows/edits the content or formula of the active cell |
- A worksheet has 1 048 576 rows and 16 384 columns (up to column XFD) in Excel 2007 and later.
- New workbooks in recent versions open with one sheet by default (earlier versions had three).
Data entry
- Data types: numbers, text (labels), dates/times, formulas, logical values (TRUE/FALSE).
- Numbers right-align and text left-aligns by default.
- AutoFill (fill handle) — drag the small square at a cell's corner to copy values or extend series (1, 2, 3…; Jan, Feb…; Mon, Tue…).
- Flash Fill (Ctrl + E) — recognises patterns (e.g. splitting names).
- Alt + Enter — new line within a cell.
- Comments/notes attach remarks to cells.
Formulas
- Always begin with = (e.g.
=B2*C2). - Operators:
+ - * / ^(exponent),&(text join), comparison= > < >= <= <>. - Order of precedence: parentheses →
^→*and/→+and-→&→ comparisons (left to right within the same level; negation applies before exponent in Excel, so=-2^2gives 4). - Show formulas: Ctrl + ` (grave accent).
Cell references
| Type | Example | Behaviour when copied |
|---|---|---|
| Relative | A1 |
Changes relative to new position (default) |
| Absolute | $A$1 |
Never changes — fixed row and column |
| Mixed | $A1 (column fixed) or A$1 (row fixed) |
Only the unfixed part changes |
- F4 cycles through reference types while editing a formula.
- References to other sheets:
Sheet2!B4; to other workbooks:[Rates.xlsx]Sheet1!C3. - Named ranges (e.g. name cell B1 as
Rate) make formulas readable:=Qty*Rate.
Functions
A function is a predefined formula: =FUNCTION(arguments). Insert via Insert Function (Shift + F3) or the Formulas tab.
Mathematical and statistical
| Function | Purpose | Example |
|---|---|---|
| SUM | Adds values | =SUM(D2:D20) |
| AVERAGE | Arithmetic mean | =AVERAGE(B2:B4) |
| MIN / MAX | Smallest / largest | =MAX(C2:C50) |
| COUNT | Counts cells with numbers | =COUNT(A1:A10) |
| COUNTA | Counts non-empty cells | =COUNTA(A1:A10) |
| COUNTBLANK | Counts empty cells | |
| COUNTIF / COUNTIFS | Counts cells meeting condition(s) | =COUNTIF(E2:E30,"<20") |
| SUMIF / SUMIFS | Conditional sum | =SUMIF(A2:A40,"Cement",D2:D40) |
| AVERAGEIF | Conditional average | |
| ROUND / ROUNDUP / ROUNDDOWN | Rounding to digits | =ROUND(12.3456,2) → 12.35 |
| INT, MOD, ABS | Integer part, remainder, absolute value | =MOD(17,5) → 2 |
| SQRT, POWER, PI | Square root, power, π | =POWER(2,10) → 1024 |
| SIN, COS, TAN, RADIANS, DEGREES | Trigonometry (angles in radians) | =SIN(RADIANS(30)) → 0.5 |
| RANK.EQ, MEDIAN, MODE.SNGL, STDEV.S | Statistics |
Logical
| Function | Example |
|---|---|
| IF | =IF(F2>=25,"Pass","Fail") |
| AND / OR / NOT | =IF(AND(B2>=20,C2>=20),"OK","Check") |
| IFERROR | =IFERROR(B2/C2,0) |
| IFS (newer) | Multiple conditions |
Lookup and reference
| Function | Syntax / notes |
|---|---|
| VLOOKUP | =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) — searches the first column of a table vertically; use FALSE (0) for exact match |
| HLOOKUP | Horizontal equivalent — searches the first row |
| XLOOKUP (Excel 2021/365) | =XLOOKUP(lookup, lookup_array, return_array) — exact match by default, can look left |
| INDEX and MATCH | =INDEX(C2:C50, MATCH(G1, A2:A50, 0)) — flexible lookup in any direction |
Text
CONCAT/CONCATENATE or & (join), LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER, TEXT (format a number as text), FIND/SEARCH, SUBSTITUTE.
Date and time
TODAY() (current date), NOW() (date and time), DATE, YEAR, MONTH, DAY, NETWORKDAYS (working days between dates), EDATE, WORKDAY, DATEDIF. Dates are stored as serial numbers, so subtracting dates gives days.
Financial
PMT (loan instalment), FV (future value), PV, NPV, IRR, RATE — useful for engineering economics.
Common error values
| Error | Cause |
|---|---|
| #DIV/0! | Division by zero or empty cell |
| #N/A | Value not available — lookup value not found |
| #NAME? | Unrecognised function or range name (typo) |
| #REF! | Invalid cell reference (deleted cells) |
| #VALUE! | Wrong data type in an operation (text in arithmetic) |
| #NUM! | Invalid numeric value (e.g. square root of negative number) |
| #NULL! | Incorrect range intersection |
| #SPILL! | Dynamic array result blocked by existing data |
| ###### | Column too narrow to display the value (or negative date/time) |
| Circular reference | Formula refers to its own cell directly or indirectly |
Formatting
- Number formats (Ctrl + 1 opens Format Cells): General, Number (decimal places), Currency/Accounting (₹), Date, Time, Percentage, Fraction, Scientific, Text, Custom.
- Alignment: horizontal/vertical, wrap text, merge & centre, text orientation, indent.
- Font, borders, fill colours, cell styles, Format Painter.
- Column width/row height — double-click the boundary for AutoFit.
- Conditional formatting — highlight cells by rules (e.g. cube strength below target in red), data bars, colour scales, icon sets.
- Format as Table — structured tables with filters and banded rows.
Data tools
| Tool | Use |
|---|---|
| Sort | Ascending/descending by one or more columns |
| Filter / AutoFilter (Ctrl + Shift + L) | Show only rows meeting criteria; Advanced Filter |
| Data validation | Restrict entries (e.g. drop-down list of materials, numbers within limits) |
| Remove duplicates | Delete repeated records |
| Text to Columns | Split text by delimiter (e.g. imported survey data) |
| Subtotal, Consolidate | Group summaries; combine data from sheets |
| Get & Transform (Power Query) | Import and clean data from files, databases, web |
| Group/Outline | Collapse detail rows |
Charts
- Select data → Insert → Chart (or F11 for a chart sheet, Alt + F1 embedded chart).
- Types: column/bar (comparisons), line (trends over time — progress S-curves), pie (parts of a whole), scatter (XY) (relationships — stress–strain, calibration curves), area, histogram, combo, waterfall.
- Chart elements: titles, axes, axis titles, legend, data labels, gridlines, trendlines (with equation and R²).
- Sparklines — tiny charts within cells.
Pivot tables
- Insert → PivotTable — summarise large datasets by dragging fields into Rows, Columns, Values (sum, count, average) and Filters.
- Example: total quantity of each material used per month across sites; PivotCharts; slicers.
What-if analysis
| Tool | Purpose |
|---|---|
| Goal Seek | Finds the input value needed to reach a target result (e.g. slab depth for a target moment capacity) |
| Data Table | Shows results for a range of input values (sensitivity) |
| Scenario Manager | Compares sets of input assumptions |
| Solver (add-in) | Optimisation with multiple variables and constraints (e.g. least-cost mix proportions) |
Viewing, protection and printing
- Freeze Panes (View tab) — keep header rows/columns visible while scrolling.
- Split window; zoom; page layout and page break preview.
- Protect Sheet/Workbook (Review tab) — lock cells and structure; Allow edit ranges.
- Print — set print area, print titles (repeat rows at top), orientation, fit to page, headers/footers, gridlines.
File formats
| Extension | Type |
|---|---|
| .xlsx | Default workbook (2007+) |
| .xls | Excel 97–2003 workbook |
| .xlsm | Macro-enabled workbook |
| .xltx | Template |
| .csv | Comma-separated values (plain text, single sheet, no formatting/formulas saved) |
| .xlsb | Binary workbook (large files) |
| Export for sharing |
Keyboard shortcuts
| Shortcut | Action |
|---|---|
| F2 | Edit active cell |
| F4 | Toggle absolute/relative references (or repeat last action) |
| Ctrl + 1 | Format Cells dialog |
| Alt + = | AutoSum |
| Ctrl + ; / Ctrl + Shift + ; | Insert current date / time |
| Ctrl + Shift + L | Toggle filters |
| Ctrl + Arrow keys | Jump to edge of data region |
| Ctrl + Home / Ctrl + End | First cell / last used cell |
| Ctrl + Space / Shift + Space | Select column / row |
| Ctrl + D / Ctrl + R | Fill down / fill right |
| Ctrl + Page Up / Page Down | Previous / next worksheet |
| Shift + F11 | Insert new worksheet |
| F11 | Create chart in a new sheet |
| Ctrl + ` | Show formulas |
| Alt + Enter | New line in cell |
| Ctrl + E | Flash Fill |
| Ctrl + T | Create table |
Worked examples
Worked ExampleExample 1 — estimate with absolute reference
Quantities are in C2:C10 and rates in D2:D10; a contingency percentage of 3% is in cell G1. Write formulas for amount, total and total with contingency.
Solution.
- Amount in E2:
=C2*D2(fill down to E10) - Total in E11:
=SUM(E2:E10) - With contingency:
=E11*(1+$G$1)— the absolute reference keeps pointing to G1 if copied.
Worked ExampleExample 2 — bar bending schedule weight
Number of bars in B3, cutting length (m) in C3 and diameter (mm) in D3. Write the weight formula.
Solution. =B3*C3*D3^2/162 — for 10 bars of 16 mm, 4.41 m long: 69.7 kg
Worked ExampleExample 3 — cube test result check
Average cube strengths are in F2:F20 and the required value is 25. Write a formula for "Accept"/"Review" and count failures.
Solution. In G2: =IF(F2>=25,"Accept","Review"); failures: =COUNTIF(F2:F20,"<25")
Worked ExampleExample 4 — VLOOKUP
A rate table with item codes in A2:A100 and rates in column C (third column of A2:C100). Find the rate for the code typed in F2.
Solution. =VLOOKUP(F2,$A$2:$C$100,3,FALSE) — returns #N/A if the code does not exist (wrap with IFERROR if needed).
Worked ExampleExample 5 — reduced levels
In a levelling sheet, the benchmark RL is in B2 and backsight in C2. The height of instrument is =B2+C2. If a foresight to point A is in D3, what is the RL of A?
Solution. RL of A in B3: =$E$2-D3, where E2 holds the height of instrument (=B2+C2).
Worked ExampleExample 6 — date difference
Start date in B2 (01-06-2026) and completion date in C2 (30-06-2026). Find calendar days and working days.
Solution. =C2-B2 → 29 days elapsed (add 1 to count both days inclusively); =NETWORKDAYS(B2,C2) gives working days excluding weekends (and optional holidays).
Frequently tested points
- Workbook contains worksheets; cell address = column letter + row number; 1 048 576 rows × 16 384 columns (XFD).
- Formulas start with =; precedence ( ) → ^ → * / → + −.
- Relative
A1, absolute$A$1, mixed$A1/A$1; F4 toggles. - COUNT (numbers) vs COUNTA (non-empty); COUNTIF, SUMIF; IF, AND, OR, IFERROR.
- VLOOKUP searches first column; FALSE = exact match; XLOOKUP and INDEX–MATCH more flexible.
- Trigonometric functions use radians — use RADIANS().
- Errors: #DIV/0!, #N/A, #NAME?, #REF!, #VALUE!, #NUM!, ###### (narrow column).
- Conditional formatting, data validation, sort/filter (Ctrl + Shift + L), remove duplicates, text to columns.
- Charts: column (compare), line (trend), pie (proportion), scatter (relationship); F11 chart sheet.
- Pivot tables summarise; Goal Seek finds input for a target; Solver optimises; Scenario Manager compares.
- Freeze Panes; protect sheet; print titles.
- .xlsx default; .xlsm macros; .csv plain text.
- Shortcuts: F2 edit, Alt + = AutoSum, Ctrl + ; date, Ctrl + 1 format cells, Ctrl + ` show formulas.
Common MistakeCommon mistakes
- Forgetting absolute references for fixed rates, causing wrong results when formulas are copied.
- Using degrees directly in SIN/COS functions.
- Using VLOOKUP with approximate match (TRUE) on unsorted data.
Revision SummaryChapter summary
- Excel organises data in workbooks and worksheets of rows, columns and cells, with AutoFill and Flash Fill speeding entry.
- Formulas use operators, precedence and relative, absolute or mixed references to compute results.
- Mathematical, statistical, logical, lookup, text, date and financial functions handle engineering calculations, with error values signalling problems.
- Formatting, conditional formatting, sorting, filtering, validation, charts and pivot tables organise, check and visualise data.
- What-if tools, protection, printing options, file formats and shortcuts make Excel a practical tool for estimates, BBS, levelling and test records.