Creating Pick Lists in Excel
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.
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.
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:
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.