VLOOKUP Function

Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet?

For example, you might have a list of people's names next to their email addresses in one spreadsheet, and a list of those same people's email addresses next to their company names in the other -- but you want the names, email addresses, and company names of those people to appear in one place.

I have to combine data sets like this a lot -- and when I do, the VLOOKUP is my go-to formula. Before you use the formula, though, be absolutely sure that you have at least one column that appearsidentically in both places. Scour your data sets to make sure the column of data you're using to combine your information is exactly the same, including no extra spaces. 

The formula: =VLOOKUP(lookup value, table array, column number, [range lookup])

The formula with variables from our example below: =VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this formula, there are several variables. The following is true when you want to combine information in Sheet 1 and Sheet 2 onto Sheet 1.

• Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In the example that follows, this means the first email address on the list, or cell 2 (C2).
• Table Array: The range of columns on Sheet 2 you're going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you're trying to copy to Sheet 1. In our example, this is "Sheet2!A:B." "A" means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The "B" means Column B, which contains the information that's only available in Sheet 2 that you want to translate to Sheet 1.
• Column Number: If the table array (the range of columns you just indicated) this tells Excel which column the new data you want to copy to Sheet 1 is located in. In our example, this would be the column that "House" is located in. "House" is the second column in our range of columns (table array), so our column number is 2. [Note: Your range can be more than two columns. For example, if there are three columns on Sheet 2 -- Email, Age, and House -- and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to change the "2" to a "3" so it pulls back the value in the third column: =VLOOKUP(C2:Sheet2!A:C,3,false).]
• Range Lookup: Use FALSE to ensure you pull in only exact value matches.

In the example below, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let's say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1.

So when we type in the formula =VLOOKUP(C2,Sheet2!A:B,2,FALSE), we bring all the house data into Sheet 1.

We've also written a full post about how to use the VLOOKUP function here (complete with video tutorial) if you still feel confused.

Keep in mind that VLOOKUP will only pull back values from the second sheet that are to the right of the column containing your identical data. This can lead to some limitations, which is why some people prefer to use the INDEX and MATCH functions instead.

Ref: http://blog.hubspot.com/marketing/how-to-use-excel-tips

Dynamic Named Ranges in Excel

by Todd Grande

Create a New Shortcut Menu

Generally there are three shortcuts in the top menu, which are Save, Undo Typing and Repeat Typing. However, if you want to use more shortcuts, like Copy and Cut, you can set them up as follows:
File->Options->Quick Access Toolbar, add Cut and Copy from the left column to the right, save it. You will see two more shortcuts added in the top menu.

Ref:  http://www.lifehack.org/articles/technology/20-excel-spreadsheet-secrets-youll-never-know-you-dont-read-this.html

Input Restriction with Data Validation Function

In order to retain the validity of data, sometimes you need to restrict the input value and offer some tips for further steps. For example, age in this sheet should be whole numbers and all people participating in this survey should be between 18 and 60 years old. To ensure that data outside of this age range isn’t entered, go to Data->Data Validation->Setting, input the conditions and shift to Input Message to give prompts like, “Please input your age with whole number, which should range from 18 to 60.” Users will get this prompt when hanging the pointer in this area and get a warning message if the inputted information is unqualified.

Ref: http://www.lifehack.org/articles/technology/20-excel-spreadsheet-secrets-youll-never-know-you-dont-read-this.html

How to create named Ranges using the name box in Excel

By Barb Henderson
The simple way to name the ranges

Sum up Cells Based on Background Color Using SUMIF

Below are steps on how to sum up cells based on background color using Function SUMIF and UDF (User Defined Function).

Expectation:
E2=B2+B5+B6; E3=B3+B8+B10; E4=B4+B6+B9

Step 1. Create User Defined Function (UDF)  COLORINDEX

• Press Alt-F11
• Select Insert > Module

• Type the codes

Step 2. On the worksheet, create another column and use the UDF COLORINDEX to get the Color Index number. In this case, cell C2.

Copy for the next cell C3 to C10 and then we can use Function SUMIF for cell F2, F3, and F4 as below:

• Sum up Yellow color. Cell F2:  =SUMIF(C2:C10,C2,B2:B10) 

• Sum up Red color. Cell F3:  =SUMIF(C2:C10,C3,B2:B10) 

• Sum up Purple color. Cell F4:  =SUMIF(C2:C10,C4,B2:B10) 

Index Match Function

Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. Here are the main differences:

1. VLOOKUP is a much simpler formula. If you're working with large data sets that would require thousands of lookups, then using the INDEX MATCH function will significantly decrease load time in Excel.
2. INDEX MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right of the results column, then you'd have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and/or lead to errors.

So if I want to combine information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 aren't the same, then to do a VLOOKUP, I would need to switch around my columns. In this case, I'd choose to do an INDEX MATCH instead.
Let's look at an example. Let's say Sheet 1 contains a list of people's names and their Hogwarts email addresses, and Sheet 2 contains a list of people's email addresses and the Patronus that each student has. (For the non-Harry Potter fans out there, every witch or wizard has an animal guardian called a "Patronus" associated with him or her.) The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet. I'd use the INDEX MATCH formula instead of VLOOKUP so I wouldn't have to switch any columns around.
So what's the formula, then? The INDEX MATCH formula is actually the MATCH formula nested inside the INDEX formula. You'll see I differentiated the MATCH formula using a different color here.
The formula: =INDEX(table array, MATCH formula)
This becomes: =INDEX(table array, MATCH (lookup_value, lookup_array))
The formula with variables from our example below: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))
Here are the variables:

• Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to Sheet 1. In our example, "A" means Column A, which contains the "Patronus" information for each person.
• Lookup Value: This is the column in Sheet 1 that contains identical values in both spreadsheets. In the example that follows, this means the "email" column on Sheet 1, which is Column C. So: Sheet1!C:C.
• Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets. In the example that follows, this refers to the "email" column on Sheet 2, which happens to also be Column C. So: Sheet2!C:C.

Once you have your variables straight, type in the INDEX MATCH formula in the top-most cell of the blank Patronus column on Sheet 1, where you want the combined information to live

Ref: http://blog.hubspot.com/marketing/how-to-use-excel-tips
Mirror: http://wikiexcel.weebly.com/blog/index-match

COUNTIF Function

Instead of manually counting how often a certain value or number appears, let Excel do the work for you. With the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells.
For example, let's say I want to count the number of times the word "Gryffindor" appears in my data set.
The formula: =COUNTIF(range, criteria)
The formula with variables from our example below: =COUNTIF(D:D,"Gryffindor")
In this formula, there are several variables:

• Range: The range that we want the formula to cover. In this case, since we're only focusing on one column, we use "D:D" to indicate that the first and last column are both D. If I were looking at columns C and D, I would use "C:D."
• Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you want the result to be text instead of a number. In our example, the criteria is "Gryffindor."

Simply typing in the COUNTIF formula in any cell and pressing "Enter" will show me how many times the word "Gryffindor" appears in the dataset.

Ref: http://blog.hubspot.com/marketing/how-to-use-excel-tips
Mirror: http://wikiexcel.weebly.com/blog/countif-function

Custom Autocorrect for Repetitive Complex Input

When you have to create a complex input repetitively, use Autocorrect to speed up.

​Step 1. Go to File > Options > Proofing > Autocorrect Options...

​Step 2. For example: lol for laugh out loud

From now on, every time the input is "lol", it will be replaced by "laugh out loud".  

Hide Data in Cell using Custom Number Format

This trick is to hide data in a cell
Step 1. ​Right click and select Format Cells or go to Home->Font->Open Format Cells

Step 2. Go to Number Tab > Custom and type ;;;

Step 3. The cell content 123 is hidden.