Difference between revisions of "MS Excel tips and tricks"
m (I created this page to contain our collective knowledge about how to work most effectively with MS Excel) |
m |
||
| Line 3: | Line 3: | ||
Even with tons of help files to Microsoft Excel it is still nice to keep a page with tips and tricks accumulated over the years. Everyone are more than welcome to add their favorite Excel tips and tricks on this page. | Even with tons of help files to Microsoft Excel it is still nice to keep a page with tips and tricks accumulated over the years. Everyone are more than welcome to add their favorite Excel tips and tricks on this page. | ||
| − | == List of useful shortcut keys == | + | == List of useful shortcut keys == |
| − | Some shortcut keys can really speedup the way you are working with Excel, below is a list of some very useful Excel shortcut keys. | + | Some shortcut keys can really speedup the way you are working with Excel, below is a list of some very useful Excel shortcut keys. |
{| border="1" class="CGTable" | {| border="1" class="CGTable" | ||
|- | |- | ||
! Key combination | ! Key combination | ||
| − | ! Description | + | ! Description |
! Version | ! Version | ||
|- | |- | ||
| − | | F2 | + | | F2 |
| − | | Edit the contents of a cell | + | | Edit the contents of a cell |
| All | | All | ||
|- | |- | ||
| − | | Ctrl + | + | | Ctrl + |
| − | | Add cells, columns or rows, depending on selection | + | | Add cells, columns or rows, depending on selection |
| All | | All | ||
|- | |- | ||
| − | | Ctrl - | + | | Ctrl - |
| − | | Delete cells, columns, or rows, depending on selection | + | | Delete cells, columns, or rows, depending on selection |
| All | | All | ||
|- | |- | ||
| − | | Ctrl L | + | | Ctrl L |
| − | | Convert an area to a table, Excel will suggest the area used if only one cell is selected | + | | Convert an area to a table, Excel will suggest the area used if only one cell is selected |
| 2007 | | 2007 | ||
|- | |- | ||
| − | | Ctrl C | + | | Ctrl C |
| − | | Copy selected cells | + | | Copy selected cells |
| All | | All | ||
|- | |- | ||
| − | | Ctrl | + | | Ctrl Alt V |
| − | | This will bring up the paste special menu, which allows you to paste as values, formulas, with formatting intact etc. | + | | This will bring up the paste special menu, which allows you to paste as values, formulas, with formatting intact etc. |
| All | | All | ||
|- | |- | ||
| − | | Ctrl Page Up | + | | Ctrl Page Up |
| − | | Move to the previous sheet in the workbook | + | | Move to the previous sheet in the workbook |
| All | | All | ||
|- | |- | ||
| − | | Ctrl Page Down | + | | Ctrl Page Down |
| − | | Move to the next sheet in the workbook | + | | Move to the next sheet in the workbook |
| All | | All | ||
|- | |- | ||
| − | | Ctrl H | + | | Ctrl H |
| − | | Search and replace dialog will be shown | + | | Search and replace dialog will be shown |
| All | | All | ||
|- | |- | ||
| − | | Ctrl F | + | | Ctrl F |
| − | | Search dialog will be shown | + | | Search dialog will be shown |
| All | | All | ||
|} | |} | ||
Revision as of 11:31, 18 December 2009
Contents
Introduction
Even with tons of help files to Microsoft Excel it is still nice to keep a page with tips and tricks accumulated over the years. Everyone are more than welcome to add their favorite Excel tips and tricks on this page.
List of useful shortcut keys
Some shortcut keys can really speedup the way you are working with Excel, below is a list of some very useful Excel shortcut keys.
| Key combination | Description | Version |
|---|---|---|
| F2 | Edit the contents of a cell | All |
| Ctrl + | Add cells, columns or rows, depending on selection | All |
| Ctrl - | Delete cells, columns, or rows, depending on selection | All |
| Ctrl L | Convert an area to a table, Excel will suggest the area used if only one cell is selected | 2007 |
| Ctrl C | Copy selected cells | All |
| Ctrl Alt V | This will bring up the paste special menu, which allows you to paste as values, formulas, with formatting intact etc. | All |
| Ctrl Page Up | Move to the previous sheet in the workbook | All |
| Ctrl Page Down | Move to the next sheet in the workbook | All |
| Ctrl H | Search and replace dialog will be shown | All |
| Ctrl F | Search dialog will be shown | All |
Tips and tricks
A fast way to convert text to numbers
If you have a cell, column, or row containing numbers represented as text, then here is a very fast way to convert text to number:
- Select the cells you want to convert and copy them using Ctrl C
- Open your favorite text editor (TextPad, UltraEdit, or Notepad++ to mention a few) and press Ctrl V
- Notice how the values from the cells appear tab separated in the text document
- Press Ctrl A to select the contents of the text document and Ctrl C to copy
- Return to Excel, place the cell marker in the top left corner of your selected area and press Ctrl V
- Any number that was previously represented as text will not appear as numbers
Notice that decimal numbers might cause some troubles depending on the localization you are using.
