Archive for the ‘ms-excel’ Category

Excel Short Cuts

Posted: January 16, 2010 in ms-excel

25 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

Next in this I’ll publish some common data situtations I have faced and I guess will be useful for all.

All your valuable comments and suggestions are priceless. Share knowledge and spread wisdom……

Keep reading..:)

Advertisements

Macros are very powerful tool in Excel. One can create all logic and funda in them and use them repeatedly. But when it comes to distributing a macro, its a real pain. Recently I came across that same problem when one of my colleague asked me to create a function to spell the amount in words, also he wanted the this funtion could be distributed easily to other clients also. Working on the problem I came across Excel Add in.

An Excel Add-In is a file (usually with an .xla or .xll extension) that Excel can load when it starts up. This is a very convieneint and Infact meant for the purpose of distribution.

How to create: Open an Excel file, Press ALT + 11, this will open a VBA window as shown here

Excel Add In (.xla file)

Excel Add In (.xla file)

Put your code in the function and save it.  File>Save as \ Microsoft Excel Add-in (*.xla). Once saved and re-opened the Workbook will be hidden and can only be seen in the “Project Explorer” via the Visual Basic Editor. It is NOT hidden in the same way as the Personal.xls as this can be seen (and made visible) via Windows>Unhide.

Once completed users can easily install your Add-in like below

  • Save a copy to C:\WINDOWS\Application Data\Microsoft\AddIns\ . If not any location, just take note of it for step 4.
  • Open any Workbook.
  • On the Tools menu, point to Add-Ins and click Browse. Locate your add-in from where you saved it, select it and then click OK.
  • Ensure your add-in is in the Add-ins available: box and it is checked.
    Now click OK and the add-in is installed.

Yes You have done it. Voilla, Add In is installed and will be available to any excel you open now. You can use the function written in xla files (shown as above) like any other microsoft build in function.

Enjoy the Excel Power!!!! Please do write in case any addtions, suggestions and comments, these matters a lot to me 🙂