Useful Tips for Popular Programs

In this new regular section we aim to provide some handy hints and tips on commonly used business programs starting in this issue with two simple, but rarely exploited, Excel features:

Vertical copying

Copying vertically can be performed with a double click, provided there is information in an adjacent column.

The stock database, in the worksheet below, contains over 300 stock records.

The formula in cell F2 (which calculates stock valuation by cost price) needs to be copied down for each stock record.

An adjacent column (E) has a sales price for each stock record, so it meets the rules for using double click.

Point your mouse onto the bottom right hand edge of the cell and you should see a black + (like the example below).



Double click and the formula will be copied down until the first blank row in the adjacent column.

Concatenation

Concatenation simply means linking.

In the context of Excel, it means that it is possible to link a cell containing a formula with some text. For example if cell M30 contains the value 63459.987 then the formula: =“The balance at the end of the period is ” &M30

Gives the result: The balance at the end of the period is 63459.987

Using the Dollar command as follows: =“The balance at the end of the period is ” &dollar(M30,0)

Gives the result: The balance at the end of the period is £63,459

More text can be continued after the formula by using another &: =“The balance at the end of the period is ” &dollar(M30,0)& “ which is above budget”

Which looks like this: The balance at the end of the period is £63,459 which is above budget