A keyboard short-cut to open “Format Cells”

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.

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.

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!

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!

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.

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.

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.

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.

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!

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.

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.

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.

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.