Computer Science 1000

1. Computer Science 1000 Spreadsheets I Permission to redistribute these slides is strictly prohibited without permission

2. Computers and Data • suppose we ask a computer to remember a retail transaction • a list of 5 items • the cost of each item • the subtotal • the tax associated • the final total • how does the computer do this? Socks $ 4.99 Pants $ 65.99 Shirt $ 47.99 Tweed Suit Jacket $119.99 Driving Gloves $ 28.99 Subtotal $267.95 Tax $ 13.40 Total $281.35

3. Computers and Data • computer memory (RAM) – a simplified version • your computer memory is like a massive set of storage “bins” • think of a post office with 8 billion mailboxes • each bin stores a byte of information • each bin has a label • called its address • each running program is given a subset of those bins, called its address space

4. Computers and Data • when your program wants to store data • it chooses a memory location that’s: • a) large enough • e.g. if it’s storing a piece of text that is 20 characters, it must find a 20 byte piece of memory • b) currently unused • we don’t want to overwrite existing program information • the data is copied to that location in memory • the address of that memory is remembered • this is accomplished using a variable, in programming language speak Computer Memory K name E V

5. Computers and Data • when your program wants to access data that it has stored • it locates the appropriate spot in memory using its remembered address • the data can be copied from that spot • perhaps to be used in a computation, or sent to an I/O device Computer Memory K name E V

6. Computers and Data • some points to consider: • the layout of the data in memory is usually not important to the program • as long as it can access the data it needs (by its memory address), that’s fine • from our previous example, perhaps the transaction items get stored at various locations in a program’s memory • the format of the data in memory is usually not important to the program • suppose the price of an item was $4,356.90 • the computer will simply store this (in binary form) as the floating-point number 4356.9

7. Computers and Data • while layout and format might not be important to the computer, those details are important to the user • layout issues: • proximity creates cohesion – like items can be placed together, while unlike items can be distanced from each other – facilitating categorization (and hence, faster searching) • certain alignment of text facilitates easier reading • aligning decimal places in numbers facilitates easy number comparison • format issues: • certain formatting (currency: commas, dollar signs, two decimal places) are familiar to us • text formatting (bold, italic) allow us to highlight critical information (headers, keywords, etc)

8. Computers and Data • to summarize: • when dealing with information, layout and formatting of data can facilitate some key advantages for users, even if that information is not necessary for the data to be stored • there are a number of applicationprograms designed to present information in a meaningful way to a person • browsers (markup languages – next week) • spreadsheets (today!)

9. Spreadsheet • a two-dimensional grid of data, that allows relationships between items of data • each grid location is called a cell • each horizontal line of cells is called a row • each vertical line of cells is called a column • hence, each cell has a unique row/column identification that differentiates it from the other cells

10. Example (Excel™) Cell

11. Example (Excel™) Row

12. Example (Excel™) Column

13. Spreadsheet – Software • Microsoft Excel • the most widely used office software • part of the Office software suite • OpenOffice Calc • a free spreadsheet alternative • part of the OpenOffice suite • LibreOffice Calc • another free alternative • part of the LibreOffice suite • many similarities (cell addressing, numbered rows, lettered columns) • some differences (e.g. ; vs , ) • for this course, we will use Microsoft Excel, as it is available in your computer lab

14. Spreadsheet – Row Labeling • each row in a spreadsheet has a label • for most spreadsheet programs, the label is a number • first row labeled with 1 • last row depends on the application and its version • e.g. my Excel 2010 goes up to row 1048576 • rows are often stated as “Row” plus their label • e.g. one would refer to the highlighted row below as “Row 4”

15. Spreadsheet – Column Labeling • each column in a spreadsheet has a label • for most spreadsheet programs, the label is a letter • first column labeled with A • columns are stated as “Column” plus their label • e.g. one would refer to the highlighted row below as “Column E”

16. Spreadsheet – Column Labeling • what happens when we run out of letters? • e.g. what is the 27th column called? • the letters roll over, much like digits do • first 26 columns: A – Z • next 26 columns: AA-AZ • next 26 columns: BA-BZ

17. Spreadsheet – Cell Labeling • a cell typically belongs to one row and one column • this ignores merged cells, but we’ll ignore that for now • a cell’s label is the combination of its column and its row • the cell highlighted in black is A1 • the cell highlighted in red is C3

18. Cell Data • at it’s simplest level, a spreadsheet simply stores data • each cell holds a value • to insert data into a spreadsheet • click on a cell (this will highlight it) • type the data you want to store • press Enter, or select another cell

19. Cell Data • what types of data can a spreadsheet hold? • numbers • text • dates/times • each of these can be formatted in a particular way • e.g. numbers can be displayed as currency ($, two decimal places) or a percentage • dates can be displayed in different formats

20. Spreadsheet – Example • insert the data from our retail transaction into the spreadsheet • items in Column A • prices in Column B (as a simple number) • solution: • highlight Cell A1, type Socks, press Enter • highlight Cell B1, type 4.99, press Enter • repeat for next seven lines Socks $ 4.99 Pants $ 65.99 Shirt $ 47.99 Tweed Suit Jacket $119.99 Driving Gloves $ 28.99 Subtotal $267.95 Tax $ 13.40 Total $281.35

22. Spreadsheets – Cell Dimensions • in our last example, Tweed Suit Jacket and Driving Gloves do not fit in their cells • hence, part of the item name is hidden behind its price • we need to make the column wider • to do this: • manually: place your cursor at right side of column label, and drag • automatically: select your column (click on column label), and choose Autofit Column Width from the Format dropdown

23. Manual Autofit

25. Spreadsheets – Cell Dimensions • note that row dimensions can also be controlled, in the same way as column dimensions • for example, suppose we wanted to provide a bit of separation between the SubTotal line and the items of purchase • manual: place mouse between row labels and drag • autofit: same menu as before, but select Autofit Row Height • note that the automatic solution won’t work for this example, as it won’t increase the height of the cell

27. Cell Formatting • as mentioned, a formatting can be applied to a cell • note that this does not affect the value being stored, just the value that is displayed • there are many possibilities for formatting • we will consider a few here • text • numbers • some others will be considered in your lab • the best method for learning: try them out

28. Cell Formatting - Text • formatting text in cells is much like formatting in a word processor • can control font, colour, background, alignment, word wrap, etc • process: • select cells you wish to format • choose the appropriate control from the Ribbon • example: italicize the Subtotal label and amount, and bold the Total and amount

31. Cell Ranges • the previous works, but is a bit inconvenient • select A6, italicize • select B6, italicize • what if Row 6 contained 25 cells that we wished to apply this to? • Solution: Cell Range • a collection of cells, highlighted at once • any formatting or editing (e.g. delete) will be applied across all cells in a range

32. Cell Ranges • to select a range: • drag your cursor from the top-left cell of your desired range to the bottom-right • e.g. to select all cells from D2 to F7

33. Cell Ranges • if a range is rectangular, it is denoted as topLeft : bottomRight • in previous example, selected range was D2:F7 • note that multiple rectangular ranges can be selected at once • denoted as range1, range 2 • e.g. D2:E6, G2:H5 • to select multiple ranges, hold down Ctrl button

34. Cell Ranges • from previous example • we need to boldface the bottom two cells • let’s use a Range to do this • select the range using drag method • click the bold (B) button in the Font group

35. Cell Formatting – Numeric • spreadsheets offer different formatting options for numbers • number of decimal places • types (currency, %) • and others • many of these options found in the Number group of the Home ribbon • if no formatting is specifically applied to the cell, then a general formatting is assumed • no trailing zeroes • no commas or symbols

36. Cell Formatting – Numeric • from previous example, numbers should be formatted as a currency • select the numbers (as a range) • from the drop-down menu in the Number group of the Home ribbon, select Currency

37. Cell Formatting – Numeric • many other options for numeric formatting not shown in Ribbon • to see these, click the symbol beside the Number label in the ribbon • this dialog box gives detailed control over how a number is displayed

38. Cells – Stored Value vs. Displayed Value • recall previous comment that formatting and layout doesn’t affect the value being stored • we can see the value being stored by examining the formula bar when a cell is highlighted • the formula bar (labeled fx) is positioned directly above the sheet • as the following slide shows: • text values are stored as is, with no bold/italic etc … • numeric values are stored without any formatting Formula Bar

40. Cells – Stored Value vs. Displayed Value • why is this important? • the power of a spreadsheet lies in its ability to compute values based on data in other cells (we’ll see this soon) • the value used for computation is the stored value, not the displayed value • in example below, cell A1 has been formatted to show only one digit after the decimal point • if we were to multiply the value of cell A1 by 2, we would get 1.5, not 1.6

41. Inserting Data • we’ve seen how to append rows of data to an existing spreadsheet • suppose we wish to add data somewhere other than the end • e.g. suppose we want to add another purchased item to our list, or a set of headers • a couple of options • select the entire table as a range, and drag the border using your mouse • insert a new row, by right clicking on the row to insert above, and select Insert

42. Inserting Rows - Drag Not a good option in this case – notice the taller rows

43. Inserting Rows – Right Click Much better – the correct rows are still tall.

44. Inserting Rows • in either case, we’re now free to add headers • we’ll apply cell shading, boldface font, and a larger font size, to make the headings really stand out Font Size Boldface Cell Shade

45. Spreadsheets - Borders • every cell has a light border surrounding it • called gridlines • extra borders can be added via the borders drop-down • to add extra borders: • select the range that you would like to border • use the drop-down to select border: • placement (all, top, bottom, left, right) • style (thick, thin, etc) • from our previous example: • add a single border above subtotal • add a single border above total • add a double border below total

46. Adding Border above Subtotal • other borders are added in similar manner

47. Spreadsheets - Borders • if you wish to disable gridlines (to really see your borders), uncheck Gridlines in View ribbon

48. Sorting Data • spreadsheets typically allow us to sort data • this has many useful applications • class grades (scholarships) • sports – points (awards, fantasy pools) • method: • select range that you wish to sort • click the sort button • select the type of sort (ascending/descending)

50. Sorting Data • what happened? • Excel’s sort assumes that your data has a header that is not to be sorted • solution (choose one): • include the header in your range • choose Custom Sort, and deselect the My Data has headers option