Topic 10-Spreadsheets

Topic 10-Spreadsheets

1. A spreadsheet application such as Excel can store all its data in a single file. This file is called a 'workbook'.

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

2. Cells

   • A cell reference always starts with a letter and ends with a number
   • A range of cells is a set of cells in a row or column
   • Data entered into cells can be different data types e.g. text, number, currency, date, percentage etc.
   • Labels - Any text in a cell describing the data in a row or column
   • Data - any values that are entered for the use of calculation
   • Formulas and functions - mathematical caiculations carried out automatically

3. Formulas

   • Must start with an =
   • Formulas perform calculations on cell contents
   • Formulas appear in the formula bar.
   • The result of a formula is automatically recalculated every time the data on a spreadsheet changes
   • 4 main operators:+, -, *, /.

4. Functions

   • Must also start with an =
   • Functions are part of spreadsheet software. They are specialised calculations built in to the spreadsheet software.
   • Function name describes what a function can do SUM, COUNT, AVERAGE, MIN, MAX, IF

5. Formatting

   • The content of a cell can be formatted
   • Cells are often formatted to reflect the data that is held in them . For example, currency, whole number,etc.

6. Replicating cells

   • When you position the mouse pointer in the bottom right hand corner of the cell and drag down or across the spreadsheet tries to respond intelligently. If there is a formuia 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.

7. 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 this can be created.
   • Bar charts - To compare values
   • Pie charts - to show how total is divided among a number of different categories.
   • Line graph - display trends

8. 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 referencing - is when a cell is used in a formula and the formula is copied to a new cell reference, the cell references changes to take account of the formula's new position
   • Absolute cell referencing - Is where a reference to a cell used in a formula where, when the formula if copied to a new cell reference , the cell reference does not change. It is glued in place by the $ character.

9. 'What if?' calculations

   • Changing the data in a spreadsheet model allows you to make and test predicitions. You may explore what would happen to your profit if VAT changed to 30%