VOices Blog Members Home Events English | Français Shopping Cart
Skip to main content
VOices

Blog > How to use a pivot table to create vlookup values in Excel

How to use a pivot table to create vlookup values in Excel

posted on Mar 8, 2017
Excel%2bBeyond%2bThe%2bBasics-350px.jpg

 

Pivot tables are a great tool for drilling down into data to find relationships you may never have realized were there. Another thing pivot tables can do is help you clean up a data set for easier analysis. Let’s look at how that can be done.

Let’s say you keep all your volunteer data (name, address, etc) in a spreadsheet. One of the things you track is how they heard about your organization. You probably have a dropdown pick list with an additional category for “other”. Here is a glimpse of what your data table might look like:

2017_03_08_Excel_1.png

You want to find out which publicity is most effective. You could make a pivot chart right away from your existing data in column F, to get a quick and dirty sense of what information you have. This is what you get. When you compare the chart with the data table, you’ll see that some of the most important data isn’t even on the chart, because you’ve got too many categories.

2017_03_08_Excel_2.png

Look more closely at your data table. A lot of these responses can be grouped. For example, if you search your volunteer list you’ll see that Chandra Lagos is an existing volunteer. So the two people who typed in her name (one of whom misspelled it) really were referred by an existing volunteer. Add a column to enter in data which will be more useful to you. When you’re done, it might look like this:

2017_03_08_Excel_3.png

Now go into your data table, add in a column next to “How did you hear about us” and use a vlookup to replace the existing data with your more useful data. Your reference table is the one above. Your vlookup will look like this:

2017_03_08_Excel_4.png

2017_03_08_Excel_5.png

At this point you’ll need to re-run your pivot chart from scratch because that original pivot table is being used to clean up your data. Rename your original pivot sheet to indicate that it is now there as a vlookup reference table. (if you want, delete the chart from that sheet because it’s not useful). Put your new pivot chart and table into a new sheet.

2017_03_08_Excel_6.png

If this is still too many categories for your analysis, then go back to your vlookup sheet and consolidate some of the categories in the third column.

Now you can draw some useful insights. For example, the greatest number of volunteers were referred by an existing volunteer. Do you have an incentive program for volunteers to refer their friends? If not, then you might want to start one. If yes, then maybe think about beefing it up to see if you can get even more referrals that way. Another thing you can notice is that your paid advertising is a waste of money. It was worth a try, but now you have the data to justify stopping it.

This is just one of the ways you can turn a confusing data sheet into actionable information. Want to learn more? Come to our workshop, Excel 201: Pivot Tables and Charts. If you’re not ready yet for pivot tables, then bone up on the basics in Excel 101: Introduction.

Click here to register  Excel 201: Pivot Tables and Charts, Monday, April 3, 9:00 a.m. to 12:00 p.m.

Click here to register  Excel 101: Introduction, VO Night School Thursday, September 28, 6:00 p.m. to 9:00 p.m.

Filed under: Operations
new comment comment