![]() ![]() Go back into Sheet 1 and highlight the area where we want the drop-down to appear, select the Data tab in the ribbon and Data Validation. Type in the name and you’ll notice the Refers to box already has the range in there (because we highlighted the months first), click OK. We want to name our range “Months”, so highlight the list of months, select Formulas tab on the ribbon and click Define Name. If we want our list to be in a different worksheet then we would have to provide a name for the range that contains the list.įor example Sheet 1 is where we want the drop-down selection to be, and Sheet 2 (called Lists) is where the list is located. This example had the list of months in the same worksheet as the cell that contains the drop-down. If we click on the arrow, the list of months should appear for us to select from…Īnd that’s how to create a drop-down list. You’ll notice a little drop-down arrow has now appeared to the right of the first cell in the highlighted area. Make sure In-cell drop-down is ticked and click OK… Select List on the drop-down arrow where it says Allow…Īnother option then appears where we specify the range that contains the items (in this example it’s the list of the months in cells E4:E15). This will open a dialogue box where we enter the criteria for the data. Next, we go to the Data tab on the ribbon and select Data Validation… Then highlight the column or area where we want the drop-down list to appear… To set this up we first need to type the list, in this case the months of the year. Part 2 shows the data being contained in a different worksheet to the list.įor example, we have a column with the heading Month, only months of the year can be input into this column so we need to set up a drop-down list specifying the months of the year, this way the user can only input what’s included in the list. Part 1 shows how to create a basic list using data contained in the same worksheet. It uses a function called Data Validation, which basically means users can only input data which is valid, data which you have specified. I think it’s one of those features that everyone should know how to do – it saves time and is very useful □ Right-click any cell in your source range, and select Insert from the dropdown menu.This tutorial is going to show you how to create a drop-down list in Excel. Excel will dynamically update your range selection in the data validation settings to include the new range that you’ve increased by one cell.ġ. Instead, you’ll need to insert a new entry somewhere in the middle of the range. You won’t be able to add an item to the end of the range, because your range selection is limited by the first and last cells you’ve selected. In addition to changing items in your range to update your lists, you can also add new items. You can have the contents of a single range control the items for all of those lists, and it doesn’t matter how many there are. This method is best when you want to format a lot of cells into a dropdown using the same list items. Any change you make there will update every dropdown list you created where you selected this range as the source. The nice thing about this approach is that you can change any of the items in that list just by modifying any cell in the range. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |