15 Excel Tips on Improving Productivity Using Excel

To format a number as SSN, use the custom format code “000-00-0000?… To format a phone number, use the custom format code “000-000-0000?… To show values after decimal point only when number is less than one, use _($#,##0.00_);_($#,##0_) as formatting code… To remove grid lines from excel worksheet, go to menu > tools > options > and un-check grid lines option. (Excel 2007: office button > excel option > advanced)… To hide a worksheet, go to menu > format…

Read More

20 Tips in How to Solve Common problems using Excel Formulas

To get the first name of a person, use =left(name,find(” “,name)-1) To calculate mortgage payments, use =PMT(interest-rate,number-of-payments,how-much-loan) To get nth largest number in a range, use =large(range,n)… To get nth smallest number in a range, use = small(range,n)… To generate a random phone number, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier… To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,””))… To count positive values in a range, use =countif(range,”>0?)… To calculate…

Read More

25 Very Useful Keyboard Shortcuts for Excel

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…

Read More