Xóa dòng trống trong Excel và dọn sạch dữ liệu trước khi lọc, tính tổng, làm báo cáo
File xuất từ phần mềm, file gộp từ nhiều người hay dữ liệu copy từ web thường có dòng trống xen giữa, cột trống thừa, dòng tiêu đề lặp lại mỗi trang, khoảng trắng thừa và ký tự ẩn khiến VLOOKUP không khớp, số và ngày bị lưu thành chữ nên SUM ra 0. Công cụ này xóa dòng trống trong Excel cùng lúc với các lỗi đó, báo rõ đã sửa bao nhiêu ô ở từng mục và cho tải lại file .xlsx sạch, không cần macro hay thao tác lọc từng cột.
Tính năng nổi bật
- Xóa dòng trống hoàn toàn và cột trống hoàn toàn, kể cả dòng chỉ chứa dấu cách
- Xóa dòng bị trống ở một hoặc nhiều cột khóa (ví dụ dòng không có mã nhân viên), chọn điều kiện bất kỳ hoặc tất cả
- Xóa dòng tiêu đề lặp lại giữa bảng khi gộp nhiều file hoặc copy báo cáo nhiều trang
- Bỏ khoảng trắng đầu cuối ô, gộp dấu cách liền nhau, đổi khoảng trắng không ngắt (NBSP) thành dấu cách thường
- Xóa ký tự ẩn: khoảng trắng độ rộng 0, BOM, ký tự điều khiển; tùy chọn nối các dòng trong ô
- Đổi số bị lưu dạng chữ thành số, giữ nguyên mã có số 0 đầu như 0012, 0912345678
- Đổi ngày dạng chữ (5/1/2024, 2024-01-05, 5.1.2024) thành ngày thật định dạng dd/mm/yyyy
- Báo cáo số dòng, cột, ô đã sửa theo từng mục; tô xanh ô đã thay đổi trong bảng xem trước
Vì sao dữ liệu cần làm sạch trước khi dùng
Một dòng trống giữa bảng làm Ctrl+Shift+Mũi tên, Filter, PivotTable và Format as Table tưởng bảng đã hết, bỏ sót phần dữ liệu phía dưới. Một dấu cách thừa sau tên hàng làm VLOOKUP, XLOOKUP, COUNTIF không khớp dù nhìn bằng mắt giống hệt nhau. Khoảng trắng không ngắt copy từ web thì hàm TRIM không xóa được. Số lưu dạng chữ bị SUM bỏ qua, ngày dạng chữ không sắp xếp đúng thứ tự thời gian. Sửa thủ công từng loại lỗi trong Excel tốn nhiều bước và dễ sót; công cụ làm các bước đó trong một lần và cho bạn xem trước kết quả.
Lợi ích khi sử dụng
- Filter, Sort, PivotTable nhận đủ dữ liệu vì không còn dòng trống cắt ngang bảng
- VLOOKUP, COUNTIF khớp đúng vì hết khoảng trắng thừa và ký tự ẩn
- SUM, AVERAGE tính được vì số đã là số thật
- Ngày sắp xếp và lọc theo tháng đúng vì là ngày thật
- Biết chính xác đã sửa gì, không phải đoán
Cách xóa dòng trống và làm sạch file Excel
- 1Chọn file .xlsx, .xls hoặc .csv (hoặc dán bảng copy từ Excel), chọn sheet cần làm sạch nếu file có nhiều sheet.
- 2Chọn việc cần làm: xóa dòng trống, cột trống, dòng tiêu đề lặp, dòng trống ở cột khóa; bỏ khoảng trắng, ký tự ẩn; đổi số và ngày dạng chữ.
- 3Xem phần kết quả: số dòng, cột trước và sau, số ô được sửa ở từng mục và các cảnh báo nếu có.
- 4Dò bảng xem trước, ô được sửa tô màu xanh.
- 5Bấm Tải file Excel đã làm sạch; bật Làm sạch mọi sheet nếu muốn xử lý cả file nhiều sheet một lần.
Xóa dòng trống bằng Go To Special trong Excel và cái bẫy của nó
Cách phổ biến: chọn vùng dữ liệu, bấm F5 (hoặc Ctrl+G), bấm Special, chọn Blanks, OK; Excel chọn mọi ô trống. Sau đó vào Home, Delete, Delete Sheet Rows. Cách này chỉ an toàn khi vùng chọn là một cột mà ô trống nghĩa là cả dòng trống. Nếu chọn nhiều cột, Blanks chọn từng ô trống riêng lẻ và Delete Sheet Rows sẽ xóa mọi dòng có ít nhất một ô trống, kể cả dòng còn dữ liệu ở các cột khác, ví dụ nhân viên chưa có số điện thoại cũng bị xóa. Hãy kiểm tra kỹ trước khi dùng, hoặc chỉ chọn cột luôn có dữ liệu ở dòng hợp lệ.
Xóa dòng trống bằng Filter hoặc cột phụ
Với một cột khóa: chọn dòng tiêu đề, vào Data, Filter, mở mũi tên lọc ở cột khóa, bỏ chọn tất cả rồi chỉ chọn (Blanks), chọn các dòng hiện ra, bấm chuột phải, Delete Row, rồi Clear filter. Muốn xóa đúng dòng trống hoàn toàn trên nhiều cột, thêm một cột phụ với công thức =COUNTA(A2:F2)=0 (chỉ có một đối số nên viết giống nhau trên mọi máy), lọc cột phụ bằng TRUE, xóa các dòng đó rồi xóa cột phụ. Lưu ý COUNTA đếm cả ô chỉ có dấu cách là có dữ liệu, nên dòng chỉ chứa dấu cách không bị xóa theo cách này. Trong Power Query có sẵn lệnh Home, Remove Rows, Remove Blank Rows.
Khoảng trắng thừa và ký tự ẩn: TRIM, CLEAN làm được gì
Hàm TRIM của Excel xóa dấu cách ở đầu, cuối và gộp dấu cách liền nhau, nhưng chỉ với dấu cách thường (mã 32). Khoảng trắng không ngắt mã 160 hay gặp khi copy từ web thì TRIM bỏ qua; phải dùng =TRIM(SUBSTITUTE(A2;CHAR(160);" ")). Hàm CLEAN xóa 32 ký tự điều khiển đầu tiên của bảng mã ASCII (mã 0 đến 31), trong đó có ký tự xuống dòng, nhưng không xóa khoảng trắng độ rộng 0 hay BOM. Công cụ xử lý cả các ký tự này: NBSP và các loại khoảng trắng Unicode được đổi thành dấu cách thường, khoảng trắng độ rộng 0, BOM, gạch nối mềm và ký tự điều khiển bị xóa. Xuống dòng trong ô chỉ bị nối lại khi bạn bật tùy chọn, vì nhiều bảng cố ý xuống dòng trong ô địa chỉ, ghi chú.
Số lưu dạng chữ: công cụ đổi những ô nào
Ô số dạng chữ thường có tam giác xanh ở góc và căn lề trái. Trong Excel có thể chọn các ô đó, bấm biểu tượng cảnh báo, chọn Convert to Number, hoặc dùng =VALUE(A2). Công cụ chỉ đổi khi chắc chắn: số nguyên như 42 hay 1500000, số thập phân như 12.5 hay 12,5. Chuỗi toàn chữ số bắt đầu bằng 0 như 0012, 0912345678 được giữ dạng chữ vì đó là mã hoặc số điện thoại. Số dài hơn 15 chữ số như số tài khoản, số căn cước được giữ dạng chữ vì Excel chỉ lưu chính xác 15 chữ số. Ô có đúng ba chữ số sau dấu như 1.500 hoặc 2,750 được giữ nguyên và báo lại, vì có thể là một nghìn năm trăm theo cách viết Việt Nam hoặc một phẩy năm theo cách viết Mỹ.
Ngày dạng chữ và thứ tự ngày tháng
Ngày dạng chữ không sắp xếp theo thời gian và không dùng được trong hàm DATEDIF, EOMONTH. Công cụ đọc các dạng d/m/yyyy, d-m-yyyy, d.m.yyyy và yyyy-mm-dd, kèm giờ phút nếu có, kiểm tra ngày có thật trong lịch (31/02/2024 bị bỏ qua), rồi ghi thành ngày thật với định dạng dd/mm/yyyy. Chuỗi như 03/04/2024 được hiểu theo cách viết Việt Nam là ngày 3 tháng 4; chỉ khi số thứ hai lớn hơn 12 như 3/25/2024 công cụ mới chắc đây là kiểu tháng/ngày của Mỹ và đảo lại, đồng thời báo số ô đã đảo. Nếu cả cột ghi theo kiểu Mỹ, hãy kiểm tra lại các ô có cả hai số nhỏ hơn hoặc bằng 12. Ô đã là ngày thật trong file giữ nguyên giá trị và được hiển thị thống nhất dd/mm/yyyy.
Dòng tiêu đề lặp và dòng trống ở cột khóa
Khi gộp báo cáo nhiều trang hay nhiều file, dòng tiêu đề thường xuất hiện lại giữa bảng và bị tính như một dòng dữ liệu. Công cụ lấy dòng có dữ liệu đầu tiên làm tiêu đề và xóa những dòng phía sau có nội dung giống hệt (không phân biệt hoa thường, bỏ qua khoảng trắng hai đầu). Tùy chọn cột khóa dùng khi bảng có dòng ghi chú, dòng tổng hay dòng rác không có mã: chọn cột mã, công cụ xóa dòng trống ở cột đó. Với nhiều cột khóa, chọn xóa khi bất kỳ cột nào trống hoặc chỉ khi tất cả đều trống. Hãy xem kỹ số dòng bị xóa trong báo cáo, vì dòng tổng cộng không có mã cũng sẽ bị xóa theo quy tắc này.
Giới hạn
File tải về chỉ chứa giá trị ô: màu, viền, độ rộng cột gốc, công thức (chỉ lấy kết quả đã tính), ô gộp, bộ lọc, hình ảnh, biểu đồ không được giữ. Định dạng số của ô số thật được giữ, ô ngày được hiển thị dd/mm/yyyy. Mỗi sheet tối đa khoảng 3 triệu ô và file tối đa 30 MB để trình duyệt không bị treo. File có mật khẩu cần gỡ mật khẩu trước. Nếu cần giữ nguyên định dạng file gốc, hãy dùng file làm sạch để đối chiếu, hoặc làm theo các cách trong Excel ở trên.
Câu hỏi thường gặp (FAQ)
Làm sao xóa dòng trống trong Excel nhanh nhất?
Tải file lên công cụ, để nguyên tùy chọn Xóa dòng trống hoàn toàn và tải file về. Trong Excel, cách nhanh là Go To Special, Blanks, Delete Sheet Rows, nhưng chỉ an toàn khi chọn một cột, vì chọn nhiều cột sẽ xóa cả dòng có dữ liệu.
Vì sao dùng Go To Special xóa dòng trống lại mất dữ liệu?
Vì Blanks chọn từng ô trống chứ không chọn dòng trống. Delete Sheet Rows sau đó xóa mọi dòng có ít nhất một ô trống được chọn, kể cả dòng còn dữ liệu ở cột khác.
Dòng chỉ có dấu cách có bị coi là dòng trống không?
Có. Công cụ coi ô chỉ có dấu cách, khoảng trắng không ngắt hoặc ký tự ẩn là ô trống, nên dòng gồm toàn những ô như vậy bị xóa khi bật xóa dòng trống.
Xóa cột trống có xóa cột chỉ có tiêu đề không?
Không. Cột bị xóa phải trống hoàn toàn, kể cả dòng tiêu đề. Cột có tiêu đề nhưng chưa có dữ liệu được giữ lại.
Hàm TRIM không xóa được khoảng trắng, vì sao?
Thường do đó là khoảng trắng không ngắt (mã 160) copy từ web. TRIM chỉ xóa dấu cách mã 32. Dùng =TRIM(SUBSTITUTE(A2;CHAR(160);" ")) hoặc bật Xử lý ký tự ẩn trong công cụ.
Công cụ có làm mất số 0 ở đầu mã hàng, số điện thoại không?
Không. Chuỗi toàn chữ số bắt đầu bằng 0 được giữ dạng chữ; ô số thật có định dạng 0000000000 được giữ nguyên định dạng đó.
Vì sao ô 1.500 không được đổi thành số?
Vì 1.500 có thể là một nghìn năm trăm (cách viết Việt Nam) hoặc một phẩy năm (cách viết Mỹ). Công cụ không đoán mà báo lại số ô như vậy; dùng công cụ đổi dấu chấm phẩy để xử lý các cột này theo kiểu nhận dạng cả cột.
Ngày 03/04/2024 được hiểu là ngày nào?
Ngày 3 tháng 4 năm 2024, theo cách viết Việt Nam ngày trước tháng. Chỉ khi số thứ hai lớn hơn 12 (như 3/25/2024) mới được hiểu là kiểu tháng/ngày.
File có nhiều sheet thì làm sao?
Chọn sheet cần xem trước, bật Làm sạch mọi sheet để xuất tất cả sheet đã làm sạch trong một file. Cột khóa chỉ áp dụng cho sheet đang chọn vì các sheet có cấu trúc cột khác nhau.
Công thức trong file có còn không?
Không. Công cụ đọc kết quả đã tính của công thức và ghi ra giá trị. Nếu cần giữ công thức, hãy làm sạch trên bản sao rồi đối chiếu.
Có xóa được dòng trùng lặp không?
Công cụ này chỉ xóa dòng tiêu đề lặp. Để xóa dòng trùng dữ liệu, dùng Data, Remove Duplicates trong Excel hoặc công cụ lọc trùng chuyên dụng sau khi đã làm sạch khoảng trắng, vì khoảng trắng thừa là lý do phổ biến khiến hai dòng giống nhau không bị coi là trùng.
Dữ liệu có bị tải lên máy chủ không?
Không. File được đọc và làm sạch hoàn toàn trong trình duyệt của bạn.
Từ khóa liên quan
- xóa dòng trống trong excel
- cách xóa dòng trống trong excel
- xóa hàng trống trong excel
- xóa cột trống trong excel
- làm sạch dữ liệu excel
- xóa khoảng trắng trong excel
- hàm trim không xóa được khoảng trắng
- xóa ký tự ẩn trong excel
- go to special blanks
- chuyển text thành số trong excel
- convert to number excel
- chuyển ngày dạng text sang date
- xóa dòng tiêu đề lặp lại
- xóa dòng trống hàng loạt
- xóa dòng trống trong excel online
- clean data excel online
- loại bỏ ký tự đặc biệt trong excel
- chuẩn hóa ngày tháng dd/mm/yyyy
