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.**
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.
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**!
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!
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.
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.
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.
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.

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!
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.
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.
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.
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.