Để tạo danh sách thả xuống trong Excel, chọn các ô, vào Data > Data Validation, đặt Allow thành List, gõ các mục vào Source, cách nhau bằng dấu phẩy (North,South,East,West), hoặc chọn vùng chứa các mục đó, rồi nhấn OK. Giờ đây mỗi ô có một mũi tên với các lựa chọn đó, và các giá trị khác sẽ bị từ chối.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Region | Sales | |
| 2 | Ana | North | 120 | North | 360 | |
| 3 | Ben | South | 85 | |||
| 4 | Cara | North | 240 | |||
| 5 | Dan | East | 60 | |||
| 6 | Eve | South | 150 |
F2 hiện 360, tổng của North. Chọn North ở B3 thì F2 tăng thêm 85 của Ben. E2 cũng có danh sách thả xuống: chọn South ở đó thì F2 hiện tổng của South. Một danh sách để nhập liệu cộng một công thức đọc giá trị đó là cách dùng danh sách thả xuống phổ biến nhất.
Cách tạo danh sách thả xuống, từng bước
- Chọn các ô cần có danh sách, ví dụ B2:B6.
- Vào Data > Data Validation (nhóm Data Tools). Trên Windows, chuỗi phím tắt là Alt, A, V, V.
- Ở thẻ Settings, đặt Allow thành List.
- Trong Source, hoặc gõ các mục cách nhau bằng dấu phẩy,
North,South,East,West, hoặc nhấp vào ô rồi chọn vùng chứa các mục trên trang tính, Excel sẽ ghi=$F$2:$F$5. - Giữ dấu chọn ở In-cell dropdown (nếu bỏ, sẽ không có mũi tên, chỉ còn phần kiểm tra).
- Nhấn OK.
Để mở danh sách bằng bàn phím, chọn ô rồi nhấn Alt+Down Arrow (Windows) hoặc Option+Down Arrow (Mac). Trong Excel cho Microsoft 365, gõ vài chữ cái đầu vào ô sẽ thu hẹp danh sách còn các mục khớp.
Hai thẻ tùy chọn trong cùng hộp thoại: Input Message hiện một gợi ý khi ô được chọn, còn Error Alert quy định điều gì xảy ra khi ai đó gõ một giá trị không có trong danh sách. Với kiểu Stop (mặc định), giá trị bị từ chối; với Warning hoặc Information, giá trị được chấp nhận sau một thông báo. Bỏ chọn Show error alert after invalid data is entered để mọi người gõ gì cũng được mà vẫn có danh sách để chọn.
Các mục gõ tay được phân cách bằng dấu phân cách danh sách trong cài đặt vùng miền của máy tính. Ở phần lớn nơi dùng dấu phẩy thập phân (Việt Nam, Pháp, Đức, Tây Ban Nha, Ý), đó là dấu chấm phẩy: Nord;Sud;Est;Ouest.
Danh sách thả xuống từ một vùng ô
Một danh sách gõ trong hộp thoại thì bị ẩn đi và phải sửa ở đó. Một danh sách nằm trong các ô thì dễ bảo trì hơn: sửa một ô là mọi danh sách thả xuống dùng nó đều thay đổi. Ở đây các khu vực nằm trong E2:E5 và danh sách thả xuống trên B2:B6 dùng vùng đó làm nguồn.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Regions | |
| 2 | Ana | North | 120 | North | |
| 3 | Ben | South | 85 | South | |
| 4 | Cara | North | 240 | East | |
| 5 | Dan | East | 60 | West | |
| 6 | Eve | South | 150 |
Đổi E5 từ West thành Central, rồi mở bất kỳ mũi tên nào trong cột B: danh sách đưa ra Central thay cho West. Các giá trị đã chọn trong cột B không thay đổi.
Để dùng một vùng ở trang tính khác, cách thường dùng để giấu danh sách đi, hãy gõ tên trang tính vào Source: =Lists!$A$2:$A$5. Để danh sách tự dài ra khi bạn thêm một mục ở cuối, trước tiên hãy biến các mục thành bảng (chọn chúng, Insert > Table) rồi chọn cột của bảng làm nguồn: tham chiếu sẽ mở rộng cùng với bảng.
Danh sách thả xuống động với UNIQUE
Khi các mục cần lấy từ chính dữ liệu (mọi khu vực xuất hiện trong một cột, mỗi khu vực một lần), hãy tạo danh sách bằng công thức rồi trỏ danh sách thả xuống vào kết quả. =SORT(UNIQUE(B2:B8)) trong G2 trả ra các khu vực khác nhau theo thứ tự bảng chữ cái.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Pick | Sales | Regions | |
| 2 | Ana | North | 120 | South | 235 | East | |
| 3 | Ben | South | 85 | North | |||
| 4 | Cara | North | 240 | South | |||
| 5 | Dan | East | 60 | West | |||
| 6 | Eve | South | 150 | ||||
| 7 | Fay | West | 95 | ||||
| 8 | Gus | East | 110 |
G2 trả ra East, North, South, West, và danh sách thả xuống trong E2 đưa ra bốn lựa chọn đó. Đổi B7 thành Central thì Central xuất hiện ở cả kết quả trả ra lẫn danh sách.
Trong Excel, đặt Source của danh sách thả xuống là =$G$2#. Dấu # sau một ô nghĩa là "toàn bộ kết quả trả ra của công thức này", nên danh sách luôn dài đúng bằng kết quả, không có hàng trống ở cuối. Tham chiếu kiểu này cần Excel 365 hoặc 2021; ô nguồn có thể nằm ở trang tính khác (=Lists!$A$2#). Nếu cột dữ liệu có ô trống, UNIQUE trả về số 0 cho chúng; hãy loại chúng ra bằng =SORT(UNIQUE(FILTER(B2:B100,B2:B100<>""))). Trang UNIQUE trình bày chi tiết về hàm này.
Danh sách thả xuống phụ thuộc
Một danh sách phụ thuộc thay đổi theo lựa chọn ở một ô khác: chọn Fruit ở A2 thì B2 chỉ đưa ra trái cây. Trong Excel 365 và 2021, một công thức FILTER tạo ra danh sách thứ hai: =FILTER(E2:E8,D2:D8=A2) trả về các mục có loại khớp với A2, và danh sách thả xuống ở B2 dùng kết quả trả ra đó làm nguồn.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Category | Item | Category | Item | Items | ||
| 2 | Fruit | Pear | Fruit | Apple | Apple | ||
| 3 | Fruit | Pear | Pear | ||||
| 4 | Vegetable | Carrot | Kiwi | ||||
| 5 | Vegetable | Leek | |||||
| 6 | Bakery | Bread | |||||
| 7 | Fruit | Kiwi | |||||
| 8 | Bakery | Bagel |
Khi A2 là Fruit, G2 trả ra Apple, Pear và Kiwi, và đó là các lựa chọn ở B2. Chọn Bakery ở A2: G2 đổi thành Bread và Bagel. B2 vẫn ghi Pear cho tới khi bạn chọn lại, vì danh sách thả xuống không bao giờ thay đổi giá trị đã có trong ô. Trong Excel, nguồn của B2 là =$G$2#.
Trong các phiên bản Excel cũ hơn, cách kinh điển dùng INDIRECT và vùng được đặt tên:
- Đặt các mục của mỗi loại vào một cột riêng, với tên loại làm tiêu đề: Fruit ở một cột, Vegetable ở cột tiếp theo.
- Chọn từng cột mục và đặt tên theo loại của nó trong Name Box (bên cạnh thanh công thức):
Fruit,Vegetable,Bakery. - Tạo cho A2 một danh sách thả xuống với nguồn
Fruit,Vegetable,Bakery. - Tạo cho B2 một danh sách thả xuống với nguồn
=INDIRECT(A2). INDIRECT biến văn bản trong A2 thành tham chiếu tới vùng mang tên đó.
Tên vùng phải khớp chính xác với văn bản của loại và không được chứa dấu cách (dùng Dairy_Products, hoặc =INDIRECT(SUBSTITUTE(A2," ","_")) trong nguồn). Xem thêm về INDIRECT ở trang INDIRECT.
Tra cứu giá trị của mục đã chọn
Danh sách thả xuống thường là đầu vào của một biểu mẫu đặt hàng hay một bảng báo giá: người dùng chọn một sản phẩm và một hàm tra cứu điền giá của nó.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Price | Product | Price | ||
| 2 | Order | Pear | Apple | $1.20 | ||
| 3 | Pear | $1.50 | ||||
| 4 | Carrot | $0.80 | ||||
| 5 | Bread | $2.40 | ||||
| 6 | Milk | $1.10 |
Đến lượt bạn: Trong C2, trả về giá của sản phẩm được chọn ở B2 từ bảng trong E:F.
Khi chọn Pear, đáp án là $1.50. Chọn sản phẩm khác ở B2 thì giá thay đổi theo. =XLOOKUP(B2,E2:E6,F2:F6) cũng dùng được; xem VLOOKUP để biết các tham số.
Tô màu ô theo mục đã chọn
Để tô màu ô theo giá trị đã chọn (xanh lá cho Done, đỏ cho Late), thêm một quy tắc định dạng có điều kiện cho chính các ô đó: chọn B2:B6, vào Home > Conditional Formatting > Highlight Cells Rules > Equal To, gõ Late rồi chọn một định dạng. Để tô màu cả hàng, chọn A2:B6 và dùng New Rule > Use a formula to determine which cells to format với =$B2="Late".
| A | B | |
|---|---|---|
| 1 | Task | Status |
| 2 | Quote | Done |
| 3 | Invoice | Late |
| 4 | Order | Open |
| 5 | Report | Late |
| 6 | Survey | Done |
B3 và B5 được tô sáng. Chọn Late ở B4 thì ô đó cũng được tô sáng; chọn Done ở B3 thì màu tô biến mất. Trang định dạng có điều kiện trình bày chi tiết về các quy tắc.
Vì sao danh sách thả xuống không hoạt động
- In-cell dropdown bị bỏ chọn trong Data > Data Validation. Danh sách vẫn giới hạn giá trị nhập, nhưng không có mũi tên.
- Mũi tên chỉ hiện ở ô đang được chọn. Không có gì trên lưới đánh dấu các ô khác có danh sách; để tìm chúng, dùng Home > Find & Select > Data Validation.
- Vùng Source có ô trống, nên danh sách hiện các dòng trống. Chỉ chọn các ô đã điền, hoặc dùng nguồn là kết quả trả ra (
=$G$2#), vốn không có ô trống. - Các mục được gõ với dấu phân cách sai:
North;Southtrong Excel dùng dấu phẩy sẽ thành một mục duy nhất tên làNorth;South. - Một danh sách thả xuống chỉ chứa một giá trị. Chọn mục thứ hai sẽ thay thế mục đầu tiên; chọn nhiều mục trong một ô cần một macro VBA.
Để sao chép danh sách thả xuống sang ô khác mà không sao chép giá trị, sao chép ô đó, rồi dùng Home > Paste > Paste Special > Validation. Để xóa một danh sách, chọn các ô rồi chọn Data > Data Validation > Clear All.
Câu hỏi thường gặp
Làm sao tạo danh sách thả xuống trong Excel?
Chọn các ô, vào Data > Data Validation, đặt Allow thành List, gõ các mục vào Source, cách nhau bằng dấu phẩy (North,South,East), hoặc chọn vùng chứa các mục đó (=$F$2:$F$5), rồi nhấn OK.
Làm sao sửa danh sách thả xuống trong Excel?
Chọn một ô có danh sách, mở Data > Data Validation và sửa ô Source. Đánh dấu ô Apply these changes to all other cells with the same settings (áp dụng thay đổi cho mọi ô có cùng thiết lập) để cập nhật mọi bản sao. Nếu nguồn là một vùng, sửa các ô trong vùng đó sẽ đổi danh sách mà không cần mở hộp thoại.
Làm sao xóa danh sách thả xuống trong Excel?
Chọn các ô, vào Data > Data Validation, nhấp Clear All rồi OK. Các giá trị đã chọn vẫn nằm trong ô; chỉ có mũi tên và giới hạn nhập liệu biến mất.
Làm sao tạo danh sách thả xuống từ một trang tính khác?
Gõ tham chiếu kèm tên trang tính vào Source: =Lists!$A$2:$A$6, hoặc nhấp sang trang tính kia và chọn vùng trong khi ô Source đang được chọn. Một vùng được đặt tên (Formulas > Define Name) cũng dùng được: =Regions.
Làm sao tạo danh sách thả xuống tự động cập nhật?
Trỏ nó vào một công thức trả ra mảng (spill): đặt =SORT(UNIQUE(FILTER(B2:B100,B2:B100<>""))) vào một ô phụ như H2 và dùng =$H$2# làm Source. Giá trị mới trong cột B sẽ xuất hiện ngay trong danh sách, và FILTER giữ các hàng trống ở ngoài. Cách này cần Excel 365 hoặc 2021.
