Spreadsheets for data

When Sudhakar wants the total marks of every student in his class , across six subjects , he could add the columns by hand, but it would take hours. A spreadsheet can do all that arithmetic in less than a second. You type one formula, drag it down, and the answers appear. Modern data-handling almost always uses spreadsheets, so it is worth learning the basics now.

Concept

A spreadsheet is a digital grid of cells. Each cell is named by a letter (the column) and a number (the row). For instance, $A1$ is the top-left cell, $B3$ is the cell in column $B$, row $3$.

You can type three kinds of things into a cell:

1. Text , like the heading "Maths".
2. A number , like $47$.
3. A formula , starts with an = sign, and the cell shows the computed result.

Cell ranges. Several cells in a row, column, or rectangle can be described by their first and last names, separated by a colon. Examples:

• B3:G3 , all cells from $B3$ across to $G3$ (one row).
• D2:D6 , all cells in column $D$, rows $2$ to $6$ (one column).
• B2:E5 , a rectangle of cells.

Key formulae.

If any of the source values changes, the formula updates automatically. This is the spreadsheet's superpower: the data stays linked to the calculations.

Filling formulae down. If you write =SUM(B3:G3) in cell $H3$, you can drag the small handle at the corner of the cell downward, and the spreadsheet will produce =SUM(B4:G4) in $H4$, =SUM(B5:G5) in $H5$, and so on. This is how a single formula computes totals for an entire class in seconds.

Common pitfalls.

• Forgetting the = sign , the cell just shows the text "SUM(B3:G3)" instead of computing.
• Using a wrong range , e.g., B3:G2 (which spans nothing).
• Including text cells inside an AVERAGE range , text is ignored, but check your range still has only the cells you want.

You can use any spreadsheet software , Google Sheets, Microsoft Excel, LibreOffice Calc , they all share the same formulae for SUM, AVERAGE, MAX, MIN, and COUNT.

Worked examples

Example 1. A class's marks in Maths are in cells $E2:E8$. What formula gives the class's mean Maths mark?

• =AVERAGE(E2:E8).

Example 2. Cells $B3:G3$ contain Nagesh's marks in six subjects. What formula gives his total?

• =SUM(B3:G3). To get the total in $H3$ for the whole class, type that in $H3$ and drag down to $H8$.

Example 3. What cell contains the marks for Farooq in Mathematics, given Mathematics is column $E$ and Farooq is in row $5$?

• Cell $E5$.

Example 4. Subjects (column headings) sit in row $1$. Six students sit in rows $2$ to $7$, with their scores in columns $B$ to $G$. Write formulae to display:

• (i) the average Science mark.

• (ii) the highest English mark.

• (iii) the total marks of the third student.

• (i) =AVERAGE(G2:G7) (if Science is column $G$).

• (ii) =MAX(D2:D7) (if English is column $D$).

• (iii) =SUM(B4:G4).

Try it yourself

1. The marks in Hindi are in cells $C2:C25$. Write a formula for the class average in Hindi.
2. Write a formula for the highest mark in the same column.
3. Marks of a student named Ratna are in $B2:G2$. Write the formula for Ratna's total.
4. What does the formula =SUM(B2:B10)+SUM(C2:C10) compute?
5. If =AVERAGE(B2:B6) gives $30$, what is the sum of $B2$ to $B6$?
6. What cell name refers to column $H$, row $4$?
7. True or false: changing the value in $B5$ will automatically update any formula that uses cell $B5$.
8. If a column has the values $25, 39, 29, 47, 33, 27, 16$, type the formula for their mean , and then compute the mean yourself to verify.

Activity

Class data on a spreadsheet. With your teacher's help, open a spreadsheet program on a computer. Enter the names of $5$ classmates in column $A$, and their scores in three subjects across columns $B, C, D$. In column $E$, type =SUM(B2:D2) for the first student's total and drag down. In row 8, type =AVERAGE(B2:B6) for the class mean in subject 1, and drag right to get all three subject averages. Now change one mark and watch the totals and averages update on their own , that's the spreadsheet at work.