Monday 30 June 2014

Why hiding cells isn’t a secure option

By , 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.

Wednesday 11 June 2014

COUNTIFS to count on multiple criteria

By , 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.