Khi cần tách mã phòng ra khỏi mã nhân viên hay viết lại số điện thoại cho dễ đọc, nhiều người gõ tay từng dòng hoặc phải nghĩ ra một công thức dài. Bài viết này hướng dẫn bạn dùng Flash Fill trong Excel để làm những việc đó chỉ bằng một ví dụ và phím Ctrl + E.

Bạn gõ kết quả mong muốn của dòng đầu tiên, chọn ô ngay bên dưới rồi nhấn Ctrl + E. Excel tự nhận ra quy luật và điền cho các dòng còn lại. Ví dụ, với mã nhân viên "HN-KT-0125" ở ô A2, bạn gõ "KT" vào ô C2, chọn ô C3 và nhấn Ctrl + E. Excel điền KD, NS, KD, KT, HC, KD, NS cho 7 nhân viên còn lại.

Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Các cột có tiêu đề màu vàng nhạt là nơi bạn gõ ví dụ hoặc công thức. Mỗi bảng ví dụ nằm ở một sheet riêng và bắt đầu từ ô A1.

Flash Fill trong Excel là gì và Ctrl + E dùng để làm gì?

Flash Fill là tính năng tự điền dữ liệu theo ví dụ. Bạn cho Excel xem kết quả của một hoặc hai dòng, Excel đoán cách bạn biến đổi dữ liệu gốc rồi làm giống vậy cho cả cột. Flash Fill làm được 3 việc chính:

  • Flash Fill tách một phần của chuỗi, ví dụ lấy mã phòng từ mã nhân viên hoặc lấy tên đăng nhập từ email.

  • Flash Fill ghép dữ liệu từ nhiều cột, ví dụ ghép họ tên với mã phòng.

  • Flash Fill đổi định dạng chữ và số, ví dụ viết số điện thoại thành từng cụm hoặc viết hoa chữ cái đầu.

Ctrl + E là phím tắt của Flash Fill. Phím này không phải một hàm, nên kết quả Flash Fill điền vào ô là giá trị cố định, không có công thức nào phía sau.

Bạn cũng mở được Flash Fill bằng chuột: trên thẻ Data, nhóm Data Tools, bạn bấm nút Flash Fill.

Flash Fill có từ Excel 2013. Theo trang hỗ trợ của Microsoft, tính năng này có trên Excel cho Microsoft 365, Excel 2024, 2021, 2019, 2016, trên Excel cho Mac bản Microsoft 365, 2021, 2024 và trên Excel bản web. Excel 2010 và Excel 2007 không có Flash Fill, nên bạn không tìm thấy nút này và nhấn Ctrl + E cũng không có tác dụng.

Bạn dùng Flash Fill như thế nào?

Ví dụ 1: Bạn tách mã phòng từ mã nhân viên

Sheet Mã nhân viên có mã nhân viên ở cột A, từ ô A2 đến A9. Mỗi mã gồm 3 phần ngăn bằng dấu gạch: chi nhánh, phòng và số thứ tự, ví dụ "HN-KT-0125". Phần chi nhánh dài 2 hoặc 3 ký tự, nên bạn không cắt được ở một vị trí cố định.

Bạn làm theo 3 bước:

  1. Bạn chọn ô C2, gõ mã phòng của nhân viên đầu tiên là "KT" rồi nhấn Enter.

Bạn đang gõ KT vào ô C2, cạnh mã nhân viên HN-KT-0125 ở ô A2
Ô C2 là ví dụ để Excel học quy luật
  1. Bạn chọn ô C3, ngay bên dưới ô ví dụ.

  2. Bạn nhấn Ctrl + E, hoặc vào thẻ Data và bấm nút Flash Fill.

Nút Flash Fill nằm ngay bên phải nút Text to Columns, ở hàng trên cùng của nhóm Data Tools. Khi cửa sổ Excel hẹp như trong ảnh, nút chỉ còn một biểu tượng nhỏ không có chữ. Khi bạn rê chuột vào nút, Excel hiện tên "Flash Fill (Ctrl+E)".

Con trỏ chuột chỉ vào nút Flash Fill trong nhóm Data Tools của thẻ Data, ô C3 đang chọn, Excel hiện chú thích Flash Fill (Ctrl+E)
Chú thích của nút ghi luôn phím tắt Ctrl+E

Excel điền mã phòng cho cả 7 dòng còn lại. Thanh trạng thái ở góc dưới bên trái ghi "Flash Fill Changed Cells: 7", nghĩa là Flash Fill đã điền 7 ô.

Cột C có KT, KD, NS, KD, KT, HC, KD, NS và thanh trạng thái ghi Flash Fill Changed Cells: 7
Flash Fill lấy đúng phần giữa hai dấu gạch dù mã chi nhánh dài 2 hay 3 ký tự

Ngay sau khi điền, Excel hiện một nút nhỏ có hình tia sét cạnh vùng kết quả, ở ảnh trên là cạnh ô D4. Bạn bấm nút này để mở menu Flash Fill Options với 4 lệnh:

  • Lệnh Undo Flash Fill hủy kết quả vừa điền.

  • Lệnh Accept suggestions giữ kết quả vừa điền. Lệnh này đang được tích sẵn.

  • Lệnh Select all 0 blank cells chọn các ô Flash Fill để trống. Số 0 cho biết Flash Fill không bỏ trống ô nào.

  • Lệnh Select all 7 changed cells chọn 7 ô vừa điền để bạn tô màu hoặc soát lại.

Menu Flash Fill Options đang mở với Undo Flash Fill, Accept suggestions, Select all 0 blank cells, Select all 7 changed cells
Con trỏ chỉ vào lệnh Undo Flash Fill ở đầu menu

Ví dụ 2: Excel gợi ý chữ xám khi bạn gõ ví dụ thứ hai

Sheet Email có email nhân viên ở cột A, ví dụ "an.nguyen@minhphat.vn". Bạn cần lấy phần đứng trước dấu @ để làm tên đăng nhập.

Bạn gõ "an.nguyen" vào ô B2 rồi nhấn Enter. Tiếp theo, bạn gõ chữ "ha" vào ô B3. Ngay khi bạn gõ, Excel tự điền nốt ".tran" trong ô B3 và hiện sẵn tên đăng nhập của các dòng bên dưới bằng chữ màu xám.

Ô B3 đang gõ chữ ha và Excel điền nốt .tran, các ô B4 đến B9 hiện gợi ý màu xám hoa.le, duc.pham, huy.do
Chữ màu xám là gợi ý, chưa được ghi vào ô

Bạn nhấn Enter để nhận gợi ý.

Cột B có đủ tên đăng nhập an.nguyen, ha.tran, hoa.le, duc.pham, huy.do, mai.vu, anh.bui, lan.hoang
Mỗi tên đăng nhập là phần đứng trước dấu @ của email cùng dòng

Gợi ý xám chỉ hiện khi Flash Fill tự động đang bật, xem cách bật ở phần sau. Nếu gợi ý không hiện, bạn vẫn dùng được Ctrl + E như Ví dụ 1.

Ví dụ 3: Bạn đổi định dạng số điện thoại

Sheet Điện thoại có số điện thoại viết liền ở cột B, ví dụ "0912345678". Bạn muốn viết lại thành "0912 345 678" để dễ đọc khi in danh bạ.

Cột B phải lưu số điện thoại dạng chữ. Nếu bạn tự gõ số vào cột B, bạn gõ dấu nháy đơn ở đầu như '0912345678, hoặc đổi định dạng cột B sang Text trước khi gõ số. Khi cột B lưu dạng số, Excel bỏ số 0 ở đầu và Flash Fill điền sai cả cột mà không báo lỗi. Ví dụ, số 987654321 ra "0912 654 321" vì Flash Fill chép lại 4 số đầu của ví dụ.

Bạn gõ "0912 345 678" vào ô C2 rồi nhấn Enter.

Bạn đang gõ 0912 345 678 vào ô C2, cạnh số 0912345678 ở ô B2
Ví dụ có dấu cách sau 4 số đầu và sau 3 số tiếp theo

Sau đó, bạn chọn ô C3 và nhấn Ctrl + E.

Cột C có số điện thoại chia 3 cụm cho cả 8 nhân viên, ô C3 là 0987 654 321
Số 0 ở đầu được giữ nguyên vì cột B lưu số điện thoại dạng chữ

Ví dụ 4: Bạn ghép họ tên với mã phòng

Sheet Ghép tên có họ tên ở cột A và mã phòng ở cột B. Bạn cần tên hiển thị dạng "Nguyễn Văn An - KT" để dán vào danh sách chấm công.

Bạn gõ "Nguyễn Văn An - KT" vào ô C2 rồi nhấn Enter.

Bạn đang gõ Nguyễn Văn An - KT vào ô C2
Ví dụ ghép cột A, một dấu cách, dấu gạch, một dấu cách và cột B

Sau đó, bạn chọn ô C3 và nhấn Ctrl + E.

Cột C có Trần Thị Thu Hà - KD, Lê Hoa - NS, Phạm Minh Đức - KD cho các dòng bên dưới
Flash Fill lấy dữ liệu từ cả hai cột A và B

Flash Fill còn đổi được kiểu chữ. Ví dụ, cột A ghi họ tên bằng chữ thường từ ô A2, như "nguyễn văn an". Bạn gõ "Nguyễn Văn An" vào ô B2, chọn ô B3 và nhấn Ctrl + E. Excel viết hoa chữ cái đầu cho cả cột, kể cả chữ có dấu như "Đỗ Quang Huy".

Khi nào Flash Fill đoán sai và bạn sửa thế nào?

Flash Fill đoán quy luật từ rất ít ví dụ và không báo khi đoán sai. Bạn luôn lướt lại cột kết quả, nhất là các dòng có dữ liệu khác kiểu với dòng ví dụ. Hai trường hợp dưới đây là kết quả thật trên bộ dữ liệu của bài.

Số 0 ở đầu bị mất làm Flash Fill hiểu sai quy luật

Quay lại sheet Mã nhân viên, bạn muốn lấy số thứ tự "0125" từ mã "HN-KT-0125" vào cột D. Bạn gõ 0125 vào ô D2.

Bạn đang gõ 0125 vào ô D2
Ô D2 đang ở định dạng General

Khi bạn nhấn Enter, Excel coi 0125 là số và lưu thành 125. Sau đó, bạn chọn ô D3 và nhấn Ctrl + E.

Cột D có 125, 8, 342, 207, 56, 19, 731, 290, ô D5 hiện 207 dù mã là HN-KD-1207
Ô D5 ra 207 thay vì 1207

Kết quả sai ở 2 điểm:

  • Mọi số mất số 0 ở đầu, ví dụ "0008" thành 8.

  • Ô D5 ra 207 thay vì 1207. Ví dụ duy nhất Flash Fill thấy là 125, nên Excel hiểu quy luật là "lấy 3 chữ số cuối".

Bạn nhấn Ctrl + Z để hủy kết quả. Sau đó, bạn gõ lại ô D2 với dấu nháy đơn ở đầu: '0125. Dấu nháy bảo Excel giữ nguyên 0125 dạng chữ và không hiện trong ô.

Bạn đang gõ '0125 vào ô D2
Dấu nháy đơn giữ số 0 ở đầu

Bạn chọn lại ô D3 và nhấn Ctrl + E.

Cột D có 0125, 0008, 0342, 1207, 0056, 0019, 0731, 0290, ô D5 hiện 1207
Thanh công thức hiện '1207 vì Flash Fill ghi kết quả dạng chữ, giữ đủ 4 chữ số

Ngoài dấu nháy, bạn có thể chọn cả cột D và đổi định dạng sang Text trước khi gõ ví dụ.

Trên máy của bạn, Excel có thể hiện tam giác xanh ở góc các ô cột D vì số đang lưu dạng chữ. Ảnh trong bài không có tam giác này vì máy chụp đã tắt kiểm tra lỗi nền. Bạn bỏ qua cảnh báo này, vì số thứ tự cần giữ số 0 ở đầu.

Flash Fill không tự bỏ dấu tiếng Việt

Sheet Tên không dấu có họ tên ở cột A. Bạn muốn tạo tên đăng nhập dạng "an.nguyen" từ "Nguyễn Văn An". Bạn gõ "an.nguyen" vào ô B2, chọn ô B3 và nhấn Ctrl + E.

Cột B có an.nguyen ở dòng 2, các dòng dưới ra chuỗi sai như ht.trầnen, h.lê hen
Flash Fill không biết "Nguyễn" và "nguyen" là cùng một chữ

Flash Fill chỉ cắt, ghép và đổi kiểu chữ hoa, chữ thường. Tính năng này không đổi được "ễ" thành "e", nên kết quả sai ở mọi dòng có dấu. Gõ thêm ví dụ thứ hai "ha.tran" cũng chỉ sửa được đúng dòng đó. Với việc này, bạn bỏ dấu trước rồi mới ghép, theo bài loại bỏ dấu tiếng Việt trong Excel.

Bạn giúp Flash Fill đoán đúng bằng cách nào?

  • Bạn chọn dòng ví dụ khó nhất, ví dụ mã có chi nhánh 3 ký tự hoặc họ tên 4 chữ.

  • Bạn nhấn Ctrl + Z để hủy kết quả, gõ thêm ví dụ thứ hai ở dòng mà Flash Fill điền sai, rồi chọn ô trống đầu tiên trong cột và nhấn lại Ctrl + E. Excel coi mọi ô đã có chữ trong cột là ví dụ, nên bạn không chạy lại khi cột còn kết quả sai.

  • Bạn in đậm dòng tiêu đề. Khi tiêu đề là chữ thường, Flash Fill có thể lấy tiêu đề làm ví dụ. Ví dụ, bảng có tiêu đề "Họ tên" và "Viết hoa" không in đậm cho ra chữ "Viết hoa" ở dòng của "lê hoa".

  • Bạn gõ số có số 0 ở đầu bằng dấu nháy đơn.

  • Bạn dùng công thức khi dữ liệu không theo một quy luật chung.

Bài tách tên trong Excel có thêm ví dụ Flash Fill tách tên từ họ tên 2 đến 5 chữ.

Kết quả Flash Fill có tự cập nhật khi dữ liệu gốc thay đổi không?

Kết quả Flash Fill không tự cập nhật. Flash Fill ghi giá trị cố định vào ô, nên khi bạn sửa dữ liệu gốc, cột kết quả vẫn giữ giá trị cũ.

Ví dụ, nhân viên ở dòng 3 chuyển từ phòng KD sang phòng KT. Bạn sửa ô A3 thành "HCM-KT-0008". Ô C3 vẫn ghi KD.

Ô A3 đã sửa thành HCM-KT-0008 nhưng ô C3 vẫn là KD
Thanh công thức của ô C3 chỉ có chữ KD, không có công thức

Bạn có 2 cách xử lý:

  • Bạn xóa cột kết quả, giữ lại ô ví dụ rồi chạy lại Ctrl + E mỗi khi dữ liệu gốc đổi.

  • Bạn dùng công thức nếu dữ liệu gốc thay đổi thường xuyên.

Để tách mã phòng bằng công thức, bạn chọn ô E2, gõ công thức sau rồi nhấn Enter:

=TEXTBEFORE(TEXTAFTER(A2,"-"),"-")

Bạn đang gõ vào ô E2 công thức =TEXTBEFORE(TEXTAFTER(A2,"-"),"-")
Hàm TEXTAFTER bỏ phần chi nhánh, rồi TEXTBEFORE lấy phần trước dấu gạch còn lại

Sau đó, bạn kéo công thức từ ô E2 xuống ô E9. Ô E3 trả về KT theo mã mới, trong khi ô C3 của Flash Fill vẫn là KD.

Ô E3 hiện KT theo mã mới HCM-KT-0008, ô C3 vẫn là KD
Cột E tự tính lại mỗi khi cột A thay đổi

Hàm TEXTBEFORE và TEXTAFTER chỉ có trên Microsoft 365 và Excel 2024. Trên Excel bản cũ hơn, vì mã phòng luôn có 2 ký tự, bạn dùng =MID(A2,FIND("-",A2)+1,2) và nhận cùng kết quả. Để ghép chuỗi bằng công thức thay cho Ví dụ 4, bạn xem bài nối chuỗi trong Excel bằng dấu &, CONCAT và TEXTJOIN.

Vì sao Ctrl + E không dùng được trong Excel?

Khi Flash Fill không tìm được quy luật, Excel hiện thông báo "We looked at all the data next to your selection and didn't see a pattern for filling in values for you" và không điền ô nào. Câu này nghĩa là Excel đã xem dữ liệu nằm cạnh ô bạn chọn nhưng không thấy quy luật để điền. Ảnh dưới là thông báo khi cột kết quả nằm cách bảng một cột trống: sheet Cột trống có ô D2 chứa ví dụ "KT", cột C để trống, và bạn nhấn Ctrl + E ở ô D3.

Ô D2 có ví dụ KT, ô D3 đang chọn, Excel hiện hộp thông báo không tìm thấy quy luật vì cột D cách bảng một cột trống
Cột C trống làm cột D không còn nằm sát bảng dữ liệu, nên Flash Fill không điền ô D3

Bảng sau liệt kê các nguyên nhân hay gặp:

Dấu hiệu

Nguyên nhân

Cách sửa

Thẻ Data không có nút Flash Fill, và Ctrl + E không có tác dụng.

Bạn dùng Excel 2010 hoặc bản cũ hơn.

Bạn dùng công thức LEFT, MID, FIND hoặc nâng cấp lên Excel 2013 trở lên.

Excel báo không tìm thấy quy luật.

Cột kết quả cách bảng dữ liệu một cột trống.

Bạn xóa cột trống hoặc gõ ví dụ vào cột nằm sát bảng.

Excel báo không tìm thấy quy luật.

Ô ví dụ là công thức. Flash Fill chỉ học từ giá trị bạn gõ tay.

Bạn gõ tay kết quả vào ô ví dụ, hoặc kéo công thức xuống cả cột.

Excel báo không tìm thấy quy luật.

Chỉ có một ví dụ nhưng quy luật chưa rõ, hoặc dữ liệu mỗi dòng một kiểu.

Bạn gõ thêm ví dụ thứ hai, thứ ba rồi nhấn lại Ctrl + E.

Flash Fill dừng giữa chừng.

Bảng có một dòng trống. Flash Fill chỉ điền tới trước dòng trống.

Bạn xóa dòng trống rồi nhấn lại Ctrl + E, hoặc gõ thêm một ví dụ ở dòng đầu tiên sau dòng trống rồi nhấn Ctrl + E ở ô ngay dưới.

Excel không hiện gợi ý chữ xám khi bạn gõ.

Ô Automatically Flash Fill đang tắt, hoặc ô Enable AutoComplete for cell values đang tắt nên ô Automatically Flash Fill bị mờ.

Bạn tích cả hai ô như phần sau, hoặc nhấn Ctrl + E.

Flash Fill điền sai ở vài dòng.

Ví dụ chưa đại diện cho dữ liệu, hoặc số mất số 0 ở đầu.

Bạn nhấn Ctrl + Z, gõ thêm ví dụ ở dòng sai rồi nhấn lại Ctrl + E. Với số có số 0 ở đầu, bạn gõ ví dụ bằng dấu nháy đơn.

Mọi số điện thoại đều bắt đầu bằng 4 số đầu của ví dụ.

Cột số điện thoại gốc lưu dạng số nên mất số 0 ở đầu.

Bạn nhấn Ctrl + Z để hủy kết quả, gõ lại cột gốc với dấu nháy đơn, hoặc đổi cột gốc sang định dạng Text rồi gõ lại số. Sau đó, bạn nhấn lại Ctrl + E.

Kết quả không đổi khi bạn sửa dữ liệu gốc.

Flash Fill ghi giá trị cố định.

Bạn chạy lại Ctrl + E hoặc dùng công thức.

Bạn bật hoặc tắt Flash Fill tự động ở đâu?

Flash Fill tự động là phần hiện gợi ý chữ xám trong Ví dụ 2. Bạn bật hoặc tắt theo 4 bước:

  1. Bạn bấm thẻ File, chọn Options.

  2. Trong hộp thoại Excel Options, bạn chọn mục Advanced ở cột bên trái.

  3. Trong nhóm Editing options, bạn tích ô Enable AutoComplete for cell values, rồi tích ô Automatically Flash Fill ngay bên dưới để bật. Bạn bỏ tích ô Automatically Flash Fill để tắt.

  4. Bạn bấm OK.

Hộp thoại Excel Options, mục Advanced, con trỏ chỉ vào ô Automatically Flash Fill ngay dưới ô Enable AutoComplete for cell values
Ô Automatically Flash Fill nằm thụt vào ngay dưới ô Enable AutoComplete for cell values

Ô Automatically Flash Fill phụ thuộc ô Enable AutoComplete for cell values ở trên. Khi ô Enable AutoComplete bị bỏ tích, ô Automatically Flash Fill bị mờ và Excel không hiện gợi ý xám. Nếu bạn thấy ô Automatically Flash Fill bị mờ, bạn tích ô Enable AutoComplete trước.

Khi bạn tắt một trong hai ô này, Excel chỉ ngừng hiện gợi ý xám. Nút Flash Fill trên thẻ Data và phím Ctrl + E vẫn chạy bình thường.

Flash Fill khác kéo chuột tự điền và công thức thế nào?

Người dùng hay gọi chung các cách này là "tự động điền dữ liệu", nhưng mỗi cách làm một việc khác nhau:

Cách

Bạn làm gì

Excel điền gì

Khi dữ liệu gốc đổi

Kéo chấm vuông góc ô (AutoFill)

Bạn kéo ô đầu xuống.

Excel điền dãy số, ngày tháng tăng dần hoặc chép công thức.

Kết quả tự cập nhật nếu ô đầu là công thức.

Flash Fill (Ctrl + E)

Bạn gõ 1 đến 2 ví dụ.

Excel điền giá trị cố định theo quy luật đoán được.

Kết quả không tự cập nhật.

Công thức

Bạn viết công thức rồi kéo xuống.

Excel tính kết quả từ ô gốc.

Kết quả tự cập nhật.

Bạn dùng Flash Fill khi cần làm sạch dữ liệu một lần, ví dụ trước khi nhập danh sách vào phần mềm. Bạn dùng công thức khi bảng được cập nhật hằng ngày hoặc khi người khác cần xem cách tính.

Bạn muốn học Excel bài bản 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.