Friday 31 January 2014

Finishing a formula

By , Director, Naturally Sales Ltd.

When you've created a formula, don't use the mouse to select another cell - depending on how you've created it, that can alter the formula you've just made. Instead, press Enter to move to the cell below, or Tab to move to the cell on the right. To move in the opposite direction, use Shift+Tab or Shift+Enter.

We look at this in more detail in Introduction to Microsoft Excel.

Thursday 23 January 2014

A “copy-and-paste” formula to restrict decimal places with Data Validation

By , Director, Naturally Sales Ltd.

Sometimes you need a formula in Excel for a specific task, which you don’t really need to understand, you just need to use it. Someone asked me recently for just such a formula.

This person wanted to use Data Validation to restrict the number of decimal places which a user can enter into a cell.

We can do this as follows:

1. On the “Data” tab, click “Data Validation”
2. In the “Allow” drop-down, choose “Custom”
3. Enter the following formula in the “Formula” box: =OR(IF(ISERROR(FIND(".",$A1)),LEN($A1)>0,LEN(MID($A1,FIND(".",$A1)+1,25))<=X))
4. Change the “X” before the final closing brackets to the number of decimals you want to allow
5. Change any reference to "A1" to the cell you're working with
6. Press “OK”

You can then copy this down a column to a whole range if necessary.

It doesn’t matter how this works (I can explain if anyone desperately needs to know) but it does the job and can be used in many Data Validation situations.

Thursday 16 January 2014

Choosing a macro keyboard shortcut

By , Director, Naturally Sales Ltd.

When you are creating macros and assigning shortcut keys, care is needed that the key chosen doesn’t perform another function in Excel, for example CTRL+P is Print, CTRL+C is Copy. 

I find that CTRL+G is a safe bet, or CTRL+L, but did you know that you can expand your range of available keys by including Shift. When you type the key into the box, press Shift at the same time, and you’ll see this indicated. This allows a much wider choice of shortcut keys to execute your macro.