Tips for Making MS Excel 2007 More Efficient

Microsoft Excel 2007 / 2010 Tips and Tricks: How to get the most out of Microsoft Excel 2007 / 2010

This tips and tricks article has been produced by the expert trainers at iTrain Scotland with a specific aim of giving you an insight into the remarkable number of shortcuts which are available in Microsoft Excel 2007 / 2010. These shortcuts, tips and tricks will allow you as a user of Microsoft Excel to squeeze so much more from the software, empowering you to become far more productive, efficient and smarter when you are working with Microsoft Excel 2007 /2010.

                  

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 article 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 worksheetThis 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 “ “

Download in PDF MS EXCEL TIPS AND TRICKS