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:
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 email@example.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:
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