Spreadsheet Applications

Spreadsheet

contains worksheets presented as a grid of cells arranged in columns (letters) and rows (numbers)

Use of spreadsheets

perform calculations and recalculations automatically using formulas or functions

Data types

text, number, date, formula, boolean

Formulas

mathematical equation or expression to perform calculations on data within cells

Data formatting

structuring data according to guidelines so it’s easier to analyse, understand and present correctly

How can formatting affect appearance of cells

aligning text, font, borders, background colours

text wrapping

ensure text fits within its cell and does not overflow (multiple lines)

merge and centre

combine one or more cells and align the text in the middle

Conditional formatting

allows cell shading/ font colour to apply to cells if specific condition is met

Template

document preformatted with a layout to serve as a starting point

Importing data

allows data from other applications to be used in spreadsheets

Headers and footers

improves presentation worksheets by adding page numbers, filenames or dates

Fill function

allows data in cells to be easily and quickly replicated down columns or across rows

Functions

used in formulas to perform specific tasks and return a value e.g

Referencing cell ranges

must be contained within brackets and use a colon e.g (A1:A5)

IF statement

examines a condition to see if it’s true or false and returns value

e.g =IF(Evaluate Condition, Do this if true, Do this if false)

Vlookup function

uses value in selected cell to ‘lookup’ match in a table and return corresponding value from the same row

e.g =VLOOKUP(value, range with match, column with return value)

Relative cell reference

will adjust and change formulas when using ‘fill’ so it refers to correct cells

Absolute cell reference

will keep formulas the same when using ‘fill’ so it refers to correct cells

Using absolute cell references

Place a $ before value that will not change

Charts

communicate information as visual representation, easier to understand

Line graph

show a change over time

Pie chart

show individual parts that make up a whole

Bar chart

compare things that aren’t directly related

Scatter graph

look for pattern/ link between two sets of data

Using macros

eliminates need to repeat steps e.g adding a date to a worksheet

Creating macros

you can write code or use a recorder to store each step you take and create a shortcut to play back or repeat commands

Data modelling

controlled by a set of rules defined by formulas which can be changed to predict future outcomes

Use of data modelling

ability to answer ‘what if’ questions so organisations can make better informed decisions e.g how much profit an increase in price will return

Limitations of data modelling

mistake in the rules made or not every situation considered/ other factors

Selecting areas for printing

highlight the data you want to print and choose ‘print selection’