## A quick trick to create in-cell charts in Excel

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 now looks as in the below figure:

Now, select the column C and then change the font face to “wingdings” (Home tab –> Font group –> Font face) and font color(Home tab –> Font group –> Font color) to a color of your choice (say blue).

That’s it..!! the in-cell chart now looks like the below in column C:

Explanation: The REPT function in Excel takes the 1st parameter to it and repeats it for required number of times indicated by the 2nd parameter to it. In our example, REPT takes the character ‘n’ and repeats it. The number of times it is repeated is indicated by the volume divided by a constant number of your choice. I am dividing the volumes by a constant number 50 to let the character ‘n’ appear for a small number of times but still retaining their proportionality to other volumes. This is required to make the chart appear compact.

Hope you enjoyed reading this post..!! Want to master Excel with more such techniques..? Follow my blog by entering your email and then hitting on FOLLOW button at the top right or simply click on the FOLLOW button at the bottom right of the window, if it appears in your browser.

## 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 challenge is to reformat the column C in a way that it may appear as in the below screenshot:

Solution: We will have to find and replace the carriage return with a comma followed by a space (i.e., “, “). Invoke the Find and Replace dialogue hitting Ctrl + H on the keyboard. Now place the cursor in the Find What text box and enter the value by hitting Ctrl + J. When you do this, the value in Find What text box appears like a very small blinking dot. Now enter a comma followed by a space in the Replace With text box. Then hit on Replace All button. Excel now pops up a message indicating the number of places the replace occurred, as in the below screenshot.

Now expand the width of column C and contract the height of all rows, so that table appears as desired.

Remember that Ctrl + J is the hotkey combination for Carriage Return.

Hope you enjoyed reading this post. Want to master Excel with more such techniques..? Follow my blog by entering your email and then hitting on FOLLOW button at the top right.

## 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 is another one.

Hoping that you understand the functioning of MATCH and INDEX functions, let’s understand how the combination of INDEX and MATCH as an efficient alternative to VLOOKUP function in terms of  addressing the issue if the column in the lookup table where the lookup value is be searched is not in the first position.

Example Scenario: Below are the customer names and their respective phone numbers. you are given a customer name and asked to lookup the customer name from this table and return the phone number of the respective customer.

Challenge: However, the customer name column in the lookup table from which the lookup value will be searched is not in the first position. see the screenshot below:

Solution: Here is the formula of combination of INDEX and MATCH functions to address this issue (see the below screenshot: =INDEX(B50:B60,MATCH(F49,C50:C60,0))

Explanation: Any nested formula is understood by understanding it from the inside to outside. i.e., the function that is nested most inside need to be understood/evaluated first and then proceed to evaluating the function nested at next higher level. So, in our example, we will understand and evaluate the MATCH function first.

Inner Function: MATCH(F49,C50:C60,0) – Here MATCH searches for the value passed in the first argument to it (i.e., value in cell F49), in the array C50:C60 and then returns number corresponding to its position in the array by counting from top to bottom in the array. so, here, it searches for “rathod” in the array C50:C60 and returns 4, since the value “rathod” appears in 4th row starting from the top of the array.

Outer Function: INDEX(B50:B60,MATCH(F49,C50:C60,0)) – Now our outer function, INDEX goes to the row number represented by its 2nd argument in the array represented by its first argument and then returns the value present in that row of the array. In our example, after the MATCH function is evaluated, the formula becomes –  INDEX(B50:B60,4). Now the INDEX function goes to the 4th row in the array B50:B60 and returns the value present in that row. i.e., 7174683825 is returned.

Testing: You can further test by changing the value in cell F49 to another customer name.

Hope you enjoyed reading this post..!!

Wondering if there are more such tricks in Excel..? No doubt, inumerable..!! Follow my blog to learn more tips, trips and techniques in MS Excel, by entering your email and then hitting on FOLLOW button at the top right. Happy learning.. 🙂

## 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 the table_array argument (i.e., the lookup table) of the function at runtime in a way ensuring that the first column is the column in the lookup table that is to be searched for the lookup value, the problem is resolved. This is achieved using CHOOSE function inside VLOOKUP in the place of table_array argument to build the lookup table in desired manner at runtime.

Example: Lets understand this with an example. Below are the customer names and their respective phone numbers. you are given a customer name and asked to lookup the customer name from this table and return the phone number of the respective customer.

Challenge: However, the customer name column in the lookup table from which the lookup value will be searched is not in the first position. see the screenshot below:

Solution: Here is the VLOOKUP function with CHOOSE function in place of lookup table to return the right values entered in cell F36 to lookup the value in cell F35 and search it in the range B36:C36 (obviously not in the first column) and return the phone number. see the below screenshot:

=VLOOKUP(F35,CHOOSE({1,2}, C36:C46, B36:B46), 2,FALSE)

Explanation:

First argument to the function:  WHAT to search/Lookup: cell F35 has the value to be looked up.

2nd argument to the function – WHERE to search/lookup: Here is the CHOOISE function is used as follows: CHOOSE({1,2}, C36:C46, B36:B46). Here the first argument {1,2} will arrange the arrays referred in 2nd and 3rd parameters in that order (i.e., the array referred in 3rd argument of CHOOSE function – C36:C46 comes first and then comes the array referred in 4th argument of CHOOSE function – B36:B46) and returns it as a range/lookup table. so when this happens, the returned lookup table will have C46:C36 as first column that has customer names where the lookup value from cell F35 will be searched, and B36:B46 as second column that has the phone number to be returned.

3rd argument to the function – WHICH column in the lookup table, returned at runtime, enumerating from left to write (corresponding to the row where the lookup value is found) has the value to be returned ? Here phone number to be returned is in column in the 2nd position (i.e., B36:B46) in the lookup table returned at runtime.

4th argument to the function – How to search. Here we only want to perform exact search. So, argument passed here is 0(zero) or FALSE.

Testing:

Now in the screenshot above, we already have a customer name in the cell F35 – “rathod”, so the value returned should be: 7174683825. When you hit enter “after” key after entering the formula in cell F36, you would see the correct value being returned as in the below screenshot.

Hope you enjoyed reading this post..!!

## 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 customer based on the number of units of the product purchased as in the below screenshot, indicating that no discount is given if less than 50 units are purchased, 2% discount is given if 50 or more but less than 500 units are purchased, 10% discount is given if 500 or more but less than 1000 units are purchased, 15% discount is given if 1000 or more but less than 2000 units are purchased, and 20% discount is given if 2000 or more units are purchased.

Challenge: Now the requirement is that you will be given the units purchased and asked to arrive at the discount to be given to the customer. for example, if the number of units purchased by a customer is 700 units then the discount percent value to be returned is 10%. However, VLOOKUP is not intelligent enough at natural language processing that it can understand that >=500 and <1000 indicates all values between 500 and 1000 including 500 and excluding 1000. so when it searches for 700 in units purchased column, it will not find the value.

Solution: The option of approximate match for the last argument of VLOOKUP function comes to your rescue in such scenarios. Just that you need to adjust the units purchased column in a way that excel can understand it. To do this, you will insert a new column in the mid and write values as follows, as in the below screenshot:

• write 0 in first row and 50 in 2nd row implying units less than 50 units for first row.
• write 500 in 3rd row and 1000 in 4th row indicating units greater than or equal to 50 and less than 500 for 2nd row and also indicating units greater than or equal to 500 and less than 1000 units for 3rd row.
• write 2000 in 5th row indicating units greater than or equal to 1000 units and less than 2000 units for 4th row and also indicating units greater than or equal to 2000 units for 5th row .

Now lets write the VLOOKUP formula against Discount % in cell G24 so that it may lookup the value against Units Purchased in the cell G23 and return the percentage discount to be given:

=VLOOKUP(G23,C24:D28,2,TRUE)

Explanation:

first argument to the function –  WHAT to search/Lookup: cell G24 has the value to be looked up.

2nd argument to the function – WHERE to search/lookup: first column of the lookup table C24:D28 is where the lookup value is to be searched.

3rd argument to the function – WHICH column in the lookup table enumerating from left to write (corresponding to the row where the lookup value is found) has the value to be returned. Here discount percent to be returned is in the 2nd position as shown in the below screenshot.

4th argument to the function – How to search. Here, you want to first search for an exact value. If an exact value is not found, then search for the nearest available value that is less than the lookup value. example, if you are searching for 700 which is not exactly not found, it will look for nearest value less than 700. i.e., 500 will be the match in this case. so, we will specify 1 or TRUE here.

Testing: Now enter a value in cell G23, say 990. For 990, since it lies between 500 and 1000, it should return 10% discount. It correctly returns the value in cell G24. you can further test by entering other values in cell G23.

Caveats: In case of all approximate search types of VLOOKUP, ensure that the first column in the lookup table (i.e., the column where the lookup value is to be searched) is in ascending ORDER. Otherwise, incorrect values will be returned. This constraint is owing to the search technique (Binary search) used by Excel internally while searching for the lookup value in the first column of lookup table.

This is one use case for approximate match type VLOOKUP. Hope you enjoyed reading this post..!! Follow this blog by entering your email and hitting on FOLLOW button at the top right, to learn VLOOKUP in more variations in the next post.

## 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: VLOOKUP: Right to Left Lookup

Part 4: INDEX and MATCH combination – An alternative to VLOOKUP

Part 6: Nested VLOOKUP

Part 7: Troubleshooting VLOOKUP errors

Part 8: VLookup Refresher

If you have come across any challenges while using VLOOKUP that are not addressed in any of these parts of this learning series, do share them in the comments.

I will be enhancing this post as and when adding a new parts relating to Lookups in Excel and will enable each part as hyperlink as and when I post the content of that post on my blog.

Happy learning and have an adventurous journey to mastering Lookups in Excel.

## 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 in the same row from another desired column.

It’s similar to searching for the phone number of a person from a phone directory where you would first search for the name of the person and then move across that row towards the phone number column and then take a note of the value found in that phone number column.

Signature of the function:

VLOOKUP(lookup_value, table_array, column_index_num, [table_range])

Explanation of arguments of the function:

lookup_value: This argument indicates WHAT is the value that you want to lookup or search.

table_array: This argument indicates WHERE do you want to lookup or search. This argument is also known as lookup table. The lookup value will be searched ONLY in the FIRST column of the lookup table.

column_index_num: This argument indicates value from WHICH column in the lookup table is to be returned.

table_range: This argument indicates whether the search process need to be exact type or approximate type. 0 (zero) or FALSE indicates exact match, 1 or TRUE indicates approximate match. This argument is optional. Although FALSE is most frequently used, TRUE is the default value for this argument, if nothing is specified.

An example is better than a precept.

So, lets understand the working of VLOOKUP function with a couple of examples using some fictitious data:

Example 1.

Scenario: Here are some customer names with their phone numbers alongside maintained by an analyst. The requirement is that the manager will determine the lucky customer each month by some lucky draw and ask for his/her phone number from the analyst, so that the customer may be contacted on Phone.

Now the analyst do not want to search for the customer in that long list of customer and phone numbers manually as we do in a phone directory. He just wanted to enter the name of the person in the cell beside “Best Employee” (i.e., in G2) so that the phone number of the customer is automatically displayed in the cell beside “Phone Number” (i.e., in G3), as in the below screenshot.

Now, lets write the VLOOKUP formula in cell G3 to return the Phone Number of the Lucky Customer  present in cell G2 by looking up or searching for that customer in the lookup table spread across the range – B3:C13.

=VLOOKUP(G2,B6:C16,2,FALSE)

Explanation:

WHAT to search: cell G2 has the name of the customer that you want to search.

WHERE to search: the first column in the range B3:C13 is the place where the customer name is to be searched.

value of WHICH column of the range in the corresponding row to return: proceeding from left to right in the range and numbering the columns starting from number 1, in mind, Phone Number column gets number 2. since we want the value from Phone Number column to be returned finally, we specify its enumerator, i.e., 2 here.

Last argument indicating Match Type: Since, we want search for the exact name of the customer in cell G2 from the first column of the lookup table, we will indicate 0 or FALSE here.

Now when you press enter after entering the formula in cell G3, it initially returns a “#NA” since we haven’t yet entered any Lucky Customer name in cell G2.

Testing:

Now lets test our VLOOKUP formula by entering a name of a customer as Lucky Customer in cell G2 to see if it returns the correct phone number of that customer.

I entered “Rathod” as Luck Customer and it correctly returned the respective Phone Number as highlighted the record of this customer in the lookup table in yellow:

* Remember that VLOOKUP does case insensitive search. Hence, in the above example, if you have entered “rathod” in the place of “Rathod”, you would still see the right value being returned.

Caveats: If the column in the lookup table where the lookup value to be searched is not the first column of the lookup table, VLOOKUP wouldn’t work.

## 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:

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:

Hope you will find this helpful.. 🙂

## 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:

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:

Hope you will find this helpful.. 🙂

## 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 character (*) in the mid. These are entered inadvertently. Now the requirement is to cleanse the data by removing these asterisk characters from the “Name” column. I used the excel keyboard shortcut “CNTRL + H” to invoke the “Find and Replace” dialogue. since i want to remove these asterisk characters, i replace the * with nothing (i.e. entered nothing in the “Replace with” text box of the “Find and Replace” dialogue) as in the below picture:

Problem: Interestingly it knocked off all the data from the worksheet as in the below picture:

Justification: It appears like Excel going mad. But the fact is that Excel is simply doing the job that it is asked to silently. The asterisk (*) character is actually the wildcard character representing zero or more characters. Hence when Excel sees asterisk in the Find What text box, it just looks for zero or more characters, said another way, all the characters. Then replaces all the characters with nothing, said another way, knocks off all the data.

Then how do we achieve this task of removing only the asterisk character from the data.?

Solution: Prefix the asterisk (*) character in the “Find What” text box with tilde (~) operator as below:

Now the results look as desired as below:

Conclusion: The tilde operator acts like the escape characters and escapes the inherent meaning of  asterisk (*) from the knowledge of Excel, letting Excel treat asterisk as just another character.