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!
Tuesday, 8 July 2014
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.
Friday, 4 April 2014
ROUND functions
By Neil Shorney, Director, Naturally Sales Ltd.
Excel has 3 options for rounding numbers: ROUND, ROUNDUP and ROUNDDOWN. Unlike rounding by formatting cells, the ROUND functions actually change the number in the cell rather than masking them with formatting.
They are all constructed in the same way: ROUND(cell_ref, number_of_decimal_places); i.e. =ROUND(A1,2) would round the value in cell A1 to 2 decimal places.
ROUND: This will round values to the nearest value, either up or down.
ROUNDUP: This will always round upwards.
ROUNDDOWN: This will always round downwards.
Excel has 3 options for rounding numbers: ROUND, ROUNDUP and ROUNDDOWN. Unlike rounding by formatting cells, the ROUND functions actually change the number in the cell rather than masking them with formatting.
They are all constructed in the same way: ROUND(cell_ref, number_of_decimal_places); i.e. =ROUND(A1,2) would round the value in cell A1 to 2 decimal places.
ROUND: This will round values to the nearest value, either up or down.
ROUNDUP: This will always round upwards.
ROUNDDOWN: This will always round downwards.
Thursday, 13 March 2014
Formatting a chart axis
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!
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, 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.
Subscribe to:
Posts (Atom)