Excel Tips and Tricks Getting Started ---PART I

Hi friends I am listing few excel tricks and tips for you please go through it and have fun!!!!!!!

Very Useful Keyboard Shortcuts

1. To format any selected object, press ctrl+1
2. To insert current date, press ctrl+;
3. To insert current time, press ctrl+shift+;
4. To repeat last action, press F4
5. To edit a cell comment, press shift + F2
6. To autosum selected cells, press alt + =
7. To see the suggest drop-down in a cell, press alt + down arrow
8. To enter multiple lines in a cell, press alt+enter
9. To insert a new sheet, press shift + F11
10. To edit active cell, press F2 (places cursor in the end)
11. To hide current row, press ctrl+9
12. To hide current column, press ctrl+0
13. To unhide rows in selected range, press ctrl+shift+9
14. To unhide columns in selected range, press ctrl+shift+0
15. To recalculate formulas, press F9
16. To select data in current region, press ctrl+shift+8
17. To see formulas in the worksheet, press ctrl+shift+` (ctrl+~)
18. While editing formulas to change the reference type from absolute to relative vice versa, press F4
19. To format a number as currency, press ctrl+shift+4 (ctrl+$)
20. To apply outline border around selected cells, press ctrl+shift+7
21. To open the macros dialog box, press alt+F8
22. To copy value from above cell, press ctrl+’
23. To format current cell with comma formats, press ctrl+shift+1
24. To go to the next worksheet, press ctrl+shift+pg down
25. To go to the previous worksheet, press ctrl+shift+pg up

Very Useful Formulas

1. To get the first name of a person, use =left(name,find(” “,name)-1)
2. To calculate mortgage payments, use =PMT(interest-rate,number-of-payments,how-much-loan)
3. To get nth largest number in a range, use =large(range,n)…
4. To get nth smallest number in a range, use = small(range,n)… 
5. To generate a random phone number, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier… 
6. To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,”"))…
7. To count positive values in a range, use =countif(range,”>0″)… 
8. To calculate weighted average, use SUMPRODUCT() function
9. To remove unnecessary spaces, use =trim(text)
10. To format a number as SSN using formulas, use =text(ssn-text,”000-00-0000″)… 
11. To find age of a person based on DOB, use =TEXT((NOW()-birth_date)&”",”yy “”years”" m “”months”" dd “”days”"”), output will be like 27 years 7 months 29 days
12. To get name from initials from a name, use IF(), FIND(), LEN() and SUBSTITUTE() formulas… 
13. To get proper fraction from a number (for eg 1/3 from 6/18), use =text(fraction, “?/?”)
14. To get partial matches in vlookup, use * operator like this: =vlookup(“abc*”,lookup_range,return_column)
15. To simulate averageif() in earlier versions of excel, use =sumif(range, criteria)/countif(range, criteria)
16. To debug your formulas, select the portions of formula and press F9 to see the result of that portion…
17. To get the file extension from a file name, use =right(filename,3) (doesn’t work for files that have weird extensions like .docx, .htaccess etc.)
18. To quickly insert an in cell micro-chart, use REPT() function…
19. COUNT() only counts number of cells with numbers in them, if you want to count number of cells with anything in them, use COUNTA()
20. Using named ranges in formulas saves you a lot of time. To define one, just select some cells, and go to menu > insert > named ranges > define

Converting xls or cvs Files to Excel 2010 
In order to manipulate large quantities of data, you must first convert xls or cvs files to Excel 2010.
(PeopleSoft and Data Warehouse use this format to export.)
1. In the upper left-hand corner, click File.

2. From the Info tab, click Convert.
Note: The Convert button will only appear if you need to convert the document to a new
format.
A Microsoft Excel dialog box will appear.

3. Click OK.
The document is now replaced with an xlsx document.

Freezing the Top Row of an Excel Worksheet 
It is helpful to freeze the top row of your Excel document in order to view the column headings without 
scrolling to the top of the document. 
1. Click somewhere in your data. 

2. From the View tab, select Freeze Panes. 
3. If you want to freeze the panes above a certain cell of your document, click Freeze Panes. 
OR 
If you want to freeze the top row of the document, click Freeze Top Row. 

Hiding and Deleting Columns 

This can be helpful if you want to hide a column so that you do not have to work around it. 

1. Select the desired column by clicking on the letter of the column. 

 

2. Right-click in the blue area. 

 

3. If you want to hide the column, click Hide. 

OR 

If you want to delete the column, click Delete. 

Notes: If you want to hide multiple columns, highlight one column, and drag the blue 

highlighted area over the other column(s) you wish to hide. 

If you delete the column, you will be unable to retrieve it. 

 

4. If you want to unhide a column: 

a. Highlight the column letters surrounding the hidden column. 

b. Right-click and select Unhide. 

Filtering Data 

It is important to filter unnecessary data in your columns so that you can better view the information in 

your document. 

1. Click anywhere in the column you wish to filter.  

 

2. From the Data tab, click Filter. 

 

 

3. Click the down arrow icon in the heading of the column you wish to filter. 

 

 

 

4. Select the box next to Select All. 

 

5. Select the item you wish to view. 

 

6. Click OK. 

Notes: You will now only view the items in your data under that subject heading. 

You can put filters on multiple columns by repeating this process on the desired columns. 

To clear filters: 

1. Click the down arrow icon in the heading of the column with the filter you wish to clear. 

 

2. Click Clear Filters. 

OR 

From the Data tab, click Clear. 

Please feel free to add your tips and tricks also or sent me mail....