Spreadsheets

1. Spreadsheets Lecture 7 Introduction to Spreadsheets Introduction to MS Excel

2. Spreadsheets • A spreadsheet is a program that can represent data in column-and-row format and can manipulate and present that data in a number of ways • In paper form, spreadsheets have been used by accountants for centuries • An example where a spreadsheet (manual or electronic) might be useful is in keeping record of students’ marks in assignments

3. Spreadsheets

4. Spreadsheets • With a manual spreadsheet: • each calculation (total or average in this case) must be done individually • a change in, for example, C. Murphy’s result for his second assignment means that three values have to be recalculated

5. Spreadsheets

6. Spreadsheets • With an electronic spreadsheet: • the computer can calculate totals and averages for you • a change in C. Murphy’s results for Assignment 2 would cause any dependant values to be automatically recalculated and updated • the spreadsheet can be stored, retrieved, edited and printed (just as a word processor document can be) • data in the spreadsheet can easily be represented graphically (e.g., pie chart, bar chart) • data in the spreadsheet can easily be incorporated into a word processor document

7. Using a spreadsheet • a spreadsheet is divided into boxes, known as cells • each cell is identified by its address, e.g., A1, C4, D3 etc. • there are thousands of available cells - use a scroll bar to move around the spreadsheet

8. Using Spreadsheets • Data entered into a cell can be • text that describes some of the other entries (Student Name, B. Murphy, January) in the spreadsheet (also known as labels) • a number that is keyed in, e.g., all the assignment results in the previous example (also known as raw data) • a formula which instructs the spreadsheet to calculate a value for this cell by using the values in other cells

9. Formulas • Formulas can contain the mathematical operators: • + addition (shift + =) • - subtraction • * multiplication (shift + 8) • / division • Anything in parentheses () is calculated first followed by multiplication & division, then addition and subtraction

10. Formulas

11. Formulas • to get the spreadsheet to calculate the totals in column E, enter formulas as follows: • In E2, = B2 + C2 + D2 • In E3, = B3 + C3 + D3 • In B6, = B2 + B3 + B4 + B5 • In E6, = B6 + C6 + D6 • or • = E2 + E3 + E4 + E5

12. Formulas • The = sign tells the spreadsheet that you are putting a formula into this cell • Once a formula has been entered in a cell, the cell displays the result of the calculation • A formula bar or cell status line displays information about the contents of the active cell, so that you can see (and edit) any formulas you have entered

13. Formulas • A formula can contain a function, e.g., • In E2, = SUM (B2:D2) • In B9, = AVERAGE (B2:B5) • In C10, = MAX (C2:C5) • SUM, AVERAGE and MAX are functions • B2:D2 specifies a range of cells • other examples of cell ranges are • D6 (cell range) • B4:D4 (row range) • D2:D5 (column range) • B2:D5 (block range) • different spreadsheets may use different function names and different symbols between cells when specifying cell range, • e.g., @SUM (B2..D2)

14. Formulas Instead of having to type practically the same formula in E2, E3, E4 and E5, you can enter the formula in E2 and copy it to the other three cells. The spreadsheet understands that the copies are relative to the new location (so the logic of the formula is copied, rather than the formula itself). This is known as relative addressing

15. Formulas • e.g. Suppose the cell F7 contains the formula = D7 + B5 — 2 * A6 • When this formula is copied to F9, it becomes = D9 + B7 — 2 * A8 • When copied to J4, it becomes = H4 + F2 — 2 * E3 • to avoid having the formula adjusted in this way during copying, you must use absolute addressing

16. Formulas

17. Formulas • Absolute addressing is used in Example 3 as the calculations in column C3 and C4 both use the figure in B1 • The formula in C3 is = B3 * $B$1 / 100 • when copied to C4, this becomes = B4 * $B$1 / 100 • and, when copied to C5, it becomes = B5 * $B$1 / 100 and so on

18. Formulas • Spreadsheets provide a number of formatting options to enhance the physical appearance of the data • A spreadsheet can store very large tables of data and can perform complicated mathematical or statistical calculations on that data • A very important feature of a spreadsheet is that it allows "what-if" analysis which enables individuals or businesses to project effects of different courses of action on individual or company goals • Spreadsheets played a large part in the widespread acceptance of computers in the business world during the 80s

19. Excel • Excel has a slightly different nomenclature than other spreadsheets • Excel consists of worksheets and workbooks • A worksheet is made up of little boxes arranged in rows and columns. I.e. it is what others call a spreadsheet • A workbook is a file that stores worksheets • Each workbook has a default of 3 worksheets and a maximum of 256

20. Excel • Cells are still called cells • Active cell: The cell in which the cursor is located • Cell reference: The location of a cell on the worksheet • There is a name box above the top left corner of a worksheet which displays the cell reference of the active cell • Can type a cell reference in the name box to make that cell the active cell • Columns: Always have uppercase letters • When entering column names it doesn’t matter what case you use • Rows: Always have numbers

21. Excel • There are four things that can be entered into a cell: • Number • Text • Cell Reference • Calculation • Excel treats each entry in a different way

22. Excel • When you enter a number into the active cell and hit enter the active cell moves to the cell below and the number right aligns • Excel assumes that you want to perform addition or some other mathematical operation so it: • Right aligns the number as you would when performing addition • Moves to the next call so that you can add more data

23. Excel • You can enter text in a cell to identify the source or meaning of the numbers in the worksheet • This text automatically left aligns • A piece of text in a worksheet cell that provides information about the number in the accompanying cell, usually below it or to the right, is called a Label

24. Excel • You can reference another cell by typing the address of the cell preceded by an = sign • When you do this Excel reproduces the content of the referenced cell • The value in the cell containing the cell reference is now dependant on the referenced cell. i.e. if you change the value of the reference cell the value of the other cell will change too • This is usually a number by can also be text

25. Excel • Excel supports two forms of references • Absolute Reference • A reference to a cell or cell range in the format $A$1. Excel does not adjust an absolute cell reference when you copy a calculation containing such a reference • Relative Reference • A reference to a cell or cell range in the format A1. Excel changes a relative reference when you copy a formula or function containing such a reference

26. Excel • You can enter a formula in a similar manner • Type = • Type the formula you wish to enter • E.g. A1 + (B3 * 2) –D4 • The cells that are the input of the formula are called arguments. • In this case A1, B3 and D4 are arguments • The mathematical symbols are called the operators • This cell will display the results of the equation

27. Excel • If a formula cannot properly calculate a result then it will display an error message in that cell • ##### :Your cell contains a number or calculation result that is too wide to display • #VALUE! :Your formula contains text, or a cell reference that points to text, instead of a number • #DIV/0! : You have tried to divide a number by 0, or by a cell reference that points to a cell containing zero. NOTE! Excel treats blank cells as zero • #REF! : Your formula contains a cell reference to a cell that has been deleted

28. Excel • Excel functions • Excel has a number of built in predefined formulas that can be used for specific purposes • These functions include: • SUM • AVERAGE • MIN • MAX • COUNT • Plus a variety of other functions for statistics, trigonometry etc.

29. Excel • You can access these functions by clicking on the Σ button to reveal a drop down menu containing the functions • E.g. While in the cell you wish to perform the calculation in, click the Σ button and choose SUM • Excel tries to guess what you wish to add together and displays a blue box around it • Highlight the cells you wish to add together by dragging and resizing the blue box • Notice that the corresponding formula is automatically generated in the formula box

30. Excel

31. Excel • Cell Ranges • A cell range is a group of cells on a worksheet • Adjacent cell range • A group of cells that are directly beside, above or below one another. Adjacent cells are sometime called contiguous cells • Can be represented in the form: A1:G10 • Non-Adjacent cell range • A group of cells that are not directly beside , or above or below one another. Also called non-contiguous cells • Can be represented by cell locations separated by commas • E.g. A1, B4, C11

32. Excel • Selecting non adjacent cell ranges • Simply highlight the desired text while holding down the Ctrl button • Selecting rows • You can select an entire row by clicking on the row number • Selecting columns • You can select an entire column by clicking on the column letter • Selecting the entire worksheet • You can select the entire worksheet by clicking the top leftmost tab on the worksheet

33. Excel • Adjusting column width and row height • Move the mouse to the column heading, drag the boundary on the right hand side to the width you want • Follow a similar procedure to adjust row height • Vertical alignment • Select the cell range you wish to use • Choose Format->Cells and select the alignment tab • Select the option you wish to use from the vertical list • Orientation • You can rotate the content of the cells by selecting Format->Cells and selecting the alignment tab and choose how much you wish to rotate the cell contents by • Font sizes & Font colours • You can adjust these in the same manner as Word

34. Excel • Cell Borders • Excel provides a wide choice of borders that you can use to highlight a particular cell or cell range • You can access these by clicking on the Borders button on the Formatting toolbar • You can access a more extensive menu by choosing Format->Cells and clicking Border • Cell backgrounds can be altered as in Word in order to change their colour

35. Excel • Inserting and deleting rows and columns • Highlight a row • Choose Insert->Row • A new row will be inserted above the one you highlighted • Any cells with relative addressing will automatically adjust • Follow the same procedure for columns • Deleting rows and columns • Right click on the column name or row number and choose delete • Alternatively you can highlight a number of rows or columns and delete them in the same manner

36. Excel • Moving Calculations • Numbers and text can be copied and pasted without problems. What about calculations? • Problems will only arise when a formula is pasted and it changes because it uses relative references • This can be ameliorated by using absolute references where appropriate. (See previous example).

37. Excel • Sorting • Rearranging columns of cells based on the values in the cells. Sorting does not change the content of cells, only their location • Sort Order • A particular way of ordering cells based on value. A sort can be alphabetic or numeric, and can be in ascending or descending sequence

38. Excel • Number format • The way in which Excel displays a number on screen and on printouts. Number format affects the appearance and not the value of numbers • You can choose a variety of number formats including currency, decimal etc. along with options to set how many decimal places or trailing zeros should be included etc. • Dates • There are several date formats that Excel recognizes. If you type in one of them and excel recognizes it, it will be right aligned. Otherwise Excel will treat it as text and left align it • To view formats for numbers and dates select Format->Cells and click on the number tab • Text across multiple columns • Text will spread across multiple columns as long as the adjacent columns are empty • Note that the text still belongs to the starting cell