By Neil Shorney, 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.
Friday, 31 January 2014
Thursday, 23 January 2014
A “copy-and-paste” formula to restrict decimal places with Data Validation
By Neil Shorney, 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.
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 Neil Shorney, 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.
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.
Subscribe to:
Posts (Atom)