Microsoft Office Excel

1. Microsoft Office Excel Using Excel To Manage Data

2. Identify major components of the Excel window • Excel is a computerized spreadsheet, which is an important business tool that helps you report and analyze information. • Excel stores spreadsheets in documents called workbooks. • Each workbook is made up of individual worksheets, or sheets. • Because all sorts of calculations can be made in the Excel spreadsheet, it is much more flexible than a paper spreadsheet. • The Excel window has some basic components, such as an Active cell, Column headings, a Formula bar, a Name box, the mouse pointer, Row headings, Sheet tabs, a Task Pane, Tab scrolling buttons and Toolbars.

3. A sample Excel worksheet

4. Excel worksheets and workbooks • When you set up calculations in a worksheet, if an entry is changed in a cell, the spreadsheet will automatically update any calculated values that were based on that entry. • When you open Excel, by default it will open a blank workbook with three blank worksheets. • When you save a workbook, you have a Save As option that can save the spreadsheet to earlier versions of Excel or to Quattro Pro, Lotus 123 formats, dBase formats, and even to a comma or tab-delimited text file.

5. Construction of worksheet • Row • 65 536 (depending of version number) • 1 048 576 (from 2007 version) • Reference with order number • Column • 255 (depending of version number) • 16384 (from 2007 version) • Reference with letters (A..IV) • A..XFD (from 2007 version) • Cell • Point of intersection of Row and Column • Reference with coordinates: D43, but no 43D

6. Identify Excel components

7. Excel 2007 screen in Excel

8. Descriptions of Excel components

9. Navigate within worksheets • To navigate within a workbook, you use the arrow keys, PageUp, PageDown, or the Ctrl key in combination with the arrow keys to make larger movements. • The most direct means of navigation is with your mouse. • Scroll bars are provided and work as they do in all Windows applications.

10. Navigation keystrokes

11. The Active Cell

12. Developing a Worksheet • Determine the worksheet’s purpose. • Enter the data and formulas. • Test the worksheet and make any necessary edits / corrections. • Document the worksheet and improve appearance. • Save and print the complete worksheet.

13. Entering Data into a Worksheet • To enter data, first make the cell in which you want to enter the data active by clicking it. • Enter the data (text, formulas, dates, etc.) into the active cell. • Use the Alt+Enter key combination to enter text on multiple lines within the same cell. • Use TAB key, arrow keys, or ENTER key to navigate among the cells.

14. Entering Data into a Worksheet

15. Entering Formulas • A formula is a mathematical expression that calculates a value. • In Excel, formulas always begin with an equal sign (=). • A formula can consist of one or more arithmetic operators. • The order of precedence is a set of predefined rules that Excel follows to calculate a formula.

16. Resize worksheet rows and columns • There are a number of methods for altering row height and column width using the mouse or menus: • Click the dividing line on the column or row, and drag the dividing line to change the width of the column or height of the row • Double-click the border of a column heading, and the column will increase in width to match the length of the longest entry in the column • Widths are expressed either in terms of the number of characters or the number of screen pixels.

17. Resize a column

18. Identify cell ranges • A group of worksheet cells is known as a cell range, or range. • Working with ranges in a worksheet makes working with the data easier. • Ranges can be adjacent or nonadjacent. • An adjacent range is a single, rectangular block of cells • Select an adjacent range by clicking on a cell and dragging to an opposite corner of a rectangle of cells • A nonadjacent range is comprised of two or more adjacent ranges that are not contiguous to each other • To select a nonadjacent range, begin by selecting an adjacent range, then press and hold down the Ctrl key as you select other adjacent ranges

19. Select and move worksheet cells • To select a large area of cells, select the first cell in the range, press and hold the Shift key, and then click the last cell in the range. • Once you have selected a range of cells, you may move the cells within the worksheet by clicking and dragging the selection from its current location to its new one. • By pressing and holding the Ctrl key as you drag, Excel will leave the original selection in its place and paste a copy of the selection in the new location. • To move between workbooks, use the Alt key while dragging the selection.

20. Adjacent and nonadjacent ranges

21. Range selection techniques

22. Moving selected cell ranges

23. Insert worksheet rows and columns • You can insert one or many additional rows or columns within a worksheet with just a few steps using the mouse or menu options. • You can insert individual cells within a row or column and then choose how to displace the existing cells. • You can click the Insert menu and then select row or column, or right click on a row or column heading or a selection of cells and then choose Insert from the shortcut menu.

24. Delete worksheet rows and columns • To delete and clear cells, rows, or columns, you can use the Edit menu, or right click on a heading or a selection of cells and choose Delete from the shortcut menu. • Clearing, as opposed to deleting, does not alter the structure of the worksheet or shift uncleared data cells. • What can be confusing about this process is that you can use the Delete key to clear cells, but it does not remove them from the worksheet as you might expect.

25. The Insert dialog box

26. Use the Undo and Redo features • Editing is an intrinsic task in any document, and especially useful are the Undo and Redo actions. • The Undo feature allows you to sequentially back up to a certain action, such as a delete, a move, an entry, etc. and allows you to reverse those actions. • Redo allows you to reapply actions one step at a time that you have previously undone.

27. Insert, move, and rename worksheets • Worksheets are much like pages within a book; you peruse through them like you flip the pages of a book. • There are several ways to move, copy and work with worksheets. • Right click on the sheet tab and choose Move or Copy. Select a new position in the workbook for the worksheet or click the Create a copy checkbox and Excel will paste a copy of that worksheet in the workbook. • The same shortcut menu for the sheet tab also gives you the option to insert, delete or rename a worksheet.

28. Use Excel’s functions • You can easily calculate the sum of a large number of cells by using a function. • A function is a predefined, or built-in, formula for a commonly used calculation. • Each Excel function has a name and syntax. • The syntax specifies the order in which you must enter the different parts of the function and the location in which you must insert commas, parentheses, and other punctuation • Arguments are numbers, text, or cell references used by the function to calculate a value • Some arguments are optional

29. Work with the Insert Function button • Excel supplies more than 350 functions organized into 10 categories: • Database, Date and Time, Engineering, Financial, Information, Logical, Lookup, Math, Text and Data, and Statistical functions • You can use the Insert Function button on the Formula bar to select from a list of functions. • A series of dialog boxes will assist you in filling in the arguments of the function and this process also enforces the use of proper syntax.

30. Math and Statistical functions

31. Define functions, and functions within functions • The SUM function is a very commonly used math function in Excel. • A basic formula example to add up a small number of cells is =A1+A2+A3+A4, but that method would be cumbersome if there were 100 cells to add up. • Use Excel's SUM function to total the values in a range of cells like this: SUM(A1:A100). • You can also use functions within functions. Consider the expression =ROUND(AVERAGE(A1:A100),1). • This expression would first compute the average of all the values from cell A1 through A100 and then round that result to 1 digit to the right of the decimal point

32. Copy and paste formulas and functions • Copying and pasting a cell or range of cells is a simple, but highly effective means for quickly filling out a large worksheet. • To copy and paste a cell or range: • Select the cell or range to be copied and then click the Copy button on the standard toolbar • Select the cell or range into which you want to copy the selection and then click the Paste button on the standard toolbar • Once you are finished pasting, press the Esc key to deselect the selection

33. Copy and paste effects on cell references • Copied formulas or functions that have cell references are adjusted for the target cell or range of cells. • For example, if cell G5 contains the formula =F5*B5/B7, and you copy and paste this formula to cell G6, the formula in cell G6 will be =F6*B6/B8. • This may or may not be correct for your worksheet, depending upon what you are trying to do. • You can control this automatic adjusting of cell references through the use of relative and absolute references.

34. Problems using copy and paste with formulas • When Excel does not have enough room to display an entire value in a cell, it uses a string of these # symbols to represent that value.

35. Use relative references • A relative reference is a cell reference that shifts when you copy it to a new location on a worksheet. • A relative reference changes in relation to the change of location. • If you copy a formula to a cell three rows down and five columns to the right, a relative reference to cell B5 in the source cell would become G8 in the destination cell.

36. Use absolute references • An absolute reference is a cell reference that does not change when you copy the formula to a new location. • To create an absolute reference, you preface the column and row designations with a dollar sign ($). • For example, the absolute reference for B5 would be $B$5. • This cell reference would stay the same no matter where you copied the formula.

37. Use mixed references • A mixed reference combines both relative and absolute cell references. • You can effectively lock either the row or the column in a mixed reference. • For example, in the case of $B5, the row reference would shift, but the column reference would not • In the case of B$5, the column reference would shift, but the row reference would not • You can switch between absolute, relative and mixed references in the formula easily in the edit mode or on the formula bar by selecting the cell reference in your formula and then pressing the F4 key repeatedly to toggle through the reference options.

38. Open the Insert Function dialog box • To get help from Excel to insert a function, first click the cell in which you wish to insert the function. • Click the Insert Function button. This action will open the Insert Function dialog box. • If you do not see the Insert Function button, you may need to select the appropriate toolbar or add the button to an existing toolbar.

39. Examine the Insert Function dialog box

40. Insert Function helpful tips • In the Insert Function dialog box you can type in a description of what you would like to do in the Search for a function text box and then click the Go button, and Excel will come up with some suggestions for you. • You may also select a category from the drop-down box, or choose All, which will list every function in Excel alphabetically. • Once you make your selection and click the OK button, another dialog box shows you all the arguments for the function. • The arguments shown in boldfaced type are required • While the cursor is in an argument's text box, you can either enter a value or click a cell and that cell will appear in the text box, saving you the trouble of typing it

41. The Average Function • The average function is necessary to calculate the average of a range of cells. • Like any other formula, the average function may be copied across cells.

42. Use Excel's Auto Fill features • When you need to copy and paste a large number of rows or columns, you can use a technique called Auto Fill using the fill handle. • The fill handle is a small black square located in the lower-right corner of a selected cell or range. • When you drag the fill handle, Excel automatically fills in the formulas and formats used in the selected cells. • The same rules for relative, absolute, and mixed references apply for Auto Fill as for copy and paste.

43. Auto Fill features • The Fill behavior depends on the date(s) with which you start. • If you select two or more dates separated by a month, then Excel will increment the auto filled dates by a month as well. • If you select only one date, Excel will increment the dates by one day only. • When you drag the fill handle, a small Auto Fill Options button appears to the lower right of the selected cell or range. • Common options are Copy Cells, Fill Series, Fill Formatting Only and Fill Without Formatting • If you are using the Auto Fill technique for dates, you have additional options to either Fill Days, Fill Weekdays, Fill Months or Fill Years.

44. Using Auto Fill • To use the Auto Fill feature, select the cell range that contains the values and/or formulas you want to copy. • Click and drag the fill handle in the direction you want to copy and then release the mouse button. • If needed, click the Auto Fill Options button, and then select the Auto Fill option you want to apply to the selected range.

45. An example of Auto Fill

46. Auto Fill Options button menu options • The Copy Cells option will copy all values and formulas into the selected range as well as the formats used to display those values and formulas. • The Fill Formatting Only option copies only the formats used to display values or formulas without copying the values and formulas themselves. • The Fill Without Formatting option copies only the values and formulas without any of the formats used in the source range.

47. Date Functions

48. Excel's date functions • Excel stores dates as integers, where the integer value represents the number of days since January 1, 1900. • For example, the integer value for the date January 1, 2008 is 39448 because that date is 39,448 days after January 1, 1900 • You typically do not see these numbers, because Excel automatically formats them to appear in a date format. • This method of storing dates allows you to work with dates the same way you work with numbers. • Excel's commonly used date functions are DATE, DAY, MONTH, NOW, TODAY, WEEKDAY and YEAR.

49. The TODAY and Now functions • The TODAY and NOW functions always display the current date and time. • You will not normally see the time portion unless you have formatted the cell to display it. • If you use the TODAY or NOW function in a cell, the date in the cell is updated to reflect the current date and time of your computer each time you open the workbook.

50. Use a formula to enter the date