https://youtube.com/watch?v=fNfooOsIq2M

In this tutorial let us see how to Create Data Validation Lists from Pivot Tables in Excel

Let us consider a situation where you need to get the data validated list of candidates, from where you need to create a drop-down menu.

Here is the data with a list of candidates and their sales data.
First, let me create a drop-down menu in the cell F4, by going to Data tab Data tools group, click the data validation tool button.

This will open up data validation dialogue. From allow drop-down select list and in source input field select the source as B4:B39.

Now look at the result and see what kind of list I got.
This is not exactly what is needed because I have got the duplicate entries as well which is not required.

So, how to solve this problem and get only the unique values in the drop-down list?

1st method is to copy the whole column containing candidate names into other worksheets and Remove duplicates command from the Data tab and select the unique list as the source for the drop-down.

But this method is also having a serious issue.

What if the list grows and names get added in the future? In that case, we will have to remove the duplicates and make the unique list every time to create the new drop-down list.

So the best solution is to use the pivot table.
Step-1: select the data and convert them into a table by pressing Ctrl+T

Step-2: Select the table now and Go to the insert tab and click on the pivot table and click ok to create a pivot table.

Step-3: from the pivot table fields, drag the candidate name field to rows. This will give you candidates’ names sorted and unique names.

Step-4: select this pivot table, go to the design tab, from layouts, click grand total, and click off for rows and columns. This will remove the grand total row from the pivot table.

Step-5: Rename this sheet as a pivot

Step-6: Now go to the cell F4, where you want to have the drop-down list

Step-7: Go to Data tab,Data tools group,click the data validation tool button. This will open up data validation dialogue. From allow drop-down select list and in the source input field put this formula:

=OFFSET(pivot!$A$4,0,0,COUNTA(pivot!$A:$A)-1)

Now, if I add any new candidate’s name and refresh the pivot table my drop-down list will get updated automatically and thus saves our time in report making.

So, friends this is how to Create Data Validation Lists from Pivot Tables in Excel

I hope you have enjoyed this tutorial, If yes, please do give me a like, share and comment.
For further more interesting videos, please do subscribe dptutorials.

For Personalized detail learning, write to dptutorials15@gmail.com

If you found this video valuable, give it a like. If you know someone who needs to see it, share it. Leave a comment below with your thoughts. Add it to a playlist if you want to watch it later.

***********************************************
★ My Online Tutorials ► https://www.dptutorials.com

LEARNING RESOURCES I Recommend: https://www.dptutorials.com/resources

Subscribe for more Awesome Tutorials: http://goo.gl/NyAtg2

Support the Channel via shopping: http://ift.tt/2jH38PR

Tools that I use for Vlogging:
• Laptop: https://amzn.to/2E5U3MO
• Canon 200D Camera: https://amzn.to/2HglDYA
• Benro Tripod: https://amzn.to/2ExRvYj
• Blue yeti microphone: https://amzn.to/2VwZyPn
• Collar Microphone: https://amzn.to/2VyOdcM
• Bluedio Headset: https://amzn.to/2W3xUZX
• Screen recorder: https://techsmith.pxf.io/2BMjA
• Boom Arm Stand: https://amzn.to/2VCQNi7
• Zoom H1 Audio Recorder: https://amzn.to/2VDumsT
• Harison Softbox Studio Lights: https://amzn.to/2LY7x3u
• Chroma Key Green Screen: https://amzn.to/2ExTr31
• Background Support Stand: https://amzn.to/2McqnEv
• Acoustic Foam Background: https://amzn.to/2EoQ1zj
• USB RGB LED Strip: https://amzn.to/2VItKm4
• Wireless Mouse: https://amzn.to/2E23cpN

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

***********************************************

You Can Connect with Me at:
YouTube: www.youtube.com/c/dptutorials
G+: http://ift.tt/2kAOpa6
Twitter: https://twitter.com/dptutorials15
Facebook: http://ift.tt/2kfRnDi
BlogSpot: http://ift.tt/2kB14dh

Follow:
www.dptutorials.com
www.askplanner.blogspot.com

Tags: –
excel formulas in English, excel in English, excel tutorial in English,ms excel in English,ms excel tutorial in English, learn excel in English,vlookup in excel in English, learn ms excel in English, excel training, excel tutorial, Microsoft Excel 2007, learn excel,tutorial excel, ms excel tutorial, excel tutorials,ms excel 2007,Microsoft Excel training,learn excel online,learning excel,free excel training,online excel training,advanced excel tutorial,excel tutorial, excel formulas and functions, excel formulas