If you have names given like – Smith Johnson Liz lotte Christy tiara Lewisk John And you need to produce abbreviations or acronyms for them like below in all capitals Smith Johnson – SJ Liz lotte – LT Christy tiara Lewisk – CTL john – J Then you can use following formula for the same…
Category: Tips and Tricks
Tips & Tricks 95 – Send Excel Workbook / Worksheet as Attachment
You can very easily send an Excel / Workbook as an attachment directly from Excel. To send Excel Workbook To use this functionality, your workbook need not be saved. When you use a step from below, it will attach that point of time copy of Excel workbook in the mail. File > Share > E…
Tips & Tricks 94 – Select Big Range Very Easily
You have a need to select A1 to A10. This is a very easy job to do. Suppose, you need to select A1 to A10000. Now, it becomes more cumbersome. Suppose you need to select E23 to AB4325. It is all the more cumbersome job for you. Approach 1 – Put the range in Name…
Tips & Tricks 93 – Sorting from Left to Right
Generally, we do sorting in a column from top to bottom. But, what happens if the data is in rows. Then you will have to sort from left to right. This is possible in Excel. 1. Select your data in the row. 2. Data tab> Sort OR Home tab > Sort & Filter OR ALT+DS…
Tips & Tricks 92 – Financial Function – Calculate Effective Interest
You are applying for a loan and an interest rate has been quoted. The interest rate which is quoted is called "Nominal Interest Rate". They will quote Nominal Interest Rate in yearly terms. Hence, if they quote 12% interest for a loan, this is yearly figure. Now, you generally pay EMIs every month. They simply…
Tips & Tricks 91 – Financial Function – Calculate Compounded Interest
As part of our Mathematics courses in our childhood, we had learned about Compounded Interest. The famous formula which we remember is Compounded Balance = Principal x (1+rate)^N
Tips & Tricks 90 – Financial Function – Calculate Interest Rate
You want to take a loan. You know how much loan to take (pmt), you know how many months you want to pay up (nper) and you want to know effective rate of interest. Excel makes it easy to do. RATE function is the answer for this.
Tips & Tricks 89 – Financial Function – Calculate Number of EMIs to Pay Up a Loan
You have taken a loan and you know your EMI capability. So, you want to know how many months will be taken to pay off a loan completely. It is fairly easy job to do it in Excel. You will need to use NPER function for this.
Tips & Tricks 88 – Financial Function – Calculate Principal Part of an EMI
This Tips and Tricks is in continuation of Tips & Tricks 84 – Financial Function – Calculate EMI. Now the EMI for a month = Interest for that month and Principal for that month. IPMT is used to calculate the interest portion of your EMI. This I talked about in Tips & Tricks 87 –…
Tips & Tricks 87 – Financial Function – Calculate Interest Part of an EMI
This Tips and Tricks is in continuation of Tips & Tricks 84 – Financial Function – Calculate EMI. Now the EMI for a month = Interest for that month and Principal for that month. IPMT is used to calculate the interest portion of your EMI. Excel defines IPMT as "Returns the interest payment for a…