Creating Pick Lists in Excel
If you want to create a dynamic dashboard in Excel, I am sure you will want to use a picklist to change your data set at one time or another.
What do I mean?
Well, what if you had the following data and you wanted to create a chart that can let the users toggle between many different data sets? That way the don’t have to see all the data sets in the same chart or see 3 different charts?
This technique will help you create dynamic dashboards, but FIRST, you need to to understand how to create a picklist in Excel.
Have you every seen a picklist or drop-down menu in an Excel spreadsheet?
It is not that uncommon. In fact, the same technique can be used in your Google spreadsheets as well.
I recently had a client ask me how she could add a pick list to a Google document spreadsheet so that she could control the data input into the spreadsheet. Turns out that it is almost identical to the way that you create a pick list in a Microsoft Excel spreadsheet.
Here is how. It is pretty simple, but you may not know it by the name that you see in the Excel Ribbons.
What you need to look for is “Data Validation” under the Data Ribbon as you see in this picture:
1) To create your Excel spreadsheet picklist, you first need to determine if you want to create a static list or if you want to create a list from a range in your spreadsheet.
If you want to create your picklist from a range in your spreadsheet, you first need to type in each unique value in the spreadsheet. In my case I can do either one as the list is in my data set or I can just type it in to my data validation list.
Most people just create a static list and that is fine as well, although the spreadsheet range list is much more flexible.
2) Now that you have determined where you want your list, you now need to assign it to a cell. To do this, select the spreadsheet cell where you want the pick list to be displayed.
For my purposes, I have chosen to put my picklist in cell F1:
First put your cursor in cell F1 and then press to the Data Validation button under the Data Ribbon:
Once you do that, you will now see the Data Validation dialog box.
3) From the Data Validation dialog box, you will want to select the “List” option from the Validation Criteria area on the Settings tab:
4) From there you will now see a “Source” field appear. In this field you want to put in one of the following:
a) The spreadsheet range that has your pick list value:
b) Your pick list values separated by commas:
Now you are all set. If you click on Cell F1, you will now see a dropdown arrow that when you click on it, you will see your picklist of values that you set up in your data validation.
Learning how to create your own pick lists is the first step in creating dynamic charts for your next dashboard.
In the next tutorial, I will show you how to use this picklist to dynamically show a different set of data for in the same chart. Here is a link to the next tutorial: how-to-create-dynamic-excel-dashboards-using-picklists
Here is a video tutorial showing you how to make your own picklist in your Excel Spreadsheet:
Also, don’t forget to sign up for my blog and YouTube channel so that you are sure to get the next post delivered directly to your inbox.
What if the recipient isn’t seeing the validation picklist?
Hi Trisha, this is strange. I recently posted a video that the validation picklists seem to be harder to see in Excel 2013. What version are they using? Also, they have to select the cell to see them at all in the first place. Steve=True
I did run into a similar problem when working in 2013. When I had a “Paste Link” view from another page, the page with the linked paste range wouldn’t show the drop down list icon unless you clicked in that area and then it would go away. Are you using Excel 2013?