Microsoft Excel

Shortcuts
We will begin by looking at our trainers’ most favourite shortcuts in Microsoft Excel 2007 / 2010.  The term shortcuts in relation to Excel in this section refers to the use of either Keyboard shortcuts in Excel (i.e. the use of the keyboard to access functions and perform actions which would otherwise take longer using the mouse) or the use of the Function Keys (The Keys at the top of the Keyboard F1 – F12) to perform shortcuts in Microsoft Excel.

1. Hold down the CTRL and the ¬ Key (This is the key to the left of the number 1 key) - to show or hide all formulas on your worksheet
   
   This tip allows you to reveal all the formulas in every cell on your worksheet at once, which helps you identify which cells contain formulas, what the specific formula is, and allows you to see patterns in lots of adjacent formulas.
   
   
2. Press SHIFT F11 to insert a new sheet within an excel workbook
   
   (NOTE: make sure you press SHIFT F11 and not CTRL F11, as CTRL F11 will insert a Macro worksheet which will not work properly)
   
   
3. Select all of your data and press F11 to produce an instant chart
   
   (NOTE: You may want to sort your data before you select it. Do this by clicking the SORT button on the Ribbon)
   
   
4. Press F2 to edit a cell
   
   This will allow you to edit say a formula without having to type it from scratch or the need to click into the formula bar
   
   
5. Press F9 to recalculate formulas
   
   Your spreadsheet is set to recalculate every time you enter new data or edit a cell, however pressing F9 will recalculate the whole spreadsheet instantly
   
   
6. Press CTRL and the TAB key to move between worksheets
   
   
7. Press CTRL and the * key (use the * on the number pad or CTRL SHFT 8) to highlight a complete range of data (i.e. a data table)
   
   
8. To hide/unhide a row(s):
   
   -To hide a row(s), select a cell(s) and press CTRL 9.
   -To unhide a row(s), select the cells containing the range of the hidden row(s) and press CTRL SHIFT (
   
   To hide/unhide a column(s):
   
   -To hide a column(s), select a cell(s) and press CTRL 0.
   -To unhide a column(s), select the cells containing the range of the hidden column(s) and press CTRL SHIFT )
   

Tips
Now we will look at our trainers’ favourite tips in Microsoft Excel.  These are generally useful bits of advice which allow you to access hidden function in Excel or simply work out solutions to problems which are commonly found when using Microsoft Excel.

• To edit a cell double click into the middle of the cell

• Right click on the Sheet navigation tabs to display a menu of all sheets in a workbook
  
  This tip allows you to see a list of all your sheets in the open Workbook.
  

• After selecting a group of cells containing numbers, look at the status bar (at the bottom of the screen) for a running total of whatever you highlight (i.e. SUM)
  
  You may have already noticed this, you can however change the way this displays from Summing numbers to Counting cells, giving you the Minimum or Maximum of the range of cells selected or displaying the Average. To do this, after selecting a group of cells, right click on the displayed Sum at the bottom right of the page and choose a different function: Sum, Average, Min, Max, Count.

• To Zoom in and out of your spreadsheet (i.e. increasing the screen magnification) hold down the CTRL key and roll your mouse wheel forward (to increase magnification) or backwards (to decrease magnification)
  
  

Formula Specific Tips
The shortcuts and tips above relate to the general use of Microsoft Excel 2007 / 2010.  Here we have included a handful of the formula specific tips which our trainers have selected as the most frequently asked for.

Date & Time

1. Age: To work out someone’s age given their Date of Birth (DOB) the following formula can be used:
   
   =INT((TODAY()-A1)/365.25)
   
   Where their DOB is in cell A1.  
   
   This formula can now be copied down to calculate ages on a whole range of DOBs if you have a list of people’s DOBs.
   
   
   
2. Converting Time to Decimal: To convert a list of times (formatted to the time format e.g. 14:35) to a simple number, the following formula can be used:
   
   =(A1-INT(A1))*24 
   
   where A1 is a cell containing the time.
   

Text

1. Use the ampersand (&) symbol to join two text cells together:
   
   =A1&B1
   
   where there is some text in cells A1 and B1
   
     (TIP: use a space inside “ “ to put a space in-between the word(s)):
   
   =A1&” “&B1

2. Use the following formula to extract someone’s first name from a cell containing their whole name:
   
   =LEFT(A1,FIND(“ “,A1))
   
   where their whole name is in Cell A1, put a space in-between the “ “
   
   
3. Use the following formula to extract someone’s second name from a cell containing their whole name:
   
   =RIGHT(A1,LEN(A1)-FIND(“ “,A1))
   
   where their whole name is in Cell A1, put a space in between the “ “

 

 

 

Microsoft Word

1. Type =rand() then press enter to display a large chunk of text
   
   
2. Select some text and hold down the CTRL and SHFT and press < or >, to decrease or increase the size of text visually
   
   
3. When Word autocorrects text (i.e. ½ or 1st ) press Undo to cancel the autocorrect
   
   
4. When in a bulleted or numbered list, hold the SHIFT key and press ENTER to insert a soft line break (allowing you to insert another paragraph within the same bullet or number)
   
   
5. Right click on the small book on the status bar (at the bottom of the screen) to quickly hide spelling errors (i.e. the red wavy line). Works with grammar also
   
   
6. Press CTRL END to jump to the end of your document
   
   
7. Press F4 to repeat the last action

 

 

 

Microsoft Outlook

1. When addressing an e-mail, type the persons name (any part) and press CTRL K to confirm the name
   
   
2. When sending an e-mail press CTRL and ENTER to send the mail quickly
   
   
3. When composing a new mail choose View, Bcc Field to show an address bar which hides anyone's name from anyone else the e-mail is addressed to
   
   
4. Right click on an e-mail in the inbox and choose Mark as Unread to remind you to read the message

 

Miscellaneous

1. In Internet Explorer- simply type any word in your address bar, then press CTRL and ENTER to add the www. and .com automatically
   
   
2. Press F12 in any office document for Save As...
   
   
3. Hold the Windows key down and press E to quickly open windows explorer
   
   
4. Hold the Widows key down and Press D to quickly return to your desktop