By Neil Shorney, Director, Naturally Sales Ltd.
The problem: you're creating a chart, and you don't want the axis to start from zero.
The solution: left-click on the axis you want to modify, then right-click and choose "Format axis". You'll then see a box to state the maximum value of the axis, and one to set the minimum value, and can show exactly what you want to!
Thursday, 13 March 2014
Thursday, 27 February 2014
An alternative to the FIND function
By Neil Shorney, Director, Naturally Sales Ltd.
Today, we'll look at a very function: SEARCH. The crucial difference, is that FIND is case-sensitive whereas SEARCH isn't. The structure is the same as FIND, so it works like this:
=SEARCH(find_text,within_text)
or
=SEARCH("t",A1)
If A1 contains the word "Date", this will return a "3", as "t" is the third character in the word "date".
So remember: they do the same thing, but FIND is case-sensitive and SEARCH isn't.
Today, we'll look at a very function: SEARCH. The crucial difference, is that FIND is case-sensitive whereas SEARCH isn't. The structure is the same as FIND, so it works like this:
=SEARCH(find_text,within_text)
or
=SEARCH("t",A1)
If A1 contains the word "Date", this will return a "3", as "t" is the third character in the word "date".
So remember: they do the same thing, but FIND is case-sensitive and SEARCH isn't.
Friday, 31 January 2014
Finishing a formula
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.
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 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.
Monday, 23 December 2013
Making a line break within a cell
By Neil Shorney, Director, Naturally Sales Ltd.
Have you ever wondered how to add a line break within a cell? That is, how to make a paragraph, rather than just one long line of text? You’ve tried pressing <Enter> but it always moves you down to the cell below? Well, today is your lucky day!
To create a line break within a cell, use <CTRL> and <ENTER>, and you can create paragraphs to your heart’s content!
Have you ever wondered how to add a line break within a cell? That is, how to make a paragraph, rather than just one long line of text? You’ve tried pressing <Enter> but it always moves you down to the cell below? Well, today is your lucky day!
To create a line break within a cell, use <CTRL> and <ENTER>, and you can create paragraphs to your heart’s content!
Monday, 9 December 2013
Using the SUMPRODUCT function to sum multiple criteria / advanced cell references
By Neil Shorney, Director, Naturally Sales Ltd.
We guide you through creating a two-dimensional table to display information from large data sets.
In the first part, we’ll familiarize ourselves with a useful advanced lookup formula: SUMPRODUCT, then we’ll learn how we can save time populating tables by using a combination of absolute and relative references.
These are best looked at in an example, so you’ll find details in a file which you can download just here.
www.nsales.co.uk/excel-examples/Populating-a-table-with-absolute-and-relative-references-using-SUMPRODUCT.xlsx
We guide you through creating a two-dimensional table to display information from large data sets.
In the first part, we’ll familiarize ourselves with a useful advanced lookup formula: SUMPRODUCT, then we’ll learn how we can save time populating tables by using a combination of absolute and relative references.
These are best looked at in an example, so you’ll find details in a file which you can download just here.
www.nsales.co.uk/excel-examples/Populating-a-table-with-absolute-and-relative-references-using-SUMPRODUCT.xlsx
Subscribe to:
Posts (Atom)