13.4 MS Excel — Formulas, Functions & Charts
Key Takeaways
- A worksheet is a grid of cells addressed by column letter and row number, and a modern worksheet has 1,048,576 rows and 16,384 columns.
- A relative reference such as B2 shifts when a formula is copied, an absolute reference such as $B$2 stays fixed, and a mixed reference such as $B2 locks only the column.
- Every formula begins with an equals sign, and the order of evaluation follows brackets, then exponent, then multiplication and division, then addition and subtraction.
- The most-tested functions are SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, IF, COUNTIF, SUMIF and VLOOKUP.
- A column chart compares values, a pie chart shows proportions of a whole and a line chart shows a trend over time.
Microsoft Excel
Excel organises data in a workbook made of worksheets. Each worksheet is a grid of cells addressed by column letter and row number (for example, B3). A modern Excel worksheet has 1,048,576 rows and 16,384 columns (columns run up to XFD) — far more than any revenue office will ever fill.
Referencing, Ranges and Operators
| Concept | Notation | Behaviour when copied |
|---|---|---|
| Relative reference | B2 | Both column and row shift |
| Absolute reference | $B$2 | Neither shifts |
| Mixed reference | $B2 | Column fixed, row shifts |
| Mixed reference | B$2 | Row fixed, column shifts |
| Range | B2:B10 | A contiguous block |
| Whole column / row | B:B, 2:2 | Entire column or row |
| Another sheet | Sheet2!B2 | Reference across sheets |
Operators and precedence: brackets first, then exponent ^, then * and /, then + and -, then comparison operators. So =2+3*4 is 14, not 20; =(2+3)*4 is 20.
Common error values, and what each tells you:
| Error | Meaning |
|---|---|
#DIV/0! | Division by zero or by an empty cell |
#VALUE! | Wrong data type — text where a number is expected |
#REF! | The referenced cell has been deleted |
#NAME? | A misspelt function name or an undefined name |
#N/A | A lookup found no match |
##### | The column is too narrow to display the value — widen it |
The Function Set to Learn
| Function | Purpose | Example |
|---|---|---|
SUM | Total | =SUM(C2:C31) |
AVERAGE | Mean | =AVERAGE(C2:C31) |
COUNT / COUNTA | Count numeric cells / count non-empty cells | =COUNTA(A2:A31) |
MAX / MIN | Largest / smallest | =MAX(C2:C31) |
IF | Conditional result | =IF(D2>=35,"Qualified","Not qualified") |
COUNTIF / SUMIF | Count or total the cells meeting a condition | =COUNTIF(E2:E31,"Dry") |
ROUND | Round to a number of decimals | =ROUND(F2,2) |
VLOOKUP | Find a value in the first column of a table and return a value from another column | =VLOOKUP(A2,Villages!$A$2:$C$50,3,FALSE) |
CONCATENATE / & | Join text | =A2&" "&B2 |
LEN, LEFT, RIGHT, MID, TRIM, UPPER, LOWER | Text handling | =LEFT(A2,3) |
TODAY / NOW | Current date / date and time | =TODAY() |
SORT, FILTER (newer versions) | Arrange and select rows | — |
Worked Calculations from a Village Office
1. Total extent held. A holder's parcels are listed in acres in C2:C6. =SUM(C2:C6) gives the total; =ROUND(SUM(C2:C6),2) presents it to two decimals.
2. Acres and guntas. Column C holds extents in guntas. Acres = =INT(C2/40) and the remaining guntas = =MOD(C2,40). For 180 guntas this returns 4 acres and 20 guntas — the same partition arithmetic as in Chapter 8, done for a whole village at once.
3. Water rate at a fixed rate. If the rate per acre is in $B$1 and each holder's irrigated extent is in column D, then =D2*$B$1 copied down the column applies the same rate to every row. This is exactly why the absolute reference matters: written as =D2*B1, copying the formula would shift the rate cell down and every row after the first would be wrong.
4. Demand, collection and balance. With demand in column E and collection in column F, balance is =E2-F2, total collection =SUM(F2:F100), and the collection percentage =ROUND(SUM(F2:F100)/SUM(E2:E100)*100,1).
5. Conditional counting. To count how many parcels are dry land, where classification is in column G: =COUNTIF(G2:G100,"Dry"). To total the extent of only the dry parcels: =SUMIF(G2:G100,"Dry",C2:C100).
Sorting, Filtering and Presentation
- Sort rearranges rows by one or more columns, ascending or descending — always select the whole data block, or the rows will be scrambled relative to each other.
- Filter hides rows that do not meet a criterion, without deleting anything.
- Freeze Panes keeps the header row visible while scrolling a long village list.
- Conditional formatting colours cells meeting a rule — for instance highlighting every unpaid balance.
- Pivot table summarises a large table by category, such as total extent by land classification.
- Data validation restricts what may be entered in a cell, preventing typing errors in a long data-entry job.
Charts: a column or bar chart compares values across categories, a pie chart shows the parts of a whole (use it only when the parts genuinely sum to 100%), a line chart shows change over time, and a scatter chart shows the relationship between two variables. Choosing the wrong chart type is a standard exam question: monthly collection over a year is a line chart; the share of dry, wet and garden land is a pie chart.
Exam Angle for Section 13.4
Four things are asked repeatedly: the relative versus absolute reference distinction, the SUM / AVERAGE / COUNT / IF function set, the error values and what each means, and chart-type selection. Do the acres-and-guntas and the water-rate examples above by hand once; they cover both the arithmetic and the referencing rule in a single exercise, and they are the version of the question most likely to appear in a Revenue Department paper.
What does an absolute cell reference like $B$2 do when the formula is copied to another cell?
A water rate per acre is stored in cell B1, and irrigated extents are in column D. Which formula, when copied down the column, correctly computes the rate due for every holder?
An Excel cell displays #REF!. What does this indicate?
Column C holds a parcel extent of 180 guntas. Which pair of formulas converts it to acres and remaining guntas?