Excel - The CONCATENATE function to add text etc. to values in a cell

In an earlier post (http://computertipsforbeginners.blogspot.com/2011/10/starting-with-formulae-in-excel-sum.html) I had started off with the simple SUM function. Now  for a function which is not so frequently used, but which I like. When you just give the sum total, it not only looks bland,  it is also meaningless unless there is say “Total” typed in the adjacent cell. So why not get the word total, or some other explanatory words you like, in the same cell? It is really quite easy. Let’s start with a simple line in a simple exercise. Say you have a group of six students who have spent for eats and beverages. Enter it in Excel in this form starting from cell A1:

Name of student	Eats	Beverages
Asha	7	9
Beni	8	8
Chandra	7	7
Deepak	4	5
Esha	10	10
Farida	9	10
Govind	10	9

 Now suppose you want to give the amount spent in Eats by the group. The formula =SUM(B2:B8) gives the total amount of 55 spent on Eats. To get the sentence My students spent 55 on Eats in cell A9, type =CONCATENATE("My students spent ", SUM(B2:B8), " on ", B1).

The following are to be remembered:

1.       Two functions have been simultaneously used here. As in any other formula, the number of opening brackets must equal the number of closing brackets. In fact a set of brackets is used per function.

2.       The text that is required is typed within inverted commas, exactly as is needed – spelling, Capitals etc. are to taken care of.

3.       To get space before and after 55, the space forms part of the text within the inverted commas.

4.       Note the commas between each set i.e. between the text My students spent and SUM(B2:B8), then between SUM(B2:B8) and  on etc.

5.       B1 refers to the contents or value of the cell B1 hence does not appear within inverted commas. In place of " on ", B1  a " on Eats" can be used. This is simpler but is not suggested as changes become difficult. Thus in case you subsequently find that the expenses were not on Eats and Beverages, but on Lunch and Dinner just change the value (contents) in cells B1 and B2 and the formula will automatically adjust the contents of cell  A9.

The Concatenate function joins text, numbers, cell values, or a combination of these. Going further, this function will be found really useful as familiarity with formulae increases.

Starting with formulae in Excel – the SUM function

One of the beauties of Excel is how easy it is to do simple calculations. If you have not used any formula at all in the past, do refresh your memory on the address of cells and the simple use of “+” (plus) – click here to view my earlier post on this - http://computertipsforbeginners.blogspot.com/2011/06/what-are-spreadsheets-in-simple-terms.html.

Now the bit about  =A1+A2  works with all addresses and any number of cells as also with all the usual mathematical formula i.e. wherever you want the result to appear or be displayed, you can use say =C7*D5+F11-G67, or whatever you want to multiply (* is used for multiply), add, subtract, divide etc. Frequently however you need to add up a long column – imagine typing out =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 .... going on and on till you reach say A75 or wherever you wanted to reach. So the first formula to start with is SUM. All you need to do is type =SUM(a1:a75) and it is done.  Quick and fast was it not?

While using formulae, the essentials to be remembered are:

1.       All formula must start with the equal to sign i.e. with  =

2.       The word SUM or whatever function you use has to be typed exactly, no change whatsoever allowed in its spelling and no extra spaces anywhere.

3.       Make sure that the cell where you are tying the formula does not feature within the range i.e. you CANNOT type this formula in any of the cells lying between A1 and A75 – if you do the system will go into a loop and will hang.

4.       In any formula, the number of opening brackets or ”(“   MUST, absolutely must, equal the number of closing brackets “)”.

5.       The cell address a75 is the same as A75 i.e. don’t bother about using capitals; Excel will automatically do it for you.

6.       If you copy this formula (CTRL+C followed by paste - Ctrl+V) into say one adjacent cell on the right, Excel will automatically convert the formula into one cells on the right .i.e. it will become =SUM(b1:b75). To avoid this, read further on in the post.

7.       See the space just above the spread sheet – next to the “fx”. Yyou will see the formula bar where the formula is also visible – it is a good idea to type here, especially when making corrections.

The software automatically or by default, changes formulae in reference to the cell to which it is copied. Now say you had typed =C7*D5+F11-G67 in cell G18. When you copy it in say cell M25, it will become =I14*J12+L18-M74  i.e. each cell has changed position of the row and the column so as to retain the same relative position to the destination cell M25.This is usually an excellent thing, which you will find as you go along. But if you do not want this to happen, you need to tell the software so, and this is very easily done by using the “$” sign. By way of example, take the cell at F11 and follow these rules

1.       C7*D5+F11-G67  changes all the cells as indicated above to I14*J12+L18-M74  

2.       C7*D5+$F11-G67  fixes the position F and changes all the others, the value in M25 then becomes  I14*J12+F18-M74  (or rather I14*J12+$F18-M74)  

3.       C7*D5+F$11-G67  fixes the position 11 and changes all the others, the value in M25 then becomes  I14*J12+L11-M74  (or rather I14*J12+L$11-M74)  

4.       C7*D5+$F$11-G67   fixes the both the positions F and 11 and changes all the others, the value in M25 then becomes I14*J12+F11-M74 (or rather I14*J12+$F$11-M74).

The dollar sign can be used to fix any position of any cell. Incidentally, to add the dollar sign, place the cursor on F11 In the formula and play around with the F4 key on the keyboard.