3 Ways to Use Excel VLOOKUP() and PivotTable for Simple Solutions
While Excel VLOOKUP() and PivotTable are both great tools to have at your disposal, they can be confusing if you don’t know how to use them properly. You might already be familiar with their functionality, but you might not be as familiar with ways to use them together. By combining these two tools together, you can make simple solutions that are useful and easy to understand by other colleagues, who may not have the same expertise as you in this area of Excel functionality. Here are three ways to use Excel VLOOKUP() and PivotTable for simple solutions.
Finding Data in an Excel Table
If you have a table of data in Excel, there are a few ways you can use the VLOOKUP function to find what you’re looking for. You can also use the PivotTable tool to quickly summarize and analyze your data. Here are three ways to use these two features together 1) To look up data from an Excel Table: The first way is to enter the value you want to look up into the criteria box on a sheet and then click Data > VLOOKUP.
A small window will pop up with all the matching values on it, which you can click on one at a time until you find the right one. The second way is to enter the index number of the column containing the information you want to look up. For example, if you wanted to know how many cars Joe Smith owns, enter Joe Smith into column C and 0 in column D (which contains indexes).
Select Data > VLOOKUP again and type cars in cell E2. Click OK and this will return 8 because Joe Smith has 8 cars! *If you have more than one row with the same name, like Bill Bricks and Bill Jones, you’ll need to provide more columns as an index to narrow down your search. 2) To analyze multiple rows at once: Another great way to use VLOOKUP is by entering multiple criteria so that it looks up not just one row but all rows that match both sets of criteria.
Importing Data into a Spreadsheet
You can use the VLOOKUP function in Excel to quickly look up and retrieve data from another spreadsheet. To do this, you’ll first need to import the data into your spreadsheet. Here’s how -Select Data tab on the Ribbon
- Select From Text on the Data tab
- A box will appear that asks what type of file you want to import from (choose CSV)
- You will now be prompted to find where the file is located and select it. Make sure that all of your data has been entered in one column per row or has a header so that it can be easily imported. If you have any blank rows in between cells with data, you will have to delete them before importing. Once the file has been selected, click Import. The data should now be shown in your Spreadsheet as an additional table below the Table already there. You may also wish to rename this new table by right-clicking on its name and selecting Rename Sheet. Now let’s talk about how we can put these two powerful tools together!
Creating Easy Charts With PivotTables
You can use PivotTables to create easy charts in Excel. Just select the data you want to chart, click the Insert tab, and then click PivotChart. From there, you can choose the type of chart you want to create. Once it is created, you can make changes to the different options by clicking on one of the buttons at the top.
For example, if you wanted a bar graph instead of a line graph for your sales numbers, just click on Bar above your sales numbers and it will change your chart accordingly. If you have any additional questions about how to change these settings or what they do, be sure to check out our previous blog post on how pivot tables work !
The great thing about using pivot tables is that they are dynamic – so once you get them set up, you can update the values without having to re-create the entire table. As long as the columns remain in order, adding new rows or changing existing ones doesn’t affect the outcome of your chart.
