Hướng dẫn tạo danh sách thả xuống trong Excel: tĩnh, động và từ một workbook khác

Trong bài viết này, bạn tạo khám phá bốn cách tạo danh sách Data Validation Excel (drop-down list) khác nhau: dựa trên danh sách giá trị, phạm vi của ô, phạm vi được đặt tên và danh sách dropdown động. Đồng thời chúng tôi cũng sẽ hướng dẫn bạn cách tạo danh sách dropdown từ một workbook khác, cũng như cách chỉnh sửa và xóa danh sách Data Validation trong Excel.

Drop-down list (danh sách thả xuống) của Excel, còn gọi là hộp thả xuống hoặc hộp kết hợp, được sử dụng để nhập dữ liệu vào bảng tính từ danh sách các mục được xác định trước. Mục đích chính của việc sử dụng danh sách thả xuống trong Exce l là để hạn chế số lượng lựa chọn có sẵn cho người dùng. Ngoài ra, menu thả xuống còn ngăn chặn lỗi chính tả và giúp nhập dữ liệu nhanh hơn.

Nội dung chính

1 Cách tạo và chỉnh sửa danh sách thả xuống (drop-down list) tĩnh trong Excel

1.1 Tạo danh sách thả xuống với các giá trị được phân tách bằng dấu phẩy
1.2 Tạo danh sách thả xuống Excel dựa trên phạm vi đã đặt tên
1.3 Danh sách Excel data validation bằng table object
1.4 Tạo một hộp thả xuống dựa trên một loạt các ô

2 Tạo danh sách thả xuống Excel động (tự động cập nhật)
3 Tạo danh sách thả xuống từ một workbook khác

3.5 Tạo danh sách thả xuống tĩnh từ một workbook khác
3.6 Danh sách thả xuống động từ một workbook khác

4 Một số điểm cần lưu ý khi tạo danh sách thả xuống trong Excel

4.7 Xử lý lỗi Data Validation không hoạt động
4.8 Các tùy chọn bổ sung cho hộp thả xuống Excel

4.8.1 Hiển thị thông báo khi nhấp vào một ô có menu thả xuống
4.8.2 Cho phép người dùng nhập dữ liệu vào combo box

5 Cách xóa danh sách thả xuống trong Excel

5.9 Xóa danh sách thả xuống khỏi (các) ô đã chọn
5.10 Xóa danh sách thả xuống Excel khỏi tất cả các ô trong trang tính hiện tại

Cách tạo và chỉnh sửa danh sách thả xuống (drop-down list) tĩnh trong Excel

Có 4 cách để tạo menu thả xuống trong Excel, mỗi cách đều có ưu, nhược điểm riêng. Dưới đây là hướng dẫn chi tiết cho từng phương pháp.

Xem thêm khóa học online Thủ thuật Excel cập nhật hàng tuần cho dân văn phòng cực kỳ hữu ích, giúp các bạn làm việc trở nên thông minh và chuyên nghiệp trong mắt bạn bè...

Tạo danh sách thả xuống với các giá trị được phân tách bằng dấu phẩy

Đây là cách tạo hộp thả xuống trong tất cả các phiên bản Excel 2016, 2013, 2010, 2007 và 2003 nhanh nhất chỉ với 3 bước.

1. Chọn một ô hoặc dãy ô cho danh sách thả xuống của bạn. Nếu bạn muốn chọn nhiều ô không liền kề, bấm và giữ phím Ctrl trong khi chọn.

2. Sử dụng Excel Data Validation để tạo danh sách thả xuống. Trong Data tab > Data Tools group, sau đó lick Data Validation

3. Nhập các mục trong danh sách và chọn các tùy chọn.

Trong cửa sổ Data Validation, trên tab Settings, hãy thực hiện như sau:

Trong hộp Allow, chọn List
Trong hộp Source, nhập các mục bạn muốn xuất hiện trong trình đơn thả xuống được phân tách bằng dấu phẩy (có hoặc không có dấu cách)
Click chọn hộp In-cell dropdown; nếu không, mũi tên thả xuống sẽ không xuất hiện bên cạnh ô
Chọn hoặc hoặc bỏ chọn Ignore blank tùy thuộc vào cách bạn muốn xử lý các ô trống
Nhấp vào OK và bạn đã hoàn tất!

Chỉnh sửa danh sách thả xuống được phân tách bằng dấu phẩy

Nếu bạn đã tạo một hộp thả xuống được phân tách bằng dấu phẩy thì hãy thực hiện theo các bước sau:

Chọn một ô hoặc các ô tham chiếu đến danh sách Data Validation Excel của bạn, tức là các ô chứa hộp thả xuống mà bạn muốn chỉnh sửa
Bấm Data Validation (Excel ribbon > Data tab)
Xóa hoặc nhập các mục mới vào hộp Source.
Bấm OK để lưu các thay đổi và đóng cửa sổ Data Validation Excel

Nếu bạn muốn áp dụng các thay đổi cho tất cả các ô chứa danh sách thả xuống này thì hãy chọn tùy chọn "Apply these changes to all other cells with the same settings"

Tạo danh sách thả xuống Excel dựa trên phạm vi đã đặt tên

1. Nhập các mục bạn muốn xuất hiện trong danh sách thả xuống của mình. Các giá trị này phải được nhập vào một cột hoặc một hàng mà không có bất kỳ ô trống nào.

2. Tạo một dải ô được đặt tên

Để tạo một Phạm vi được đặt tên (Named range) theo cách sau:

Chọn tất cả các mục bạn muốn đưa vào danh sách thả xuống, bấm chuột phải vào chúng và chọn Define Name từ menu ngữ cảnh. Ngoài ra, bạn có thể nhấp vào Name Manager trên tab Công thức hoặc nhấnCtrl + F3.
Trong hộp thoại Trình quản lý tên, bấm New
Trong trường Name, hãy nhập tên cho các mục nhập của bạn, đảm bảo rằng phạm vi chính xác được hiển thị trong hộp Tham chiếu đến , sau đó bấm OK. Đảm bảo rằng tên phạm vi của bạn không có bất kỳ dấu cách hoặc dấu gạch nối nào, thay vào đó hãy sử dụng dấu gạch dưới (_)

3. Chọn vị trí cho danh sách thả xuống của bạn

4. Áp dụng Excel Data Validation và thiết lập các cài đặt phù hợp

Chỉnh sửa danh sách thả xuống Excel dựa trên phạm vi đã đặt tên

Nếu bạn đã tạo một hộp thả xuống dựa trên phạm vi được đặt tên thì chỉ cần chỉnh sửa các mục trong phạm vi của mình và sau đó thay đổi tham chiếu đến Named Range. Tất cả các hộp thả xuống dựa trên phạm vi được đặt tên này sẽ được cập nhật tự động.

Thêm hoặc xóa các mục trong phạm vi đã đặt tên. Mở trang tính có chứa phạm vi đã đặt tên, xóa hoặc nhập các entry mới. Hãy nhớ sắp xếp các mục theo thứ tự bạn muốn chúng xuất hiện trong danh sách thả xuống Excel của bạn
Thay đổi tham chiếu đến Named Range.

Trên thanh công cụ Excel, bạn cần chuyển đến tab Formulas tab > Name Manager hoặc nhấn tổ hợp Ctrl + F3 để mở cửa sổ Name Manager.
Trong cửa sổ Name Manager, hãy chọn phạm vi đã đặt tên mà bạn muốn cập nhật.
Thay đổi tham chiếu trong hộp Refers to bằng cách nhấp vào biểu tượng Collapse Dialog và chọn tất cả các entry cho danh sách thả xuống của bạn.
Nhấp vào Close, sau đó trong thông báo xác nhận xuất hiện, click vào Yes để lưu các thay đổi của bạn.
Để tránh việc cập nhật các tham chiếu của dải ô đã đặt tên sau mỗi lần thay đổi danh sách nguồn, bạn có thể tạo một menu thả xuống Excel động. Trong trường hợp này, danh sách thả xuống của bạn sẽ được cập nhật tự động trong tất cả các ô được liên kết ngay sau khi bạn xóa hoặc thêm các mục mới vào danh sách.

Danh sách Excel data validation bằng table object

Thay vì sử dụng một dải ô được đặt tên thông thường, bạn có thể chuyển đổi dữ liệu của mình sang một bảng Excel đầy đủ chức năng ( Insert > Table hoặc bấm Ctrl + T) và sau đó tạo danh sách xác thực dữ liệu từ bảng đó.

Để làm điều này, bạn nhập = your_table_name [column_name] vào trường Refers to field hoặc chọn tất cả các ô không có tiêu đề cột trước khi mở Name Manager và tự động điền hộp Refers to.

Khi bạn tạo danh sách thả xuống theo cách này, mỗi khi bạn thêm một hàng mới vào bảng, danh sách thả xuống của bạn sẽ tự động cập nhật.

Tạo một hộp thả xuống dựa trên một loạt các ô

Để tạo một hộp thả xuống dựa trên một dải ô, hãy thực hiện các bước sau:

Nhập các mục vào các ô riêng biệt.
Chọn ô mà bạn muốn danh sách thả xuống xuất hiện.
Trên tab Data tab, chọn Data Validation.

Đặt con trỏ vào hộp Source hoặc nhấp vào biểu tượng Collapse Dialog và chọn phạm vi ô để đưa vào danh sách thả xuống của bạn. Phạm vi có thể nằm trong cùng một hoặc trong một trang tính khác. Bạn chỉ cần chuyển đến trang tính khác và chọn một phạm vi bằng chuột.

Chỉnh sửa menu thả xuống dựa trên một loạt các ô

Nếu bạn đã tạo một hộp thả xuống bằng cách chỉ định một dãy ô thay vì tham chiếu một dải ô đã đặt tên thì hãy tiến hành theo cách sau.

Đi tới bảng tính chứa các mục xuất hiện trong hộp thả xuống của bạn và chỉnh sửa danh sách theo cách bạn muốn.
Chọn ô hoặc các ô có chứa danh sách thả xuống của bạn.
Nhấp vào Data Validation trong tab Data.
Trong cửa sổ Data Validation Excel, trên tab Settings, bạn cần thay đổi tham chiếu ô trong hộp Source. Bạn có thể chỉnh sửa theo cách thủ công hoặc nhấp vào biểu tượng Collapse Dialog.
Nhấp vào nút OK để lưu các thay đổi và đóng cửa sổ.

Tạo danh sách thả xuống Excel động (tự động cập nhật)

Nếu bạn thường chỉnh sửa các mục trong trình đơn thả xuống, bạn có thể muốn tạo danh sách thả xuống động trong Excel. Trong trường hợp này, danh sách của bạn sẽ được cập nhật tự động trong tất cả các ô chứa nó, sau khi bạn xóa hoặc thêm các mục mới vào danh sách nguồn.

Cách tạo danh sách thả xuống được cập nhật động trong Excel đơn giản nhất là tạo một danh sách được đặt tên dựa trên một bảng. Nếu bạn thích một dải ô được đặt tên thông thường hì hãy tham chiếu nó bằng công thức OFFSET theo các bước bên dưới:

Bắt đầu bằng cách tạo một danh sách thả xuống thông thường dựa trên một phạm vi được đặt tên như mô tả ở trên.
Ở bước 2, khi tạo tên, bạn đặt công thức sau vào hộp Refers to.

= OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1).

Trong đó

Sheet1 - tên của trang tính
A - cột nơi chứa các mục trong danh sách thả xuống của bạn
$A$1 - ô chứa mục đầu tiên của danh sách

Như bạn thấy, công thức này bao gồm 2 hàm Excel là OFFSET và COUNTA. Hàm COUNTA đếm tất cả các ô trống trong cột được chỉ định, sau đó trả về một tham chiếu đến một phạm vi chứa các ô không trống, bắt đầu từ ô đầu tiên bạn chỉ định trong công thức.

Tạo danh sách thả xuống từ một workbook khác

Bạn có thể tạo menu thả xuống trong Excel bằng cách sử dụng danh sách từ một workbook khác làm nguồn. Để thực hiện việc này, bạn sẽ phải tạo 2 phạm vi được đặt tên - một trong sách nguồn và một trong sách mà bạn muốn sử dụng danh sách Excel Data Validation của mình.

Tạo danh sách thả xuống tĩnh từ một workbook khác

Danh sách thả xuống được tạo theo cách này sẽ không tự động cập nhật khi bạn thêm hoặc xóa các entry trong danh sách nguồn và bạn sẽ phải sửa đổi phần danh sách nguồn theo cách thủ công.

1. Tạo một phạm vi đã đặt tên cho danh sách nguồn.

Mở workbook có chứa danh sách nguồn, ở ví dụ này chúng ta sẽ chọn SourceBook.xlsx, sau đó tạo một phạm vi đã đặt tên cho các entry mà bạn muốn đưa vào danh sách thả xuống của mình, ví dụ: Source_list.

2. Tạo một tham chiếu được đặt tên trong workbook chính.

Mở workbook mà bạn muốn danh sách thả xuống xuất hiện và tạo tên tham chiếu đến danh sách nguồn của bạn. Trong ví dụ này, tham chiếu hoàn chỉnh là = SourceBook.xlsx! Source_list

Bạn phải đặt tên workbook trong dấu nháy (') nếu nó chứa bất kỳ khoảng trắng nào. Ví dụ: ='Source Book.xlsx'!Source_list

Tạo danh sách thả xuống tĩnh từ một workbook khác

3. Áp dụng Data Validation

Trong workbook chính, chọn (các) ô cho danh sách thả xuống của bạn, click vào Data > Data Validation và nhập tên bạn đã tạo ở bước 2 vào hộp Source.

Danh sách thả xuống động từ một workbook khác

Danh sách thả xuống được tạo theo cách này sẽ được cập nhật ngay sau khi bạn thực hiện bất kỳ thay đổi nào đối với danh sách nguồn.

Tạo tên phạm vi trong workbook Source bằng công thức OFFSET giống như chúng ta đã thực hiện ở phần Tạo menu thả xuống Excel động (tự động cập nhật, chỉ khác ở phạm vi được lấy từ workbook khác.
Trong workbook chính, bạn chỉ cần áp dụng Data Validation theo cách thông thường.

Một số điểm cần lưu ý khi tạo danh sách thả xuống trong Excel

Xử lý lỗi Data Validation không hoạt động

Bạn không thể tạo danh sách thả xuống do tùy chọn Data Validation của bạn bị tắt hoặc bị vô hiệu hóa? Điều này có thể xảy ra do một số nguyên nhân:

Không thể thêm danh sách thả xuống vào trang tính được bảo vệ hoặc chia sẻ. Trong trường hợp này, bạn cần loại bỏ bảo vệ hoặc ngừng chia sẻ trang tính, sau đó thử nhấp vào Data Validation một lần nữa.
Bạn đang tạo danh sách thả xuống từ bảng Excel được liên kết với trang SharePoint. Nếu vậy bạn cần hủy liên kết bảng hoặc xóa định dạng bảng và thử lại.

Các tùy chọn bổ sung cho hộp thả xuống Excel

Trong hầu hết các trường hợp, các tùy chọn của tab Settings mà chúng ta đã thảo luận ở trên là hoàn toàn đầy đủ. Nếu không vẫn có hai tùy chọn khác có sẵn trên các tab khác của cửa sổ hộp thoại Data Validation.

Hiển thị thông báo khi nhấp vào một ô có menu thả xuống

Nếu bạn muốn hiển thị cho người dùng một pop up (thông báo bật lên) khi họ nhấp vào bất kỳ ô nào chứa danh sách thả xuống của bạn thì có thể làm theo cách sau:

Trong hộp thoại Data Validation (Data tab > Data Validation), bạn cần chuyển sang tab Input Message.
Hãy chắc chắn rằng bạn đã chọn tùy chọn Show input message when cell is selected.
Nhập tiêu đề và thông báo vào các trường tương ứng (tối đa 225 ký tự).
Nhấp vào nút OK để lưu tin thông báo và đóng hộp thoại.

Hiển thị thông báo khi nhấp vào một ô có menu thả xuống

Cho phép người dùng nhập dữ liệu vào combo box

Theo mặc định, danh sách thả xuống bạn tạo trong Excel là mặc định và không thể chỉnh sửa, do đó các giá trị trong danh sách cũng bị hạn chế. Tuy nhiên, bạn có thể cho phép người dùng nhập các giá trị của riêng họ vào hộp.

Về mặt kỹ thuật, điều này biến danh sách thả xuống thành một hộp tổ hợp Excel. Thuật ngữ "combo box" có nghĩa là một danh sách thả xuống có thể chỉnh sửa cho phép người dùng chọn một giá trị từ danh sách hoặc nhập một giá trị trực tiếp vào hộp.

Trong hộp thoại Data Validation (Data tab > Data Validation), bạn cần chuyển đến tab Error Alert.
Chọn hộp "Show error alert after invalid data is entered" nếu bạn muốn hiển thị cảnh báo khi người dùng cố gắng nhập một số dữ liệu không có trong menu thả xuống. Nếu bạn không muốn hiển thị thông báo này thì có thể bỏ chọn.
Nếu bạn muốn hiển thị thông báo cảnh báo, hãy chọn một trong các tùy chọn từ hộp Style rồi nhập tiêu đề và thông báo. Hoặc bạn có thể chọn Information hoặc Warning để cho phép người sử dụng nhập văn bản trong hộp combo.
- Một thông báo Information sẽ hiển thị nếu người dùng của bạn có khả năng nhập nhiều lựa chọn của mình khá thường xuyên.
- Một thông báo Warning sẽ hiển thị khi người dùng chọn một mục từ hộp thả xuống thay vì nhập dữ liệu của riêng họ, mặc dù nó không cấm các entry tùy chỉnh.
- Stop (mặc định) sẽ ngăn người dùng nhập bất kỳ dữ liệu nào không có trong danh sách thả xuống Excel của bạn.
Nếu bạn không biết nên nhập tiêu đề hoặc nội dung thông báo nào thì có thể để trống các trường. Trong trường hợp này, Microsoft Excel sẽ hiển thị cảnh báo mặc định "The value you entered is not valid. A user has restricted values that can be entered into this cell".

Cách xóa danh sách thả xuống trong Excel

Nếu bạn không muốn có các hộp thả xuống trong trang tính Excel của mình nữa thì có thể xóa các hộp thả xuống này khỏi một số hoặc tất cả các ô.

Xóa danh sách thả xuống khỏi (các) ô đã chọn

Chọn một ô hoặc một số ô mà bạn muốn xóa các hộp thả xuống.
Chuyển đến tab Data và nhấp vào Data Validation.
Trên tab Settings, bạn cần chọn nút Clear All.

Phương pháp này sẽ loại bỏ các menu thả xuống khỏi các ô đã chọn nhưng vẫn giữ các giá trị hiện đang được chọn.

Nếu bạn muốn xóa cả danh sách thả xuống và giá trị của ô thì hãy chọn các ô và nhấp vào nút Clear trên Home tab > Editing group > Clear.

Cách xóa danh sách thả xuống trong Excel

Xóa danh sách thả xuống Excel khỏi tất cả các ô trong trang tính hiện tại

Bạn có thể làm theo các bước dưới đây để xóa danh sách thả xuống khỏi tất cả các ô được liên kết trong trang tính hiện tại. Thao tác này sẽ không xóa cùng một hộp thả xuống khỏi các ô trong các trang tính khác nếu có.

Chọn bất kỳ ô nào chứa danh sách thả xuống của bạn.
Nhấp vào Data Validation trong tab Data.
Trong cửa sổ Data Validation, trên tab Settings, bạn cần chọn hộp kiểm "Apply these changes to all other cells with the same settings". Sau khi chọn, tất cả các ô tham chiếu đến danh sách Data Validation Excel này sẽ được chọn, bạn có thể quan sát ảnh chụp màn hình bên dưới.
Nhấp vào nút Clear All để xóa danh sách thả xuống.
Bấm OK để lưu các thay đổi và đóng cửa sổ Data Validation.

Phương pháp này sẽ xóa danh sách thả xuống khỏi tất cả các ô chứa nó nhưng vẫn giữ lại các giá trị đang được chọn. Nếu bạn đã tạo một danh sách thả xuống dựa trên một dải ô hoặc một dải ô đã đặt tên thì danh sách nguồn vẫn giữ nguyên. Để xóa danh sách nguồn, bạn cần mở trang tính có chứa các mục của danh sách thả xuống và xóa.

Hy vọng bài viết này đã cung cấp cho bạn đầy đủ những thủ thuật bạn cần để làm việc với danh sách thả xuống trong Excel. Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy tham gia Gitiho ngay hôm nay.