Khi nhiều người cùng nhập một cột, cùng một trạng thái có thể bị gõ thành nhiều kiểu khác nhau, và hàm đếm hay Pivot Table sẽ tính sai. Bạn có thể tạo danh sách thả xuống trong Excel để người nhập chỉ cần bấm chọn một giá trị có sẵn. Tính năng này còn được gọi là drop list hoặc list trong Excel.

Bài viết hướng dẫn 4 cách từ dễ đến khó, và bạn có thể làm theo ngay trên file thực hành. Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh, nên phiên bản khác có thể khác đôi chút về vị trí nút. Ở các ảnh có thao tác, con trỏ chuột chỉ vào chỗ bạn cần bấm hoặc nằm ngay cạnh ô bạn cần gõ.

Tải file thực hành: [Dữ liệu mẫu] - Danh sách đơn hàng thực hành tạo danh sách thả xuống (file Excel trên OneDrive)

Bạn dùng tài liệu này mà bị vướng ở đâu? Bạn có thể đăng câu hỏi trong nhóm Vườn ươm nhà Nghề để cùng trao đổi và được giải đáp. Vào nhóm hỏi đáp

File có 2 sheet. Sheet Đơn hàng có 10 dòng đơn hàng đang để trống các cột cần chọn. Sheet Danh mục chứa sẵn nhóm hàng, sản phẩm và khách hàng.

Cách

Bạn nên dùng khi

Cột trong file thực hành

Cách 1: Gõ tay

Danh sách ngắn và ít khi đổi

Trạng thái

Cách 2: Lấy từ vùng ô

Danh sách đã nằm sẵn trong file

Nhóm hàng

Cách 3: Tự mở rộng

Danh sách thường có thêm mục mới

Khách hàng

Cách 4: Danh sách phụ thuộc

Lựa chọn thay đổi theo một ô khác

Sản phẩm

Cách 1: Bạn gõ trực tiếp các lựa chọn vào Source

Cách này nhanh nhất, nên bạn dùng nó cho danh sách ngắn như trạng thái đơn hàng.

Đầu tiên, bạn quét chọn vùng E2:E11 ở sheet Đơn hàng. Sau đó, bạn mở tab Data và bấm nút Data Validation trong nhóm Data Tools.

Nút Data Validation trên tab Data của Excel
Con trỏ chuột chỉ vào nút Data Validation sau khi bạn đã chọn vùng E2:E11.

Hộp thoại Data Validation hiện ra ở thẻ Settings. Bạn bấm ô Allow và chọn List.

Chọn List ở ô Allow trong hộp thoại Data Validation
Bạn chọn List để Excel hiện ô Source.

Bạn bấm vào ô Source và gõ các lựa chọn, mỗi lựa chọn cách nhau bằng dấu phẩy:

Chờ giao,Đang giao,Đã giao

Ô Source chứa ba trạng thái cách nhau bằng dấu phẩy
Ô Source chứa ba trạng thái mà người nhập được phép chọn.

Bạn bấm OK để hoàn tất. Nếu máy của bạn dùng dấu chấm phẩy làm dấu phân cách trong công thức, bạn thay dấu phẩy trong ô Source bằng dấu chấm phẩy.

Khi bạn chọn một ô trong vùng E2:E11, một mũi tên sẽ hiện ở cạnh phải của ô. Bạn bấm vào mũi tên và chọn giá trị cần nhập.

Danh sách ba trạng thái mở ra dưới ô E2
Con trỏ chuột đang chỉ vào lựa chọn Đang giao trong danh sách của ô E2.

Excel còn chặn những giá trị nằm ngoài danh sách. Ví dụ, khi bạn gõ chữ Hủy vào ô E3 và nhấn Enter, Excel sẽ báo lỗi và không nhận giá trị đó. Bạn bấm Cancel để hủy, hoặc bấm Retry để gõ lại.

Thông báo lỗi khi gõ giá trị nằm ngoài danh sách
Excel báo lỗi vì chữ Hủy không nằm trong danh sách đã khai báo.

Cách 2: Bạn lấy danh sách từ một vùng ô ở sheet khác

Cách này phù hợp khi danh sách đã có sẵn trong file, vì bạn không phải gõ lại từng mục.

Bạn quét chọn vùng C2:C11 ở sheet Đơn hàng, mở Data Validation và chọn List ở ô Allow. Tiếp theo, bạn bấm vào ô Source, bấm sang sheet Danh mục rồi quét chọn vùng A2:A4. Excel tự điền địa chỉ ='Danh mục'!$A$2:$A$4 vào ô Source.

Hộp thoại Data Validation khi chọn vùng A2:A4 ở sheet Danh mục
Ô Source nhận địa chỉ vùng A2:A4 của sheet Danh mục.

Bạn bấm OK. Excel sẽ đưa bạn về sheet Đơn hàng, và các ô ở cột Nhóm hàng đã có mũi tên để chọn.

Danh sách nhóm hàng mở ra dưới ô C2
Cột Nhóm hàng lấy các lựa chọn từ sheet Danh mục.

Cách này có một giới hạn mà người mới dễ bỏ sót. Khi nguồn nằm ở sheet khác và bạn thêm một dòng ngay dưới vùng đã chọn, Excel không tự đưa dòng đó vào danh sách, kể cả khi vùng nguồn đã là Table. Cách 3 sẽ xử lý giới hạn này.

Cách 3: Bạn tạo danh sách tự mở rộng bằng Table và tên vùng

Cách này hợp với danh sách hay có thêm mục mới, như danh sách khách hàng. Bạn cần làm 3 việc.

Việc 1: Bạn đổi danh sách thành Table. Ở sheet Danh mục, bạn bấm một ô trong vùng Khách hàng, ví dụ G2, rồi nhấn Ctrl + T. Hộp thoại Create Table hiện ra, bạn giữ dấu tích ở My table has headers và bấm OK.

Hộp thoại Create Table cho danh sách khách hàng
Hộp thoại Create Table nhận vùng G1:G5 có dòng tiêu đề Khách hàng.

Việc 2: Bạn đặt tên cho cột dữ liệu. Bạn quét chọn vùng G2:G5, bấm vào ô Name Box ở góc trên bên trái, gõ DS_Khach và nhấn Enter. Tên vùng không được chứa khoảng trắng, nên bài dùng dấu gạch dưới.

Ô Name Box chứa tên DS_Khach
Ô Name Box nhận tên DS_Khach cho vùng G2:G5 của Table.

Việc 3: Bạn dùng tên vùng làm nguồn. Bạn trở về sheet Đơn hàng, quét chọn vùng B2:B11 và mở Data Validation. Bạn chọn List ở ô Allow, gõ =DS_Khach vào ô Source rồi bấm OK.

Ô Source chứa công thức bằng DS_Khach
Ô Source dùng tên vùng thay cho địa chỉ ô.

Bây giờ bạn có thể thử thêm một khách hàng mới. Bạn gõ Công ty Thành Đạt vào ô G6, ngay dưới dòng cuối của Table, rồi nhấn Enter. Table tự nới thêm một dòng.

Table Khách hàng có thêm dòng Công ty Thành Đạt
Table tự mở rộng khi bạn gõ ngay dưới dòng cuối.

Bạn trở về sheet Đơn hàng, bấm ô B2 và bấm mũi tên. Danh sách đã có khách hàng mới mà bạn không phải sửa Data Validation.

Danh sách khách hàng có thêm mục Công ty Thành Đạt
Danh sách ở ô B2 đã có Công ty Thành Đạt ở dòng cuối.

Cách 4: Bạn tạo danh sách phụ thuộc bằng hàm INDIRECT

Danh sách phụ thuộc đổi các lựa chọn theo giá trị của một ô khác. Trong file thực hành, ô Sản phẩm chỉ hiện các sản phẩm thuộc nhóm hàng mà bạn đã chọn ở cột Nhóm hàng.

Việc 1: Bạn đặt tên vùng theo tiêu đề cột. Ở sheet Danh mục, bạn quét chọn vùng C1:E4. Sau đó, bạn mở tab Formulas và bấm Create from Selection trong nhóm Defined Names. Nếu cửa sổ Excel hẹp như trong ảnh, bạn bấm nút Defined Names trước để thấy lệnh này.

Lệnh Create from Selection trong nhóm Defined Names
Con trỏ chuột chỉ vào lệnh Create from Selection sau khi bạn đã chọn vùng C1:E4.

Hộp thoại Create Names from Selection hiện ra. Bạn chỉ giữ dấu tích ở Top row, bỏ dấu tích ở Left column nếu có, rồi bấm OK. Excel tạo 3 tên vùng là Bút, Giấy và Mực.

Hộp thoại Create Names chỉ tích Top row
Bạn chỉ giữ Top row để Excel lấy tiêu đề cột làm tên vùng.

Việc 2: Bạn dùng INDIRECT trong ô Source. Ở sheet Đơn hàng, bạn chọn Giấy ở ô C2 trước, vì công thức cần đọc giá trị của ô này. Sau đó, bạn quét chọn vùng D2:D11 từ trên xuống, mở Data Validation và chọn List. Bạn gõ công thức sau vào ô Source rồi bấm OK:

=INDIRECT($C2)

Ô Source chứa công thức INDIRECT
Công thức INDIRECT đọc tên nhóm hàng ở cột C của cùng dòng.

Công thức này dùng $C2, nên cột C được giữ nguyên và số dòng tự đổi theo từng ô. Bạn có thể đọc thêm về hàm này ở bài hàm INDIRECT trong Excel.

Bạn kiểm tra kết quả bằng cách chọn nhóm hàng rồi mở ô Sản phẩm trên cùng dòng. Khi ô C2 là Giấy, ô D2 hiện 3 loại giấy.

Danh sách sản phẩm của nhóm Giấy
Ô D2 hiện các sản phẩm của nhóm Giấy vì ô C2 đang là Giấy.

Bạn chọn Mực ở ô C3, và ô D3 sẽ hiện 3 loại mực.

Danh sách sản phẩm của nhóm Mực
Ô D3 hiện các sản phẩm của nhóm Mực vì ô C3 đang là Mực.

Danh sách phụ thuộc có 3 điểm bạn cần chú ý:

  • Tên nhóm hàng phải khớp với tên vùng. Nếu tên nhóm có khoảng trắng như Văn phòng phẩm, Excel đặt tên vùng là Văn_phòng_phẩm, nên bạn dùng công thức =INDIRECT(SUBSTITUTE($C2," ","_")).

  • Excel có thể hỏi The Source currently evaluates to an error nếu ô C2 còn trống khi bạn bấm OK. Bạn chọn Yes để tiếp tục, vì danh sách sẽ chạy đúng sau khi cột C có dữ liệu. Trong lúc ô cột C còn trống, ô Sản phẩm trên cùng dòng chưa chặn được giá trị gõ sai.

  • Ô Sản phẩm không tự xóa khi bạn đổi nhóm hàng. Ví dụ, khi bạn đổi ô C2 từ Giấy sang Bút, ô D2 vẫn giữ Giấy A4, nên bạn cần chọn lại sản phẩm.

Người mới thường hỏi gì về danh sách thả xuống trong Excel?

Làm sao để bỏ danh sách thả xuống?

Bạn chọn các ô cần bỏ, mở Data Validation và bấm Clear All ở góc dưới bên trái rồi bấm OK. Giá trị đang có trong ô vẫn được giữ nguyên.

Nút Clear All trong hộp thoại Data Validation
Nút Clear All xóa quy tắc của ô nhưng không xóa giá trị đã nhập.

Vì sao tôi không thấy mũi tên trong ô?

Mũi tên chỉ hiện khi bạn đang chọn đúng ô đó. Nếu chọn ô mà vẫn không thấy mũi tên, bạn mở Data Validation và kiểm tra dấu tích ở In-cell dropdown. Bạn cũng có thể chọn ô rồi nhấn Alt + Mũi tên xuống để mở danh sách bằng bàn phím.

Vì sao ô vẫn nhận giá trị ngoài danh sách?

Data Validation chỉ kiểm tra giá trị mà người dùng gõ tay. Giá trị được dán từ nơi khác có thể lọt qua và còn ghi đè quy tắc của ô. Vì vậy, bạn nên nhập bằng cách chọn trong danh sách thay vì dán.

Bạn muốn dùng Excel thành thạo cho công việc văn phòng?

Danh sách thả xuống giúp dữ liệu đầu vào đồng nhất, nên các bước tính toán và báo cáo phía sau chính xác hơn.

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.