Spreadsheets

Spreadsheets

1. A spreadsheet application such as Excel can store all its data in a single file (with a .xls extension). This file is called a workbook

2. A workbook is made up of one or more 'worksheets'. A worksheet is a single tab in the spreadsheet workbook

3. Cells

   • A cell reference always starts with a letter and ends with a number e.g A5 or Z9
   • A range of cells is a set of cells in a row or a column
   • Data entered into cells can be different data types e.g text, number, currency, date, percentage etc.
   • Each cell can contain:
     • Labels- any text in a cell describing the data in a row or column (titles, headings that identify data)
     • Data-any values (text or numbers) that are entered for the use of calculation
     • Formulas and functions-mathematical calculations carried out automatically

4. Formulas

   • Must start with =
   • Formulas appear in the formula bar
   • The rest of a formula is automatically recalculated every time the data on a spreadsheet changes
   • Mathematical operations
     • + ADD
     • - SUBTRACT
     • * MULTIPLY
     • /DIVIDE
   • E.g =(B3*C6)+E12

5. Functions

   • Must also start with an =
   • Functions are part of spreadsheet software. They are specialized calculations built into the spreadsheet software
   • Function name describes what a function can do SUM, COUNT, AVERAGE, MIN, MAX, IF
   • e.g =SUM(D2:F7)

6. Formatting

   • The content of cells can be formatted:
     • Font style, size, colour, border style, alignment, cell colour
     • Multiple cells can be merged into one cell
   • Cells are often formatted to reflect the data inside of them. Data types include:
     • Currency, number, real, integer (whole number), date, text

7. Replicating cells

   • When you position the mouse in the bottom right hand corner of the cell and drag down or across, the spreadsheet tries to respond intelligently. If there is a formula in the cell, the spreadsheet tries to adjust it as it goes down the page to fit in with the new position and the cells being referred to

8. Graphs and charts

   • Using charts/graphs allows us to notice patterns & trends more easily
   • Specific data can be selected on a spreadsheet and a chart/graph of the data can be created
   • Bar charts-to compare values
   • Pie charts-to show how a total is divided among a number of different categories
   • line graph - display trends

9. Cell referencing

   • When a formula is copied by 'filling down' the cell references in cells are adjusted as you drag the pointer down the page
   • Relative cell reference :
     • If a formula is copied by filling down, the cell references are adjusted as you drag the replication pointer down the page. This is because it uses the relative address of the cells based on their position in the worksheet
   • absolute cell references:
     • Some we need to use part of a formula that does not change. For example if VAT was recorded in B2 as 20% then the B2 part of the formula must not change because the rate will be 20% for everything. All the other cells will change. When the formula is copied down, the relative address changes but the absolute doesn't