Spreadsheets: Using Functions, Formulae and Features

Introduction to Spreadsheets

Spreadsheets like Excel or Google Sheets are digital tools used to organise, manipulate and analyse data.
Spreadheets operate on a grid system, made up of rows (identified by numbers) and columns (identified by letters).
Each box in the grid is called a cell, and can be identified by its unique coordinate - the column letter and the row number (e.g A1).

SUM Function: Calculates the sum of a range of cells. (e.g. =SUM(A1:A10) will add all the numbers in cells from A1 to A10)
AVERAGE function: Calculates the average of a range of cells. (e.g., =AVERAGE(B2:B5))
COUNT function: Counts the number of cells in a range that contain numbers. (e.g., =COUNT(C1:C20))

Formulas are expressions which perform calculations with the data in your spreadsheet.
They always begin with an equal sign (=).
Operands could be numbers, cell references or text.
Operators: Excel uses standard operators for calculations such as ‘+’ for addition, ‘-‘ for subtraction, ‘*’ for multiplication, and ‘/’ for division.
Cell reference: Instead of adding specific numbers in an equation, you can refer to the cells in which the numbers are contain (e.g. =A1+B2).

IF function: Allows you to make logical comparison between a value and what you expect (e.g., =IF(D1>50, "Pass", "Fail")).
VLOOKUP and HLOOKUP function: Used for looking up specific information in your spreadsheet (e.g., =VLOOKUP(A2, B2:E7, 3, False)).
Pivot tables: A tool that allows you to reorganise and consolidate large data sets in a way that’s more manageable and useful.

Formatting cells: Can be used to change the appearance of a spreadsheet (e.g., changing the font, size, colour, alignment).
Sorting and filtering: Spreadsheets can sort data in ascending or descending order, and filter data based on specific conditions.
The ‘freeze panes’ feature is used for locking rows or columns so they remain visible as you scroll through the sheet.
Conditional formatting: Allows you to automatically apply formatting such as colours and icons to cells based on the cell data.
Data validation: Enables the creation of specific rules that dictate what can be entered into a cell.

Spreadsheets identify errors in formulae and functions with a start of the cell reference (e.g., #NAME?, #VALUE!, #DIV/0!).
Understanding these error messages can help identify and resolve issues in the spreadsheet.

Remember, practicing with actual spreadsheets and solving problems will make these concepts easier to understand. Good luck!