Excel 2002…More Basics

1. Excel 2002…More Basics Introduction to Excel 2002

2. Create a Simple Workbook • This lesson will cover: • What is a Workbook? • How to… • Open a Workbook • Select a Worksheet • Move around the Worksheet • Store Data in a Cell • Use the Undo and Redo Commands • Create Simple Calculations • Use AutoFill • Use the Office Clipboard • Use Smart Tags • Save and Close a Workbook These topics are from Chapter 2 of the text... Introduction to Excel 2002

3. What is what? • Excel stores data in files which are called “Workbooks”: • Workbooks are made up of Worksheets • Worksheets are made up of Columns and Rows • The basic unit of a Workbook (or spreadsheet) is a Cell • Excel workbook files have the extension .xls • The maximum number of worksheets a workbook can have is only limited by the amount of available memory • The maximum worksheet size is 65,536 rows by 256 columns • Objects, such as charts, images or drawings exist on top of the cells, not in them. • These concepts are basic to understanding and using Excel… Introduction to Excel 2002

4. Many features of Excel can be customized to suit your needs. To change the program options select Tools > Options For example, you can change the default number of worksheets that are automatically created here…among other things… Introduction to Excel 2002

5. Opening a Workbook • As is usual with most Windows based programs, there are several ways to do things with Excel… • To open an existing workbook: • Open the File menu • Select Open • Select the file from the “Open” window’s listing, or browse to find the one you want • …or • Use the shortcut keys…Ctrl+Oto display the Open window • Select the file from the list Introduction to Excel 2002

6. First select Open from the File menu…or use the Crtl+O shortcut. You could also select from the “Most Recently Used” (MRU) list, if the file you want to open is listed there... Introduction to Excel 2002

7. Clicking the Open command will display the “Open” dialog box…from the list select a worksheet file. Excel worksheet files end with the XLS extension. You can also browse to find a file. These files are located on a floppy disk. Introduction to Excel 2002

8. Many times when you select to open a file there will be a warning like this one. It just means that there is a bit of programming code attached to the file, and warns you about it. If the file came from a “trusted” source, select Enable Macros. If it didn’t come from a trusted source, click the Disable Macros button and the file will open without running any code. Introduction to Excel 2002

9. Once you click through the Macro warning, the file will be displayed… remember, you will not always see the warning shown on the previous slide. Many workbooks will open right from the Open dialog box. Introduction to Excel 2002

10. Excel can also open many other types of files and convert them to the Excel workbook format…file types such as HTML, XML, TXT and CSV are all supported, and Excel will even open many files created by other spreadsheet software or databases, like Lotus 1-2-3, Quattro Pro, Microsoft Access and dBase. Introduction to Excel 2002

11. Selecting a Worksheet To select a worksheet simply click on the sheet’s tab located on the Sheet Bar. The Sheet Bar is located at the bottom of the window above the Status Bar. When you click on a tab it moves to the front of the group of tabs. The tab scrolling buttons allow scrolling through tabs not visible on the bar, or from a pop-up menu. Introduction to Excel 2002

12. You can move through the worksheets in the open workbook by using the sheet tabs, or the tab scrolling buttons... tab scrolling buttons sheet tabs Introduction to Excel 2002

13. Right-clicking on the tab scrolling buttons will display a pop-up menu showing all the available tabs. Notice the “Wellcome” tab is selected, it is the “active” sheet. It is spelled wrong on purpose… Introduction to Excel 2002

14. Moving Around a Worksheet • Moving about the rows and columns in Excel can be done with both a mouse and a keyboard. Standard Windows shortcut keys, such as Ctrl+X for Cut or Ctrl+C for Copy work with Excel. • Navigation keys you can use are: • Arrows keys move up, down, left and right one cell • Tab move left one cell • Shift+Tab move right one cell • Page Up or Page Down move up or down one window • Home move to the beginning of a row • Ctrl+Home move to cell A1 • Ctrl+ End move to the last cell in a worksheet Introduction to Excel 2002

15. Putting Data in its Place… • Literal data is a distinct set of data like 255 or 01/01/2002 it does not change unless you do it manually. Excel stores such data by simply entering it into an Active cell. • To activate a cell and enter data: • Activate the cell by clicking on it or using the keyboard to move to it • Type the data • Press the Enter key or move off the cell…such as using the Tab or Arrow keys Introduction to Excel 2002

16. Whether you “type” the data in the formula bar or in the cell, it appears in both, but is actually stored in a location that Excel knows as the cell’s address… in this case it would be C5. Introduction to Excel 2002

17. Some data types are formatted a certain way by default…so entering something like “December 7, 1941” will change to… Introduction to Excel 2002

18. …“7-Dec-41” when you move off of the cell. This can be changed as you will learn later. But, be aware the value is still the same, only the way Excel is displaying the data is different…it could also be “12-7-1941”, or “12/7/1941” depending on your choice of format. Introduction to Excel 2002

19. Basic Data Types… • Excel uses 3 basic types of data: • Numeric values – Number type data can include any number mixed with the following characters + - ( ) , / $ % . E e. If you try to enter a number that is to big for a cell, Excel will display a set of number signs (##########) in the cell. • Text – Excel uses text mainly for labels. But you can also use numbers as text. A cell defined as holding textual data can have up to 32,767 characters • in it (only 1024 are displayed). Storing numbers as text prevents them from being used as numbers in calculations…they are considered alphanumeric text. • Formulas – Formulas are instructions to Excel to perform some type of operation and return the result in the cell. Formulas are entered by first entering an equal sign ( = ) followed by the formula. Excel usually thinks that anything preceded by an equal sign is a formula. Introduction to Excel 2002

20. Everything stored in a cell is a number…here you see how changing the formatting of the cell changes the values which are displayed…each cell on the left contains the numbers 1234. Introduction to Excel 2002

21. Right-clicking on a cell will display a pop-up menu. From there you can select the Format Cells command to open a dialog box giving you many ways to change how your data is displayed… but this is another topic…for another day. Introduction to Excel 2002

22. Undoing and Redoing… Excel stores the 16 most recent operations in memory so that it can retrieve and correct your mistakes. These actions are known as the Undo and Redo commands. If you decide you really didn’t want to delete a value in a cell, or a font formatting change to a label you can select the Undo command from either the toolbar or the Edit menu. If you then decide that you liked it the way it was before select Redo. Using these commands you can step your way back to a given state that your worksheet was in, as long as you stay within the 16 most recent actions. Anything past that is gone forever. Introduction to Excel 2002

23. Undo and Redo as they appear on the Standard toolbar… Undo and Redo as they MIGHT appear on the Edit menu…the shortcut keys are to the right of the menu commands. Introduction to Excel 2002

24. Working with Formulas… • Calculations placed in a cell are called formulas in Excel. • Formulas begin with an equal sign ( = ) so Excel knows that you aren’t entering simple data. • Formulas are constructed of numbers, cell references (or cell names) and operators, such as: < > + - / * • A simple formula might look like: = (B3*H6)-D6 • More complex formulas might include functions, which are predefined calculations that Excel offers to save time • A more complex formula might look like: • = AVE(B3:B16) * H6 • This formula uses the AVErage function, which returns the average of a set of numbers. Introduction to Excel 2002

25. Working with Formulas… • Calculation Operators: • + addition A1 + B2 A1 plus B2 • - subtraction A1 - B2 A1 minus B2 • / division A1 / B2 A1 divided by B2 • * multiplication A1 * B2 A1 times B2 • % percent A1 * 10% A1 times 10 percent • ^ Exponentiation A1 ^ 3 A1 to the 3rd power Introduction to Excel 2002

26. Working with Formulas… • Comparison Operators: • = Equal to • > Greater than • < Less than • >= Greater than or Equal to • <= Less than or Equal to • <> NOT Equal to Introduction to Excel 2002

27. Working with Formulas… • Calculations are evaluated according to an “Order of Precedence” with regard to the operators: • First calculations inside Parentheses ( ) • Second Negation, negative numbers - 1 • …next Percent % • …then Exponentiation ^ • …and Multiplication and Division * / • …and Addition and Subtraction + - • …and Concatenation & (joining text strings) • …and finally = > < >= <= Introduction to Excel 2002

28. Working with Formulas… • Putting together a Calculation: • Activate the cell you want the calculation result to be displayed in… • Type an equal sign • …then type the values, references, operators and functions into the cell • Press enter and that should do it, the actual text you typed should be replaced with the result of the calculation. Introduction to Excel 2002

29. Here you see the formula: The value 4 times 4 plus 4, multiplied by 4, or as shown in the Formula bar: (4*4+4)*4 Introduction to Excel 2002

30. This formula is a bit more complex since it uses two of Excel’s built-in functions : The value 2 raised to the 2 power (or squared in this case), multiplied by pi, or as shown in the Formula bar: = POWER(2,2) * PI() Introduction to Excel 2002

31. Common Formula Errors… • The #REF! error value occurs when a cell reference is not valid. Deleting or changing cells referred to by other formulas can cause the error. • The #NAME? error value occurs when Excel doesn't recognize text in a formula, such as misspelling the name of a function, or entering text in a formula without enclosing the text in double quotation marks. Also, omitting a colon (:) in a range reference can be the cause. • The #NULL! error value occurs when you specify an intersection of two areas that do not intersect. To refer to two areas that don't intersect, use the union operator, the comma (,). For example, if the formula sums two ranges, make sure a comma separates the two ranges (SUM(A1:A10,C1:C10)). • The #NUM! error value occurs when there is a number problem in a formula or function, or entering a formula that produces a number too large or too small to be represented in Excel. Using an unacceptable argument in a function that requires a numeric argument can also be the cause. Introduction to Excel 2002

32. Common Formula Errors… • The #VALUE! error value occurs when the wrong type of argument or operand is used, or if the Formula AutoCorrect feature cannot correct the formula. Entering text when the formula requires a number or a logical value, such as TRUE or FALSE, or supplying a range to an operator or a function that requires a single value (not a range) can also be the cause. • The #DIV/0! error value occurs when a formula divides by 0 (zero). • The #N/A error value occurs when a value is not available to a function or formula. If certain cells on your worksheet will contain data that is not yet available, enter #N/A in those cells. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value. • A ##### error value occurs when the cell contains a number, date, or time that is wider than the cell or when the cell contains a date and/or time formula that produces a negative result. Introduction to Excel 2002

33. Using AutoFill… • Excel’s AutoFill feature is used for those times when you need to enter data in some sort of predictable sequence. For instance, suppose you need to enter a column of numbers from 1 to 1000. You could type them all in one by one, or you could use AutoFill. • Highlight a set of cells that are already in a sequence • Drag the the selection over the entire range you want to enter…or • Use the Fill command from the Edit menu • You really need to see this one… Introduction to Excel 2002

34. Enter a series of numbers, to the right you see 1,2,3 in adjacent cells. Then you select all three of the cells and grab the small “handle” at the lower right of the selected range of cells. You then drag the cells in the direction you want to insert the numbers…. …as you move the mouse pointer you will see each new cell value displayed. When you release the mouse button… Introduction to Excel 2002

35. The new range of cells will be created in the pattern established by the first three cells…in this case 1,2,3,4,5,6, and so on. The tag that appears at the end of the series provides you with more ways to customize the AutoFill… Introduction to Excel 2002

36. AutoFill can use many patterns, here the pattern 3, 9 and 27 was used. Each value is 3 times the previous (except 3). The first new cell value should have been 81 and then 243, right? Pay attention to the series you are using and if necessary you may have to further define it in the Fill options. You can access a quick set of AutoFill options by right clicking on the tag at the end of the new range of cells. Sometimes you may need more choices… Introduction to Excel 2002

37. The AutoFill options can be accessed from the Edit menu under the Fill command. Here you see the Series configuration dialog. It takes some practice to master many of these settings, but there is the “What’s this help” available to help you along. Introduction to Excel 2002

38. The Office Clipboard… The Office Clipboard is related to the system Clipboard in the following ways: When you copy multiple items to the Office Clipboard, the last item you copy is always copied to the system Clipboard. When you clear the Office Clipboard, the system Clipboard is also cleared. When you use the Paste command, the Paste button, or the shortcut keys (CTRL+V), you paste the contents of the system Clipboard, not the Office Clipboard Introduction to Excel 2002

39. You activate or open the Office Clipboard by selecting the Office Clipboard command from the Edit menu… The Clipboard opens a pane on the right with all the objects currently in the Clipboard “buffer” shown…the only object here is a screen capture taken for one of the previous slides. Notice the PowerPoint icon to the right of the image, this tells you which Office program used it… Introduction to Excel 2002

40. You can paste any of the objects in the Clipboard to the active worksheet by clicking the object as listed in the Clipboard pane… You can change several of the Clipboard’s options by clicking the Options button at the bottom of the Clipboard pane. Introduction to Excel 2002

41. …clicking an object automatically pastes that object on the worksheet… it obviously needs some cleaning up, but the first step was to get it on the sheet. Introduction to Excel 2002

42. Smart Tags… You can save time by using smart tags to perform actions in Excel that you'd usually open other programs to perform. Excel recognizes certain types of data that it labels with smart tags. The type of actions you can take depend on the data that Excel recognizes and labels with a smart tag. When Excel recognizes types of data, the data is marked with a smart tag indicator - a purple triangle in a worksheet cell. To find out what actions are available with a smart tag, move the insertion point over the text with a smart tag indicator. Introduction to Excel 2002

43. Smart Tags… Since Smart Tags are turned off by default you may have to make sure smart tags are turned on. To do this: On the Tools menu, click AutoCorrect options. Click the Smart Tags tab, and select the Label data with smart tags check box. Introduction to Excel 2002

44. Now you see that the stock prices have been recognized by Excel. They are highlighted with a small purple triangle at the lower right corner of each “recognized” cell… Introduction to Excel 2002

45. When you click on the Smart Tag you are given a choice of actions…for example you could make this stock quote “refreshable”. That way it would always display a current price as long as the PC on which the workbook was opened had a valid Internet connection. Introduction to Excel 2002

46. Smart Tags… You can get other Smart Tags to use with your documents that provide information from such sources as LexisNexis, Federal Express, ESPN, Expedia and many more, by accessing the Office website at: http://office.microsoft.com/Services/category.aspx?cid=4 Introduction to Excel 2002

47. Saving and Closing a Workbook Saving changes to your workbook should be done continually as you work in Excel. Every 10 or 15 minutes stop and save your work as a safeguard. A new workbook that has never been saved, in reality, doesn’t exist, except in your computer’s memory. It must be saved…in Windows terminology, “Saved as…” When you save an Excel workbook the default file type is as an Excel workbook (.xls), but you can also save it as a web page (.htm), a text file (.txt), or even as a Lotus 1-2-3 file (.wk4), not to mention older file versions of Excel. Introduction to Excel 2002

48. Saving and Closing a Workbook To Save or Close a file, or to Exit Excel you can use the File menu: • The File menu offers commands to: • Create a new workbook • Open a file • Close a open workbook • Save an existing file • Save a new file (Save As) • Setup your page margins, paper type and other page related options • Print your work • …and Exit the program Introduction to Excel 2002

49. To exit Excel, or just close the current worksheet use the File menu…to Close the current worksheet select Close… To Exit the program, which will also close the current worksheet …select Exit…remember to save your work if necessary. Introduction to Excel 2002

50. If you select Exit or Close with an “unsaved” workbook you will be prompted to save it… • Select: • Yes to save your changes… • No to Close or Exit and Not Save your changes, or… • Cancel to return to the workbook and Not Exit or Close the workbook. Introduction to Excel 2002