JULY/AUGUST 2011 Article

Excel Tips and Tricks: A Shortcut for Function Arguments and Selecting a Region

Question: When I start typing a formula, such as an IF formula, I forget what the required information might be for the formula. Is there any way to quickly see what's required for my formula?

Answer: Yes, by using a keyboard shortcut.

Process (Excel 2003 and 2007):

After using a formula in Excel a few times, you can become familiar enough with the syntax (i.e. the way the formula is structured) that you can type the formula directly into a cell. A good tip while you're becoming more adept at using this method is to use the Fx button, which is to the immediate left of the formula bar (shown below):

This will bring up a window that allows you to select the function you'd like to use, then provides a handy interface which lets you insert the values needed to make the formula work. Below is a picture of the 'Function Arguments' window for the IF function:

This window makes it very easy to add the parameters needed, and Excel will build the formula in the background. Occasionally, when we are hand typing a formula, we may get stuck on the structure of the formula, and seeing this window would be a great help. Here's how to bring it up with one, simple, keyboard shortcut. We'll be using a table of test scores, then setting up an IF function to check to see whether the score is a pass or fail:

1.  Select cell B2 and type in =IF. Do not press Enter at this time.

2.  Press CTRL + A.

This shortcut will automatically bring up the 'Function Arguments' window for the formula you started typing, in this instance the IF function.

3.  In the Logical_test box, type in A2>=75 and press the Tab key, which shifts you to the next field. This formula will check to see whether the score is equal to or greater than 74.

4.  In the Value_if_true field, type in the word Pass and then press the Tab key. If the number in cell A2 is indeed equal to or greater than 75, this is the value that will be returned. You'll note that the word Pass is automatically put inside quotation marks to show that the response should be in text format.

5.  In the Value_if_false field, type in the word Fail, then click the OK button.

6.  Cell B2 now returns the result Fail, as cell A2 is 42. You can now double-click on the AutoFill handle (highlighted below)...

... which will copy this formula down to cell B15.

Question: Is there a quick way to select an entire table in a spreadsheet?

Answer: Yes, using keyboard shortcuts.

Process (Excel 2003 and 2007):

When you want to apply settings to a range of information, or even selecting the range as a source for a chart or pivot table, people generally highlight the entire table of information with their mouse.

In the graphic above, people would typically select cell A1, then while holding their left mouse button, drag down to cell H22. This tip will cover two very quick methods that will save you time when selecting large amounts of data.

Method 1:

7.  Select any cell in the range of data to be used.

8.  Press CTRL + *. NB: if you do not have a keypad on the right hand side of your keyboard, you will need to press CTRL + SHIFT + 8 as the * on the number 8 key can only be accessed by using the SHIFT key.

This will select all of the data in the table, finishing its selection when it reaches the first empty row and first empty column.

Method 2:

1.  Select any cell in the range of data to be used.

2.  Press the F5 key. This will bring up the Go To window.

3.  Click on the Special… button in the bottom left hand corner.

4.  In the Select window that appears, click on the Current region radio button, then click OK.

The entire region of data is now selected.

If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip Of The Week.

This Excel Tip is brought to you by Sage ERP MAS Intelligence, for powerful Excel-based business reporting. Find out more here.

 

Next: NEW! Rave Reviews for Sage FAS Fixed Assets 2012