1 ADVANCED FEATURES OF MS EXCEL(KNOWLEDGE & SKILLS) CA JAYANTI GOYAL

2 [email protected], [email protected]VLOOK UP Searches for a value in the leftmost column of a table and then, returns a value in the same row from a column you specify in the table. The value that is being looked up must be in the leftmost column of the table. CA JAYANTI GOYAL ,

3 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)CA JAYANTI GOYAL ,

4 [email protected], [email protected]HLOOKUP Searches for a value in the top row of a table, and then returns a value in the same column from a row you specify in the table or array. CA JAYANTI GOYAL ,

5 HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)CA JAYANTI GOYAL ,

6 [email protected], [email protected]Filter Short Cut key= ALT+D+F+F Enable filtering of the selected cells Once filtering is turned on, click the arrow in the column header to choose a filter for the column. CA JAYANTI GOYAL ,

7 [email protected], [email protected]Sort Data Sort the selected data in ascending and descending order, or custom sort as per requirement and specification. CA JAYANTI GOYAL ,

8 [email protected], [email protected]Text –to – Column Separate the contents of One excel cell into separate Columns. Splitting the text into columns at each comma, period, or other Character you specify. CA JAYANTI GOYAL ,

9 [email protected], [email protected]Remove Duplicates Delete duplicate rows from a sheet Specify which column should be checked for duplicate information. CA JAYANTI GOYAL ,

10 [email protected], [email protected]PIVOT TABLE Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. CA JAYANTI GOYAL ,

11 [email protected], [email protected]DATE Return the number of days from the ist jan 1900 to the entered date. =DATE(year,month ,day) CA JAYANTI GOYAL ,

12 [email protected], [email protected]TODAY Returns the serial number of Today’s Date =TODAY() NOW Returns the Serial number of Current date and time =NOW() CA JAYANTI GOYAL ,

13 [email protected], [email protected]WEEKDAY Returns the day of the week corresponding to serial number. =TEXT(WEEKDAY(range),”DDDD”) CA JAYANTI GOYAL ,

14 IF FUNCTION A Statement can be formed that will be used by Excel to branch in one of two directions. =if(Range>=50,”pass”,”fail”) Above means if test result>=50 then write Pass else write fail CA JAYANTI GOYAL ,

15 [email protected], [email protected]Short Cut Keys Ctrl+A Select All Ctrl+B,Ctrl+2 bold Ctrl+C copy Ctrl+D Fill Down Ctrl+F find Ctrl+G goto Ctrl+H replace Ctrl+I, Ctrl+3 italic Ctrl+K Insert Hyperlink CA JAYANTI GOYAL ,

16 [email protected], [email protected]Ctrl+N New Workbook Ctrl+O open Ctrl+P print Ctrl+R Fill Right Ctrl+S save Ctrl+U, Ctrl+4 underline Ctrl+V paste Ctrl W close Ctrl+X cut Ctrl+Y repeat Ctrl+Z Undo CA JAYANTI GOYAL

17 [email protected], [email protected]Ctrl+: Insert Current Time Ctrl+; Insert Current Date Ctrl+“ Copy Value from Cell Above Ctrl+’ Copy Formula from Cell Above Ctrl+Spacebar Select the entire column Shift+Spacebar Select the entire row CA JAYANTI GOYAL ,

18 [email protected], [email protected]CTRL+SHIFT+SPACEBAR Selects the entire worksheet. If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the entire worksheet. When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet CA JAYANTI GOYAL ,

19 Thank You!!!!!!!! Stay Connected Contact Info: CA JAYANTI GOYAL ,