Drop down list แบบพิมพ์ค้นหาได้ ลดเวลาการหาข้อมูลที่ต้องการ

Introduction

This tutorial will guide you through creating a searchable dropdown list in Excel. This feature helps you quickly find the information you need without scrolling through long lists. The method described here allows for automatic updates when new data is added, making it a time-saving tool for your workflow.

Step 1: Prepare Your Data

• Start by organizing your data in a single column in an Excel worksheet.
• Ensure that the data is free of duplicates and sorted, if necessary.
• Name your data range for easier reference:
  • Select your data.
  • Go to the "Formulas" tab and click "Define Name."
  • Enter a name for your range (e.g., "MyData").

Step 2: Create a Dropdown List

• Select the cell where you want the dropdown list to appear.
• Navigate to the "Data" tab and click on "Data Validation."
• In the Data Validation dialog box:
  • Under the "Settings" tab, choose "List" from the "Allow" dropdown.
  • In the "Source" field, enter the name of your range (e.g., =MyData).
• Click "OK" to create the dropdown list.

Step 3: Add a Search Functionality

To make your dropdown list searchable, you will need to use a combination of a helper column and a formula. Follow these steps:

1. Create a Helper Column

   • Next to your original data, create a new column.
   • Enter the following formula in the first cell of the helper column (assuming your original data starts at A1):

     =IF(ISNUMBER(SEARCH($B$1, A1)), A1, "")
     

   • Drag the formula down to fill the cells in the helper column. This will filter your data based on the search term entered in cell B1.

2. Link the Dropdown to the Helper Column

   • Go back to the cell with the dropdown list.
   • Open "Data Validation" again.
   • In the "Source" field, enter the range of your helper column instead (e.g., =HelperColumnRange).

Step 4: Test Your Dropdown List

• Type a search term in the designated search cell (B1).
• Click on the dropdown list and check if it displays the filtered results.
• Adjust the search term to see how the dropdown updates dynamically.

Conclusion

By following these steps, you have created a searchable dropdown list in Excel that can automatically accommodate new data entries. This functionality not only enhances your data management but also saves you time when searching for specific items. Explore further by experimenting with additional Excel functions to customize your dropdown list even more!