Microsoft Excel I

1. Microsoft Excel I Bob Herring June 2010

2. Microsoft Excel I Basics • Excel is a spreadsheet tool • Holds data (usually numeric) • Constantly recalculates output based on changing input • Think of it as a calculator that you can make notes on • What it isn’t • Not a word processor -- Use Word instead • Not a database -- Use Access • Not a presentation tool -- Use PowerPoint 2

3. Microsoft Excel I Converting to the Newer Version Microsoft has provided several useful tools to help you convert from the 2003 suite to the new 2007 version Step one: Go to the URL below to find the tool you need http://office.microsoft.com/en-us/training/HA102295841033.aspx Step two: Download Adobe Flash player if it’s not already installed Step three: Run the tool, either online or as downloaded to your computer Step four: Assume your rightful place as the go-to guy on 2007! 3

4. Microsoft Excel I One Big Change • All documents are created as “web-ready” XML documents • The Excel file extension is now .xlsx, reflecting this • Excel 2007 can read all previous versions of Excel • Excel 2003 CANNOT read the 2007 version unless you go to the Microsoft website and download a converter • This converter is called FileFormatConverters.exe and is found athttp://www.microsoft.com/downloads/details.aspx?FamilyId=941B3470-3AE9-4AEE-8F43-C6BB74CD1466&displaylang=en • Watch what version you are creating – you can choose to create documents in the old format to send to people who don’t have 2007 4

5. Microsoft Excel I Initial View Your initial view of the program, with the Home Ribbon selected Office Button Quick Access Toolbar Home Ribbon Document Window 5

6. Microsoft Excel I Worksheet Layout Home Ribbon Cells F6 Status Bar E12 6

7. Microsoft Excel I Worksheet Layout • Worksheet Layout, continued Formula Bar Name Box Sheet Tabs 7

8. Microsoft Excel I The Office Button • This is sort of the old “File” Menu item • This is also where to come to set Excel options (more later) • Can be annoying to work with, but you can use keyboard shortcuts 8

9. Microsoft Excel I Home Ribbon • This is called the Home ribbon for the same reason that the old Standard toolbar was called that – the most common actions are all here Clipboard Section Alignment Section Styles Section Editing Section Font Section Number Section Cells Section 9

10. Microsoft Excel I Entering Data Enter and edit some data ... Text is Bold and Centered 10

11. Microsoft Excel I Excel Capabilities Use a function to analyze the data ... AutoSum Tool Formula Bar Formula in Cell 11

12. Microsoft Excel I Excel Capabilities Obtain results … Row Headers Added Columns Totaled 12

13. Microsoft Excel I Charts Create charts with the data 13

14. Microsoft Excel I Formulas Start with some data ... Data 14

15. Microsoft Excel I Formulas, continued Insert a formula ... Formula Appears in Cell 15

16. Microsoft Excel I Exercise Formulas • These are the formulas for the exercise • Use the numeric keypad for + , - , * , / operators • The caret ^ (over the 6) is the exponent operator 16

17. Microsoft Excel I Formulas, continued • Formulas may be combined • Use parentheses to change the order of operations Combined Formula 17

18. Microsoft Excel I Copying Cells • Cells can be copied and ranges extended with the Fill Handle • Cursor changes to plus sign ( + ) • Click and drag with the left mouse button Fill Handle 18

19. Microsoft Excel I Copying and Pasting Formulas A copied formula will automatically apply itself to its new location Select Cell and Click Copy Select Destination And click Paste 19

20. Microsoft Excel I Formatting Rows and Columns • Select the Home Ribbon, then click ‘Format’ and choose ‘Row Height’ • (or ‘Column Width’) • Type the new height (or width) 20

21. Microsoft Excel I Formatting Rows and Columns • Select the Home Ribbon, then click ‘Format’ and choose ‘Hide & • Unhide’ • Click to hide (or unhide) rows or columns Hide & Unhide 21

22. Microsoft Excel I Inserting Rows and Columns • Select the Home Ribbon, then click ‘Insert’ and choose • ‘Insert Sheet Rows’ (or ‘Insert Sheet Columns’) • The new row appears above selected row; new column to the left 22

23. Microsoft Excel I Deleting Rows and Columns • Select the row or column by clicking on the number or letter • On the Home Ribbon, select ‘Delete’ and choose ‘Delete Sheet • Rows’ or ‘Delete Sheet Columns 23

24. Microsoft Excel I Viewing Worksheet Tabs • Sheet Tabs are controlled by buttons at the bottom left corner • of the document window • If not all tabs are in view, use the buttons to make them visible Show First Tab Show Last Tab Move Left Move Right 24

25. Microsoft Excel I Naming a Worksheet • Double-click the Sheet Tab, or • Right-click the tab, Select ‘Rename’ and type the new name Rename 25

26. Microsoft Excel I Copying a Worksheet • Right-click the Sheet Tab • Select ‘Move or Copy…’ • Check the ‘Create a copy’ box and select the sheet’s new location Select Move to End Move or Copy Check the Copy box 26

27. Microsoft Excel I Using the Calculator • Excel has a built-in calculator that you can use • Highlight the numbers that you want to add, and the sum appears in • the status bar • Calculator can also average, count, and find the min or max • To change operations, right-click in the status bar Sum, Average, etc. Appear in Status Bar Highlight a group of Numbers 27

28. Microsoft Excel I Centering Across Columns • Select the cell in which the text begins Text to be Centered 28

29. Microsoft Excel I Centering Across Columns, 2 • Highlight the cells in the columns that the label will span • Select the Merge and Center Tool Merge and Center Tool Columns Highlighted 29

30. Microsoft Excel I Centering Across Columns, 3 • Text is now centered in a merged cell • Merged cells behave differently from unmerged ones Centered Text 30

31. Microsoft Excel I Saving Excel Worksheets • Remember to SAVE your work! • Click the disk icon on the toolbar , OR • Select the Office Button, then ‘Save’ 31

32. Microsoft Excel I Using the Name Box • Cells and ranges can be named • To got to a named cell, type it in the name box, or, select the dropdown Click on dropdown to select defined names Type a Name and press ‘Enter’ to go to the Cell 32

33. Microsoft Excel I Using the Name Box, 2 • To name, select the cell or range of cells to be named • Select the Formulas Ribbon and click ‘Define Name’ • Type a name for the cell(s) in the dialog box that appears Define Name 33

34. Microsoft Excel I Using Names in Formulas • Enter the table shown in the picture below. (Use the Fill Handle to help) • Use a formula to calculate the profit ( =B2-B3 ) • Name cell B3 “Jan”, C3 “Feb”, D3 “Mar”, etc Name the cells in this row Use the names to calculate quarter totals 34

35. Microsoft Excel I Page Setup, Page Size and Orientation • Select the Page Layout Ribbon and click for the Page Setup dialog box • First Tab is for the page size and orientation • Many spreadsheets are printed as landscape (page sideways) • Use ‘Fit to:’ to squeeze data onto the page the way you want 35

36. Microsoft Excel I Page Setup, Margins • Select the Page Layout Ribbon and click for the Page Setup dialog box • Use this Tab to set standard margins -- Or, • Select Print Preview, then click ‘Show Margins’ to set them by eye Text can also be centered 36

37. Microsoft Excel I Page Setup, Headers and Footers • Select the Page Layout Ribbon and click for the Page Setup dialog box • Use this Tab to format page headers and footers Automatic date Automatic page numbering 37

38. Microsoft Excel I Page Setup, Sheet Options • Select the Page Layout Ribbon and click for the Page Setup dialog box • Use this Tab to print row and column headings and gridlines Row and Column Headings Gridlines Check box 38

39. Microsoft Excel I List Options • Select the Office Button, then ‘Excel Options’. In the ‘Popular’ section • click the ‘Edit Custom Lists…’ button • Lists can be used to fill in values (e.g., Days and Months) • You can make your own lists to save time and typing • Type in a list and click ‘Add’, or ‘Import’ to get it from a worksheet 39

40. Microsoft Excel I Adding a List • Click in the ‘List entries’ window • Type each entry in the list. Press ‘Enter’ after each entry, OR • Separate entries with commas • Click ‘Add’ to add the list to the Custom lists pane Add Type List Here 40

41. Microsoft Excel I Importing a List • To begin, either type the list of cells to be imported into the dialog • entry, or click the collapse dialog button • Highlighting cells will copy them into the collapsed dialog • Click the expand dialog button to return to Custom Lists • Click the ‘Import’ button to add the list Highlight Cells Expand Dialog Button Collapse Dialog Box 41

42. Microsoft Excel I Correcting Errors • Generally Speaking • Beware of GIGO; or, a computer program is only as good • as its inputs • Be cautious of the results -- know what answer to expect • Errors in Data and Formulas • To fix bad data, click on the cell and retype the number • To fix bad formulas, click the cell containing the formula, then • highlight the error in the formula bar and make corrections • Excel Error Messages • The program can help spot errors • Excel error messages begin with “#” 42

43. Microsoft Excel I Recognizing Errors • Common Error Messages • ##### Cell isn’t wide enough to show the data • #VALUE! Wrong type of data for a function • #DIV/0! Tried to divide by zero • #NAME? Cell name not defined or (usually) misspelled • #REF! Cell reference is not valid • #NUM! Function requires a number • #NULL! Called a non-intersecting range of cells 43

44. Microsoft Excel I Clearing an Entire Worksheet • Select worksheet by clicking the row and column intersection • Select the Home Ribbon, then click the ‘Clear’ button (eraser symbol) • Choose All, or Formats, or Contents, or Comments Place Cursor at Row & Column Intersection 44

45. Microsoft Excel I Clearing Cell Contents • Select cells to be cleared by clicking left mouse button and dragging • Select the Home Ribbon, then click the ‘Clear’ button • Choose All, or Formats, or Contents, or Comments Cells to be Cleared 45

46. Microsoft Excel I Cell Formatting -- Alignment • Set text location in cells by using the vertical and horizontal buttons • Set text direction by clicking the ‘Orientation’ button • Wrap text in cells by clicking the ‘Wrap Text’ button Text Direction Vertical Controls Text Wrapping Horizontal Controls 46

47. Microsoft Excel I Cell Formatting -- Alignment Tab • The Alignment tab sets the location of the text within cells • Text can be vertical as well as horizontal • Use the ‘Wrap text’ check box to keep column headers narrow Text Orientation Wrap text 47

48. Microsoft Excel I Cell Formatting -- Font • Grow or shrink the text one size at a time with the ‘A’ arrow buttons • Set cell borders with the ‘Borders’ button • Set cell background colors with the ‘Fill Color’ button • Set text color with the ‘Font Color’ button Grow/Shrink Text Text Color Cell Fill Color Cell Borders 48

49. Microsoft Excel I Cell Formatting -- Font Tab • The Font tab displays all the font commands in one dialog box • Allows users to make subscripts ( CO2 ) and superscripts ( X2 ) • Underlines can be set for accounting (both single and double) 49

50. Microsoft Excel I Cell Formatting -- Number Tab • Use the down arrow for quick formats; the right arrow for all formats 50