Posts

VLOOKUP/HLOOKUP & LOOKUP formula with Example

Image
VLOOKUP is a very powerful function to join 2 tables and find reference values from another table. Correct use of the same can help in quickly achieving the task. In this various LOOKUP functions are explained in very detail with various examples. What is VLOOKUP: VLOOKUP is used to join two tables, where you want to get reference data from another table. VLOOKUP - Basic Example (Exact Match) VLOOKUP Formula is =VLOOKUP(What to find, where to find, which column, type of match). If there is no error, it gives #N/A error, using the IFNA function such error can be handled. Also references needs to be locked, so that VLOOKUP can be copied correctly. VLOOKUP - Advanced Example - Using Function Dialogue box, Exact Match, Named Range, From Different worksheet VLOOKUP can be used to find Exact Match or Approximate(closest match). Also using Name Range is another way to lock the range. VLOOKUP – Approximate Match Example VLOOKUP with Type = 1 is used to find an approximate match. This help...
Post a Comment
Read more

Most useful Finance formulas and functions in Excel

Image
Undoubtedly Excel is the most used tool in Finance Industry. Whether you are working as a financial professional or a financial planner or want to plan your own finance, Excel has many formulas related to Financial calculations. 1) Period Payment (PMT): This formula is used for calculating period payments like EMI, monthly income plan, etc. Apart from PMT, there are other related functions like: - IPMT & PPMT which gives interest component and principal component for a period - CUMIPMT & CUMPRINC gives cumulative interest for the period range. 2) Future Value (FV): This formula is used for calculating Future value for current value, payments for a given period of time, and interest rate 3) Present Value(PV): This formula is used for calculating the Present value for future amounts, payments for a given period of time, and interest rate. Please check this video for complete understanding. Also attaching the sample file.
Post a Comment
Read more

How to make Interactive Dashboard on Excel and PowerPoint using Slicer

Image
If you want to make very interactive dashboards in Excel and Powerpoint, Slicer is the feature that you are looking for. Slicer is useful in doing data interaction very easily, be it a Table, Pivot table or any chart. Slicer is a very interactive and single touch alternate to Filter. Also if you have to give your excel to someone for presentation, he/she can manage the same easily using slicer. The slicer can be used in PowerPoint to make interactive presentations and better visualization. In this video Slicer usage with various options is explained with 5 examples. How to apply slicer on a table and properties of a slicer, like size, colour etc? How to apply slicers on multiple fields of a table? How to apply slicer on Pivot Table and Chart? How to apply Slicer on multiple Pivot tables and Charts using the connection and control them using a single slicer? How to use the slicer on Powerpoint? How to insert Slicer: Slicer Options/Styles: Samples: Slicer in PowerPoint
Post a Comment
Read more

How to Transpose Data in Excel

Image
Excel works in form of a Table structure and Row & Column format. At times, we would start making entries in a format, just to realize later that transposing the data would have given a better view of the data. Also, sometimes we would copy data from the internet or somewhere, which is not in the format we desire. Transpose data means, to switch or rotate cells. There are 3 different ways to achieve this. In this video, I explain all the methods with relevant examples. 1. Static Transpose: This is when we want to transpose the data completely and does not want the original data. There is a simple copy Paste Special method to achieve this. 2. There is a relatively new array function to achieve this. This is formula hence works well, when base data needs to be kept. This being array function is not easy to use. Please check the video carefully 3. There is another easy method to achieve Transpose by reference. It can be also used to convert a single column to a table structure. Many t...
Post a Comment
Read more

Flash Fill functionality in Excel

Image
  Flash Fill in Excel is one of the most powerful functions. With this single feature, one can perform most functions for which complex TEST formulas would require. Check this video with different examples of how to use Flash Fill. Example 1: In this example, I explain how to split name, merge name, change first name-last name, change case. Also how to extract the name from email id.   Example 2: GST consists of State id, PAN No, and other 3 characters. In this example, I explain how to separate PAN No from GST id, with a single click.   Example 3: Phone number in the US and other countries follows a specific format. In this example, I explain how to format phone numbers in various formats.   Example 4: Data like Account numbers, Phone numbers, etc needs to be masked for various reasons. In this example, I explain how such data can be easily masked using the flash-fill function.   Example 5: If there is an id like abcd123, separating text and n...
Post a Comment
Read more