Spreadsheets: Using Functions, Formulae and Features

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).

Spreadsheet Formulae and Functions

Basic Spreadsheets Functions

  • 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))

Creating Formulas

  • 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).

Advanced Functions

  • 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.

Spreadsheet Features

  • 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.

Error Checking

  • 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!