The core idea
Use one record per row, clear column headings and formulas that refer to cells. Check units, ranges and missing values before trusting a total. Sorting and filtering change how data is presented; they do not repair incorrect data.
1. Give every cell a clear role
A worksheet is a grid. Columns commonly have letters and rows have numbers, so B2 identifies a particular cell. A cell can hold a number, text, a date or a formula. Start a table with headings that define the fields: Item, Quantity, Unit price (₹), and Total (₹). Each later row should describe one item using the same rules. Keep units in headings rather than mixing strings such as “eight rupees” with numeric prices. A number stored as text may behave differently in calculations. Separate notes from the data area, and avoid merged cells inside an ordinary calculation table. A workbook can contain several worksheets; label them by purpose so an input sheet is not mistaken for a summary. Clean structure makes errors easier to notice before any formula is written.
Sources: Microsoft: Overview of formulas in Excel ↗ · Microsoft: Accessible Word documents ↗
2. A formula is a rule, not just an answer
A formula normally begins with =. In D2, the formula =B2*C2 multiplies quantity by unit price. If B2 changes, D2 can recalculate, whereas a typed answer stays unchanged. A reference makes the relationship visible and reusable. Copying that formula down to D3 normally changes it to =B3*C3 because the references are relative to their new position. An absolute reference such as $H$1 stays fixed when the formula is copied. Use that when many rows depend on a single rate stored in H1. The dollar signs control copying behaviour; they do not mean that the value is in US dollars. Parentheses make calculation order explicit. Write =(B2+C2)*D2 when the addition must happen first, rather than assuming the program reads your intention.
3. Worked scenario: a small workshop budget
Use these invented practice purchases. Row 2 contains pencils: quantity 10 and unit price ₹8. Row 3 contains notebooks: 4 at ₹25. Row 4 contains chart sheets: 2 at ₹60. With quantity in B, price in C and total in D, =B2*C2 gives 80; copied down, the other totals are 100 and 120. Put =SUM(D2:D4) in D5 to obtain ₹300. The colon means the complete range from D2 through D4. Do not include D5 in its own sum. Suppose a fictional supplier offers a 10% discount on each listed item. Enter 10% in H1 and =D2*(1-$H$1) in E2, then copy down. Results are ₹72, ₹90 and ₹108, totalling ₹270. The ₹30 difference also equals 10% of ₹300, providing an independent arithmetic check.
See the calculation before copying the formula
| A: Item | B: Quantity | C: Unit ₹ | D: B × C | E: D × 0.90 |
|---|---|---|---|---|
| Pencils | 10 | 8 | 80 | 72 |
| Notebooks | 4 | 25 | 100 | 90 |
| Chart sheets | 2 | 60 | 120 | 108 |
| Total | — | — | 300 | 270 |
4. Worked scenario: missing attendance is not zero
A youth club records fictional attendance of 12, 18 and 0 across three completed sessions. The mean is (12 + 18 + 0) ÷ 3 = 10. Zero means a session happened and nobody attended. Now suppose the third session's record is missing instead. Averaging only 12 and 18 gives 15, but that describes the two recorded sessions, not all three. AVERAGE ignores text in referenced cells; a label such as “missing” does not automatically count as zero. Do not replace unknown values with zero merely to remove blanks. Record the missing-data explanation and identify the denominator in your summary. If a spreadsheet shows 15, ask “average over how many recorded sessions?” before deciding attendance improved. A correct formula can still support a misleading sentence when its scope is hidden.
Sources: Google Sheets: AVERAGE function ↗
5. Keep each record together when sorting
Sorting rearranges records according to a chosen field. If you sort only the quantity column, quantities can become attached to the wrong items. Select the complete data table, identify the header correctly and exclude a separate totals row unless the tool handles it explicitly. Check a known row after sorting: its item, quantity and price should still belong together. Filtering hides records that do not meet a condition; it does not necessarily delete them. An ordinary SUM can still include hidden rows, so do not assume the displayed total describes only the visible subset. Decide whether the question concerns every record or only filtered records and use the appropriate supported method. When sharing the result, state the filter and date range so somebody else can understand which records were included.
Sources: Google Sheets: SORT function ↗ · Microsoft: Overview of formulas in Excel ↗
6. Audit inputs, formulas and interpretation
Check a spreadsheet at three levels. First, inspect input meaning: are quantities counts, prices per item and dates interpreted consistently? Second, inspect formulas: do copied references point to the intended rows, does the total include every item once, and is a rate stored as 10% rather than 10? Third, inspect the conclusion: does “workshop cost” include only stationery or also travel and refreshments? Formatting can make a number look polished without correcting any of these issues. A displayed rounded value may differ from the underlying value, so use a deliberate rounding policy when needed. Keep the original practice data before making large edits. A small manual calculation and one controlled input change often reveal more than repeatedly staring at the grand total. Document assumptions beside the summary.
Sources: Microsoft: Overview of formulas in Excel ↗ · Google Sheets: AVERAGE function ↗
PUT IT INTO PRACTICE
Build and audit a practice budget
- Enter the three workshop purchases with headings and units. Calculate row totals using references and the grand total using SUM. Predict each answer before viewing it.
- Put 10% in H1 and calculate discounted totals with an absolute reference. Copy the formula and inspect it in the last row.
- Change notebook quantity from 4 to 6. Predict the new undiscounted and discounted totals, then compare. Sort the complete data rows by item name.
- Solution reasoning: notebooks become ₹150, so the total becomes ₹350 and the discounted total ₹315. The ₹45 increase after discount equals two extra notebooks at ₹22.50 each. Sorting must preserve each item’s associated fields.
Check your understanding
Why use =B2*C2 rather than type 80?
The formula records the relationship and can update when inputs change. A typed total does not automatically follow a changed quantity or price.
What do the dollar signs in $H$1 do?
They keep the referenced column and row fixed during copying. They do not change the currency or the numerical value.
Why is a blank attendance record different from zero?
Zero records known absence; blank may mean unknown data. Substituting zero changes the meaning and can distort the average.
Why can sorting one column corrupt a table?
It separates fields belonging to the same record. Sorting complete rows keeps each item attached to its own quantity and price.
Does filtering necessarily change SUM?
No. An ordinary sum may include hidden rows. Choose a calculation that matches whether you need all records or the visible subset.
Can a correct ₹300 total still mislead?
Yes, if it is labelled as the whole event cost while covering only stationery. The interpretation must match the included inputs.