Jaydeep 's Excel Tips are for beginners to intermediate users. The secret to a great spreadsheet is planning. Most people just jump in and start creating the spreadsheet without thinking about future enhancements. In my personal opinion a good spreadsheet is not static. It is fluid and dynamic, making it more efficient and more powerful. Excel is a powerful tool in the hands of someone who plans & utilizes Excel's full potential power. Here I hope to share some useful tips for BEGINNERS when using Excel 2007.
Basic Formulas
Totals
Every body uses totals right? See the example on the right. Most users would create the total on row 3. If they need to expand to insert more rows, generally they would tend to insert above row 3. This may resultthe total excluding the new rows(unless Excel XP is used). To overcome this problem,always create the total in row 4, leaving a blank row above the total. Resize the row height as shown above and if you need to insert more rows, insert above the blank row (ie row 3). By the way the formula for the total would be =sum(a1:a3) Simple huh?
More Totals
Lets say you need to total numbers that are not in a range, example a1, a5, a10. Well normally the formula would be =a1+a5+a10. If you have 10 cells to total up that would be slow coz you need to type in "+". Next time try using type =sum( first, then hold down the control key, use the mouse & click on a1, a5, a10, then release the control key and type ) and you're done! The formula would be =sum(a1,a5,a10) Fast wasn't it?
Dates
Excel recognizes dates whether you use slash ("/") or hyphen ("-"). However if you use full stop (".") Excel would assume the date as a text. Therefore you will not be able to customize the date styles or even perform calculations with the date. So dates should be 25/12/2004 or 25-12-2004 and not 25.12.2004 (a pretty common mistake).
More Dates
Do you want excel to use the system date? Use this formula =today() and presto you can see the system date. Cool huh? But if you just want the system date without using the formula just press ctrl : and Excel will insert the system date. If you want the date & time use =now() and the date & time will appear.
Text / Strings
There are times you need include numbers or dates together with your text. Typing it in doesn't do much if the numbers keep on changing because it's a formula and you want it linked to your text. Example you need your text to say"Print date : 31-Dec-2004" but you want the date to be linked to a cell with formula so that whenever you print your report, the date in the text is always changing according to your system date. Do do that you will need a formula. Let’s say cell "A1" contains the date (formula =today() remember?). Type this formula anywhere you want the text to appear ="Print date : "&TEXT(A1,"dd-mmm-yyyy") and you're done. Each time you print your report, the date automatically changes. Similarly if you need a number or value to be linked, you could change the number format to 2 decimals this way (assuming "A1" contains the value)="Profit for the month is $ "&TEXT(A1,"0,000.00")
File Name
It is always a good idea to insert the file name & path and include it in the print range so that you will know the filename & it's location when you need to find the file. Just type in this formula =cell("filename") Note that the word "filename" is typed and not the actual filename. And lo & behold, the filename appears in the cell.
It is always a good idea to insert the file name & path and include it in the print range so that you will know the filename & it's location when you need to find the file. Just type in this formula =cell("filename") Note that the word "filename" is typed and not the actual filename. And lo & behold, the filename appears in the cell.
