Spreadsheets — Study Notes

1. Introduction

A spreadsheet is an application software used to store, organise and manipulate numeric data in a grid of rows and columns. Microsoft Excel is the most popular spreadsheet program, and other examples include Google Sheets and LibreOffice Calc. A spreadsheet is ideal for creating lists, keeping records, performing calculations, making budgets and drawing charts. A file created in Excel is called a workbook and is saved with the extension .xlsx. The word spreadsheet comes from the paper sheets used by accountants to lay out numbers in columns.

The great advantage of a spreadsheet over a word processor is its ability to perform calculations automatically. When we change a number, the spreadsheet recalculates all the formulas that use that number instantly. This makes spreadsheets extremely powerful for marks lists, bill calculations, savings plans and science experiments. This chapter explains the spreadsheet window, cells, entering data, formulas and functions, cell references, formatting, sorting and filtering, and creating charts.

2. The Spreadsheet Window and Cells

A spreadsheet is made up of a grid. The vertical lines are called columns and are labelled with letters A, B, C and so on. The horizontal lines are called rows and are numbered 1, 2, 3 and so on. A cell is the box where a column and a row meet, and each cell has a unique cell address made of the column letter and row number, such as A1, B5 or C10. The cell in which we click is called the active cell, and its address appears in the name box on the left of the formula bar. The formula bar shows the contents of the active cell.

A worksheet is one grid of cells in the workbook, and the workbook is the whole file, which can contain many worksheets. The tab at the bottom of the window shows the worksheet name, usually Sheet1. A range is a group of cells selected together, written with a colon, such as A1:A10 for a column of cells or B2:D4 for a block.

3. Entering Data

Data is entered by clicking on a cell and typing. There are three types of data we can enter: text (words and labels), numbers (values used in calculations) and formulas (calculations). Text is aligned to the left by default, numbers to the right, and formulas begin with the equals sign (=). To edit a cell, we double-click it or select it and press F2. To delete the contents of a cell, we press the Delete key. To move to the next cell, we press Enter (goes down) or Tab (goes right). We can also fill a series of numbers like 1, 2, 3 automatically using the fill handle.

4. Formulas

A formula is a calculation written in a cell that begins with the equals sign (=). Formulas use arithmetic operators: the plus sign (+) for addition, the minus sign (-) for subtraction, the asterisk () for multiplication, the slash (/) for division and the caret (^) for power. For example, =A1+B1 adds the values in cells A1 and B1, and =C5D5 multiplies them. Formulas can also contain brackets, for example =(A1+B1)C1, where the bracket part is calculated first. A very important rule is that formulas work on cell references* rather than on the numbers themselves, so if the value in a cell changes, the result of the formula changes automatically.

5. Functions

A function is a ready-made formula that performs a common calculation. Functions always begin with an equals sign, followed by the function name and brackets. The most important functions are SUM which adds a range of numbers, for example =SUM(A1:A10); AVERAGE which finds the mean, for example =AVERAGE(B1:B10); MAX which finds the largest value; MIN which finds the smallest value; and COUNT which counts the number of cells containing numbers. Functions save time because one function can replace a long formula. For example, =SUM(A1:A5) is the same as =A1+A2+A3+A4+A5.

6. Cell References

A cell reference is the address of a cell used in a formula. There are three types of cell references. A relative reference (like A1) changes when the formula is copied to another cell. An absolute reference (like $A$1) does not change when the formula is copied; the dollar signs lock the column and row. A mixed reference locks only one part, such as $A1 (column locked) or A$1 (row locked). Absolute references are useful when a formula must always refer to a fixed cell, such as a tax rate stored in one cell.

7. Formatting a Worksheet

Formatting makes the data clear and attractive. We can change the font, font size, bold, italic and colour of text, and the background colour of cells. Formatting numbers shows them with currency symbols, decimal places or percentages. We can adjust the column width and row height by dragging the borders. We can merge cells to make a heading span across several columns, and wrap text so that long text fits inside a cell. Borders and colours can be added to make the sheet look neat.

8. Sorting, Filtering and Charts

Sorting arranges data in ascending or descending order, for example arranging marks from highest to lowest. Filtering shows only the rows that match a condition, hiding the rest. A chart (or graph) is a visual representation of data, and it makes trends easy to see. Excel can create many chart types: a column chart compares categories with vertical bars, a bar chart uses horizontal bars, a line chart shows trends over time, and a pie chart shows parts of a whole. To create a chart, we select the data and choose Insert then Chart, then select the chart type.

Quick Revision Tables

Table 1: Arithmetic Operators in Excel

Table 2: Common Functions

Mind Map

Important Diagrams (SVG)

Diagram 1: Parts of a Spreadsheet

Diagram 2: Types of Cell References

Common Mistakes

1. Writing a formula without the equals sign. Every formula must begin with =.
2. Using the multiplication sign as x. In Excel, the asterisk (*) is used for multiplication.
3. Saying a workbook is one grid of cells. A worksheet is one grid; a workbook is the file holding worksheets.
4. Forgetting that text aligns left and numbers align right by default.
5. Writing the cell address as row then column. The correct order is column letter first, then row number, like B3.
6. Believing SUM(A1:A5) and A1+A2+A3+A4+A5 are different; both give the same answer, but SUM is shorter.
7. Using A1 rather than $A$1 when the reference must not change when copied.
8. Calling the chart that shows parts of a whole a line chart. That is a pie chart.
9. Thinking that functions do not begin with an equals sign; they do.

Exam Tips

1. Learn the operators table: +, -, *, / and ^ with one example each.
2. Memorise at least five functions: SUM, AVERAGE, MAX, MIN and COUNT, with their syntax.
3. Be able to explain the difference between a worksheet and a workbook in one sentence.
4. Know the three types of cell references: relative (A1), absolute ($A$1) and mixed ($A1).
5. Draw the spreadsheet window and label the name box, formula bar, columns, rows and worksheet tab.
6. Give the extension .xlsx for Excel files in file-type questions.
7. Remember that a pie chart shows parts of a whole, a line chart shows trends, and a column chart compares values.

Conclusion

A spreadsheet is a powerful tool for storing, organising and calculating numeric data. It is built from columns and rows, and each cell has a unique address. Formulas beginning with an equals sign perform calculations on cell references, while functions like SUM and AVERAGE provide ready-made shortcuts. Formatting, sorting, filtering and charts help present the data clearly. Spreadsheets are used in marks lists, budgets and scientific records, and they form the basis of data handling. In the next chapter we move from numeric work to visual presentation with presentation software.