Cách tạo danh sách tên trong Excel

Áp dụng cho Excel 2007, 2010, 2013,.. 2021….

Khi sử dụng Excel bạn sẽ thấy rằng nếu bạn thực hiện gõ Thứ 2 và kéo xuống Excel sẽ tự động điền Thứ 3, Thứ 4, Thứ 5… và Excel mặc định cho chúng ta một số danh sách để làm được việc này.

Tuy nhiên, trong công việc chúng ta có nhiều danh sách phải sử dụng mà Excel không thể cung cấp sẵn được.

Có thể là tên các địa phương, tên các sản phẩm, tên giáo viên trong trường… mỗi lần muốn sử dụng ta lại phải bật file lên copy và dán vào Excel làm mất rất nhiều thời gian, mà đôi khi chúng ta còn không nhớ file đó lưu chỗ nào nữa cơ.

Nói như vậy chắc các bạn đã hiểu được vấn đề rồi đúng không.

Trong bài này mình sẽ hướng dẫn các bạn tự tạo danh sách cho riêng mình để khi cần các bạn chỉ cần nhập giá trị đầu tiên vào và kéo xuống là ra toàn bộ danh sách bạn đã chuẩn bị từ trước, đơn giản vậy thôi 😀

Làm thế nào để tạo danh sách tự động điền trong Excel?

Ví dụ mình có file dữ liệu sau, mình muốn khi nhập cụm từ TP. ĐÀ LẠT và kéo xuống thì Excel tự động điền tên các đơn vị còn lại.

Cách thực hiện như sau:

+ Bước 1: Nhấn vào File => chọn Options.

+ Bước 2: Tiếp theo bạn hãy nhấn vào mục Advanced và kéo xuống gần cuối, chọn Edit Custom Lists

+ Bước 3: Các bạn sẽ thấy các list mặc định mà Excel đã tạo như hình bên dưới.

Cách tạo 1 list dánh sách mới trong Excel

+ Tại mục Import list fromcells nhập địa chỉ ô có chứa danh sách mà bạn muốn tạo hoặc ấn vào biểu tượng  

  [cạnh nút Import] rồi quét các ô trong danh sách.

+ Ấn Import, mục Customlist và List entries sẽ hiện ngay danh sách mới được đưa vào.

+ Bước 4: Xong rồi đấy. Bây giờ các bạn chỉ cần đánh chính xác ô đầu tiên => sau đó kéo xuống Excel sẽ tự điền những giá trị còn lại trong danh sách mà bạn đã tạo.

Note: Mình thấy đánh chữ hoa hay chữ thường Excel 2010 đều nhận ra, các phiên bản cũ hơn hoặc mới hơn thì các bạn tự kiểm tra nhé.

Lời kết

Như vậy chỉ mất chưa tới một phút bạn đã tạo danh sách kéo tự động trong Excel thành công rồi đó, bạn hãy tạo thêm những list khác mà mình thường gặp để thao tác nhanh hơn trong Excel nhé.

Mình tin chắc rằng đồng nghiệp của bạn sẽ tròn xoe con mắt khi thấy bạn đánh vào tên 1 nhân viên trong phòng rồi có thể kéo thêm ra được những người khác hay đánh tên bạn gái rồi có thể kéo được cả gia đình của người ta ra :D.

Mình hi vọng thủ thuật nhỏ này sẽ giúp bạn tiết kiệm được nhiều thời gian trong công việc. Chúc các bạn thực hiện thành công !

Đọc thêm:

Cộng tác viên: Nguyễn Xuân Ngọc

Note: Bài viết này hữu ích với bạn chứ? Đừng quên đánh giá bài viết, like và chia sẻ cho bạn bè và người thân của bạn nhé !

4 cách tạo Drop List trong Excel không hề khó khăn

Liệu bạn đã bao giờ nghe qua về Drop List chưa? Đây là một công cụ hỗ trợ bạn rất nhiều trong công việc, nhất là đối với những bạn cần thực hiện hàng trăm, hàng ngàn thông tin trên cùng một sheet. Vậy nên, hãy cùng ThuthuatOffice khám phá những cách tạo Drop List trong Excel ở bài đăng này để hiểu hơn về công cụ này nhé.

Drop List trong Excel là gì

Drop List, hay trong tiếng Việt còn được gọi là menu đổ xuống trong Excel, là kiểu định dạng dữ liệu rất tiện lợi. Một số công dụng phổ biến của nó có thể kể đến là:

• Tạo danh mục cần nhập vào ô.
• Tạo được các đặc tính [tên, nghề nghiệp,…] nhằm phục vụ mục đích lấy thông tin.
• Có thể thực hiện chấm công thông qua Drop List.
• Có thể dễ dàng quản lý hàng, kho thông qua Drop List.

Những cách tạo Drop List mà ThuthuatOffice sắp giới thiệu đến bạn đều có thể áp dụng cho mọi phiên bản của Excel. Tức là, cách tạo Drop List trong Excel 2010 hay 2016 thì đều có các bước thực hiện hoàn toàn giống nhau.

Cách tạo Drop List trong Excel với dữ liệu nhập thủ công

Cách tạo Drop List trong Excel với dữ liệu nhập thủ công rất đơn giản, bạn có thể làm theo các bước sau:

• Bước 1: Chọn ô muốn tạo danh sách thả xuống trong Excel, vào thẻ Data, ở mục Data Validation chọn Data Validation…

• Bước 2: Bảng chọn Data Validation hiện lên. Bạn vào thẻ Settings, thực hiện các bước:

1. Ở mục Allow, bạn nhấnchọnList.
2. Ở mục Sources, bạn nhập tên các thành phần cần tạo list.

Lưu ý: Khi nhập tên các thành phần, bạn cần lưu ý ngăn cách chúng bằng dấu chấm phẩy.

Ví dụ: Nam;Nữ

Sau khi hoàn thành nhập các thông tin, bạn nhấn OK để hoàn tất.

Kết quả:

Cách tạo Drop List trong Excel từ liên kết dữ liệu có sẵn

Khi bạn đã tạo riêng một bảng các dữ liệu cần nhập vào Drop List, thì cách tạo Drop List trong Excel sẽ diễn ra như sau:

• Bước 1: Chọn ô muốn tạo danh sách thả xuống trong Excel, vào thẻ Data, ở mục Data Validation chọn Data Validation…
• Bước 2: Bảng chọn Data Validation hiện lên. Bạn vào thẻ Settings, thực hiện các bước:

1. Ở mục Allow, bạn nhấnchọnList.
2. Ở mục Sources, bạn nhấp chuột vào hình ô vuông bên nhỏ bên phải:

Sau đó chọn các thành phần đã được bạn tạo sẵn và nhấn ENTER:

Excel sẽ tự động nhập vào mục Source cho bạn như thế này, bạn nhấn OK.

Kết quả:

Cách tạo 2 Drop List phụ thuộc trong Excel

• Bước 1: Tạo các bảng tính bao gồm những thông tin cần nhập.

Ví dụ:

• Bước 2: Đặt tên cho những thành phần được nhập bằng cách: Bôi đen từng cột, nhấn chuột phải chọn Define Name…

Ví dụ: trong danh sách dưới đây sẽ cần đặt tên 3 lần cho 3 cột tương ứng là Danh sách phụ trách team Đỏ, Danh sách phụ trách team Vàng và danh sách phụ trách team Xanh.

Hộp thoại New Name hiện lên, bạn nhập tên vào mục Name và nhấn OK. Lưu ý là tên bạn nhập không được có khoảng trắng và phải khớp với thông tin phụ thuộc [ví dụ Đỏ, Vàng, Xanh].

• Bước 3: Bôi đen các ô cần tạo Drop List. Vào thẻ Data, ở mụcData Validation, bạn chọn Data Validation…
• Bước 4: Bảng chọn Data Validation hiện lên. Bạn vào thẻ Settings, thực hiện các bước:

1. Ở mục Allow, bạn nhấnchọnList.
2. Ở mục Sources, bạn nhập công thức sau và nhấn OK.

=INDIRECT[SUBSTITUTE[địa chỉ ô phụ thuộc;” “;””]]

Trong đó, ở phần địa chỉ ô phụ thuộc,bạn cần nhập vị trí ô đầu tiên chứa thông tin phụ thuộc.

Như ở ví dụ bên dưới, cột Phụ trách viên sẽ phụ thuộc vào việc Team Đỏ, Vàng hay Xanh. Chính vì lí do đó, địa chỉ ô bắt đầu của thông tin phụ thuộc sẽ là ở cột Team, tức là địa chỉ là C7.

Kết quả:

Cách tạo Drop List trong Excel với Data Validation nâng cao

Không cho phép nhập dữ liệu khác ngoài danh sách thả xuống

Khi bạn đã tạo Drop List trong Excel cho bất kỳ một cột nào, nếu bạn muốn chỉ có thể lựa chọn những thành phần bạn đã tạo cho cột đó từ danh sách thả xuống, bạn nhớ tick chọn ô In-cell dropdown khi bảng chọn Data Validation hiện lên.

Lúc ấy, bạn sẽ chỉ được lựa chọn dữ liệu đã tạo Drop List trong Excel mà không được nhập dữ liệu khác từ bên ngoài. Nếu bạn nhập dữ liệu khác, Excel sẽ báo lỗi.

Thêm hoặc xóa một mục trong Drop List

Chỉnh sửa Drop List dựa trên một dải ô

• Bước 1: Thêm hoặc xóa bất kì ô nào bạn mong muốn trong cột thông tin của bạn.
• Bước 2:Chọn các ô cần chỉnh sửadanh sách thả xuống trong Excel, vào thẻ Data, ở mục Data Validation chọn Data Validation…
• Bước 2: Bảng chọn Data Validation hiện lên. Bạn vào thẻ Settings, thực hiện các bước:

1. Ở mục Allow, bạn nhấnchọnList.
2. Ở mục Sources, nhấp vào biểu tượng vuông nằm bên cạnh và chọn lại vùng thông tin sau khi đã thêm hoặc xóa.

• Bước 3:Nhấn tick vào ô Apply these changes to all other cells with the same settings. Cuối cùng, nhấn OK.

Chỉnh sửa Drop List chứa các mục được nhập theo cách thủ công

• Bước 1: Chọn ô cần chỉnh sửa danh sách thả xuống trong Excel, vào thẻ Data, ở mục Data Validation chọn Data Validation…
• Bước 2: Bảng chọn Data Validation hiện lên. Bạn vào thẻ Settings, thực hiện các bước:

1. Ở mục Allow, bạn nhấnchọnList.
2. Ở mục Sources, bạn chỉnh sửa các thành phần như mong muốn.

• Bước 3:Nhấn tick vào ô Apply these changes to all other cells with the same settings. Cuối cùng, nhấn OK.

Tạo danh sách thả xuống động bằng hàm OFFSET

Nếu bạn tạo Drop List trong Excel ở dạng động, khi bạn chỉnh sửa thông tin của các cột dữ liệu thì phần Drop List của bạn cũng sẽ được tự động cập nhật theo. Việc này giúp bạn tiết kiệm được khá nhiều thời gian cho bạn trong công việc.

• Bước 1: Tạo các bảng tính bao gồm những thông tin cần nhập.
• Bước 2: Đặt tên cho những thành phần được nhập bằng cách: Bôi đen từng cột, nhấn chuột phải chọn Define Name…Bạn đặt tên trong cột Name, lưu ý đặt tên không có dấu cách. Sau đó ở mục Refers to, bạn nhập công thức:

= OFFSET[địa chỉ ô bắt đầu,0,0,COUNTA[cột cần tạo Drop List]]

Ví dụ:

1. Địa chỉ ô bắt đầu ở đây là địa chỉ ô bắt đầu của cột cần tạo danh sách thả xuống, trong ví dụ sẽ là ô C7.
2. Cột cần tạo Drop List là địa chỉ từ ô đầu đến ô cuối của cột, trong ví dụ sẽ là C7:C14.

• Bước 3: Bôi đen các ô cần tạo Drop List. Vào thẻ Data, ở mụcData Validation, bạn chọn Data Validation…
• Bước 4: Bảng chọn Data Validation hiện lên. Bạn vào thẻ Settings, thực hiện các bước:

1. Ở mục Allow, bạn nhấnchọnList.
2. Ở mục Sources, bạn nhập công thức sau và nhấn OK.

= TÊN BẠN MỚI TẠO

Ví dụ: ở hình trên, tên của cột mới tạo là Xanh, thì khi đó, mục Sources sẽ nhập là: =Xanh,cuối cùng nhấn OK.

Kết quả:

Sau này, khi bạn thêm mới vào cột thông tin, cột Drop List sẽ tự động cập nhật:

Cách đánh dấu Drop List chưa nhập liệu

Khi tạo Drop List trong Excel với những danh sách dài, bạn sẽ cần đánh dấu ô trống để dễ quản lý và chỉnh sửa. Để làm được điều này, ở bước tạo Drop List, bạn bỏ tick ở ô Ignore Blank.

Sau đó, bạn không cần lựa chọn lại cột là Drop List mà miễn bạn ở trong trang tính này, thì bạn chỉ cần vào thẻ Data, ở mục Data Validation chọn Circle Invalid Data.

Kết quả Excel sẽ khoanh tròn những ô trống chưa được nhập liệu:

Để xóa đánh dấu, bạn chỉ cần vào thẻ Data, ở mục Data Validation chọn Clear Validation Circles.

Cách xóa Drop List trong Excel

• Bước 1: Chọn ô có danh sách thả xuống. Vào thẻ Data, ở mục Data Validation, chọn Data Validation…
• Bước 2:Bảng chọnData Validation hiện ra, bạn nhấn Clear All và nhấn OK.

Ngoài ra, bạn cũng có thể tham khảo các mẹo dưới đây của ThuthuatOffice về Excel:

• Cách in Excel có tiêu đề lặp lại
• Hàm xếp hạng trong Excel

Sau bài đăng này, chắc chắn bạn đã có thêm những kiến thức về Drop List cũng như cách tạo Drop List trong Excel. Đừng quên Like, Share bài viết, cũng như ghé thăm ThuthuatOffice thường xuyên để biết thêm nhiều thủ thuật hay ho nhé.

Excel -

• Cách chọn vùng in trong Excel cực nhanh

• 3 bước cố định cột trong Excel cực nhanh chóng

• Cách định dạng ngày tháng trong Excel nhanh gọn lẹ

• 2 cách lọc dữ liệu trong Excel cực đơn giản bạn nên biết

• Cách đánh số trang trong Excel liệu bạn đã biết

• Những cách định dạng số trong Excel

• Hướng dẫn cách dùng hàm căn bậc 2 trong Excel đơn giản, nhanh chóng