The basics of dates in Excel

To start with, any date can be entered in a cell, using a slash or a hyphen to separate the parts of a date, and Excel normally recognises it as a date. If it does not, you will be frustrated to find a number appearing instead and any amount of retyping does not help. To resolve this, the cell or cells need to be formatted as dates. This is very simply done, right click on the cell, select “Format cells...” , in the number tab which would have been selected automatically, scroll down to “Date” and select the style you prefer. And do make sure that you have picked your location correctly. Since I am based in India, I pick “English (India)”. This gives the date first followed by the month. A location change to “English (United States)” gives the month first followed by the date.

To use as data, Excel converts all dates into numbers. The date 1^st January 1900 is the base date with the serial number “1”. And say 1^st January 2012 (when someone has wished me Happy New Year) has the serial number “40909”. To find out what the number of any date is, just type the date in a cell, and then change its format to number.

To find out the difference between dates, the simple minus sign is used. The two dates may be entered in two cells say in B6 and B7 and wherever the difference is to appear, the formula is used. Here it would be “=B6-B7”. Let us say B6 is 17^th February 2012 and B7 is 5^th February 2012. The result displayed is “12”.

The autofill function works equally well with dates. Thus selecting B6 and B7, and dragging down, gives a series of dates with a gap of 12 days. For the basics of autofill, click here for an earlier post: http://computertipsforbeginners.blogspot.in/2011/05/excel-speed-up-data-entry.html

Some simple and interesting formulae on dates are:

1. To find out what day of the week the date appearing in cell B6 is, type =TEXT(B6,"dddd"). “Friday” appears which is the full form of the day of the week. On the other hand using =TEXT(B6,"ddd") (i.e. one “d’ less in the formula) gives “Fri” which is the abbreviated day.

2. Today’s date is displayed with “=TODAY()”. It is to be remembered that this displays the current date whenever the Excel sheet is opened and is useful when using a countdown or calculating the day before or after a specific event.

3. And if the current date is merely to be entered as a fixed entry, without being changed each time the Excel file is opened, press CTRL+; (semi-colon).

(This post is in response to a specific request by a reader. I do hope the requirement is fully met. I would be happy to respond further on this or any other issue).

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.