MS-Excel Manual-2

1. MS-Excel Manual-2 PradeepVelugoti LakshmanTallam

2. Auto Fill Options • Type in the month name “January” in any cell say A1. • Now drag the fill handle to the right to select the range (Do not release the mouse button). • Release the mouse button to create a month name series. • In the similar way we can do it for autofilling of numbers.

5. Cell Reference • Relative cell reference… • The reference to cell changes relative to where a formula or function is placed. • Eg. If the formula =A5+ B5 is moved down one row it will change to =A6+B6

6. Relative cell reference:

7. Cell Reference • Absolute cell reference. • Suppose that the cell $C$16 is used in a formula. • =A9*$C$16 If this formula is copied two rows down it would be =A11*$C$16 The references preceded by the $ do not change.

8. Cell Ref. Continue • A lot of the things you type into Excel can change. For example, if you type "=B6+C6" into D6, you can easily copy that equation all the way down the page. If you do, however, most of the time the equation will change to "=B7+C7" on the 7th line and "=B8+C8" on the 8th. If you want to keep the equation B6 or D6 without changing, you put a $ in front of the 6. Similarly, you might be copying over, so maybe the letters might change to accommodate your new equation. If you want the letter to stay the same, you put the $ in front of it. Finally, if you don't want the cell value to change, regardless of where you end up putting it or what you end up doing with it, you use both $.

9. Absolute cell reference Fixed column value B4 for all rows Fixed column value B4 for all rows

10. B4 is fixed B4 is fixed

11. VLOOKUP() • The V in VLOOKUP stands for vertical. • It Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. • Syntax • VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

12. Lookup_value  The value to search in the first column of the table array. Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value. • Table_array  Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

13. Col_index_num  The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. • Range_lookup  A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match.

19. Conditional Statements • You can select between two alternative tasks by evaluating a condition. The evaluation must evaluate to True or False. • Click on Formulas tab and then choose the Insert Function button. • Select the IF function and write the logical statements as shown in the screenshots.

22. IF function Nested: • We can use IF as an argument within another IF function. • Suppose we want to provide remark to each grade specifically we do that by mentioning the remark independently for each grade like this: • A=Outstanding, B=Good, C=Bad, D=Worst • Following slide explains how we can achieve this.

23. IF function Loop: IF LOOP IF function loop format Nested If

24. =IF(I4="A","outstanding",IF(I4="B","Good",IF(I4="C","bad",IF(I4="D","Worst")))) means • IF condition I4 =“A” is true • Outstanding will be print False(if the above condition is false check the following) IF I4=“B” is true Good is print False(if above condition is false check) IF I4=“C” is true Bad is print False(if above condition is false ) print WORST

25. Freezing Rows and Columns • We can freeze any number of rows or columns in Excel. • So that when we scroll vertically or horizontally , particular row or column remains at the same position in the spreadsheet.