Excel 下拉列表：創建、編輯、刪除及更多進階操作

下拉列表類似於列表框，允許用戶從選擇列表中選擇一個值。本教程將演示下拉列表的基本操作：在 Excel 中創建、編輯和刪除下拉列表。此外，本教程還提供了下拉列表的高級操作，以增強其功能，解決更多 Excel 問題。

創建簡單的下拉列表

要使用下拉列表，首先需要學習如何創建它。本節提供了6 種方法來幫助您在 Excel 中創建下拉列表。

從一個單元格區域創建下拉列表

在這裡，我們將演示如何從 Excel 中的單元格區域創建下拉列表。請按以下步驟操作

1. 選擇一個單元格區域以放置下拉列表。

提示：您可以通過按住 "Ctrl" 鍵同時選擇多個不連續的單元格來創建下拉列表。

2. 點擊 "數據" > "資料驗證" > "資料驗證"。

3. 在 "資料驗證" 對話框中，請在 "設定" 標籤下配置如下。

注意：

現在下拉列表已創建。點擊下拉列表單元格時，旁邊會顯示一個箭頭，點擊箭頭以展開列表，然後您可以從中選擇一個項目。

從表格創建動態下拉列表

您可以將數據區域轉換為 Excel 表格，然後基於表格區域創建動態下拉列表。

1. 選擇原始數據區域，然後按 "Ctrl" + "T" 鍵。

2. 在彈出的 "創建表格" 對話框中點擊 "確定"。然後數據區域被轉換為表格。

3. 選擇一個單元格區域以放置下拉列表，然後點擊 "數據" > "資料驗證" > "資料驗證"。

4. 在 "資料驗證" 對話框中，您需要：

然後，動態下拉列表被創建。當從表格區域添加或刪除數據時，下拉列表中的值將自動更新。

使用公式創建動態下拉列表

除了從表格區域創建動態下拉列表外，您還可以使用公式在 Excel 中創建動態下拉列表。

1. 選擇要輸出下拉列表的單元格。

2. 點擊 "數據" > "資料驗證" > "資料驗證"。

3. 在 "資料驗證" 對話框中，請配置如下。

=OFFSET($A$13,0,0,COUNTA($A$13:$A$24),1)

然後，動態下拉列表被創建。當從特定區域添加或刪除數據時，下拉列表中的值將自動更新。

從命名區域創建下拉列表

您還可以從 Excel 中的命名區域創建下拉列表。

1. 首先，創建一個命名區域。選擇您將基於其創建命名區域的單元格區域，然後在 "名稱" 框中輸入區域名稱，按 "Enter" 鍵。

2. 點擊 "數據" > "資料驗證" > "資料驗證"。

3. 在 "資料驗證" 對話框中，請配置如下。

現在使用命名區域數據的下拉列表已創建。

從另一個工作簿創建下拉列表

假設有一個名為 "SourceData" 的工作簿，您想在另一個工作簿中基於此 "SourceData" 工作簿中的數據創建下拉列表，請按以下步驟操作。

1. 打開 "SourceData" 工作簿。在此工作簿中，選擇您將基於其創建下拉列表的數據，在 "名稱" 框中輸入一個區域名稱，然後按 "Enter" 鍵。

在這裡我將區域命名為 City。

2. 打開您將插入下拉列表的工作表。點擊 "公式" > "定義名稱"。

3. 在 "新建名稱" 對話框中，您需要基於您在 "SourceData" 工作簿中創建的區域名稱創建一個命名區域，請配置如下。

=SourceData.xlsx!City

注意：

4. 打開您將插入下拉列表的工作簿，選擇下拉列表的單元格，然後點擊 "數據" > "資料驗證" > "資料驗證"。

5. 在 "資料驗證" 對話框中，請配置如下。

現在下拉列表已插入選擇的區域。下拉值來自另一個工作簿。

使用一個驚人的工具輕鬆創建下拉列表

在這裡，我強烈推薦 "Kutools for Excel" 的 "創建簡單下拉列表" 功能。使用此功能，您可以輕鬆創建具有特定單元格值的下拉列表或創建基於 Excel 中預設自訂列表的下拉列表。

1. 選擇您要插入下拉列表的單元格，然後點擊 "Kutools" > "下拉列表" > "創建簡單下拉列表"。

2. 在 "創建簡單下拉列表" 對話框中，請配置如下。

注意：如果您想基於 Excel 中預設的自訂列表創建下拉列表，請在 "來源" 部分選擇 "自訂列表" 選項，在 "自訂列表" 框中選擇一個自訂列表，然後點擊 "確定" 按鈕。

現在下拉列表已插入選擇的區域。

編輯下拉列表

如果您想編輯下拉列表，本節中的方法可以幫助您。

基於單元格區域編輯下拉列表

要基於單元格區域編輯下拉列表，請按以下步驟操作。

1. 選擇包含您要編輯的下拉列表的單元格，然後點擊 "數據" > "資料驗證" > "資料驗證"。

2. 在 "資料驗證" 對話框中，更改 "來源" 框中的單元格引用，然後點擊 "確定" 按鈕。

基於命名區域編輯下拉列表

假設您在命名區域中添加或刪除值，並且下拉列表是基於此命名區域創建的。要在下拉列表中顯示更新的值，請按以下步驟操作。

1. 點擊 "公式" > "名稱管理員"。

提示：您可以按 "Ctrl" + "F3" 鍵打開 "名稱管理員" 視窗。

2. 在 "名稱管理員" 視窗中，您需要配置如下：

2.1) 在 "名稱" 框中選擇您要更新的命名區域；

2.2) 在 "引用位置" 部分，點擊按鈕 以選擇更新的下拉列表區域；

2.3) 點擊 "關閉" 按鈕。

3. 然後彈出一個 "Microsoft Excel" 對話框，點擊 "是" 按鈕以保存更改。

然後基於此命名區域的下拉列表將被更新。

刪除下拉列表

本節討論如何在 Excel 中刪除下拉列表。

使用 Excel內建功能刪除下拉列表

Excel 提供了一個內建功能來幫助從工作表中刪除下拉列表。請按以下步驟操作。

1. 選擇包含您要刪除的下拉列表的單元格區域。

2. 點擊 "數據" > "資料驗證" > "資料驗證"。

3. 在 "資料驗證" 對話框中，點擊 "清除全部" 按鈕，然後點擊 "確定"以保存更改。

現在下拉列表已從選擇的區域中刪除。

使用一個驚人的工具輕鬆刪除下拉列表

"Kutools for Excel" 提供了一個方便的工具 - "清除資料驗證限制"，可以幫助輕鬆地從一個或多個選擇的區域中一次性刪除下拉列表。請按以下步驟操作。

1. 選擇包含您要刪除的下拉列表的單元格區域。

2. 點擊 "Kutools" > "限制輸入" > "清除資料驗證限制"。見截圖：

3. 然後彈出一個 "Kutools for Excel" 對話框詢問您是否清除下拉列表，請點擊 "確定" 按鈕。

然後選擇的區域中的下拉列表立即被刪除。

為下拉列表添加顏色

在某些情況下，您可能需要製作一個帶有顏色編碼的下拉列表，以便一目了然地區分下拉列表單元格中的數據。本節提供了兩種方法來幫助您詳細解決此問題。

使用條件格式為下拉列表添加顏色

您可以為包含下拉列表的單元格創建條件規則，使其具有顏色編碼。請按以下步驟操作。

1. 選擇包含您要使其具有顏色編碼的下拉列表的單元格。

2. 點擊 "首頁" > "條件格式" > "管理規則"。

3. 在 "條件格式規則管理器" 對話框中，點擊 "新建規則" 按鈕。

4. 在 "新建格式規則" 對話框中，請配置如下。

4.1) 在 "選擇規則類型" 框中，選擇 "僅格式化包含的單元格" 選項；

4.2) 在 "僅格式化包含的單元格" 部分，從第一個下拉列表中選擇 "特定文本"，從第二個下拉列表中選擇 "包含"，然後在第三個框中選擇來源列表的第一個項目；

提示：在這裡我在第三個文本框中選擇單元格 A16。A16 是我基於其創建下拉列表的來源列表的第一個項目。

4.3) 點擊 "格式" 按鈕。

4.4) 在 "設定儲存格格式" 對話框中，轉到 "填充" 標籤，為指定的文本選擇一個背景色，然後點擊 "確定" 按鈕。或者您可以根據需要為文本選擇某種字體顏色。

4.5) 返回到 "新建格式規則" 對話框時點擊 "確定" 按鈕。

5. 返回到 "條件格式規則管理器" 對話框時，重複上述步驟3 和4 為其他下拉項目指定顏色。完成顏色指定後，點擊 "確定"以保存更改。

從現在開始，當從下拉列表中選擇一個項目時，單元格將根據選擇的文本以指定的背景色突出顯示。

使用一個驚人的工具輕鬆為下拉列表添加顏色

在這裡，我們介紹 "Kutools for Excel" 的 "建立顏色下拉清單" 功能，幫助您輕鬆地在 Excel 中為下拉列表添加顏色。

1. 選擇包含您要添加顏色的下拉列表的單元格。

2. 點擊 "Kutools" > "下拉列表" > "建立顏色下拉清單"。

3. 在 "建立顏色下拉清單" 對話框中，請按以下步驟操作。

提示：如果您想基於下拉列表選擇突出顯示行，請在 "應用於" 部分選擇 "整列" 選項，然後在 "醒目列區域" 框中選擇您將突出顯示的行。

現在下拉列表已按以下截圖顯示的顏色編碼。

基於下拉列表選擇突出顯示單元格

基於下拉列表選擇突出顯示行

在 Excel 或 Google 表單中創建依賴下拉列表

依賴下拉列表有助於根據第一個下拉列表中選擇的值顯示選擇。如果您需要在 Excel 工作表或 Google 表單中創建依賴（級聯）下拉列表，本節中的方法可以幫助您。

在 Excel 工作表中創建依賴下拉列表

以下演示顯示了 Excel 工作表中的依賴下拉列表。

請點擊如何在 Excel 中創建依賴級聯下拉列表？ 獲取分步指南教程。

在 Google 表單中創建依賴下拉列表

如果您想在 Google 表單中創建依賴下拉列表，請參見如何在 Google 表單中創建依賴下拉列表？

創建可搜尋的下拉列表

對於包含長列表項目的下拉列表，在工作表中很難從列表中選擇某個項目。如果您記得某個項目的初始字符或幾個連續字符，您可以在下拉列表中使用搜尋功能來輕鬆篩選。本節將演示如何在 Excel 中創建可搜尋的下拉列表。

假設您要基於的源數據位於 Sheet1 的 A 列，如下圖所示。請按以下步驟操作，使用這些數據在 Excel 中創建可搜尋的下拉列表。

1. 首先，在源數據列表旁邊創建一個輔助列，使用數組公式。

在這種情況下，我選擇單元格 B2，將以下公式輸入其中，然後按 "Ctrl" + "Shift" + "Enter" 鍵以獲得第一個結果。

=IFERROR(INDEX($A$2:$A$50,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$50)>0,$A$2:$A$50,""),$A$2:$A$50,0),""),ROW(A1))),"")

選擇第一個結果單元格，然後拖動其 "填充手柄"直到列表末尾。

注意：在此數組公式中，$A$2:$A$50 是您將基於其創建下拉列表的源數據區域。請根據您的數據區域進行更改。

2. 點擊 "公式" > "定義名稱"。

3. 在 "編輯名稱" 對話框中，請配置如下。

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$50)-COUNTIF(Sheet1!$B$2:$B$50,""),1)

現在您需要基於命名區域創建下拉列表。在這種情況下，我將在 Sheet2 中創建可搜尋的下拉列表。

4. 打開 Sheet2，選擇下拉列表的單元格區域，然後點擊 "數據" > "資料驗證" > "資料驗證"。

5. 在 "資料驗證" 對話框中，請按以下步驟操作。

5.1) 在 "允許" 框中選擇 "列表"；

5.2) 點擊 "來源" 框，然後按 "F3" 鍵；

5.3) 在彈出的 "粘貼名稱" 對話框中，選擇您在步驟3 中創建的命名區域，然後點擊 "確定"；

提示：您可以直接在 "來源" 框中輸入命名區域為 "=命名區域"。

5.4) 點擊 "錯誤警告" 標籤，取消勾選 "輸入無效數據後顯示錯誤警告" 框，最後點擊 "確定" 按鈕。

6.右鍵點擊工作表標籤（Sheet2），從右鍵菜單中選擇 "檢視代碼"。

7. 在打開的 "Microsoft Visual Basic for Applications" 視窗中，將以下 VBA代碼複製到代碼編輯器中。

VBA代碼：在 Excel 中創建可搜尋的下拉列表

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

8. 按 "Alt" + "Q" 鍵關閉 "Microsoft Visual Basic for Applications" 視窗。

現在可搜尋的下拉列表已創建。如果您想選擇一個項目，只需在下拉單元格中輸入該項目的首字母或幾個連續字符，點擊下拉箭頭，然後根據輸入內容的項目將顯示在下拉列表中。見截圖：

注意：此方法區分大小寫。

創建下拉列表但顯示不同的值

假設您已創建一個下拉列表，當從中選擇一個項目時，您希望在單元格中顯示其他內容。如以下演示所示，您已基於國家名稱列表創建下拉列表，當從下拉列表中選擇國家名稱時，您希望在下拉單元格中顯示所選國家名稱的縮寫。本節提供 VBA 方法來幫助您解決此問題。

1. 在源數據的右側（國家名稱列），創建一個新列，包含您希望在下拉單元格中顯示的國家名稱縮寫。

2. 選擇國家名稱列表和縮寫列表，然後在 "名稱" 框中輸入一個名稱，按 "Enter" 鍵。

3. 選擇下拉列表的單元格（在這裡我選擇 D2:D8），然後點擊 "數據" > "資料驗證" > "資料驗證"。

4. 在 "資料驗證" 對話框中，請配置如下。

5. 創建下拉列表後，右鍵點擊工作表標籤，然後從右鍵菜單中選擇 "檢視代碼"。

6. 在打開的 "Microsoft Visual Basic for Applications" 視窗中，將以下 VBA代碼複製到代碼編輯器中。

VBA代碼：在下拉列表中顯示不同的值

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20201027
    selectedNa = Target.Value
    If Target.Column = 4 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

注意：

7. 按 "Alt" + "Q" 鍵關閉 "Microsoft Visual Basic for Applications" 視窗。

從現在開始，當從下拉列表中選擇某個國家名稱時，所選國家名稱的相應縮寫將顯示在單元格中。

創建帶有複選框的下拉列表

許多 Excel 用戶傾向於創建帶有多個複選框的下拉列表，以便他們可以通過勾選複選框從列表中選擇多個項目。

如以下演示所示，當點擊包含下拉列表的單元格時，會出現一個列表框。在列表框中，每個項目前都有一個複選框。您可以勾選複選框以在單元格中顯示相應的項目。

如果您想在 Excel 中創建帶有複選框的下拉列表，請參見如何在 Excel 中創建帶有多個複選框的下拉列表？

為下拉列表添加自動完成功能

如果您有一個包含大量項目的資料驗證下拉列表，您需要在列表中上下滾動以找到合適的項目，或直接在列表框中輸入整個單詞。如果下拉列表可以在輸入第一個字母時自動完成，一切將變得更容易。

要在 Excel 的工作表中使下拉列表自動完成，請參見如何在 Excel 下拉列表中輸入時自動完成？

基於下拉列表選擇篩選數據

本節將演示如何應用公式創建下拉列表篩選器，以根據下拉列表中的選擇提取數據。

1. 首先，您需要創建一個包含您將根據其提取數據的特定值的下拉列表。

提示：請按照上述步驟在 Excel 中創建下拉列表。

創建具有唯一項目列表的下拉列表

如果您的區域中有重複項目，並且您不想創建包含重複項目的下拉列表，您可以按以下步驟創建唯一項目列表。

1) 使用 "Ctrl" + "C" 鍵複製您將基於其創建下拉列表的單元格，然後將它們粘貼到新區域。

2) 選擇新區域中的單元格，點擊 "數據" > "刪除重複項"。

3) 在 "刪除重複項" 對話框中，點擊 "確定" 按鈕。

4) 然後彈出一個 "Microsoft Excel" 對話框告訴您刪除了多少重複項，點擊 "確定"。

現在您獲得了唯一項目列表，您現在可以基於此唯一列表創建下拉列表。

2. 然後您需要創建三個輔助列，如下所示。

2.1) 對於第一個輔助列（在這裡我選擇 D 列作為第一個輔助列），將以下公式輸入第一個單元格（不包括列標題），然後按 "Enter" 鍵。選擇結果單元格，然後拖動 "填充手柄"直到區域底部。

=ROWS($A$2:A2)

2.2) 對於第二個輔助列（E 列），在單元格 E2 中輸入以下公式，然後按 "Enter" 鍵。選擇 E2，然後拖動 "填充手柄" 到區域底部。

注意：如果在下拉列表中未選擇任何值，這裡公式的結果將顯示為空白。

=IF(A2=$H$2,D2,"")

2.3) 對於第三個輔助列（F 列），在 F2 中輸入以下公式，然後按 "Enter" 鍵。選擇 F2，然後拖動 "填充手柄" 到區域底部。

注意：如果在下拉列表中未選擇任何值，公式的結果將顯示為空白。

=IFERROR(SMALL($E$2:$E$17,D2),"")

3. 基於原始數據區域創建一個區域，以使用以下公式輸出提取的數據。

3.1) 選擇第一個輸出單元格（在這裡我選擇 J2），將以下公式輸入其中，然後按 "Enter" 鍵。

=IFERROR(INDEX($A$2:$C$17,$F2,COLUMNS($J$2:J2)),"")

3.2) 選擇結果單元格，然後拖動 "填充手柄" 向右拖動兩個單元格。

3.3) 保持選擇 J2:l2 區域，拖動填充手柄直到區域底部。

注意：

現在已創建下拉列表篩選器，您可以輕鬆地根據下拉列表選擇從原始數據區域中提取數據。

從下拉列表中選擇多個項目

默認情況下，下拉列表允許用戶每次在一個單元格中選擇一個項目。重新選擇下拉列表中的項目時，先前選擇的項目將被覆蓋。然而，如果要求您從下拉列表中選擇多個項目並將它們全部顯示在下拉單元格中，如下演示所示，您該如何操作？

要在 Excel 中從下拉列表中選擇多個項目，請參見如何在 Excel 中創建具有多個選擇或值的下拉列表？本教程提供了兩種詳細的方法來幫助您解決此問題。

為下拉列表設置默認（預選）值

默認情況下，下拉列表單元格顯示為空白，只有在點擊單元格時才會顯示下拉箭頭。如何一目了然地找出工作表中包含下拉列表的單元格？

本節將演示如何在 Excel 中為下拉列表設置默認（預選）值。請按以下步驟操作。

在應用以下兩種方法之前，您需要創建一個下拉列表並進行一些配置，如下所示。

1. 選擇下拉列表的單元格，點擊 "數據" > "資料驗證" > "資料驗證"。

提示：如果您已經創建了下拉列表，請選擇包含下拉列表的單元格，然後點擊 "數據" > "資料驗證" > "資料驗證"。

2. 在 "資料驗證" 對話框中，請配置如下。

2.1) 在 "允許" 框中選擇 "列表"；

2.2) 在 "來源" 框中選擇您將在下拉列表中顯示的源數據。

提示：對於您已經創建的下拉列表，請跳過這兩個步驟。

2.3) 然後轉到 "錯誤警告" 標籤，取消勾選 "輸入無效數據後顯示錯誤警告" 框；

2.4) 點擊 "確定" 按鈕。

創建下拉列表後，請應用以下方法之一為其設置默認值。

使用公式為下拉列表設置默認值

您可以應用以下公式為您創建的下拉列表設置默認值，如上步驟所示。

1. 選擇下拉列表單元格，將以下公式輸入其中，然後按 "Enter" 鍵以顯示默認值。如果下拉列表單元格是連續的，您可以拖動結果單元格的 "填充手柄" 將公式應用於其他單元格。

=IF(C2="", "--Choose item from the list--")

注意：

使用 VBA代碼一次性為工作表中的所有下拉列表設置默認值

假設您的工作表中有很多下拉列表位於不同的區域，要為它們全部設置默認值，您需要重複應用公式。這很耗時。本節提供了一個有用的 VBA代碼，幫助您一次性為工作表中的所有下拉列表設置默認值。

1. 打開包含您要設置默認值的下拉列表的工作表，按 "Alt" + "F11" 鍵打開 "Microsoft Visual Basic for Applications" 視窗。

2. 在 "Microsoft Visual Basic for Applications" 視窗中，點擊 "插入" > "模塊"，然後將以下 VBA代碼粘貼到代碼窗口中。

VBA代碼：一次性為工作表中的所有下拉列表設置默認值

Sub SetDropDownListToDefaultValue()
'Updated by Extendoffice 20201026
Dim xWs As Worksheet
Dim xRg, xFRg As Range
Dim xET: xET = Null
Dim xStr As String
xStr = "- Choose from the list -"
Set xWs = Application.ActiveSheet
Set xRg = xWs.UsedRange.Cells
    On Error Resume Next
    For Each xFRg In xRg
    xET = Null
    xET = xFRg.Validation.Type
    If Not IsNull(xET) Then
        If xFRg.Validation.Type = 3 Then
            xFRg.Value = "'" & xStr
        End If
    End If
    Next
End Sub

注意：在上述代碼中，"- 從列表中選擇 -" 是要在下拉列表單元格中顯示的默認值。您也可以根據需要更改默認值。

3. 按 "F5" 鍵，然後彈出一個宏對話框，確保在 "宏名稱" 框中選擇 "DropDownListToDefault"，然後點擊 "運行" 按鈕以運行代碼。

然後指定的默認值立即填充到下拉列表單元格中。

增加下拉列表字體大小

通常，下拉列表具有固定的字體大小，如果字體大小太小而無法閱讀，您可以嘗試以下 VBA 方法來放大它。

1. 打開包含您要放大字體大小的下拉列表的工作表，右鍵點擊工作表標籤，然後從右鍵菜單中選擇 "檢視代碼"。

2. 在 "Microsoft Visual Basic for Applications" 視窗中，將以下 VBA代碼複製到代碼編輯器中。

VBA代碼：放大工作表中下拉列表的字體大小

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice 20201027
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 130
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

注意：在這裡，代碼中的 "xZoom =130" 表示您將當前工作表中所有下拉列表的字體大小放大到130。您可以根據需要更改它。

3. 按 "Alt" + "Q" 鍵關閉 "Microsoft Visual Basic for Applications" 視窗。

從現在開始，當點擊下拉單元格時，當前工作表的縮放級別將被放大，點擊下拉箭頭，您可以看到所有下拉項目的字體大小也被放大。

從下拉列表中選擇一個項目後，您可以點擊下拉單元格外的任何單元格以返回到原始縮放級別。