Danh sách thả xuống trong Excel: Thiết lập, Mẹo và Khắc phục sự cố

Bạn đã bao giờ nhấp vào một mũi tên nhỏ trong ô Excel và thấy hiện ra một danh sách tùy chọn chưa? Đó có thể là danh sách phòng ban, khu vực, trạng thái hoặc danh mục. Nếu câu trả lời là có, bạn đã tương tác với một danh sách thả xuống.

Danh sách thả xuống trong Excel hướng người dùng nhập liệu nhất quán, giảm khả năng gõ sai hay nhập lệch chuẩn. Dù bạn đang điền một bảng theo dõi dự án, tạo bảng ngân sách hay thiết kế biểu mẫu cho người khác sử dụng, danh sách thả xuống giúp mọi thứ gọn gàng và dễ dự đoán.

Trong hướng dẫn này, tôi sẽ chỉ bạn cách tạo các danh sách này từ đầu, tùy chỉnh để phù hợp nhu cầu, sửa khi phát sinh lỗi, và thậm chí xây dựng các phiên bản linh hoạt, tương tác cho quy trình nâng cao. Bạn không cần là chuyên gia Excel để bắt đầu; chỉ cần một bảng tính đang hoạt động và vài điểm dữ liệu là đủ.

Nếu bạn mới bắt đầu với Excel, khóa học Introduction to Excel của chúng tôi bao quát các kỹ năng như điều hướng giao diện, hiểu định dạng dữ liệu và làm việc với các hàm cơ bản. Ngoài ra, tôi thấy Excel Formulas Cheat Sheet (bạn có thể tải về) là tài liệu tham khảo hữu ích vì có đầy đủ các hàm Excel thường dùng nhất.

Tạo danh sách thả xuống cơ bản trong Excel

Giờ hãy xem cách bạn có thể tạo một danh sách thả xuống trong Excel

Hướng dẫn từng bước

Để tạo một danh sách thả xuống trong Excel, hãy làm theo các bước sau:

Bước 1: Chuẩn bị dữ liệu nguồn

Trước khi tạo danh sách thả xuống, hãy quyết định các mục sẽ có trong danh sách. Bạn có thể nhập trực tiếp các lựa chọn khi thiết lập danh sách thả xuống, hoặc liệt kê chúng trong các ô trên bảng tính.

Bước 2: Áp dụng xác thực dữ liệu

Khi danh sách đã sẵn sàng:

• Tô sáng ô hoặc phạm vi ô nơi bạn muốn hiển thị danh sách thả xuống.

• Vào thẻ Data trên ribbon và nhấp Data Validation.

• Trong hộp thoại xuất hiện, dưới mục Allow, chọn List.

• Trong ô Source, nhập trực tiếp các giá trị của bạn hoặc tham chiếu đến phạm vi ô nơi danh sách đang đặt.

Bước 3: Hoàn tất và kiểm tra

Sau khi xác minh bạn đã nhập đúng phạm vi:

• Nhấn OK để hoàn tất.
• Nhấp vào một trong các ô đã được xác thực. Bạn sẽ thấy một mũi tên nhỏ bên phải để chọn mục từ danh sách.

Nếu bạn nhập thủ công một mục chưa được xác định trước vào ô, bạn sẽ nhận được thông báo lỗi. Việc xác thực này giúp ngăn lỗi trong quá trình nhập liệu.

Danh sách động dựa trên Table (Table Magic)

Nếu bạn cần kiểm soát nhiều hơn với danh sách, bạn có thể dùng bảng Excel (Table) để tạo danh sách động. Thực hiện theo các bước dưới đây:

• Chọn danh sách nguồn và nhấn Ctrl + T (hoặc vào Insert > Table).
• Đảm bảo chọn “My table has headers”.

• Đặt tên có ý nghĩa cho bảng ở thẻ Table Design.

• Chọn phạm vi ô nơi danh sách thả xuống sẽ hiển thị, sau đó chọn thẻ Data > Data Validation > List.

• Trong trường “Source”, nhập =INDIRECT("DepartmentList[Department]")

Khi bạn chuyển danh sách nguồn thành bảng, bạn cho phép Excel tự động bổ sung các mục mới vào danh sách thả xuống khi chúng được thêm vào.

Xem thêm Excel Shortcuts Cheat Sheet để học cách tăng năng suất bằng các phím tắt cho nhiều tính năng Excel.

Cách thêm hoặc xóa mục khỏi danh sách thả xuống

Sẽ có lúc bạn cần cập nhật danh sách thả xuống. Tôi sẽ chỉ bạn cách xóa hoặc thêm mục vào danh sách thả xuống.

Nếu bạn đã tạo danh sách thả xuống bằng cách nhập thủ công, chỉ cần thêm mục mới ở cuối danh sách.

Bạn cũng có thể thêm mục mới vào phạm vi ô nếu bạn đã chọn “Source” là một phạm vi ô.

Nếu bạn đang tham chiếu danh sách từ một bảng Excel (như ví dụ trước), hãy nhập giá trị mới bên dưới hàng cuối cùng. Excel sẽ tự động mở rộng và cập nhật bảng, đồng thời cập nhật danh sách của bạn.

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

Bạn có thể xóa danh sách thả xuống khỏi trang tính Excel mà không xóa dữ liệu đã nhập.

Để xóa danh sách thả xuống được tạo bằng Data Validation:

• Chọn ô hoặc phạm vi có danh sách thả xuống.
• Vào Data > Data Validation.
• Trong hộp thoại, nhấp Clear All > OK.

Cách này xóa quy tắc xác thực và mũi tên thả xuống. Giá trị hiện có trong ô vẫn giữ nguyên nhưng không còn bị giới hạn bởi các tùy chọn trước đó.

Nếu bạn dùng combo box hoặc điều khiển ActiveX:

• Vào Developer > Design Mode.
• Chọn điều khiển, rồi nhấn Delete trên bàn phím.

Lưu ý dữ liệu hiện có của bạn vẫn được giữ nguyên ngay cả sau khi xóa các quy tắc xác thực của danh sách thả xuống.

Kỹ thuật nâng cao: Danh sách động và danh sách phụ thuộc

Giờ bạn đã nắm cơ bản về danh sách thả xuống của Excel, hãy xem cách tạo các danh sách linh hoạt cho mục đích nâng cao.

Danh sách thả xuống động

Danh sách thả xuống động tự động cập nhật mỗi khi bạn thay đổi dữ liệu nguồn. Nếu danh sách có trùng lặp, trước hết nên dùng hàm UNIQUE() để lấy các giá trị duy nhất. Ví dụ, nếu dữ liệu ở “A2:A21”, bạn có thể dùng công thức dưới đây ở vị trí khác để tạo danh sách sạch hơn cho danh sách thả xuống.

=UNIQUE(A2:A21)

Sau đó bạn có thể dùng phạm vi đầu ra này làm nguồn cho danh sách thả xuống.

Bạn cũng có thể dùng hàm OFFSET() nếu danh sách tăng dần mà bạn không muốn chuyển nó thành bảng chính thức.

=OFFSET(ListData!$A$2, 0, 0, COUNTA(ListData!$A:$A) -1)

Danh sách thả xuống động được dùng trong biểu mẫu trực tiếp, bảng theo dõi, hoặc bảng tính cộng tác. Tính năng này đảm bảo cập nhật tự động khi người dùng nhập dữ liệu hoặc khi dữ liệu thay đổi.

Danh sách thả xuống phụ thuộc (cascading)

Danh sách thả xuống phụ thuộc (cascading) là nhóm các danh sách mà lựa chọn ở danh sách này phụ thuộc vào lựa chọn ở danh sách khác. Chúng lý tưởng cho dữ liệu phân cấp như danh mục và danh mục con.

Khi tạo danh sách thả xuống phụ thuộc, trước tiên bạn tạo các vùng được đặt tên cho từng nhóm mục con. Danh sách thả xuống thứ hai dùng hàm INDIRECT() để tham chiếu đến vùng tên tương ứng với lựa chọn đầu tiên.

Bước 1: Chuẩn bị dữ liệu nguồn

Tạo danh sách danh mục và danh mục con ở các cột riêng theo một thứ tự nhất định. Đảm bảo mỗi vùng danh mục con được đặt tên theo đúng tên “Category” như hiển thị trong danh sách.

Bước 2: Tạo danh sách thả xuống thứ nhất

Ở danh sách thả xuống thứ nhất, chọn phạm vi danh mục chính. Dùng Data Validation > List và đặt nguồn từ phạm vi cột ‘Category’. Bước này tương tự như chúng ta đã dùng trước đó.

Bước 3: Tạo danh sách thả xuống phụ thuộc

Tiếp theo, thiết lập danh sách thả xuống danh mục con. Vào Data Validation > List. Với nguồn, tham chiếu đến ô ở danh sách thả xuống thứ nhất.

=INDIRECT(A2)

Bước 4: Kiểm tra danh sách thả xuống

Kiểm tra xem các mục đã được đặt đúng ở các cột ‘Category’ và ‘Sub-Category’ chưa.

Sau đây là các vấn đề thường gặp cần chú ý khi dùng danh sách thả xuống phụ thuộc:

• Đảm bảo các vùng tên cho danh mục con khớp với văn bản trong danh sách danh mục chính. Không có khoảng trắng thừa và đúng kiểu chữ.

• Nếu INDIRECT() trả về lỗi #REF!, hãy kiểm tra lại rằng các vùng tên tồn tại và tương ứng với giá trị trong danh sách chính.

Tùy chỉnh và trải nghiệm người dùng

Bạn có thể làm cho danh sách thả xuống linh hoạt hơn để nâng cao trải nghiệm và khả năng sử dụng. Phần này tôi sẽ chỉ bạn cách tùy chỉnh danh sách thả xuống cho các tình huống khác nhau.

Thông điệp nhập liệu và cảnh báo lỗi

Excel cho phép gắn các thông điệp ngắn vào ô có danh sách thả xuống để giúp người dùng chọn đúng. Để thiết lập thông điệp nhập liệu:

• Chọn ô có danh sách thả xuống.
• Vào Data > Data Validation.
• Chuyển sang tab Input Message.
• Nhập tiêu đề và thông điệp như “Chọn một phòng ban từ danh sách.”

Mũi tên thả xuống sẽ xuất hiện với thông điệp cạnh ô được chọn. Điều này giúp người dùng hiểu dữ liệu cần nhập cho trường đó.

Bạn có thể tạo cảnh báo “Error” xuất hiện nếu ai đó cố nhập dữ liệu không khớp với các tùy chọn trong danh sách thả xuống. Để tùy chỉnh tính năng này:

• Vào Data Validation > tab Error Alert.
• Chọn kiểu cảnh báo: Stop (ngăn nhập dữ liệu không hợp lệ), Warning (cảnh báo nhưng cho phép bỏ qua), hoặc Information (hiển thị thông tin nhưng cho phép tiếp tục nhập)
• Cung cấp thông điệp giải thích giới hạn, chẳng hạn “Vui lòng chọn phòng ban hợp lệ từ danh sách.”

Luôn dùng ngôn ngữ rõ ràng, thân thiện trong cả thông điệp nhập liệu lẫn cảnh báo lỗi để nâng cao độ rõ ràng.

Danh sách thả xuống có thể tìm kiếm

Trong các phiên bản Excel hiện đại, như Microsoft 365 và Excel cho web, bạn có thể dùng tính năng danh sách thả xuống có thể tìm kiếm, đặc biệt hữu ích khi duyệt danh sách dài. Khi nhấp mũi tên thả xuống, bạn có thể bắt đầu gõ và Excel sẽ lọc danh sách khớp với đầu vào của bạn. Tính năng này quan trọng khi làm việc với danh sách dài như tên khách hàng, SKU sản phẩm hoặc tên quốc gia.

Tuy nhiên, danh sách thả xuống có thể tìm kiếm chỉ có trên các phiên bản Excel gần đây, không có trên các bản desktop cũ như Excel 2016 hoặc 2019. Nếu bạn dùng phiên bản cũ, hãy dùng combo box hoặc điều khiển biểu mẫu với khả năng tìm kiếm tích hợp qua VBA để tạo danh sách thả xuống có thể tìm kiếm.

Cho phép mục khác hoặc nhập thủ công

Đôi khi bạn muốn thêm mục vào danh sách trong khi bỏ qua xác thực ban đầu. Nếu muốn thêm dữ liệu tùy chỉnh:

• Chọn ô hoặc phạm vi ô nơi bạn muốn hiển thị danh sách thả xuống.
• Vào Data > Data Validation.
• Ở tab Error Alert, bỏ chọn “Show error alert after invalid data is entered”.

Khi bỏ chọn tùy chọn này, bạn có thể nhập bất kỳ giá trị nào không có trong danh sách tùy chọn thả xuống.

Dù cho phép nhập thủ công tăng tính linh hoạt, nó có thể dẫn đến dữ liệu không nhất quán hoặc không hợp lệ nếu người dùng gõ sai hoặc nhập giá trị ngoài dự kiến. Nó cũng giảm lợi ích của danh sách kiểm soát.

Để xử lý các mục không có trong danh sách:

• Dùng định dạng có điều kiện để gắn cờ các ô có giá trị không nằm trong danh sách đã xác thực để rà soát.

• Tạo cột trợ giúp để kiểm tra tính hợp lệ. Ví dụ, dùng COUNTIF() để xác minh mục nhập có tồn tại trong danh sách nguồn hay không.

• Thường xuyên kiểm tra và cập nhật danh sách nguồn để bổ sung các mục mới hợp lệ.

Xem khóa học Conditional Formatting in Google Sheets của chúng tôi để học cách áp dụng định dạng có điều kiện nhằm xác thực dữ liệu và rút ra insight nhanh.

Form Controls và ActiveX để tăng cường chức năng

Nếu bạn cần thêm tính linh hoạt và chức năng cho danh sách thả xuống, Excel cung cấp các điều khiển nâng cao như Form Control và combo box ActiveX.

Combo box Form Control

Form Control combo box hoạt động như danh sách thả xuống tiêu chuẩn nhưng cho phép liên kết với ô khác. Cách này hữu ích khi tích hợp danh sách thả xuống với biểu mẫu hoặc bảng điều khiển.

Cách dùng combo box:

• Vào thẻ Developer.
• Nhấp Insert > Form Controls, chọn Combo Box.

• Vẽ combo box trên trang tính.

• Nhấp chuột phải vào điều khiển và chọn Format Control.

• Ở tab Control, đặt “Input Range” và “Cell Link” nơi chỉ số của mục được chọn sẽ hiển thị.

Bạn sẽ thấy combo box trả về một số tương ứng với vị trí của mục trong danh sách. Bạn có thể lấy giá trị thực tế theo vị trí mục bằng hàm INDEX().

Form controls được ưa chuộng khi xây dựng bảng điều khiển hoặc báo cáo tương tác. Chúng cũng hữu ích khi không cần VBA nhưng bạn cần định dạng linh hoạt hơn so với xác thực dữ liệu tiêu chuẩn.

Hộp điều khiển ActiveX

ActiveX Control cung cấp nhiều sức mạnh và tùy biến hơn, gồm điều khiển phông chữ, tự động hoàn thành và khả năng kích hoạt macro theo tương tác của người dùng.

Để thêm hộp ActiveX Control

• Vào thẻ Developer > Insert > ActiveX Controls > Combo Box.

• Vẽ hộp trên trang tính.
• Nhấp chuột phải và chọn Properties để cấu hình như “ListFillRange”, “LinkedCell”, rồi tùy chỉnh phông chữ, màu nền và kiểu viền.

Ưu điểm của ActiveX Combo Box là cung cấp khả năng định dạng linh hoạt hơn cho phông chữ, màu sắc, bố cục. Nó cũng cho phép lập trình theo sự kiện cho các biểu mẫu và ứng dụng tương tác cao, tích hợp với macro và tự động hóa.

Tuy nhiên, điều khiển ActiveX chỉ hoạt động trên Windows và không được hỗ trợ trong Excel for Mac hoặc Excel Online. Chúng cũng nặng hơn form controls và có thể làm chậm hiệu năng trong sổ làm việc lớn. Với mục đích nâng cao, bạn có thể cần kiến thức VBA.

Khắc phục sự cố thường gặp

Ngay cả với các tính năng nâng cao, bạn vẫn có thể gặp vấn đề khi làm việc với danh sách thả xuống trong Excel. Hãy cùng xem các lỗi thường gặp và cách gỡ lỗi.

Lỗi danh sách và cách sửa

Sau đây là các vấn đề phổ biến và cách khắc phục:

• Tùy chọn trống trong danh sách thả xuống: Danh sách có thể bao gồm dòng trống. Hãy đảm bảo phạm vi nguồn không chứa ô trống.
• Thiếu mục trong danh sách thả xuống: Một số mục có thể không hiển thị. Hãy xác minh phạm vi nguồn hoặc bảng bao gồm đủ các mục mong đợi. Với danh sách dựa trên phạm vi, đảm bảo nguồn Data Validation bao phủ toàn bộ danh sách và điều chỉnh khi cần.
• Tham chiếu sai hoặc lỗi thời: Nếu tham chiếu không chính xác, hãy xác nhận công thức nguồn Data Validation hoặc danh sách trỏ đúng trang tính và phạm vi ô. Đồng thời chú ý lỗi gõ hoặc thay đổi nhầm vùng tên.
• Danh sách thả xuống không xuất hiện hoặc thiếu mũi tên: Nếu bạn xóa xác thực dữ liệu, danh sách có thể không xuất hiện. Luôn đảm bảo bạn chọn “List” làm loại xác thực và áp dụng lại Data Validation mỗi khi xóa định dạng.

Gỡ lỗi nâng cao

Một số vấn đề có thể liên quan đến lỗi công thức hoặc hành vi đổ tràn (spill) liên quan đến danh sách động và vùng tên:

• Lỗi #REF!: Xảy ra khi công thức hoặc vùng tên tham chiếu tới ô, trang tính hoặc bảng đã bị xóa. Xem lại và cập nhật các vùng tên hoặc công thức dùng OFFSET(), INDIRECT() hoặc các hàm mảng động để sửa lỗi này.

• Lỗi #SPILL!: Xảy ra khi công thức mảng động như UNIQUE() cố gắng xuất giá trị nhưng vùng đổ tràn bị chặn bởi dữ liệu khác. Hãy kiểm tra không có ô trộn (merged) hoặc dữ liệu cản trở để công thức có thể lấp đầy các ô kề.

• Sai lệch mảng động: Nếu bạn tạo danh sách bằng các hàm như UNIQUE(), SORT() hoặc FILTER(), kích thước đầu ra có thể thay đổi. Do đó, hãy dùng vùng tên động hoặc tham chiếu toàn bộ cột chứa kết quả công thức.

Tôi khuyên bạn tham gia khóa Advanced Excel Functions để tìm hiểu thêm về offset và vùng động trong Excel.

Kết luận

Danh sách thả xuống trong Excel hữu ích để định hướng nhập liệu, đảm bảo tính nhất quán và nâng cao khả năng sử dụng tổng thể của bảng tính. Từ tạo danh sách cơ bản và liên kết bảng động đến lựa chọn phân tầng và điều khiển biểu mẫu tùy chỉnh, các công cụ này giúp bảng tính tương tác, chính xác và thân thiện hơn với người dùng.

Thành thạo kỹ thuật danh sách thả xuống giúp giảm lỗi, cải thiện tính nhất quán dữ liệu và tạo nền tảng cho các giải pháp bảng tính chuyên nghiệp, có khả năng mở rộng. Tôi khuyến khích bạn nâng cao kỹ năng bằng cách tích hợp danh sách thả xuống với Power Query hoặc dùng VBA để mở khóa tự động hóa và tính thông minh cao hơn trong quy trình Excel của bạn.

Nếu bạn muốn nâng cao kỹ năng Excel, tôi khuyên bạn tham gia khóa Data Analysis in Excel của chúng tôi. Khóa học này sẽ giúp bạn làm chủ phân tích nâng cao và thúc đẩy sự nghiệp. Tôi cũng đề xuất khóa Intermediate Power Query in Excel để học về biến đổi dữ liệu và dùng ngôn ngữ M để tạo các hàm động.