Getting started With Excel Getting started With Excel.

  • Published on
    26-Mar-2015

  • View
    230

  • Download
    8

Transcript

  • Slide 1

Getting started With Excel Getting started With Excel Slide 2 Know your Worksheet Formatting Cells In Excel Go To Cells Quickly Select Cells Enter Data Edit a Cell Wrap Text Delete a Cell Entry Save a File Close Excel Slide 3 Working with Excel worksheet Hide / Unhide a sheet Rename a sheet Slide 4 Add color to sheet tabsConditional formatting Move or copy sheets Slide 5 Uncheck to hide gridlines Show or hide gridlines on a worksheet Change the color of gridlines Slide 6 Formatting Cells in Excel When you enter data into a cell in Excel, it is handled differently depending on what type of formatting you have assigned to the cell. For example, if you choose Currency formatting, Excel will automatically convert 3 into $3.00; if you choose Date formatting, Excel would convert 3/1 to March 1, 2009; and if you choose Percent formatting, Excel would change 0.3 to 30%. All of these choices are available when you highlight/select the cell(s) you want to format, then go to the Format menu and choose Cells, then click on the Number tab in the Format Cells dialog box. There is also a choice named Special and when you choose that, you can select formats like Social Security Number, Phone Number, etc. But, what if you want a format that is not included in Excel's pre-made formats? Well, you can make your own custom format, following the directions below. Type the number 123456789 in an empty cell and hit Enter. Now click back onto that cell and go to the Format Menu and select Cells. Click on the Number tab and select Custom from the list. In the box on the left, see all the different custom formats Excel offers. There are formats in here for most of the styles you would get if you chose one of the options for any of the choices in the list on the left. For example, in the list on the right, there is a custom format that is simply an @ symbol. If you choose this, it simply formats your cell as text. I recommend you look through the list and try some of them and see how they affect the number 123456789 that you have typed in your cell. Since there are so many, I can't possibly go through all of them with you. But, what I do want to do is teach you how to make some of your own. Slide 7 Go To Cells Quickly The following are shortcuts for moving quickly from one cell in a worksheet to a cell in a different part of the worksheet. EXERCISE 2 Go to -- F5 The F5 function key is the "Go To" key. If you press the F5 key, you are prompted for the cell to which you wish to go. Enter the cell address, and the cursor jumps to that cell. Press F5. The Go To dialog box opens. Type J3 in the Reference field. Press Enter. Excel moves to cell J3. Go to -- Ctrl+G You can also use Ctrl+G to go to a specific cell. Hold down the Ctrl key while you press "g" (Ctrl+g). The Go To dialog box opens. Type C4 in the Reference field. Press Enter. Excel moves to cell C4. The Name Box You can also use the Name box to go to a specific cell. Just type the cell you want to go to in the Name box and then press Enter. Type B10 in the Name box. Press Enter. Excel moves to cell B10. Slide 8 Select Cells If you wish to perform a function on a group of cells, you must first select those cells by highlighting them. The exercises that follow teach you how to select. EXERCISE 3 Select Cells To select cells A1 to E1: 1.Go to cell A1. 2.Press the F8 key. This anchors the cursor. 3.Note that "Extend Selection" appears on the Status bar in the lower-left corner of the window. You are in the Extend mode. 4.Click in cell E7. Excel highlights cells A1 to E7. 5.Press Esc and click anywhere on the worksheet to clear the highlighting. Alternative Method: Select Cells by Dragging You can also select an area by holding down the left mouse button and dragging the mouse over the area. In addition, you can select noncontiguous areas of the worksheet by doing the following: Go to cell A1. Hold down the Ctrl key. You won't release it until step 9. Holding down the Ctrl key enables you to select noncontiguous areas of the worksheet. Press the left mouse button. While holding down the left mouse button, use the mouse to move from cell A1 to C5. Continue to hold down the Ctrl key, but release the left mouse button. Using the mouse, place the cursor in cell D7. Press the left mouse button. While holding down the left mouse button, move to cell F10. Release the left mouse button. Release the Ctrl key. Cells A1 to C5 and cells D7 to F10 are selected. Press Esc and click anywhere on the worksheet to remove the highlighting. Slide 9 Enter Data In this section, you will learn how to enter data into your worksheet. First, place the cursor in the cell in which you want to start entering data. Type some data, and then press Enter. If you need to delete, press the Backspace key to delete one character at a time. EXERCISE 4 Enter Data 1.Place the cursor in cell A1. 2.Type John Jordan. Do not press Enter at this time. Delete Data The Backspace key erases one character at a time. 1.Press the Backspace key until Jordan is erased. 2.Press Enter. The name "John" appears in cell A1. Slide 10 Edit a Cell After you enter data into a cell, you can edit the data by pressing F2 while you are in the cell you wish to edit. EXERCISE 5 Edit a Cell Change "John" to "Jones." Move to cell A1. Press F2. Use the Backspace key to delete the "n" and the "h." Type nes. Press Enter. Alternate Method: Editing a Cell by Using the Formula Bar You can also edit the cell by using the Formula bar. You change "Jones" to "Joker" in the following exercise. 1. Move the cursor to cell A1. 2. Click in the formula area of the Formula bar. 3. Use the backspace key to erase the "s," "e," and "n." 4. Type ker. 5. Press Enter. Slide 11 Wrap Text When you type text that is too long to fit in the cell, the text overlaps the next cell. If you do not want it to overlap the next cell, you can wrap the text. EXERCISE 6 Wrap Text 1.Move to cell A2. 2.Type Text too long to fit. 3.Press Enter. 1.Return to cell A2. 2.Choose the Home tab. 3.Click the Wrap Text button 4. Excel wraps the text in the cell. Slide 12 Delete a Cell Entry To delete an entry in a cell or a group of cells, you place the cursor in the cell or select the group of cells and press Delete. EXERCISE 7 Delete a Cell Entry Select cells A1 to A2. Press the Delete key. Save a File This is the end of Lesson1. To save your file: Click the Office button. A menu appears. Click Save. The Save As dialog box appears. Go to the directory in which you want to save your file. Type Lesson1 in the File Name field. Click Save. Excel saves your file. Close Excel Close Microsoft Excel. Click the Office button. A menu appears. Click Close. Excel closes. Slide 13 Calculating Data with Advanced Formulas Calculating Data with Advanced Formulas Slide 14 NAMES When entering formulae or referring to any area on the spreadsheet, it is usual to refer to a "range". For example, B6 is a range reference; B6:B10 is also a range reference. Defining Names There are a number of ways to set up names on a spreadsheet. A common way is to use the Insert, Name, Define menu. In the example, there is a range of sales figures that could be named 1st_Qtr; Selection of cells for naming Slide 15 To name range: i. Select the cells you wish to name. ii. Click the DEFINE NAME button in the NAMES GROUP in the insert tab iii. The DEFINE NAME dialog box appears iv. To name the cells, simply type a name in the Name box and choose OK. To manage names: The Define Name Dialog box also lets you manage the range names you can add delete or modify the existing range name in the same dialog box. Slide 16 Go To The GOTO feature can be used to go to a specific cell address on the spreadsheet. It can also be used in conjunction with names. i. Press [F5]. The following dialog box appears; ii. Click on the name required, then choose OK. Using Names Not only does the cell pointer move to the correct range, but it also selects it. This can be very useful for checking that ranges have been defined correctly, and also for listing all the names on the spreadsheet. Slide 17 Names In Formulae Names can be used in any simple formula, as well as any of Excel's built in functions. Instead of typing cell references or selecting cells, simply type the name or paste the name into the formula. Slide 18 Excel Functions CONDITIONAL & LOGICAL FUNCTIONS If Statements Logical Test Value If True / False Nested If AND, OR, NOT And Or Not LOOKUP FUNCTIONS Lookup Hlookup Vlookup OTHER USEFUL FUNCTIONS ISERROR TRIM IFERRORNOW COUNTIFRIGHT AVERAGEIFLEFT SUMIFFind & Replace CONCATENATE Slide 19 Absolute Cell Addressing Before learning Excel formula we will study about absolute cell referencing in excel. In Excel, a reference to a particular cell or group of cells that does not change, even if you change the shape or size of the spreadsheet, or copy the reference to another cell. For example, the cell reference "$A$3" is an absolute cell reference that always points to the cell in the first column and third row. In contrast, the reference "A3" is a relative cell reference that initially points to the cell in the first column and third row, but may change if you copy the reference to another cell.Absolute cell references are particularly useful for referencing constant values (i.e., values that never change). 1.Move to cell C12. 2.Type =. 3.Click cell C9. 4.Press F4. Dollar signs appear before the C and the 9. 5.Type +. 6.Click cell C10. 7.Press F4. Dollar signs appear before the C and the 10. 8.Type +. 9.Click cell C11. 10.Press F4. Dollar signs appear before the C and the 11. 11.Click the check mark on the formula bar. Excel records the formula in cell C12. Slide 20 CONDITIONAL & LOGICAL FUNCTIONS Excel has a number of logical functions which allow you to set various "conditions" and have data respond to them. For example, you may only want a certain calculation performed or piece of text displayed if certain conditions are met. The functions used to produce this type of analysis are found in the Insert, Function menu, under the heading LOGICAL. Slide 21Greater than < Less than > = Greater than or equal to < = Less than or equal to = Equal to Not equal to"> If Statements The IF function is used to analyze data, test whether or not it meets certain conditions and then act upon its decision. the IF statement is accompanied by three arguments enclosed in one set of parentheses; the condition to be met (logical_test); the action to be performed if that condition is true (value_if_true); the action to be performed if false (value_if_false). Each of these is separated by a comma, as shown; =IF ( logical_test, value_if_true, value_if_false) Logical Test This part of the IF statement is the "condition", or test. You may want to test to see if a cell is a certain value, or to compare two cells. In these cases, symbols called LOGICAL OPERATORS are useful; > Greater than < Less than > = Greater than or equal to < = Less than or equal to = Equal to Not equal to Slide 22 1000,"GOOD",IF(B2$E$2,"GOOD",IF(B2100,B2>100),B1+B2,"Figures not high enough")"> And This function is a logical test to see if all conditions are true. If this is the case, the value "TRUE" is returned. If any of the arguments in the AND statement are found to be false, the whole statement produces the value "FALSE". This function is particularly useful as a check to make sure that all conditions you set are met. Arguments are entered in the AND statement in parentheses, separated by commas, and there is a maximum of 30 arguments to one AND statement. The following example checks that two cells, B1 and B2, are both greater than 100. =AND(B1>100,B2>100) If either one of these two cells contains a value less than a hundred, the result of the AND statement is "FALSE. This can now be wrapped inside an IF function to produce a more meaningful result. You may want to add the two figures together if they are over 100, or display a message indicating that they are not high enough. =IF(AND(B1>100,B2>100),B1+B2,"Figures not high enough") Slide 25 100,B2>100),"at least one is OK","Figures not high enough") In the above formula, only one of the numbers in cells B1 and B2 has to be over 100 in order for them to be added together. The message only appears if neither figure is high enough."> Or This function is a logical test to see if one or more conditions are true. If this is the case, the value "TRUE" is returned. If just one of the arguments in the OR statement is found to be true, the whole statement produces the value "TRUE". Only when all arguments are false will the value "FALSE" be returned. This function is particularly useful as a check to make sure that at least one of the conditions you set is met. =IF(OR(B1>100,B2>100),"at least one is OK","Figures not high enough") In the above formula, only one of the numbers in cells B1 and B2 has to be over 100 in order for them to be added together. The message only appears if neither figure is high enough. Slide 26 Not NOT checks to see if the argument is false. If so, the value "TRUE" is returned. It is best to use NOT as "provided this is not the case" function. In other words, so long as the argument is false, the overall statement is true. In the example, the cell contents of B1 are returned unless the number 13 is encountered. If B1 is found to contain 13, the message "Unlucky!" is displayed; =IF(NOT(B1=13),B1,"Unlucky!") The NOT function can only contain one argument. If it is necessary to check that more than one argument is false, the OR function should be used and the true and false values of the IF statement reversed. Suppose, for example, a check is done against the numbers 13 and 666; =IF(OR(B1=13,B1=666),"Unlucky!",B1) Slide 27 LOOKUP FUNCTIONS These functions allow you to create formulae which examine large amounts of data and find information which matches or approximates to certain conditions. They are simpler to construct than nested IFs and can produce many more varied results. Slide 28 Lookup The syntax for LOOKUP is as follows; =LOOKUP( lookup_value, lookup_vector, result_vector ) number or text entry to look for area in which to search for the lookup_value adjacent row or column where the corresponding value or text is to be found It is essential that data in the lookup vector is placed in ascending order, i.e. numbers from lowest to highest, text from A to Z. If this is not done, the LOOKUP function may return the wrong result. Slide 29 In the diagram, column D contains varying salaries, against which there is a company car in column E which corresponds to each salary. For example, a 20030 salary gets a Golf, a 35000 salary gets a Scorpio. A LOOKUP formula can be used to return whatever car is appropriate to a salary figure that is entered. In this case, the lookup_value is the cell where the salary is entered (B13), the lookup_vector is the salary column (D3:D11), and the result_vector is the car column (E3:E11). Hence the...