By Neil Shorney, Director, Naturally Sales Ltd.
Most of us use the “Format Cells” dialogue on an almost daily basis – we reach it by right-clicking on the cell (or cells) we want to format and selecting it from the list.
I don’t know about you, but this always seems to me to take a little too long. Fear not, because there is a keyboard short-cut to take us straight to this dialogue: CTRL+1.
Friday, 26 September 2014
Monday, 18 August 2014
Picking data from a list
By Neil Shorney, Director, Naturally Sales Ltd.
This week, we have a time-saving tip to help with data entry.
Imagine you’re entering data into a spreadsheet and you have to enter something into a cell which you’ve already typed further up the worksheet. As you may know, if you start typing, Excel will try to guess what you want to write. However, if you have many values beginning with the same letters, you sometimes have to type quite a lot before Excel guesses correctly.
An alternative is to pick the data you want from a list of previously-entered values. You can do this by pressing Alt+Down (arrow). You can then select the value you want, either using the mouse or by using the arrow keys followed by enter. What’s nice about this is that it presents the values in alphabetical order, so it’s much easier to find what you need to.
This week, we have a time-saving tip to help with data entry.
Imagine you’re entering data into a spreadsheet and you have to enter something into a cell which you’ve already typed further up the worksheet. As you may know, if you start typing, Excel will try to guess what you want to write. However, if you have many values beginning with the same letters, you sometimes have to type quite a lot before Excel guesses correctly.
An alternative is to pick the data you want from a list of previously-entered values. You can do this by pressing Alt+Down (arrow). You can then select the value you want, either using the mouse or by using the arrow keys followed by enter. What’s nice about this is that it presents the values in alphabetical order, so it’s much easier to find what you need to.
Tuesday, 5 August 2014
A quick keyboard shortcut
By Neil Shorney, Director, Naturally Sales Ltd.
We have a short-and-sweet Excel tip for you this week.
Have you discovered the keyboard shortcut to move between worksheets in a workbook?
CTRL + Page Down moves to the right, CTRL + Page Up moves to the left!
We have a short-and-sweet Excel tip for you this week.
Have you discovered the keyboard shortcut to move between worksheets in a workbook?
CTRL + Page Down moves to the right, CTRL + Page Up moves to the left!
Tuesday, 8 July 2014
Moving the formula helper
By Neil Shorney, Director, Naturally Sales Ltd.
We have a quick and easy Excel tip for you this week.
We often refer to the Formula Helper on our Excel courses – the little yellow box which pops up by the cell when you start to type a formula. However, you may find that it’s in the way of something you want to click as you construct a formula. Rather than having to type that part in manually, did you know that you can move the helper with the mouse? Just click and drag, and put it wherever you like!
We have a quick and easy Excel tip for you this week.
We often refer to the Formula Helper on our Excel courses – the little yellow box which pops up by the cell when you start to type a formula. However, you may find that it’s in the way of something you want to click as you construct a formula. Rather than having to type that part in manually, did you know that you can move the helper with the mouse? Just click and drag, and put it wherever you like!
Monday, 30 June 2014
Why hiding cells isn’t a secure option
By Neil Shorney, Director, Naturally Sales Ltd.
If you’re hoping to secure something in a spreadsheet by hiding rows or columns then protecting the sheet, think again, because with a little spreadsheet slight-of-hand, the content of the hidden cells can be revealed to anyone who wants to take a look, even without the password.
Just by entering the cell reference you want to see in the name box to the left of the formula bar, Excel will display the content of that cell in the formula bar, even if the row or column is hidden. To prevent this right-click on the cells you want to hide, choose the ‘Protection’ tab and click ‘Hidden’. Once you then protect the sheet with a password, the content will no longer be available by the above-mentioned method, thus keeping it secure.
If you’re hoping to secure something in a spreadsheet by hiding rows or columns then protecting the sheet, think again, because with a little spreadsheet slight-of-hand, the content of the hidden cells can be revealed to anyone who wants to take a look, even without the password.
Just by entering the cell reference you want to see in the name box to the left of the formula bar, Excel will display the content of that cell in the formula bar, even if the row or column is hidden. To prevent this right-click on the cells you want to hide, choose the ‘Protection’ tab and click ‘Hidden’. Once you then protect the sheet with a password, the content will no longer be available by the above-mentioned method, thus keeping it secure.
Wednesday, 11 June 2014
COUNTIFS to count on multiple criteria
By Neil Shorney, Director, Naturally Sales Ltd.
In this e-tip, we'll introduce you to the COUNTIFS function, which allows multiple criteria.
It works like this:
=COUNTIFS(range_1, criteria_1, range_2, criteria_2, etc...)
...where "range" is the range you'd like to analyse, and "criteria" is what you're looking for. For example, if A1:A5 had the following values:
USA, UK, Germany, USA, France
and B1:B5 had the following:
English, English, German, Spanish, French
...you could then enter this formula into C1:
=COUNTIFS(A:A,"USA",B:B,"English"), which would return a value of "1" because only in row 1 do both criteria match.
In this e-tip, we'll introduce you to the COUNTIFS function, which allows multiple criteria.
It works like this:
=COUNTIFS(range_1, criteria_1, range_2, criteria_2, etc...)
...where "range" is the range you'd like to analyse, and "criteria" is what you're looking for. For example, if A1:A5 had the following values:
USA, UK, Germany, USA, France
and B1:B5 had the following:
English, English, German, Spanish, French
...you could then enter this formula into C1:
=COUNTIFS(A:A,"USA",B:B,"English"), which would return a value of "1" because only in row 1 do both criteria match.
Wednesday, 14 May 2014
Using SUMIF to sum multiple criteria
By Neil Shorney, Director, Naturally Sales Ltd.
Last time we looked at the COUNTIFS function. This time, we take it a stage further to look at SUMIFS.
Just adding an “S” and making it SUMIFS allows us to sum multiple criteria. It works like this:
=SUMIFS(sum_range_1, criteria_range_1, criteria_1, criteria_range_2, criteria_2, …)
There is, however, a limitation, which is that you can’t refer to other workbooks unless they’re open at the time. For this, you’d need the SUMPRODUCT function, which is one of my favourites.
Last time we looked at the COUNTIFS function. This time, we take it a stage further to look at SUMIFS.
Just adding an “S” and making it SUMIFS allows us to sum multiple criteria. It works like this:
=SUMIFS(sum_range_1, criteria_range_1, criteria_1, criteria_range_2, criteria_2, …)
There is, however, a limitation, which is that you can’t refer to other workbooks unless they’re open at the time. For this, you’d need the SUMPRODUCT function, which is one of my favourites.
Subscribe to:
Posts (Atom)