Here I share a tricky way to create in-cell charts in Excel with an example. Below is the data of a few employees and the respective volume of requests completed by them. Now enter the below formula in cell C2 and copy it till C8. =REPT("n", B2/50) Column C... Continue Reading →
Find and Replace carriage return in MS Excel
Scenario: Here is a scenario that I have come across recently in Excel. I have the data in column that is split across multiple lines in each cell, more technically speaking, separated by carriage returns. i.e., the text in the cell is separated by pressing ALT + ENTER. see the below screenshot: Challenge: Now the... Continue Reading →
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 trick to address this. Here... Continue Reading →
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 is, if we can build... Continue Reading →
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 to be given to the... Continue Reading →
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: Approximate match example Part 3:... Continue Reading →
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 then returns the corresponding value... Continue Reading →
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 Hope you will find this... Continue Reading →
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 Hope you will find this... Continue Reading →
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 names are containing an asterisk... Continue Reading →