Employability Skills IV: Spreadsheets for data and calculations

Hello students, welcome to Kwickprep. Your class teacher has marks of forty students in five subjects. Adding them by hand takes hours and mistakes creep in. Can a computer do it in one second? Yes, with a spreadsheet. Today we will learn spreadsheet basics, formulas, functions, sorting, filtering and charts.

First, a new term. A spreadsheet is a program that stores data in rows and columns and calculates with it. LibreOffice Calc is free and open source, which means anyone can use and change it. Microsoft Excel is a popular paid spreadsheet. Google Sheets works online in a web browser.

Let us learn the parts. A workbook is the whole file, and it holds one or more worksheets, which are single sheets. A column runs top to bottom and has a letter name. A row runs left to right and has a number. A cell is the box where a row and a column meet, and its address is column letter then row number. The active cell is the one you have selected right now.

A cell can hold three kinds of data. Text, called a label, is a name or heading and sits on the left. A number, called a value, can be used in calculations and sits on the right. A formula starts with an equals sign and shows its answer in the cell.

Here are the everyday steps. Open a new workbook with File, then New, then Spreadsheet. Save it with Ctrl+S, and LibreOffice Calc saves in the .ods format by default. Use File, Save As, to keep a copy as .xlsx for Excel users. Close the file, and reopen it later with File, then Open.

A formula is an instruction to calculate, and it always begins with an equals sign. The plus sign adds, as in B2 plus C2. The minus sign subtracts. The star sign multiplies, so price times quantity gives the bill amount. The forward slash divides. The caret sign raises a number to a power.

Spreadsheets follow the BODMAS rule you learnt in maths. Brackets are worked out first. Then powers, then multiply and divide, from left to right. Add and subtract come last. So equals two plus three times four gives fourteen, but equals bracket two plus three bracket times four gives twenty.

A function is a ready-made formula with a name. SUM adds all numbers in a range, and a range like B2 colon B6 means every cell from B2 to B6. AVERAGE gives the mean of the range. MAX gives the highest value, like the topper's marks. MIN gives the lowest value. COUNT tells how many cells in the range contain numbers.

Pause and work these out, using marks 40, 70 and 90 in cells A1 to A3. SUM of A1 to A3 gives two hundred. AVERAGE gives two hundred divided by three, which is about sixty six point six seven. MAX gives ninety, the highest. COUNT gives three, because three cells hold numbers.

When you copy a formula to another cell, its cell addresses may change. A relative reference like B2 changes as you copy, so the formula fits each new row. An absolute reference like dollar B dollar 2 never changes, which suits a fixed value like a tax rate. A mixed reference fixes only the column or only the row.

Formatting makes data easy to read. Use bold, font size and colour for headings. Merge and centre a title across the top columns. Format numbers as currency, percentage or with two decimal places. Add borders and fit column width so nothing is hidden.

Big lists become useful when you arrange them. Sorting arranges rows in ascending order, smallest to largest, or descending order, largest to smallest. To sort, select the data and choose Data, then Sort. Filtering hides rows you do not need, like showing only students above eighty. To filter, choose Data, then AutoFilter, and pick a condition from the arrow on the heading.

A chart turns numbers into a picture that is quick to understand. A column chart compares values, like marks of five students. A line chart shows change over time, like monthly electricity bills. A pie chart shows parts of a whole, like how pocket money is spent. To add one, select the data and choose Insert, then Chart.

Let us put it all together in a class marksheet. Start by typing the headings, then names and marks. Next, add a SUM formula for the first student. Copy it down so relative references fit every row. Sort by total in descending order to find the ranks. Finally, insert a column chart to show the result.

Let us recap. A spreadsheet stores data in cells, named by column letter and row number. Every formula starts with an equals sign and follows BODMAS. SUM, AVERAGE, MAX, MIN and COUNT do common work instantly. Dollar signs make an absolute reference that stays fixed when copied. Sort, filter and charts turn raw data into answers.