Topic 10: Spreadsheets

Topic 10: Spreadsheets

1. A spreadsheet application such as Excel can store all its data in a single file (with an .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. A cell reference always starts with a letter and ends with a number e.g. A5 or H28

4. A range of cells is a set of cells in a row or column

5. Data entered into cells can be different data types e.g. text, number, currency, date, percentage etc

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

7. Must start with an =

8. Cells

9. Formulas

10. Formulas perform calculations on cell contents.

11. Formulas appear in the formula bar.

12. The result of a formula is automatically recalculated every time the data on a spreadsheet changes.

13. Below are the 4 main mathematical operations:

    • + Add
      • - Subtract
        • * Multiply
          • / Divide

14. Functions

15. Must start with an =

16. Functions are part of spreadsheet software. They are specialised calculations built in to the spreadsheet software.

17. Function name describes what a function can do SUM, COUNT, AVERAGE, MIN, MAX, IF

18. Formatting

19. The content of a cell can be formatted:

    • Font style, size, colour, border style, alignment, cell colour.

20. Multiple cells can be merged in to one cell

21. Cells are often formatted to reflect the data that is held in them. Data types include:

    • Currency, number, real, integer (whole number), date, text

22. Replicating Cells

23. 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 with a relevant pattern.

24. Bar charts – to compare values

    • Pie charts – to show how a total is divided among a number of different categories.
      • Line graph – display trends

25. Cell Referencing

    • Relative cell references is when a cell is used in a formula and the formula is copied to a new cell reference (address) , the cell references (cell address) 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 is copied to a new cell reference (cell address), the cell reference (cell address) does not change. It is glued in place by the $ character.

26. What If calculations allow you to use a spreadsheet to see what would happen if certain figures changed in a spreadsheet.