VLOOKUP Function

Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID.

In its simplest form, the VLOOKUP function says:

 

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

How to get started

There are four pieces of information that you will need in order to build the VLOOKUP syntax:

 

The value you want to look up, also called the lookup value.

 

The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.

 

The column number in the range that contains the return value. For example, if you specify B2:D11 as the range, you should count B as the first column, C as the second, and so on.

 

Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don’t specify anything, the default value will always be TRUE or approximate match.

 

Now put all of the above together as follows:

 

=VLOOKUP(lookup value, range containing the lookup value, the column number in the range containing the return value, Approximate match (TRUE) or Exact match (FALSE)).

Syntax

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Argument name Description
lookup_value    (required) The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify in the table_arrayargument.

For example, if table-array spans cells B2:D7, then your lookup_value must be in column B.

Lookup_value can be a value or a reference to a cell.

table_array    (required) The range of cells in which the VLOOKUP will search for the lookup_valueand the return value.

The first column in the cell range must contain the lookup_value. The cell range also needs to include the return value you want to find.

Learn how to select ranges in a worksheet.

col_index_num    (required) The column number (starting with 1 for the left-most column of table_array) that contains the return value.
range_lookup   (optional) A logical value that specifies whether you want VLOOKUP to find an approximate or an exact match:

·         Approximate match – 1/TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don’t specify one. For example, =VLOOKUP(90,A1:B100,2,TRUE).

·         Exact match – 0/FALSE searches for the exact value in the first column. For example, =VLOOKUP(“Smith”,A1:B100,2,FALSE).

Delete a Pivot table

When you no longer need a PivotTable, select the entire PivotTable, and press the Delete key to remove it.

If you get a “Cannot change this part of a PivotTable report” message, make sure the entire PivotTable is selected. Press Ctrl+A, and press Delete again.

 

If you’re using a device that doesn’t have a keyboard, try removing the PivotTable like this:

 

Pick a cell anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

Select Entire PivotTable

 

Click Analyze > Select, and then pick Entire PivotTable.

 

Press Delete.

Refresh Pivot table data

At any time, you can click Refresh to update the data for the PivotTables in your workbook. You can refresh the data for PivotTables connected to external data, such as a database (SQL Server, Oracle, Access, or other), Analysis Services cube, data feed, and many other sources. You can also refresh data from a source table in the same or a different workbook. And you can set your workbook to refresh its PivotTable data automatically when you open it.

 

By default, PivotTables are not refreshed automatically, but you can specify that the PivotTable is automatically refreshed when you open the workbook that contains the PivotTable.

 

Manually refresh

Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

 

PivotTable Tools

 

Click Analyze > Refresh, or press Alt+F5.

 

Refresh button on the Analyze tab

 

Tip:  To update all PivotTables in your workbook at once, click Analyze > Refresh All.

 

If refreshing takes longer than you expect, click Analyze > Refresh arrow > Refresh Status to check the refresh status.

 

To stop refreshing, click Cancel Refresh.

 

Prevent column widths and cell formatting from adjusting

If the column widths and cell formatting of your data adjust when you refresh the PivotTable data, and you don’t want that to happen, make sure the following options are checked:

 

Click Analyze > Options.

 

Options button on the Analyze tab

 

On the Layout & Format tab, check the Autofit column widths on update and Preserve cell formatting on update boxes.

 

Refresh data automatically when opening the workbook

Click anywhere in the PivotTable to show the PivotTable Tools on the ribbon.

 

PivotTable Tools

 

Click Analyze > Options.

 

Options button on the Analyze tab

 

On the Data tab, check the Refresh data when opening the file box.

 

Create Pivot table with External Data Source

Being able to analyze all the data can help you make better business decisions. But sometimes it’s hard to know where to start, especially when you have a lot of data that is stored outside of Excel, like in a Microsoft Access or Microsoft SQL Server database, or in an Online Analytical Processing (OLAP) cube file. In that case, you’ll connect to the external data source, and then create a PivotTable to summarize, analyze, explore, and present that data.

 

Here’s how to create a PivotTable by using an existing external data connection:

 

Click any cell on the worksheet.

 

Click Insert > PivotTable.

 

PivotTable button on the Insert tab

 

In the Create PivotTable dialog box, under Choose the data that you want to analyze, click Use an external data source.

 

Create PivotTable dialog box with Use an external data source selected

 

Click Choose Connection.

 

On the Connections tab, in the Show box, keep All Connections selected, or pick the connection category that has the data source you want to connect to.

 

To reuse or share an existing connection, use a connection from Connections in this Workbook.

 

In the list of connections, select the connection you want, and then click Open.

 

Under Choose where you want the PivotTable report to be placed, pick a location.

 

To place the PivotTable in a new worksheet starting at cell A1, choose New Worksheet.

 

To place the PivotTable in the active worksheet, choose Existing Worksheet, and then in the Location box, enter the cell where you want the PivotTable to start.

 

Click OK.

 

Excel adds an empty PivotTable and shows the Field List so that you can show the fields you want and rearrange them to create your own layout.

 

Field List showing external data fields

 

In the field list section, check the box next to a field name to place the field in a default area of the areas section of the Field List.

 

Typically, nonnumeric fields are added to the Rows area, numeric fields are added to the Values area, and date and time fields are added to the Columns area. You can move fields to a different area as needed.

 

Tip: You can also right-click a field name, and then select Add to Report Filter, Add to Column Labels, Add to Row Labels, or Add to Values to place the field in that area of the areas section, or drag a field from the field section to an area in the areas section.

 

Use the Field List to further design the layout and format of a PivotTable by right-clicking the fields in the areas section, and then selecting the area you want, or by dragging the fields between the areas in the areas section.

 

Connect to a new external data source

To create a new external data connection to SQL Server and import data into Excel as a table or PivotTable, do the following:

 

Click Data > From Other Sources.

 

From Other Sources button on the Data tab

 

Click the connection you want.

 

Click From SQL Server to create a connection to a SQL Server table.

 

Click From Analysis Services to create a connection to a SQL Server Analysis cube.

 

In the Data Connection Wizard, complete the steps to establish the connection.

 

On page 1, enter the database server and specify how you want to log on to the server.

 

On page 2, enter the database, table, or query that contains the data you want.

 

On page 3, enter the connection file you want to create.

 

To create a new connection to an Access database and import data into Excel as a table or PivotTable, do the following:

 

Click Data > From Access.

 

From Access button on the Data tab

 

In the Select Data Source dialog box, locate the database you want to connect to, and click Open.

 

In the Select Table dialog box, select the table you want and then click OK.

 

If there are multiple tables, check the Enable selection of multiple tables box so you can check the boxes of the tables you want, and then click OK.

 

In the Import Data dialog box, select how you want to view the data in your workbook and where you want to put it, and then click OK.

 

The tables are automatically added to the Data Model, and the Access database is added to your workbook connections.

Filter Data in a Pivot table

Filter your data to focus on a smaller portion of your PivotTable data for in-depth analysis. First, insert one or more slicers for a quick and effective way to filter your data. Slicers have buttons you can click to filter the data, and stay visible with your data, so you always know what fields are shown or hidden in the filtered PivotTable.

 

Tip: Now in Excel 2016, you can multi-select slicers by clicking the button on the label as shown above.

 

Filter data in a PivotTable

Select a cell in the PivotTable. Select Analyze > Insert Slicer  Slicer .

 

Select the fields you want to create slicers for. Then select OK.

 

Select the items you want to show in the PivotTable.

 

Filter data manually

Select the column header arrow   Filter drop-down arrow  for the column you want to filter.

 

Uncheck (Select All) and select the boxes you want to show. Then select OK.

Use Slicers to Filter Data

Slicers provide buttons that you can click to filter table data ,or PivotTable data. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to understand what exactly is shown in a filtered PivotTable.

 

You can use a slicer to filter data in a table with ease.

 

Note: In order to use a slicer, your data should be in a table.

 

Use a slicer to filter data

Click anywhere in the table.

 

Select Insert > Slicer.

 

Insert Slicer

In the Insert Slicers dialog box, select the check box of the PivotTable fields for which you want to create a slicer.

 

Select OK.

 

A slicer is displayed for every field that you selected.

 

Optionally you can adjust your slicer preferences in the Slicer tab (in newer versions of Excel) or the Design tab (Office 2016 and older versions) on the ribbon.

 

To clear a slicer’s filters, select Clear Filter  Delete in the slicer.

 

Note: To select more than one item, hold Ctrl, and then select the items that you want to show. Select and hold the corner of a slicer to adjust and resize it.

Create a Pivot Chart

 

Create a PivotChart

Select a cell in your table.

 

Select Insert > PivotChart  PivotChart option on the ribbon .

 

Select OK.

 

Create a chart from a PivotTable

Select a cell in your table.

 

Select PivotTable Tools > Analyze > PivotChart  PivotChart option on the ribbon .

 

Select a chart.

 

Select OK

Create a Pivot Table

Create a PivotTable

Select the cells you want to create a PivotTable from.

 

Note:  Your data shouldn’t have any empty rows or columns. It must have only a single-row heading.

 

Select Insert > PivotTable.

 

Pivot Table

 

Under Choose the data that you want to analyze, select Select a table or range.

 

Pivot Table

 

In Table/Range, verify the cell range.

 

Under Choose where you want the PivotTable report to be placed, select New worksheet to place the PivotTable in a new worksheet or Existing worksheet and then select the location you want the PivotTable to appear.

 

Select OK.

 

Building out your PivotTable

To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.

 

Note: Selected fields are added to their default areas: non-numeric fields are added to Rows, date and              time hierarchies are added to Columns, and numeric fields are added to Values.

 

Pivot Table

 

To move a field from one area to another, drag the field to the target area.

Create a named range in Excel

Here’s how you can create a named range in your spreadsheet:

 

On the Formulas tab, in the Defined Names group, click Define Name.

In the New Name dialog box, in the Name box, type the name you want to use for your reference.

Note: Names can be up to 255 characters in length.

To specify the scope of the named range, in the Scope drop-down list box, select Workbook or the name of a worksheet in the workbook.

A named range set to a Workbook scope will be available for use throughout the workbook, whereas a range set to a particular sheet’s scope can be used within the sheet only.

Optionally, in the Comment box, enter a descriptive comment up to 255 characters.

In the Refers to box, do one of the following:

 

Click Collapse Dialog  Button image (which temporarily shrinks the dialog box), select the cells on the worksheet, and then click Expand Dialog  Button.

 

To enter a constant, type = (equal sign) and then type the constant value.

 

To enter a formula, type = and then type the formula.

 

Click OK.