Friday 26 September 2014

A keyboard short-cut to open “Format Cells”

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

Monday 18 August 2014

Picking data from a list

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

Tuesday 5 August 2014

A quick keyboard shortcut

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

Tuesday 8 July 2014

Moving the formula helper

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

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.

Wednesday 14 May 2014

Using SUMIF to sum multiple criteria

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

Friday 4 April 2014

ROUND functions

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


Thursday 13 March 2014

Formatting a chart axis

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

Thursday 27 February 2014

An alternative to the FIND function

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

Friday 31 January 2014

Finishing a formula

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

Thursday 23 January 2014

A “copy-and-paste” formula to restrict decimal places with Data Validation

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

Thursday 16 January 2014

Choosing a macro keyboard shortcut

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