Spreadsheets

1. Spreadsheets Spreadsheets using Microsoft Excel 2007 MiciHalse (M.Halse@ru.ac.za)

2. Resources? The course web page is at http://cs.ru.ac.za/courses/CSc1L1/sem2/spreadsheets/ You can download the slides from here on a daily basis, and find other links to related resources e.g. extra study materials and study aids like games and flash cards, etc. There is no prescribed textbook but resources are drawn from: Microsoft Office 2007 (Windows XP Edition) in the Shelly Cashman Series (Thomson publishers) ISBN:1-4188-4327-X

3. Section 1 - Basics So what’s a spreadsheet anyway? • Appearance? • Uses? • Types of data? • Users? • Organisations? • Disciplines?

4. Section 1 - Basics • A spreadsheet is a grid of rows and columns with the functionality of a powerful calculator • Purpose • Organising data • Solving numerical problems • Make decisions • Graph data • Create reports • Publish data to the web • Examples • Multi-disciplinary use – maths, science, business, psychology, anthropology, sports science, art, music, languages, etc. Familiarity is expected at university • Business or personal financial management • Stock details and calculations in a company • Personnel details & Scheduling • Assessment – e.g. growth of subscribers of a newspaper, reader demographics, etc

5. Starting Excel • Start All Programs  Microsoft Office  Microsoft Office Excel 2007

6. Excel Environment The Ribbon Title bar shows blank workbook title Name Box Column letters Scroll bars Row numbers Sheet tabs Status bar indicates worksheet is ready for data entry

7. Environment - Navigating • Spreadsheets are made up of rows and columns • The active cell is referred to as the highlight • Click in a cell to highlight it before you type • Can also navigate to cells using arrow keys or by typing the cell you want to go to into the name box

8. Environment - Ribbon Help button Home tab Quick access toolbar, to which you can add shortcuts to things you do often • 3 ways to minimise/maximise the Ribbon: • Press CTRL and F1 at the same time • Double-click the active tab • Right-click on the Ribbon and choose Minimise/Maximise as appropriate Other top level tabs Groups (of related commands)

9. Environment - Tabs • Most commands and options are available from the Ribbon • They are arranged on tabs, in groups of similar functions • Document management options are not on a tab, but are instead available from the Office button • Options specific to the wayexcel functions are available from the Office button dialog box

10. The Quick Access Toolbar • You can add options to the Quick Access toolbar to make commands that you use often easy to access

11. Galleries & Live Preview • A gallery is a set of choices (often graphical) arranged in a grid or list • Some galleries support Live preview - a feature which shows you the effect a particular gallery choice will have on your worksheet when you hover your mouse over it without you actually applying the choice

12. Mini Toolbar • When you select text, you can show or hide a handy, miniature, semi-transparent toolbar called the Mini Toolbar. • Options  fonts, font styles, font sizing, alignment, text color, indent levels, and bullet features. • The Mini Toolbar is supposed to help minimise mouse movements and thus help prevent repetitive strain injury • It is transparent until you place your mouse pointer on it – then it becomes solid and ready for use

13. Environment - Ranges • Left-click and hold in the mouse to select more than 1 cell (called a range of cells). This is the range (B2:C4) • To select 2 separate ranges, hold down the Ctrl key after selecting the first range, and then select a second range • To select a range using the Name Box, type the range into the Name Box and press ENTER • To select a named cell or range, type its name and press ENTER, or click the arrow next to the Name box, and then click the name that you want.

14. Zoom • A useful option available from the View tab is Zoom • For working with slightly more than a standard screenfulof data, or for the visually impaired • Can also “Zoom to Selection”

15. File Management (1) • Saving files can be accomplished • via the Quick Access Toolbar • or by selecting Save or Save As from the Office menu • Remember that can save your file as an Excel 2007 document or an Excel 2003 document (in case you need to open it on a machine running an older copy of Office)

16. File Management (2) • Browse to wherever you want to save the file (probably your homespace) and save it under a sensible name that will enable you to remember what it is

17. Making your mark (1) • To enter data, click on the cell you want to type into (to highlight it) and simply type • Once you have pressed enter, if you need to correct the entry clicking on the cell and typing will overwrite the previous content. To just correct the data in a cell highlight the cell as normal but make the correction in the formula bar, or double-click on the cell • If you make a mistake, don’t panic - you can undo it using the undo button on the Quick Access toolbar • Options to undo most recent action or a series of actions • Pressing CTRL and Z at the same time also works to undo the last thing you did

18. Making your mark (2) • When you press Enter, having typed data into a cell, the active cell becomes the one below (the next cell down) • To change this: • select Excel Options from the Office menu • under the Advanced section of the Excel Options dialog box, change the direction from the Editing options

19. Autocorrect (1) • Autocorrect is the way MS Office 2007 fixes mistakes for you while you type • It does 3 kinds of corrections: • When you put two initial capital letters it changes the second to lowercase • It capitalises the first letter in names of days • It replaces commonly misspelled words with their correct spellings, e.g. Replacing recieved with received

20. Autocorrect (2) • Autocorrect’sbehaviour can be changed, and you can add new words you often spell wrongly, or use the feature as a handy way to expand short-hand • To do this: • Office button  Excel Options  Proofing  Autocorrect Options

21. Entering Numbers • A number in Excel 2007 can contain the following characters: • 0 1 2 3 4 5 6 7 8 9 + - ( ) , / . $ % E e • Cell entries containing any other characters will be treated as text • Numbers can be between -1 x 10308 and (+) 1 x 10308 • To enter a very long number like 6 000 000 000 000 000 you can type out the whole number, or type 6E15 which means 6 and 15 zeroes – like writing 6 x 1015

22. Help for the needy • Help systems are a standard feature of both application software and many programming development environments • In Excel 2007 you can choose • to search for a specific topic (if for example you know what you want to do but not how to do it) • or to browse the table of contents (click on the icon depicting an open book -> to display the Table of Contents section ) if your idea of what you need is more vague or the search terms you have tried have been unsuccessful.

23. More on Rows and Columns • The behaviour of rows and columns • Columns can be resized by placing the mouse pointer at the rightmost boundary of the column you want to resize. It will change into a double-headed arrow and you can click and drag the column to a new width or double-click to have the column autosize itself to fit the data • The same principle can be appliedto rows

24. Inserting and Deleting Rows and Columns • Insert new rows and columns (useful when you’ve left something out) by selecting the row below or column after which you want to insert (by clicking on the row number or column letter) • Select Insert from the Cells group on the Home tab of the Ribbon, and choose to insert rows or columns • To delete a row/column highlight it and then choose Delete from the Cells group on the Home tab of the Ribbon (using the keyboard instead will delete the data but not the row/column itself) • New Worksheets can be inserted and deleted in the same way

25. Crouching data, hidden rows & columns • Hiding temporarily removes a row or column from the screen • Select the row or column you want to hide • Chose Format from the Cells group on the Home tab of the Ribbon • Select Visibility  Hide & Unhide  Hide Rows (or Columns or even a whole Sheet) from the Format menu and choose Hide • Redisplay the hidden row or column by selecting the rows/columns on each side of the hidden ones and choosing Unhide

26. Auto-Inserting Dates & Times • To enter the current date into a cell, just press Crtl and semicolon on the keyboard at the same time • To enter the current time into a cell, just press Crtl and shift and semicolon on the keyboard at the same time

27. Fractions • How do you enter a fraction (for example two thirds)? • Entering 2/3 is interpreted as a date by Excel which then displays 02-Mar or 03-Feb (depending on its date settings) • To enter a fraction you thus need to type zero, a space and then the fraction like this: • Excel then formats the cell using the Fraction Number format

28. Data Validation (1) • Data validation lets you define restrictions on what data can or should be entered in a cell. • You can configure data validation to prevent users from entering data that is not valid. • Can also • allow users to enter invalid data but warn them when they try to type it in the cell • provide instructions to help users know what to data to enter or correct errors

29. Data Validation (2) • To set up a cell or a range of cells so that it only accepts certain kinds of data: • Select the cells • Choose Data Validation from the Data Tools group on the Data tab of the Ribbon • Using the Data Validation dialog box will allow you to configure settings, messages and error messages

30. Data Validation (3) • EXAMPLE (slide 1/5):

31. Data Validation (4) • EXAMPLE (slide 2/5):

32. Data Validation (5) • EXAMPLE (slide 3/5):

33. Data Validation (6) • EXAMPLE (slide 4/5):

34. Data Validation (7) • EXAMPLE (slide 5/5):

35. Section 2 – Appearance and Formatting • The world is an unfortunately shallow place! • Appearance can be customised for ease of comprehension, clarity, impact • General rule of business: the higher up the corporate hierarchy a document is destined, the greater the effort that must be invested in formatting it • Sloppy formatting sends the message that the document or task is meaningless to the author, or that the author is uneducated

36. Unformatted...

37. After Simple Formatting Changes

38. Formatting Data Formatting can be accomplished by selecting the cells/range of cells you want to format and • using the limited formatting options available from the Mini toolbar • right-clicking, and selecting Format Cells from the context menu, or • selecting the appropriate option from the Home tab (bigger picture soon!)

39. Formatting Data - Number If you choose to right-click you will be presented with the Format Cells dialog box. This has various tabs along the top, each of which allows the formatting of different things.

40. Formatting Data - Alignment

41. Formatting Data - Font

42. Formatting Data - Border

43. Formatting Data - Fill

44. Formatting Data - Protection

45. Formatting options on the Home tab (1) Increase/DecreaseFont Size Font Face Align Bottom Align Top Font Size Align Middle Orientation Bold Merge and Centre Align Left Italics Cell borders Align Right Font Colour Underscore Center Fill Colour Increase/Decrease Indent

46. Formatting options on the Home tab (2) Comma Style Conditional Formatting Format as Table Pre-defined Cell Styles Percent Increase/Decrease Decimal Currency

47. Conditional Formatting • Conditional Formatting allows you to format a cell according to its contents • E.g. If a product needed to make a 10% profit to be economically viable to produce, all cells below this threshold could be formatted to appear red/bold/etc

48. Conditional Formatting Options (1)

49. Conditional Formatting Options (2)

50. Conditional Formatting Options (3)