Fill Handle

2 posts

Harness the (Power) of Excel VLOOKUP

Excel VLOOKUP is quite possibly one of the most popular and yet misunderstood Excel functions. It is a powerful function that can be used to simplify and automate so many things in Excel.

As an Excel user advances in their skills, VLOOKUP typically becomes one of the next powerful tools used.

See how to use it, as any tool becomes more useful as the user’s knowledge and skill in manipulating it increases.

Start With the Basics

Basically, what VLOOKUP does is look in a column of a table of data for a value that you tell it to find. It then returns whatever value is in the same row of that table of data, but from a column that you tell it to look in.

The one restriction with VLOOKUP is that you can’t return a value that is to the left of the column that you are looking in. You can only return values in columns to the right.

Examine the Excel VLOOKUP Function

Here’s the function:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

  • lookup_value = the value you want to find in the table
  • table_array = the table of data that you want to look in
  • col_index_num = the column in the table of data that you want the value returned to you
  • range_lookup = logical value. If you want to find the closest match (TRUE) or an exact match (FALSE)

See How Excel VLOOKUP Is Used

The image below is a table that will be used for the VLOOKUP function. The store number of ‘1184’ will be looked up in the table to find that store’s phone number. It’s in cell A4 and highlighted in a red box.

Table of Data for VLOOUP

This is the formula that will be used to get the phone number for that store number using VLOOKUP:


The result of that formula will be (555) 555 6492, which is the value in cell B4.

Let’s break the formula down into plain English:

“Using the data in cells A2 through C5, find the exact match of the value ‘1184’ in left-most column. Once you find ‘1184’ in that column (which is always column 1), show the value in column 2 of that same row.”

The Real Power of VLOOKUP

In the example above, the value of “1184” was hard-entered into the formula. That was just done for the sample.

The real power of VLOOKUP is that you can pass a cell value, a formula result, or the result of a function to VLOOKUP.

Imagine that you have a table of data with those store numbers. It has hundreds, thousands, or even more rows. You want to know the address and phone number for each of those rows.

Just like this sample that has only a few rows:

Sample Data Needing VLOOKUPs

All that you need to do is add two columns to the data – one for the address, and one for the phone number.

Now, add the VLOOKUP formula to those two columns, and instead of hard-entering the value that you want to look up, enter the cell address that has the store number.

This is what it will look like:

The VLOOKUP Formulas

Copy that formula down in each column, using this technique, and you now have all of that information available.

Note, as seen in the formulas above, that you will need absolute references to the cells that are the data table if you want to use AutoFill to copy the formulas down. This ensures that each formula looks in the same, correct range of cells.

Here is the result:

Result of Adding VLOOKUPs


VLOOKUP is a great time saver that allows you to lookup values in a cell in a table of data and pull another value from the same row that it found the data in. The resulting data can also be used in a PivotTable. The possibilities are nearly endless.

With the PowerPivot feature being added to Excel you have an option to VLOOKUPs. See this post to read more about PowerPivot and how to use it instead of VLOOKUP.

Let me know if you have any tips or questions related to using the VLOOKUP function.

Fill Data Automatically in Cells Using AutoFill

One of the benefits of Excel is how it reduces your time needed to enter items. One great feature is the AutoFill function.

If you have Excel 2013 and above, there is an extra ability added to those versions of Excel. It is Flash Fill, and we will cover in another post.

Autofill Features

AutoFill uses a pattern that you have already entered in cells and uses that to automatically fill other cells with that pattern.

You can quickly take a repeating, or changing series of numbers and have Excel extrapolate that pattern in more cells. By default, this works with days of the week and months as well.

For example, say you want to have all the months of the year in a column. You could type each month individually, or use AutoFill and save some time.

If you enter two month names and then use AutoFill, it will fill the rest of the cells with the month names following what you’ve entered.

To do this, use the fill handle. The fill handle is the little square that shows in the bottom right corner of the cell you select. Or if you select more than one cell, it will be the bottom, rightmost cell of the group that you select.

The default setting for the fill handle is to show it, but it can also be changed to hide it by default. If the fill handle isn’t showing for you, check your Excel Options.

Excel AutoFill Fill Handle Highlighted in Red Square

When you click on the fill handle and drag down, it fills in the cells with next value, which in this case is the next month. You can also drag up or drag left of the items, which would decrease the values.

Excel AutoFill Month Names
Using the Fill Handle to Autofill Month Names

Additional AutoFill Features

AutoFill can also be used to copy values and formulas down with only one double-click.

For examplie, if you had a column of data and wanted a formula that referred to each cell in the adjacent column of cells, type the formula in the first cell and then double-click the fill handle. This copies the formula down to the bottom of the column.

Formula to Have Autofill Applied
Result of Double-clicking to Autofill Formula


The Autofill feature in Excel is a real time saver for data entry. Not only that, but it also reduces the chances of spelling and formula errors.

Do you have some tips or comments about Autofill? Please let me know in the comments below.