Excel Tip and Trick, NOT ONLY shows how to use excel but more than that, it takes advantage of excel spreadsheet for daily usage in life and work such as : how to calculate installment of bank loan, how to simple cash flow, etc.
I hope from this web site, I can help you to work more efficiently as well.
lets go for it !
I. MORTGAGE PAYMENT
Mortgage payment/ annuity is a series of equal cash payments spaced evenly over timeORDINARY ANNUITY : The cash payments occur at the END of each time period
Life example :
* You borrow $ 20,000 from the bank for your new House. You agree to make payments at the end of each month for the next 4 years.If the interest rate on this loan is 9%.
HOW MUCH IS YOUR MONTHLY PAYMENT ?
For Solve this Life sample, we can use Excel Formula :
PMT (PAYMENT) = PMT(rate, periodic, PV, FV, Type)
sample formula Monthly Payment = -PMT(9%/12,4*12,20000)
Loan = $ 20,000.00
Interest Rate (Effective) = 9%/ Year
Period = 4 Years
MONTHLY PAYMENT = $497.70
Or you can download the sample xls in the link below :
Download
II. LEASING COMPANY
In some leasing company, the calculation of annuities also can be in the format "Annuity in Advance". It means that in the first month installment = repayment.For better sample, you can see if we will buy a heavy equipment such as Excavator CAT 966, how much installment that we have to pay every month ?
you can download this sample xls in the link below : Download
III. SAVING MONEY
Do you know about 72's formula ?72's formula is a formula to calculate how many years our money can grow become 2 times from initial saving.
for example : How many years our money become 2 times IF we put on the bank with interest 10% ?
formula = 72/interest(%) = 72/10 = 7.2 years.
so in 7.2 years, our money will become 2 times from initial saving.
Another Example, If we want to have money $100,000, how much we have to deposit every month ? of course, thats depend on the interest and the period.
How to calculate it ? you need to make a table with horizontal header is year and vertical header is interest/ year.
The Formula is still same with the formula for montly payment above :
PMT (PAYMENT) = PMT(rate, periodic, PV, FV, Type)
Example : if the interest rate = 6%, saving period = 10 years, every month we have to save = $610.21 to get $100,000 at the end of 10th year.
Download
IV. WORKING WITH DATE
Working with Date in Excel, can be confusing because the date format of each countries are different, such as : mm/dd/yy, dd/mm/yy, dd/mm/yyyy etcso for working with date, we need to notice some important things as below :
1. Format the cell in the column for the date
I prefer to format the cell so the month wording can be seen, such as 17-july-2007. this is better rather than 17/07/07
2. entry the cell with "mm/dd/yyyy" or "mm/dd/yy". Key in date in "dd/mm/yy"'s format often cannot work. Most of the setting in computers for key in Date is mm/dd/yy.
3. for calculating the difference between two dates we can use the formula = Days360(cell1, cell2)
you can download the sample xls in the link below :
Download
you can find the sample of calculation from a vendor for the late payment. in this example they aggree only charge the penalty for those invoices that are still outstanding by 13 June 2007, pls check the sample in this link
sample penalty calculation
V. ROUNDING
Rounding can be make some problem, especially when we will compare between two numbers, for example 2.0000000001 is not same with 2. Eventhough we know that those numbers are about the same.you can make rounding for example = ROUND(2.0000000001,0) = 2
it will give you result = 2
Round (Cell,0) will give you rounding to the nearest round number, if the number decimal less than 0.5 it will round down and if more than 0.5 it will ruound up.
VI. INVOICING
Can be Excel become as a database ? yes, it can be as a databaseExcel has a <=Lookup> function, where it can pull out the data in other Sheet or even in other spreadsheet.
For example if we want make spreadsheet for Invoice , we dont need to type one by one the item, what do you need just link it to other sheet or other spreadsheet. In other sheet you can make "item database".
The sample formula can be like this : =IF(B9="","",VLOOKUP(B9,'Item Database'!$B$5:$E$100,2,FALSE))
in cell B9, you just only need to type the item no, then the item description and unit price will be automatically appear.
you can download the sample xls in the link below :
Download
VII. MACRO IN EXCEL
What is Macro in Excel ?Macro is a Routine task that automatically runs when we click some key in the keyboard. For example : if we will edit the content of the cell Part No from the thousands list like below :
A00100
A00101
A00102
etc............
if we want to edit A00100 become only A100 and the list is thousands records, no choice you need to use Macro.
Most of the users of Excel spreadsheet think that Macro is very complicated tool and very difficult to use it, but actually this opinion is not too correct. Excel provide the tool for creating Macro for us actually.
Basically, Macro comprise of :
1. The Task that we need to repeat
2. Looping or repeat instruction
3. Limitation for stoping the Looping
The good news, for process no. 1 excel can help us with the code. For no. 2 and no. 3 it is we need to learn the macro code little bit, but after we do one macro we can copy the part of the coding for other macro.
VIII. OPEN OFFICE vs EXCEL
Recently, a lot of people use Open Office (freeware) instead of use Excel (Licensed software). Open Office has most of the features in Excel and it is FREE software (no cost). But it has some limitation, especially if we work with several people in one file and some users use excel while other use Open Office.1. Open Office cannot support "link" between file. Sometimes we found error in link between sheets as well.
2. Please be careful with the format .xls when you saving in Open Office. I suggest you’d better save it in excel 95, NOT 97 or XP, because if u save with the higher version, the file will be cannot be opened in Excel.
3. In Open Office, print selection cannot be seen in Preview and I found hard to print “selection”
4. Auto save facility cannot run in Open Office.
5. Sometimes not support large data
6. If one file opened by 2 person, the last person cannot see warning "Read only"
7. Shortcuts in Open Office are less than Excel's
8. Sometimes if we change the settings in Open Office, need to restart computer for take effect.
9. In Open Office, the password is cannot work ifwe save as "xls".
In Summary, open office is rather difficult for file’s sharing and working together with Excel spreadsheet.
IX. SUMIF
SUM A ROW OF DATA WITH CONDITIONThe Basic Format is =SUMIF(RANGE, CRITERIA, SUM RANGE)
The formula will look like =SUMIF($B$8:$AP$8,$AQ$8,$B13:$AP13)
In Financial report normally we need to compare the budget number with the actual number every month. At the end of the report we need to know how much the actual along the year where in this case we can take advantage of =sumif formula to calculate the total amount per row. For more explanation you need to see my sample in the link below, pls check in the cyan color shade for the application of sumif formula.
you can download the sample sumif xls in the link below :
Download
X. AVERAGE
Average function in Excel sometimes weird, when in the middle of array there were Zero or Blank number.For example : Average of array number the working hour sheet, let say that every employee doesnt have same working every day and sometimes they off at certain day.
This is the sample of working hour : 10,9,8,0,12,8,Blank,8,9,Blank,9,Blank,13
How many average working hour per day for each employee ?
if you use calculator u can find = 86/9 = 9.56
Now, u try with Average Function in excel = Average(C6..AG6) = 8.60, how come it is different ?
the number 8.60 is coming from = 86/10 = 8.60
why it divide by 10 NOT 9 ? the answer is : Excel assume Zero number is a number eventhough its Zero !
Excel doesnt calculate "Blank" cell, which is good but if some one typing Zero in the middle of the array, Excel will consider it as a number.
So for not consider the Zero number, you can use this Formula :
=AVERAGE(IF(C6:AG6<>0, C6:AG6,""))
what means by that is lets Excel calculate the average of the array number that are not Zero.
but the tricky thing of this formula is, sometimes it will gives you result like = #Value! and for solve it you can press "F2" in that cell and continue with press CTRL + SHIFT + ENTER.
for more detail you can download the excel in this link
Download
XI. ZERO FRONT
Sometimes we need put zero in the numbers, for example for the Employee number instead of put empl no : 23 , we put zero on it like : 00000023
Normally an ID no. need certain digit numbers such as 8 digits like example above.
To put zero in the front of number based on certain digits of number, we can do with the step below :
Right click on the cell(s) you want to change.
Click on formatting.
Scroll to the bottom of the list, and it?s custom.
Under ?Type? put in as many 0?s as there are digits you want to show.
For example, if you put 00000 and it was showing 123 before, it would now show 00123.
"DARE to dream. Its all about dreaming and from dreams come great things. If you don't dream you will never get there." - Datuk Tony Fernandes, Air Asia CEO
