Hark, ye Excel users! Lo, behold the wondrous drop-down list, a tool so swift and efficient it doth allow thee to choose from predefined data with mere ease. In sooth, thou canst save time galore when compared to the laborious task of manual data entry. Verily, these drop-down lists art perfect for myriad purposes, such as inputting information into a form.
How now to summon forth this marvel within Microsoft Excel on both Windows and Mac? Let us unfurl this tale.
Create a drop-down list by manually entering data
Aye, there be several methods to conjure a drop-down list in Excel, yet this particular path be the most straightforward. ‘Tis a boon for Excel novices and lists that require not frequent updates.
Step 1: Selecteth the cell in the column where thou seek’st to bestow a drop-down list.
Step 2: Make way to the Data tab, where ye shall uncover the Data validation button or opt to choose Data validation from the dropdown menu.
Step 3: Delve into the Allow menu within the ensuing window and select List.
Step 4: Within the Source field, input thou desired entries for the drop-down list. Remember to punctuate each item with a humble comma.
Lo and behold, the cell you selected in step 1 shall now boast a fully functioning drop-down list with all the entries from step 4.
Create a drop-down list by selecting a range of cells
The most common method for crafting a drop-down list in Excel with multiple choices is to employ a range sourced from other cells.
Step 1: Designate a column where thou intendest to house the data for the drop-down list. ‘Tis permissible to draw this data from the same spreadsheet or from a separate sheet. The latter shalt lend thy primary spreadsheet an air of tidiness and professionalism.
Step 2: Enter all thy data entries into the column, assigning each its rightful cell.
Step 3: Select the cell where thou wishest the drop-down list to grace with its presence.
Onwards to the Data tab, where the cherished Data validation is to be found in the dropdown menu.
Step 5: The Allow menu shall beckon thee to select List. An arrow shalt grace the edge of the Source field, calling for thy click.
Step 6: Back to the vista of thy spreadsheet, select the range of cells by dragging the cursor from the first to the last.
Step 7: A click on the Arrow button in the Data validation window shall yield a menu showcasing the entire cell range thou hast selected. ‘Tis time to exclaim, “OK!”
Hark! Lo and behold, a drop-down list now resides in the cell thou chose in step 3.
Display a message when the drop-down list is selected
Once thou hast forged thy drop-down list, ‘tis wise to make it more inviting by appending an input message.
Step 1: Visit the cell hosting the drop-down list and unveil the Data validation window once more.
Step 2: Traverse to the Input message tab. There shalt thou inscribe a fitting title and the text to be unveiled upon clicking the drop-down list. Take heed, this text must not surpass 225 characters.
Behold, the message is upon thy drop-down list.
Display an error alert
Just as thou canst impart a message describing the list’s purpose, an error alert may also be displayed when invalid data dareth to grace the hallowed list.
Step 1: Visit the cell where the drop-down lieth. Unfold the Data validation window anew, and select the Error alert tab. Mark yon box that proclaims, ‘Show error alert after invalid data is entered’.
Craft a custom title and message or allow Excel to furnish a default one.
Step 2: Make choice of a style from those proffered, such as Stop (X), and decree, “OK!”
Yet an alternative awaits should ye desire a message that permits the entry of invalid data. ‘Tis the choice betwixt Information and Warning, each with its own design.
Protect your drop-down list
To shield the source of thy drop-down list data from prying eyes and meddlesome fingers, ‘tis wise to lock those cells.
Step 1: Wander to the column bearing thy list’s data entries. Choose the cells to be locked against unauthorized alterations.
With the cells thus selected, saunter to the Home tab.
Step 2: Within the Alignment section, seek out the Small arrow to unveil the Format cells window. Proceed to the Protection tab, mark the Locked box, and intone, “OK!”
Step 3: Journey to the Review tab and opt for either Protect sheet or Protect workbook. Make amendments as needed and decree, “OK!”
Tarry not, for the use of drop-down lists in Excel doth work wonders in saving time. Though it demands some configuration at the outset, the fruits of thy labor shall be sweet. More Excel sorcery awaits, should thou seek to enhance thy workflow.
Support our work ❤️
If you enjoyed this article, consider leaving a tip to help us keep publishing great content.
