MS Excel 2003

1. MS Excel 2003 Office Management Tools Course for 3rd Monthly Exam By: MsSaimaGul

2. Formatting numbers by using the toolbar • The Formatting toolbar contains several buttons that let you quickly apply common number formats. When you click one of these buttons, the active cell takes on the specified number format. You also can select a range of cells (or even an entire row or column) before clicking these buttons. If you select more than one cell, Excel applies the number format to all the selected cells. Table on next slide summarizes the formats that these Formatting toolbar buttons perform.

3. Number-Formatting Buttons on the Formatting Toolbar

4. Changing Document Appearance

5. Formatting Text • You can format your text using the Font tab of the Format Cell dialog box.

6. Formatting Numbers • You can format your numbers using the Number tab of the Format Cell dialog box. • Or use the icons on the Formatting toolbar.

7. Manipulating Data • The Alignment tab of the Format Cells dialog gives you great control over how your text is aligned and orientated.

8. Formatting with Colours and Patterns • You can customize your spreadsheets by changing the Font Colour or by adding a Fill Colour or Pattern. (Right click -> Format Cells -> Patterns)

9. Adding and Editing Borders • The Border tab of the Format Cell dialog box provides many options to customise your borders.

10. Using AutoFormat • Excel 2003 has many pre-defined table styles to help you format your table of information quickly. (Format -> Auto Format)

11. Using Page Setup • You can use the Page Setup dialog to customize the printing of your Spreadsheet. (File -> Page Setup)

12. Margins • Use the Margin tab of the Page Setup dialog box to define margins and centre data on the printed page.

13. Headers and Footers • Use the Header/Footer tab to add standard or custom Header and Footer.

14. Entering and Editing Worksheet Data

15. Understanding the Types of Data You Can Use • An Excel workbook can hold any number of worksheets, and each worksheet is made up of a large number of cells. A cell can hold any of three basic types of data: • Numerical values • Text • Formulas • A worksheet also can hold charts, drawings, pictures, buttons, and other objects. These objects are not contained in cells. Rather, they reside on the worksheet’s draw layer, which is an invisible layer on top of each worksheet.

16. Forcing text to appear on a new line within a cell • If you have lengthy text in a cell, you can force Excel to display it in multiple lines within the cell. Use Alt+Enter to start a new line in a cell.

17. Practice “Conditional Formatting” yourself

18. Entering numbers with fractions • To enter a fraction into a cell, leave a space between the whole number and the fraction. For example, to enter 6 ¾, enter 6 3/4, and then press Enter. When you select the cell, 6.75 appears in the Formula bar, and the cell entry appears as a fraction. • If you have a fraction only (for example, ¼), you must enter a zero first, like this: 0 1/4—otherwise Excel will likely assume that you are entering a date. When you select the cell and look at the Formula bar, you see 0.25. In the cell, you see ¼.

19. Hiding and unhiding a worksheet • In some situations, you may want to hide one or more worksheets. Hiding a sheet may be useful if you don’t want others to see it, or if you just want to get it out of the way. When a sheet is hidden, its sheet tab is also hidden. At least one sheet must remain visible. (You can’t hide all the sheets in a workbook.) • To hide a worksheet, choose Format➪Sheet➪Hide. The active worksheet (or selected worksheets) will be hidden from view.

20. Keeping the titles in view by freezing panes • If you set up a worksheet with row or column headings, it’s easy to lose track of just where you are when you scroll to a different location in the worksheet. Excel provides a handy solution to this problem: freezing panes. This keeps the headings visible while you are scrolling through the worksheet. • To freeze panes, start by moving the cell pointer to the cell below the row that you want to remain visible as you scroll and to the right of the column that you want to remain visible as you scroll. Then, select Window➪Freeze Panes. Excel inserts dark lines to indicate the frozen rows and columns. You’ll find that the frozen row and column remain visible as you scroll throughout the worksheet. To remove the frozen panes, select Window➪Unfreeze Panes.

21. Monitoring cells with a Watch Window • In some situations, you may want to keep track of the value in a particular cell. As you scroll throughout the worksheet, that cell may disappear from view. Using a Watch Window can help. • The Watch Window is actually a special type of toolbar. To display the Watch Window toolbar, choose View➪Toolbars➪Watch Window. Then click Add Watch and specify the cell that you want to watch. The Watch Window will display the value in that cell. You can add any number of cells to the Watch Window, and you can move the toolbar to a convenient location.

22. Pasting in special ways • To control what is copied into the destination range, you use the Edit➪Paste Special command—a much more versatile version of the Edit➪Paste command. This dialog box has several options, which are explained in the following list. • All: Equivalent to using the Edit➪Paste command. It copies the cell’s contents, formats, and data validation from the Windows Clipboard. • Formulas: Only formulas contained in the source range are copied. • Values: Copies the results of formulas. • Formats: Copies only the formatting. • Comments: Copies only the cell comments from a cell or range. This option doesn’t copy cell contents or formatting.

23. Pasting in special ways (Contd.) • All except borders: Copies everything except any borders that appear in the source range. • Column widths: Copies column width information from one column to another. • Formulas and number formats: Copies all formulas and numeric formats, but no values. • Values and number formats: Copies all current values and numeric formats, but not the formulas themselves.

24. Creating range names in your workbooks • To create a range name, start by selecting the cell or range that you want to name. Then, click in name box, type a name there, and press Enter. • The Name box is a drop-down list and shows all names in the workbook. To choose a named cell or range, click the Name box and choose the name. The name appears in the Name box, and Excel selects the named cell or range in the worksheet.

25. Deleting names • If you no longer need a defined name, you can delete it. Deleting a range name does not delete information in the range. • To remove a range name, choose Insert➪Name➪Define to display the Define Name dialog box. Choose the name that you want to delete from the list and then click the Delete button. • If you delete the rows or columns that contain named cells or ranges, the names contain an invalid reference. For example, if cell A1 on Sheet1 is named Interest and you delete row 1 or column A, Interest then refers to =Sheet1!#REF! (that is, to an erroneous reference).

26. Adding Comments to Cells • To add a comment to a cell, select the cell and then choose Insert➪Comment (or press Shift+F2). Excel inserts a comment that points to the active cell. Initially, the comment consists of your name. Enter the text for the cell comment and then click anywhere in the worksheet to hide the comment. You can change the size of the comment by clicking and dragging any of its borders. • Cells that have a comment attached display a small red triangle in the upper-right corner. When you move the mouse pointer over a cell that contains a comment, the comment becomes visible. • If you want all cell comments to be visible (regardless of the location of the cell pointer), select View➪Comments. This command is a toggle; select it again to hide all cell comments. To edit a comment, activate the cell, right-click, and then choose Edit Comment from the shortcut menu. • To delete a cell comment, activate the cell that contains the comment, right-click, and then choose Delete Comment from the shortcut menu.

27. Creating Charts

28. To create an embedded chart • Select the cells to provide data for the chart. • On the Standard toolbar, click the Chart Wizard button. • In the Chart type section, click the desired chart type; and then, in the Chart subtype section, click the desired subtype. • Click Next to move to the next wizard page. • Verify that the axis and data series names are correct. • Click Next to move to the next wizard page. • In the Chart title box, type the name of the chart and then press D. • Type names for the chart title and axes in the boxes provided, and then click Next. • Click Finish to accept the default choice to create the chart as part of the active worksheet.

29. To change a chart’s background • Right-click anywhere in the Chart Area of the chart, and then, from the shortcut menu that appears, click Format Chart Area. • In the Area section of the Format Chart Area dialog box, click the Fill Effects button. • Click the Texture tab to display the Texture tab page. • Click the desired texture. • Click OK twice to close the Fill Effects dialog box and the Format Chart Area dialog box.

30. To customize chart labels • Double-click the chart label to be customized. • Use the controls in the dialog box that appears to customize the chart label. • To change the text of a chart label, click the label and edit it in the text box that appears. To customize chart number formats • Double-click the axis of the chart with the numbers to be customized. • In the Format Axis dialog box that appears, click the Number tab. • Use the controls on the Number tab page to format the chart numbers. • Click OK.

31. Charts

32. Charts

33. Charts

34. Charts

35. Functions and Formulas

36. Purpose • One important task you can perform in Excel is to calculate totals for the values in a series of related cells. You can also use Excel to find out other information about the data you select, such as the maximum or minimum value in a group of cells. • Regardless of your bookkeeping needs, Excel gives you the ability to find the information you want. And if you should make an error, you can find the cause and correct it quickly. • Excel makes it easy to reference a number of cells at once, letting you define your calculations quickly.

37. Creating Formulas to Calculate Values • Once you’ve added your data to a worksheet and defined ranges to simplify data references, you can create a formula, or an expression that performs calculations on your data. • To write an Excel formula, you begin the cell’s contents with an equal sign—when Excel sees it, it knows that the expression following it should be interpreted as a calculation and not text. After the equal sign, you type the formula. • For instance, you can find the sum of the numbers in cells C2 and C3 using the formula =C2+C3. • After you have entered a formula into a cell, you can revise it by clicking the cell and then editing the formula in the formula bar.

38. Formulas Contd. • To create a new calculation, you click Function on the Insert menu. The Insert Function dialog box appears, with a list of functions, or predefined formulas, from which you can choose.

39. Finding and Correcting Errors in Calculations • Excel makes it easy to find the source of errors in your formulas by identifying the cells used in a given calculation and describing any errors that have occurred. The process of examining a worksheet for errors in formulas is referred to as auditing. • Excel identifies errors in several ways. The first way is to fill the cell holding the formula generating the error with an error code. • When a cell with an erroneous formula is the active cell, an Error button appears next to it. You can click the button’s down arrow to display a menu with options that provide information about the error and offer to help you fix it.

40. Error Codes

41. Entering and Editing Formulas • Entering a new formula into a worksheet appears to be a straightforward process: • Select the cell in which you want to enter the formula. • Type an equals sign (=) to tell Excel that you’re entering a formula. • Type the formula’s operands and operators. • Press Enter to confirm the formula. • Excel divides formulas into four groups: arithmetic, comparison, text, and reference.

42. Using Arithmetic Formulas • Arithmetic formulas are by far the most common type of formula. They combine numbers, cell addresses, and function results with mathematical operators to perform calculations. • It consists of operators like addition (+), subtraction (-), multiplication (*), division (/), percentage (%), exponentiation (^).

43. Using Comparison Formulas • A comparison formula is a statement that compares two or more numbers, text strings, cell contents, or function results. • If the statement is true, the result of the formula is given the logical value TRUE (which is equivalent to any nonzero value). If the statement is false, the formula returns the logical value FALSE (which is equivalent to 0)

44. Using Text Formulas • A text formula is a formula that returns text. • Text formulas use the ampersand (&) operator to work with text cells, text strings enclosed in quotation marks, and text function results. • One way to use text formulas is to concatenate text strings. For example, if you enter the formula =“soft"&"ware" into a cell, Excel displays software. • You also can use & to combine cells that contain text. For example, if A1 contains the text Ben and A2 contains Jerry, entering the formula =A1&" and " &A2 returns Ben and Jerry.

45. Using Reference Formulas • The reference operators combine two cell references or ranges to create a single joint reference.

46. Operator Precedence • 3 ^ (15/5) * 2 – 5 • 3 ^ ((15/5) * 2 – 5) • 3 ^ (15 / (5 * 2 – 5 ))

47. Understanding Relative Reference Format • When you use a cell reference in a formula, Excel looks at the cell address relative to the location of the formula. • For example, suppose that you have the formula =A1*2 in cell A3. To Excel, this formula says, “Multiply the contents of the cell two rows above this one by 2.” This is called the relative reference format, and it’s the default format for Excel. • This means that if you copy this formula to cell A4, the relative reference is still “Multiply the contents of the cell two rows above this one by 2,” but the formula changes to =A2*2 because A2 is two rows above A4. • This way of handling copy operations will save you incredible amounts of time when you’re building your worksheet models.

48. Understanding Absolute Reference Format • When you refer to a cell in a formula using the absolute reference format, Excel uses the physical address of the cell. • You tell the program that you want to use an absolute reference by placing dollar signs ($) before the row and column of the cell address. • Talking about the old example, Excel interprets the formula =$A$1*2 as “Multiply the contents of cell A1 by 2.” • No matter where you copy or move this formula, the cell reference doesn’t change. The cell address is said to be anchored.

49. Functions • Excel has various function categories, including the following: • Text • Logical • Information • Lookup and reference • Date and time • Math and trigonometry • Statistical • Financial • Database and table

50. Typing a Function into a Formula • Whether you use a function on its own or as part of a larger formula, here are a few rules and guidelines to follow: • You can enter the function name in either uppercase or lowercase letters. Excel always converts function names to uppercase. • Always enclose function arguments in parentheses. • Always separate multiple arguments with commas. (You might want to add a space after each comma to make the function more readable. Excel ignores the extra spaces.) • You can use a function as an argument for another function. This is called nesting functions. For example, the function AVERAGE(SUM(A1:A10), SUM(B1:B15)) sums two columns of numbers and returns the average of the two sums