Spreadsheets: Formulas and Charts

A spreadsheet is application software that stores data in a grid of rows and columns and can perform calculations on that data automatically. The file you create and save is called a workbook. Inside a workbook are one or more worksheets (also called sheets), and a worksheet is the grid you actually type into. This relationship is asked often, so keep the wording exact: the workbook is the file, a worksheet is a page inside that file.

Each worksheet is divided into columns and rows. Columns run vertically and are labelled with letters — A, B, C and so on, continuing to AA, AB after Z. Rows run horizontally and are numbered 1, 2, 3 upwards. The rectangle formed where one column crosses one row is a cell, and the cell is the smallest unit of a worksheet; it is where data is actually stored. The exact number of rows and columns available depends on the program and its version, so do not quote a fixed figure in an answer unless your textbook gives one.

Every cell has a unique cell address, also called a cell reference. It is written as the column letter followed by the row number: C5 means column C, row 5. The order matters — column first, then row — so 5C is wrong. The cell currently selected is the active cell, and a thick border called the cell pointer marks it. Its address appears in the Name Box at the top-left of the window and its contents appear in the Formula Bar. Remember this distinction, because it is examined: if a cell holds a formula, the cell displays the result while the Formula Bar displays the formula itself.

A cell range is a rectangular block of cells treated as a single group. It is written as the address of the top-left cell, a colon, then the address of the bottom-right cell. B2:B10 is nine cells down one column, A1:E1 is five cells across one row, and A1:C4 is a block of twelve cells (three columns multiplied by four rows). Ranges are what make functions short: instead of naming twelve cells one by one, you name the block once.

Data entered into a cell falls into three broad types. Text, also called a label, is any entry containing letters or symbols — a student name, a subject heading, a city. Numbers, also called values, are entries you can calculate with — marks, quantities, an amount in rupees. Dates and times are a special case: the program stores them as numbers internally and displays them in a date format, which is why one date can be subtracted from another to get the number of days. The third type is a formula, an instruction that produces a value.

Most spreadsheet programs give you a free clue about what they stored. By default, text is aligned to the left of the cell and numbers to the right. So if the roll numbers you typed sit on the left, the program has treated them as text, and functions such as SUM will simply ignore them. Currency symbols, thousands separators, percentage signs and decimal places are matters of formatting only. Formatting changes how a value is displayed, never the value stored underneath — a favourite examiner trap, and a sentence worth writing in full when the question asks about number formatting.

Any calculation you type into a cell must begin with an equals sign, =. The = tells the program that the rest of the entry is an instruction to calculate, not text to display. Type B2+C2 without it and the cell simply shows the characters B2+C2 as a label. Some programs also accept a leading + and convert it into an equals sign, but always write =.

A formula is an expression written by the user that performs a calculation on values, cell references or both. The arithmetic operators are + for addition, - for subtraction, * for multiplication, / for division, ^ for exponentiation (power) and % for percentage. The comparison operators =, <, >, <=, >= and <> (not equal to) are used inside conditions, and in most programs & joins two pieces of text into one.

Order of evaluation follows ordinary mathematical precedence: brackets first, then percentage, then exponentiation, then multiplication and division from left to right, then addition and subtraction from left to right. So =2+3*4 gives 14, not 20. If the addition must happen first, write =(2+3)*4, which gives 20.

The single most important habit is to use cell references instead of typed constants. =450*5 is a dead calculation that must be retyped whenever a figure changes; =B2*C2 recalculates by itself the moment either cell changes. This automatic recalculation is the main reason spreadsheets are used for fee registers, stock lists and mark sheets, and it is the standard first point in any answer about the advantages of a spreadsheet.

A function is a ready-made, pre-defined formula built into the software and identified by a name. Its general syntax is the function name followed by round brackets containing the arguments — the values, cell references or ranges the function is to work on:

=FUNCTION_NAME(argument1, argument2, ...)

Arguments are separated by a comma or a semicolon depending on the program and its regional settings, so follow whichever your textbook uses and stay consistent. A function that needs no argument, such as =TODAY(), still needs the empty pair of brackets.

The distinction the examiner wants is this. A formula is written by the user and may be of any length or shape; a function is supplied by the software, has a fixed name and a fixed syntax, and is shorter and less error-prone for long jobs. Both begin with =, and a function is always used inside a formula — which is why =SUM(B2:E2) is correctly described as a formula that uses the SUM function. Compare =B2+C2+D2+E2 with =SUM(B2:E2): the answer is identical, but the second is shorter, easier to check for mistakes, and does not have to be rewritten cell by cell if the block of data grows.

Formulas and functions mix freely. =SUM(B2:E2)/4 and =(SUM(B2:E2)/400)*100 are both formulas containing a function together with ordinary arithmetic. One more rule to state in answers: a formula must never refer to the cell it is written in. Typing =A1+5 into cell A1 creates a circular reference, and the program warns you instead of producing an answer.

Finally, note how a formula looks from outside: click the cell and the Formula Bar shows =SUM(B2:E2) while the cell itself shows something like 342. Most programs also offer an option that displays formulas in place of results across the sheet, the fastest way to check somebody else's work.

These seven functions are the working core of the chapter. Learn the syntax exactly, because a missing bracket or a missing pair of quotation marks costs the mark. Take this small mark sheet as the working example.

     A          B         C        D         E
1  Name      English   Maths   Science   Total
2  Ananya       78       92       85
3  Rehan        65       54       71
4  Divya        88       95       90

SUM adds all the numbers in the range or list given to it. In E2, =SUM(B2:D2) gives Ananya's total, 255. Any text or empty cell inside the range is ignored rather than causing an error.

AVERAGE returns the arithmetic mean. =AVERAGE(B2:D2) gives 85. One detail is easy to miss: AVERAGE divides by the number of cells that actually contain numbers, not by the size of the range. A blank cell is skipped entirely, but a cell containing 0 is counted — and the two give different answers.

MAX and MIN return the largest and the smallest number in a range. For the English column, =MAX(B2:B4) gives 88 and =MIN(B2:B4) gives 65.

COUNT and COUNTA are the classic comparison question. COUNT counts only those cells in a range that contain numbers. COUNTA counts every cell that is not empty, whatever it holds — numbers, text, dates or even error values. In the names column, =COUNT(A2:A4) returns 0 because names are text, while =COUNTA(A2:A4) returns 3. Use COUNT to find how many students actually have marks entered for a subject, and COUNTA to find how many rows of the register have been filled in at all. Truly blank cells are counted by neither.

IF makes a decision. Its syntax is =IF(condition, value_if_true, value_if_false). The condition is a comparison that must be either true or false. Text results must be enclosed in double quotation marks; numbers must not be. To mark a pass at 33:

=IF(B2>=33,"Pass","Fail")

IF statements can be nested — one IF placed inside the value_if_false slot of another — to award grades. Always test the conditions from the highest downwards, or a lower grade will be awarded first and the rest never checked:

=IF(E2>=90,"A",IF(E2>=75,"B",IF(E2>=60,"C","D")))

Read it as: if the mark is 90 or more give A; otherwise if it is 75 or more give B; otherwise if it is 60 or more give C; otherwise give D. Count the closing brackets — three IFs need three of them.

The same functions work just as well on money. If a shop's daily sales in rupees are in B2:B31, then =SUM(B2:B31) is the month's takings, =AVERAGE(B2:B31) the daily average, =MAX(B2:B31) the best day's sale, and =IF(SUM(B2:B31)>=100000,"Target met","Short") checks the monthly target. Note carefully that the rupee symbol comes from currency formatting; you never type it into the cell as part of the number, because the entry would then be text and every numeric function would ignore it.

Two habits are worth building. Write a range wherever a range applies instead of listing cells one by one. And remember what each function ignores: SUM, AVERAGE, MAX and MIN all skip text and blank cells, so a stray heading inside a numeric range does not produce an error — it quietly changes the count, which is far harder to notice.

A formula is rarely typed twice: you write it once and fill it down the column. Whether the copies come out right is decided by the kind of cell reference used, and there are three kinds to tell apart.

A relative reference is a plain address such as B2. The program does not really store the text B2; it stores the position of B2 relative to the cell the formula sits in. So when the formula is copied, the reference adjusts by the same offset. If =B2*C2 in D2 is copied down to D3 it becomes =B3*C3, and copied one column right to E2 it becomes =C2*D2. This is the default behaviour, and it is usually exactly what you want: write the formula once, fill it down the whole column, and every row calculates its own values.

An absolute reference is locked with the dollar sign: $B$2. The $ before the column letter fixes the column and the $ before the row number fixes the row, so the reference does not change no matter where the formula is copied. The simplest way to remember it is to read $ as this part must not move.

A mixed reference locks only one part. $B2 keeps the column fixed while the row adjusts; B$2 keeps the row fixed while the column adjusts. Many programs let you cycle through the four combinations with a function key (F4 in several of them) while the formula is being edited.

Now the reason it matters. Suppose a tax rate of 0.18 is stored once in cell E1, and item prices are in B2:B11. In C2 you write =B2*E1 and copy it down the column. In C3 the relative reference E1 slides to E2, which is empty, so the tax shows as 0; in C4 it becomes E3, and so on. Every row below the first is wrong, and nothing on screen looks broken. The fix is a single pair of dollar signs: =B2*$E$1. Now B2 still adjusts to B3, B4, B5 as it should, while $E$1 stays pinned to the rate cell.

The same logic applies to any shared value — a percentage of total marks, a conversion rate, a fixed delivery charge, a discount rate. Store the shared value in one cell, refer to it absolutely, and changing that one cell updates the entire column at once.

How to decide in an exam: for each reference in the formula, ask when this formula is copied, should this address move with it? If yes, leave it relative. If no, put dollar signs on it. A complete answer states the rule, gives both forms, and shows one worked copied example.

Two related points. Copying a formula copies the logic, so references adjust; moving a formula by cut and paste usually leaves the references unchanged in most programs, because you are relocating the same calculation rather than repeating it. And a reference can point at another worksheet, written as the sheet name, an exclamation mark and the address — for example Sheet2!A1 — which is how a summary sheet pulls totals out of twelve monthly sheets.

Ranges can be made absolute too. =SUM($B$2:$B$11) keeps the whole block pinned. If you are copying a percentage-of-total or a running-comparison formula across several columns, this is exactly what stops the range from sliding away from your data.

Sorting arranges the rows of a list into order based on the values in one or more columns. Ascending order means smallest to largest for numbers, A to Z for text and earliest to latest for dates; descending order is the reverse. The column you order by is called the sort key. Most programs allow several keys at once: sort by Class first, and where two students are in the same class, sort by Marks in descending order — the second key breaks the tie left by the first.

The single biggest mistake in a practical examination is sorting only one column. If you select just the Marks column and sort it, the marks are rearranged while the names stay where they were, and every record in the sheet is now wrong. Always select the whole data block including all related columns, or accept the program's offer to expand the selection. Also tell the program whether your list has a header row, so that the headings are not sorted in among the data.

Filtering displays only those rows that satisfy a stated condition and temporarily hides the rest. Filtering does not delete, reorder or alter any data — clearing the filter brings every row straight back, and that sentence alone often carries a mark. In most programs an AutoFilter option places a small drop-down arrow on each heading; from it you can tick the values you want to see, or set a custom condition such as Marks greater than 75, City equals Chennai, or a range such as between 40 and 60. Conditions can be combined with AND, where both must be true, and OR, where either may be true.

Sorting and filtering answer different questions, and that comparison is the standard examination item. Sorting rearranges all the records into an order; filtering keeps the existing order but shows only a subset. Use sorting to produce a merit list; use filtering to look at only the students who need a re-test.

Conditional formatting applies a chosen format — a fill colour, a font colour, bold text, a data bar — to a cell only when a stated condition is true, and removes that format automatically when the condition stops being true. The format is therefore not fixed to the cell; it depends on the cell's current value. Typical uses in a school or shop sheet: colour every mark below 33 in red, highlight stock quantities that have fallen below the reorder level, shade the three highest sales figures of the month, or flag any fee entry above 10,000 rupees.

Two things make it powerful, and both belong in a full answer. It is dynamic: edit the underlying number and the highlight appears or disappears immediately, with no further work by the user. And it is a visual summary that draws attention to exceptions in a large sheet, so the reader does not have to scan every value. Note the limits too — conditional formatting changes appearance only. It never changes the stored value, and it has no effect on what SUM, AVERAGE or COUNT calculate.

In most programs the rule is built the same way: select the range, open the conditional formatting option, choose the type of condition (greater than, less than, between, equal to, or text containing), enter the value or cell reference to compare against, and pick the format. The exact menu names differ between programs and versions, so describe the steps in general terms rather than quoting one product's menu path.

A chart, or graph, is a pictorial representation of worksheet data. Its purpose is comparison and trend-spotting: a reader sees at a glance what a table of forty numbers would take a minute to work out. Charts in a spreadsheet are linked to the cells they were built from, so editing the data updates the chart automatically — a point worth stating whenever the question asks for the advantages of charts.

Learn the parts by name. The data series is a set of related values being plotted, for example one student's marks across all subjects. The category axis, or X-axis, usually carries the labels — subjects, months, cities. The value axis, or Y-axis, carries the numbers and the scale. The legend is the key that shows which colour represents which series. Data labels print the actual value on each bar or slice; the chart title and axis titles state what is being shown; gridlines are the faint lines that help the eye read a value off the scale. A chart placed on the same sheet as its data is an embedded chart; one on a sheet of its own is called a chart sheet in programs that offer that option.

Choosing the right type is what the paper actually asks, so learn the types as matched pairs of chart and purpose.

A column chart uses vertical bars to compare values across separate categories — marks of five students, sales of four branches. A bar chart is the same thing drawn with horizontal bars, and it is preferred when the category names are long. A line chart joins points in sequence and is the correct choice for showing a trend over time — monthly rainfall, daily temperature, a shop's sales across twelve months. A pie chart shows how a single whole is divided into parts, each slice being a share of the total: how a monthly pocket money of 2,000 rupees is split between travel, food and books. An XY (scatter) chart plots pairs of numeric values against each other to show whether a relationship exists, such as hours studied against marks scored. An area chart is a line chart with the space beneath it filled in, used when the volume beneath the trend also matters.

The rules that decide the answer are few and firm. Use a pie chart only for a single data series whose parts add up to a meaningful whole, and only when there are a few categories — a pie of fifteen slices cannot be read. Never use a pie chart to compare two months' figures; that is a column chart. Use a line chart when the X-axis is time and the order of the points carries meaning. Use a column or bar chart when the categories are separate things with no natural order between them. Use a scatter chart only when both axes are numeric.

Creating a chart follows the same broad steps in most programs: select the data range including the row and column headings, choose the chart type, check whether the series should be read from rows or from columns, add the chart title, axis titles and legend, then place the chart. Including the headings usually lets the program label the axes and build the legend for you.

One caution for written answers: a chart shows relationships, not precise values. If the exact figures matter — a fee statement, a mark sheet to be signed — keep the table as well, or switch on data labels so the numbers appear on the chart itself.

A spreadsheet never leaves a broken calculation looking finished. Instead of a result it writes a short error value into the cell, and each one begins with a hash sign. Naming the cause of each is straightforward marks. The exact set of error values and their wording varies a little between programs and versions, so describe the cause rather than insisting on one product's spelling.

#DIV/0! means division by zero. It appears when a formula divides by a cell that contains 0, or by an empty cell — an empty cell is treated as zero, which is why this error usually appears before the data has been filled in. If B2 holds the total marks and C2, the number of subjects, is still blank, then =B2/C2 shows #DIV/0!. The cure is to guard the division with IF:

=IF(C2=0,"",B2/C2)

That leaves the cell looking blank until a real value arrives. If you would rather print a note, use =IF(C2=0,"NA",B2/C2). Mathematically the reason is simple and worth one line in an answer: division by zero is undefined, so the program has no value to display.

#VALUE! means the wrong type of data has been used — arithmetic attempted on text, for example =B2*C2 where C2 contains the word absent. #NAME? means the program does not recognise a name in the formula: a misspelt function such as =SUMM(B2:B6), or text typed without quotation marks. #REF! means an invalid reference — the row, column or sheet the formula pointed at has been deleted. #NUM! indicates a number that is invalid for the operation, such as the square root of a negative value.

One display looks like an error but is not. A cell filled with ##### simply means the column is too narrow to display the number or date it holds; widen the column and the value reappears unchanged. Say this explicitly if the question asks, because the marking point is that no data has been lost.

A circular reference is the other classic fault: a formula that refers, directly or through a chain of other formulas, back to its own cell. Writing =SUM(A1:A5) in cell A5 is circular, and the program warns you rather than calculating.

Good practice prevents most of these. Keep one value in one place and refer to it, instead of retyping it into every row. Never type units, commas or a currency symbol into a numeric cell — apply formatting instead, or the entry becomes text and every numeric function will ignore it. After copying a formula, click one of the copies and check in the Formula Bar that it still points where you intended. Before deleting a row or a column, check whether other formulas refer to it. And when a sheet is going to be used by somebody else, guard your divisions with IF so that unfilled rows look tidy rather than alarming.

Finally, a short checklist for tracking down a mistake. Click the cell and read the Formula Bar rather than the display. Check every reference in the formula one at a time. Check that the brackets pair up and that the separators between arguments are correct for your program. Check that the cells being calculated with really contain numbers — the left or right alignment tells you at a glance. Most programs also provide a tool that traces which cells a formula depends on.