Category: Excel and Power BI

• Excel and Power BI

  INDEX and MATCH – an efficient alternative to VLOOKUP

  Although VLOOKUP in Excel is a very useful function, it suffers a drawback. If the lookup value to be searched is not in the first column of lookup table, VLOOKUP wouldn’t work. In the last part of this learning series, titled “VLOOKUP: Right to Left Lookup” I have taken you through a…

  Jul 24, 2013
• Excel and Power BI

  VLOOKUP: Right to Left Lookup

  We all now know that VLOOKUP has one major constraint that the column in the lookup table from where the lookup value is to be searched should be the first column in the lookup table. Here is a tricky way that you can use to overcome this constraint. The trick…

  Jul 23, 2013
• Excel and Power BI

  VLOOKUP: Approximate match example

  In the first part of this VLOOUP series, I have taken you through a simple example of VLOOKUP where while searching for the lookup value, we were doing an exact match. Now lets see an example scenario of approximate match. Scenario: You have a table showing the percentage of discount…

  Jul 22, 2013
• Excel and Power BI

  Mastering LOOKUPs in Excel learning Series

  Here I thought of compiling a learning series covering the basics to classics of Excel Lookup functionality. So I have divided them into parts organized in some logically organized fashion. However, you are free to access and learn them in any order. Part 1: Introduction to VLOOKUP Part 2: VLOOKUP:…

  Jul 21, 2013
• Excel and Power BI

  Introduction to VLOOKUP in MS Excel

  Hi friends… one of the important features of Excel which every user of Excel is expected to be aware of, is VLOOKUP function. Here I take you through the nuts and bolts of VLOOKUP: What does VLOOKUP do ? VLOOKUP function looks up (searches)  a value in one column and…

  Jul 21, 2013
• Excel and Power BI

  Microsoft Office Excel 2003 menu to 2010 ribbon mapping Workbook and Interactive Reference Guide

  Friends.. Here is the link where Excel workbook containing the mapping between menu of Excel 2003 and Ribbon of Excel 2010 can be downloaded: http://office.microsoft.com/en-in/templates/results.aspx?qu=Excel%202010%20Menu%20to%20ribbon%20reference%20workbook&queryid=6b2d60e7%2D5451%2D4a60%2Daee7%2Dc4f4983aa767#ai:TC101842354 Here is the link where an interactive guide showing the the mapping between menu of Excel 2003 and Ribbon of Excel 2010 can be downloaded: http://www.microsoft.com/en-us/download/details.aspx?id=16642…

  Feb 27, 2013
• Excel and Power BI

  Microsoft Office Excel 2003 menu to 2007 ribbon mapping Workbook and Interactive Reference Guide

  Friends.. Here is the link where Excel workbook containing the mapping between menu of Excel 2003 and Ribbon of Excel 2007 can be downloaded: http://office.microsoft.com/en-in/templates/excel-2007-menu-to-ribbon-reference-workbook-TC010212862.aspx?CTT=5&origin=HA010149151 Here is the link where an interactive guide showing the the mapping between menu of Excel 2003 and Ribbon of Excel 2007 can be downloaded: http://www.microsoft.com/en-us/download/details.aspx?id=14650…

  Feb 27, 2013
• Excel and Power BI

  Microsoft Excel behaves strange when tried to replace or remove asterisk (*). Why..?

  Here I came across a scenario of Microsoft Excel behaving strange, when trying to find and replace asterisk character (*) with another character. Scenario: Here is a list of data in an excel worksheet containing some fictitious data of names and salaries of a few employees. You can see that some of the…

  Jan 9, 2013
• Excel and Power BI

  Interesting cell formatting in MS Excel to disappear whatever you enter

  Here is an interesting cell format that disappears whatever the text you enter.. Step:1.  Select any cell and then click on Format cells dialogue launcher as in the below screen shot (Ctrl + 1 is the keyboard shortcut to invoke Format Cells dialogue) Step: 2. Select “Custom” in the Category box.…

  Nov 25, 2012