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.  

What are spreadsheets in simple terms? What is Excel used for?

Just as word processing softwares make our life easy when working with words and text, programs commonly called spreadsheets are used when working with figures and numbers, statistics, data and where calculations are required to be done. Spreadsheets are mainly used for accounting or data analysis, or wherever mathematical formulae are to be used.

There are several spreadsheet programs, the first well known one being VisiCalc, others are Lotus 1-2-3, Microsoft Excel, Numbers, OpenOffice.org Calc, Gnumeric etc. Excel and other spreadsheets are frequently used for financial planning and budgeting, data analysis, What-If scenarios, and can produce charts, graphs and pivot tables,

Like all spreadsheets, Excel opens with a blank worksheet which is actually a grid of rows and columns. The rows are serially numbered 1,2,3 .... and the columns A,B,C..... This numbering enables each space or cell to have its unique address; thus D15 represents the cell in the 4th column and the 15^th row. Now by using the ‘=’ sign, simple mathematical equations or formula can be used and sums, differences, products etc. calculated. Thus typing say, ‘=23+56’ in any cell will give the result of 79. The same work can be done by typing in the numbers 23 in cell A1, 56 in A2 and ‘=A1+A2’ in cell A3. Using formula with cell references would allow you to change the value in either cell A1 or A2 to get the revised result. This has significant advantages when using a complex formula or the same formula across multiple cells.

The value in a cell may be numbers, date, time, text, currency, percentages. The formula can be arithmetical, trigonometric or statistical; functions can be logical or even related to text.

While the basic functions of Microsoft Word such as Save, Copy, Paste, Find, Replace Undo, Redo etc. are available, Excel offers several basic functions suited to working with numbers. Some which must be tried by beginners are Paste Special, Filter, Sort, Wrap Text, Autofill (my post “Speed up data entry” ), Comments, and Freeze panes.

As a fresher to this software, to start with, use it for any of the following - calculations of your income and allowances, expenses, taxes, your own monthly budget, or even your time sheets. And do try using some formula too.

What are word processors? How does Microsoft Word help?

Most of us beginners start using the computer for simple typing – and that is how we also learn rudimentary two finger typing. This typing can be towards a letter, small notings by way of reminders, a small poem or story, a research thesis or even a whole book. The work being done is known as a document. Now to help us in this work, there are several software programs available known as word processors.  While the start of this was the electric typewriter, WordStar, Word Perfect, Microsoft Word are some of the popular softwares developed that help us when we are working in basic text.

Microsoft Word, MS Word, or simply Word (as also all other word processors) have several basic functions. When you open it, it starts you of with a blank page on which you can type. Every time you type something you can retain (or save it) by giving it a file name and going back to it again after any length of time. This a softcopy and that which has been typed can be retrieved and edited or changed without having to retype it from the beginning, text can be added to it, bits can be deleted or inserted, parts or the whole can be copied and pasted in the same file/ document or used in another. Paragraphs or sentences or phrases can be moved, spellings and grammar can be checked. Corrections and changes can tracked, hidden or made without a trace, as desired.  The document can be made more elegant – using bold, italics, increasing the size (or font) of any bit, giving special headings. Tables, graphs or images can be added and captions given. And by attaching a printer, the file or document can be printed on paper to get a hardcopy.

To help you do this many simple commands are available – Save, Open, Cut, Copy, Paste, Find, Replace, Bold, Italics, Redo, Undo, Headings, Numbering, Styles, Revisions, Track Changes, Spelling And Grammar, Thesaurus, Subscripts and Superscripts, Headers, Footers and Endnotes, Indexes, Cross References, Tables and Captions  to name only a few.

The more you use it, the more features you can learn as you go along.

I want to learn Windows - what is Windows?

Frequently people say they want to learn Windows and I wonder what they mean.  I see that someone reached my blog searching for what Excel means. Unfortunately they would not have found the answer here. So I will try devoting a few posts is to explain some basics.

First, I’ll use a small comparison that I gave to a niece of mine.  Let’s take electricity. As a user, you don’t learn electricity; you learn how to use gadgets or machines that run on electricity, even though the uses that you put to electricity are innumerable. Thus you use electricity to cool (fans, ACs) or to heat (geysers, room heaters), to light up the room (bulbs, tube lights) to increase shelf life (refrigeration) or to cook (electric stoves, microwaves) – the list is endless but I have given these examples to show how very varied the uses can be.  But basically electricity is only a source of energy and the same use can be got from other sources as well – thus you can cook with coal, heat up a room with wood and get hot water from solar energy. And you find  stoves that run on electricity but also those on wood or gas.

Keeping this parallel in mind it is easy to understand that Windows is an operating system i.e. it is the source of communication within the machine that we call the computer, something which we do not need to bother about unless our computer stops working. Of course as we go along we do need to understand more and more to make our computer system more efficient and to maintain it well.

Now a program is a series of commands of what is to be done. There are many such programs that are quite simple where the options are fixed and so is what the machine has to do e.g. in a microwave or in a washing machine. But if the uses are varied, changing, complicated, having many users each with their own needs and preferences, an operating system is used.

Besides Windows there are several other Operating systems – a few are DOS, Unix, Linux, Mac System. This enables the user to make changes in how the program runs, to keep software up-to-date and, most importantly, to add on many many more programs and hence to make your computer more versatile and useful. But since such changes are usually done by someone who is more experienced, to start with one needs to know how to use the mouse and the keyboard, how to open programs and files in Windows, how to search for files etc. only. It is the programs that need real learning.

Excel - Speed up data entry

If you are filling data in Excel, the system attempts to help you by automatically filling in a suitable option from those already filled in, in one of the cells above. (If you like what is suggested simply press Enter). This is really helpful. However there are many more ways that will make entry really fast, here are some that I use:

Use Ctrl + D to copy the contents of the cell directly above it – this works whether the cell above has numbers, text or a formula or even if it is hidden. This can also be used simultaneously on several cells by first selecting the cells and then pressing Ctrl + D.

Use Ctrl  + R to copy the contents of the cell on the left.

To simultaneously type in many worksheets, first select the sheets (see bottom left corner of the file) one by one by clicking on the sheet tabs, keeping Ctrl pressed. Then type in whatever is required. To stop this simultaneous entry, just click on any sheet name. This process can be helpful when you are typing say Headings of each column that you want to repeat on each worksheet.

Excel provides an Autofill process. Go to any cell and note the square black dot on the right hand bottom column. This is extremely useful and is called the auto fill handle. Type whatever is needed in a cell, click on the handle and drag it over multiple cells to enter the same contents (or formula) in all the cells. Further uses of this are in entering data in in a series such as 1,2,3..., or 3,6,9 etc. Select say three cells already filled (to establish clearly the pattern) and then drag the auto fill handle. Incidentally this also works with series such as days of the week or months of the year.  If you need to use autofill over a large number of rows, simply double click the handle and the system will complete your work for you. Do play around with this handle and you will find that it works for many combinations.

Using tables in Word to align your text

While typing which requires alignment other than in a running sentence, it is a good idea to use tables (without borders). The table should be created in the usual style after deciding on the number of rows and columns required, the information or data may then be entered.

Look at the top left corner, just below the Menu, you will see an icon with just an “L”- this is the tab mark. This provides for left alignment of the contents (of each row and column, or even of a paragraph in a regular Word Document). Clicking on this icon will change it to an inverted “T” which aligns along the centre, a reverse “L” which aligns on the right, and best of all an inverted “T” with a dot which aligns on the full stop mark. This is absolutely wonderful when working with numbers as you can then align along the decimal point. So just select the row(s) or column(s) where you want to align the text or numbers and then click on the Tab mark till you reach the type you want.  

And just so the table is not really visible, select the entire table, click on the borders icon and  select the option of 'No Border'. 

This works well while working with data, forms etc. and can also be used for the address of letters, or even the salutation.

General Tips - Use the Quick Launch Toolbar for files or programs you open regularly

There are many files or programs that are opened frequently. Rather than having to look for them repeatedly, it is quite easy to create shortcuts to them – simply go to the folder that has the file, then right click on the file and select Send to and then  Desktop (create shortcut).  This of course requires going to the Desktop each time. An easier method is to use the Quick Launch toolbar which appears on the bottom of your Desktop. Just drag the file to the toolbar and a shortcut is created there. This will add an icon of the file to the toolbar and you can more easily open the file. A suggestion – do remember to change the icon for this so that you can identify the file immediately.

If you have too many shortcuts on the toolbar, either increase its width or create another toolbar by right clicking on the taskbar.

(Tip by Vibha:
re the use of toolbar for opening files that are very regularly used. i am sending to u my way of doing things.

if u dont want your toolbar to be very big, but you have too many icons on it, just drag the dotted lines over the icons, the files are compressed, and two arrows appear, you can click on them and one can see the files on the toolbar, and access those that u want. this way the screen isnt occupied. also, even if you dont change the icon, point the cursor on the icon and you know which file it is.)

Excel - Entering long text in a Cell

While entering text in any cell, the existing space frequently proves inadequate. If there is nothing in the adjacent cell, the contents just spill over, otherwise the text is hidden. The simplest option is to extend the width of the column – Go to the column head (depicted by an alphabet), go to the right edge and watch the cursor change shape. Click and Drag to as wide as you would like; double click to allow the software to automatically go to the maximum required.

However you may like the contents to fit onto the width selected by you. If so, Format the Cell and select the option Wrap Text.

Having typed a lot, you might want to split it into another paragraph – just press Alt and Enter together at the required place.

And if you have already moved onto another cell but want to add some more text go back to the cell, click anywhere and then press End to reach the last word. Or simply press F2 to add at the end while on the spreadsheet itself - this works directly on the keyboard without even using the mouse so is quite fast.

Keeping your privacy while working on the computer

Are you working on a Computer or Laptop which is used by many people, and would like to ensure that others cannot see some content which is private to you? There are two options available:

1. When Windows starts, it asks you to login. Individual login ids can be created with individual passwords.Once you login with your id and password there are many benefits available - you can customise the whole set of wall paper and icons, on your desktop. Another major advantage is that if you use Outlook or Windows Mail, no one else has access to your mails. the downside is that whenever anyone else has to work, you would need to log off and for the other person to log on. This can tend to get irritating if there are frequent changes in the person who is on the PC.
2. You can easily password protect your document by creating a password, in the Options, Save.

Decide your requirements and go ahead

Word - Change from small letters to capitals etc.

Having typed something, a need may be felt to change say capitals into small eg. Caps Lock erroneously remained pressed etc. There is no need to retype the whole thing. Word treats change of capital letter(s) to small and vice-versa as Change Case, and provides a really fast and easy method to do this. This option is available in in the Format menu.  The options available are:

• Change all to Capitals - Known as Upper Case
• Change all to small letters - Known as Lower Case.
• Change the first letter of the first word only to capital - Sentence case
• Change the first letter of each word to Capitals - Capitalise each word or Title case
• Change each Capital letter to small and each small letter to Capital - Toggle case.

All you need to do is select the word, sentence(s), paragraph or even page, go to Change Case and then pick the option you require. 

General Tips - Give design to your Folders

In one of my earlier posts, I had given tips on creating folders. All these folders created manually have a similar look – they are yellow in colour and are shaped like a file folder, but have different names. They can also be given different looks to identify them more easily – or to make them more interesting. Right click on the mouse, select Properties, then Customise, then Change Icon and pick any that you select. Make sure that you do not pick one that has already been selected by the system and is on your desktop e.g. that for the Recycle Bin, or Internet Explorer etc.  Now just Apply and it’s done.

You can keep changing the image / icon or go back to the earlier one by reaching Change Icon again and then selecting the Restore Defaults. Try it. It’s simple and fun.

General Tips – Settings for the Mouse

It is a good idea to set up how your mouse works. Click on Start, then on Control Panel and double click on Mouse. The settings I definitely recommend are:

1. If you are a left hander, alternate the left and right clicks.
2. Check your speed of double click to adjust how quick or slow you would like it to work – practice on the folder or ‘jack-in-the box’ icon and make it faster or slower depending on the speed that you want.
3. In pointer options also you can adjust the speed that you are comfortable with.
4. Again in pointer options, an absolute must, I feel, is checking the Snap to option. You will be amazed at how comfortable this makes working on the computer.
5. If you are working on the laptop you would have a track pad. These options can also be changed. An essential here I recommend is the scroll option on the edge of the track pad.

Do try to play around with all of this – after all you can only go wrong – and it is so easy to re-adjust.

Deleting a few words

I frequently find I need to delete a few words. Instead of repeatedly pressing only the delete key - when you end up often deleting more than you planned - try simultaneously using the Ctrl and Delete key, this deletes one word. If you keep the Ctrl key continuously pressed, each press of the delete key, deletes one word at a time.
Practice this a few time and see how easy and fast this becomes.

Selecting a word or paragraph

Selection : Do you need to select part of what you have typed? Take the mouse anywhere on the word and double click.  And click 3 times when you want to select the entire paragraph. Now try various combinations of the arrow keys with Ctrl or Shift keys to see what all can be selected.

Repeat actions in Word, Auto Correct

Frequent bits:  Do you need to add the same things again and again. Try using AutoCorrect. First what is it that you type again and again. It could be your address for example which runs into a few lines, or even the closure to your letter - Yours truly, followed by a few blank lines and then follwed by your name in brackets. Next decide on a combination of letters ( I suggest 3 consonants) that you do not think you will ever use as such - say "vbn " (the space after them is always a good idea). Now open AutoCorrect and in Replace type "vbn " and in With type the repeated item. It works each time. Try it.

General Tips - Folders

1. Where to save? Folders: If left to its own, the software saves all files you create to the folder “My Documents”. If the files are small in number this is fine, but if you have a larger number, it's a good idea to create Folders for different purposes. Thus you might have a separate folder for, major areas of your work or home front, your family, your hobbies, your personal matters etc. Do give meaningful and logical names. Also remember that when you want to open this file you would have to know the folder name yourself.
2. Make or create a new Folder: This is actually quite simple, you would be having a small image or icon on your computer styled “My Documents”. Just take your mouse to it and click on the right side of your mouse. A whole lot of options will be displayed, select “New”, then “Folder”. On its own the name given is “New Folder”, then “New Folder2” which is not really helpful. So immediately change its name to a more logical one.
3. Right clicking on it allows you to rename the folder.

You can create New Folders not just in "My Documents" but also on the Desktop, within another folder, or anywhere else you want, using the same method.

Long Emails

When sending a long e-mail, you might like to type it out in Word. This helps in easy save, formatting, etc. Then, in place of using "Print", use the send button. There are two basic options - send this as the contents of the e-mail itself, or send it as an attachment to the email. Works really well if you already use Outlook etc.

Do you want to number your paragraphs automatically?

1.  While writing, a need is frequently felt to number the paragraphs. Microsoft Word allows you to do this quite simply by several means. The first is to give the software a clue that you want to do so - start your paragraph by typing “1.” Follow that by pressing Tab, and then tying your content. When the para is over, press Enter and the next para should automatically start with “2.” If the system is acting stupid and does not go ahead, try numbering the second para yourself and then the 3^rd should be automatic. The second method is to type a few words of your first para, right click somewhere and select the option of Numbering. This gives you a number of options – instead of 1., 2., you might prefer the style of say a), b) or any other available. The third option is to find Numbering on your Menu or toolbar.
2. There is also a provision to do Multilevel numbering i.e. paragraph 1. can have sub paras a, b, c, etc. Try using Tab and Shift alonqwith Tab to move between the various levels.
3. Instead of using numbering, sub-paras can be identified by Bullets – once again right click on the para and select Bullets. You can also select the symbol or style that marks the sub-paras.

It’s actually quite simple, do try it.

Excel and formatting dates

Entering Dates: While using Excel and entering dates a major difficulty that is usually faced is that of the format. The American style is to give the month first followed by the date; other countries follow different conventions. In India for example, the date comes first and then the month. Thus a date of 4/3/2011 would be read as 3^rd of April by an American but as 4^th of March by an Indian. Unfortunately the person who sets up your computer and loads various software usually does not bother to do the small adjustment that would take care of this. It is actually quite simple, go to the start button and then open the Control Panel. You would find a Regional Settings icon. Click on that and select the date option of your choice.

It is a good idea to simultaneously also select your language – the English language comes with a very large selection.

General Tips - Saving

Save: While tying, it is a good idea to save a file frequently especially if, like me, you are not a fast typist. The first time you save the file, Word gives it a name which could be either be “Doc1” (and the next time, Doc2), or the first few words of what you have typed – say “Dear Sir” if it is a letter. It is best to give it a name of your choice which, by the very name indicates what it is about. Also if you are frequently redoing the same work but do not want to erase the earlier attempt, add a running number to it. I prefer adding this number prefixed by a “v” (for version). So if this is say my second Post but my third attempt at it, I might save it as “Post Two v3”. Going on, do remember to keep saving your work, a simple Ctrl with "S" is enough.

Emails - Using Bcc

While sending a mail, the "To" option is always used, frequently so is the "cc". Ever tried using the "Bcc" (Cannot readily recall what this stands for). While sending the mail to several persons, you may not want others to see one or more recepient. The e-mail will reach  though the addresses entered in Bcc will not be visible to the others.

Word - Spacing between paragraphs

Paragraphs: Many of us prefer to keep an additional space between paragraphs and usually press the Enter Key twice. A simple way is to press the Ctrl key alongwith the zero button the first time you add a paragraph. This has the added advantage of automatically increasing the gap each time you add a paragraph.