Với tính năng nhập và tổ chức dữ liệu, Microsoft Excel cũng có thể thực hiện phân tích dữ liệu từ sơ cấp đến trung cấp một cách dễ dàng ngày nay. Đối với việc sử dụng hàng ngày của chúng tôi, nó có thể cung cấp các công cụ chức năng đi một chặng đường dài. Làm sạch dữ liệu là bước chính cho bất kỳ phương pháp phân tích dữ liệu nào. Nó bao gồm việc loại bỏ, chuẩn bị hoặc chuyển đổi các giá trị không mong muốn hoặc bất thường thành những giá trị có thể sử dụng được. Trong hướng dẫn này, chúng ta sẽ thảo luận về các kỹ thuật làm sạch dữ liệu khác nhau và cách thực hiện chúng trong Microsoft Excel.
Bạn có thể tải xuống sổ làm việc được sử dụng cho phần trình diễn từ liên kết bên dưới.
19 Kỹ thuật làm sạch dữ liệu trong Excel sẽ hữu ích
Chúng tôi sẽ thảo luận về tổng số mười chín kỹ thuật làm sạch dữ liệu khác nhau trong Excel trong bài viết này. Tất cả chúng đều quan trọng trong các trường hợp khác nhau ít nhiều. Hãy theo dõi để biết cách hoạt động của từng loại hoặc tìm cái bạn cần từ mục lục ở trên.
1. Xóa các hàng trùng lặp
Dữ liệu của bạn có thể có các hàng trùng lặp, bất kể lý do gì. Hầu hết thời gian, bạn cần phải loại bỏ các hàng trùng lặp. Ngày xưa, việc loại bỏ dữ liệu trùng lặp là một công việc thủ công - mặc dù công việc loại bỏ có thể được thực hiện bằng các kỹ thuật tiên tiến. Nhưng với Xóa từ trùng lặp lệnh, loại bỏ trùng lặp bây giờ là một công việc dễ dàng. Xóa từ trùng lặp lệnh đã được giới thiệu trong Excel 2007.
Hãy sao chép cột bên cạnh và sau đó xóa các bản sao để so sánh chúng sau này.
Làm theo các bước sau để xóa các bản sao.
Các bước:
- Trước tiên, hãy chọn cột bạn muốn xóa trùng lặp.
- Bây giờ, hãy chuyển đến Dữ liệu trên ruy-băng của bạn.
- Sau đó, chọn Xóa bản sao từ Công cụ dữ liệu nhóm ở đó.
- Sau đó, chọn Tiếp tục với lựa chọn hiện tại trong hộp bật lên và nhấp vào Xóa bản sao .
- Tiếp theo, nhấp vào OK .
Thao tác này sẽ xóa tất cả các bản sao khỏi lựa chọn.
2. Đánh dấu các giá trị trùng lặp
Đôi khi, thay vì xóa hoàn toàn các giá trị trùng lặp, đánh dấu chúng sẽ hữu ích. Tại đây, chúng tôi sẽ cho bạn thấy các tình huống và phương pháp khác nhau để làm nổi bật các giá trị trùng lặp.
Sử dụng định dạng có điều kiện
Trong phần đầu tiên, chúng tôi sẽ sử dụng Định dạng có điều kiện tính năng để làm nổi bật các giá trị trùng lặp. Chúng tôi sẽ thực hiện điều đó trên tập dữ liệu sau.
Hãy làm theo các bước sau để xem cách chúng tôi có thể thực hiện điều đó.
Các bước:
- Trước hết, hãy chọn các ô mà chúng tôi muốn sao chép. Trong trường hợp này, nó là D5:D18.
- Thứ hai, đi tới Trang chủ > chọn Định dạng có điều kiện > đi tới Quy tắc đánh dấu ô > chọn Giá trị trùng lặp .
- Cuối cùng, Giá trị trùng lặp cửa sổ sẽ xuất hiện.
- Thứ ba, chọn Sao y .
- Thứ tư, chọn bất kỳ tùy chọn màu nào trong giá trị với Trong trường hợp này, chúng tôi đã chọn Tô màu đỏ nhạt với văn bản màu đỏ đậm .
- Do đó, chúng tôi sẽ thấy rằng các giá trị trùng lặp trong D Cột được đánh dấu.
Đánh dấu các bản sao bao gồm cả lần xuất hiện đầu tiên
Bây giờ, đối với cùng một tập dữ liệu, chúng ta sẽ đánh dấu các giá trị trùng lặp bao gồm cả lần xuất hiện đầu tiên. Điều này cũng giống như phần trước đã đề cập. Nhưng chúng tôi sẽ đi theo một lộ trình khác ở đây cùng với chức năng COUNITF . Làm theo các bước sau để xem phương pháp này hoạt động như thế nào.
Các bước:
- Trước tiên, hãy chuyển đến Quy tắc định dạng mới bằng cách đi tới Định dạng có điều kiện
- Thứ hai, chọn Sử dụng công thức để xác định ô cần định dạng .
- Thứ ba, viết công thức sau vào hộp công thức.
=COUNTIF($D$5:$E$16,$D5)>1
- Do đó, chúng tôi sẽ nhận thấy rằng D được đánh dấu để tìm các bản sao, bao gồm cả các lần xuất hiện đầu tiên.
Đánh dấu các trường hợp trùng lặp loại trừ lần xuất hiện đầu tiên
Bây giờ chúng ta sẽ thảo luận về một trong những kỹ thuật hữu ích nhất để xác định các giá trị trùng lặp. Nó không làm nổi bật chúng ngoại trừ những lần xuất hiện đầu tiên. Điều này rất hữu ích nếu chúng ta chỉ muốn quan sát các giá trị lặp lại thay vì tất cả các giá trị trùng lặp. Cũng giống như phần trước, chúng tôi sẽ sử dụng hàm COUNTIF tại đây.
Hãy làm theo các bước sau để xem cách chúng tôi có thể thực hiện điều đó.
Các bước:
- Để thực hiện việc này, trước tiên, hãy chuyển đến Quy tắc định dạng mới thông qua Định dạng có điều kiện như chúng ta đã thảo luận trước đây.
- Thứ hai, chọn Sử dụng công thức để xác định ô cần định dạng theo cách tương tự như trước đây.
- Thứ ba, viết công thức sau vào hộp công thức.
=COUNTIF($D$5:$D5,$D5)>1
- Sau khi nhấp vào OK , nó sẽ đánh dấu tất cả các bản sao không bao gồm các lần xuất hiện đầu tiên.
3. Tách văn bản thành nhiều ô
Khi bạn nhập dữ liệu từ một nguồn khác, có thể xảy ra trường hợp nhiều giá trị đã được nhập vào một cột. Hình ảnh sau đây cho thấy một ví dụ về loại sự cố nhập khẩu này.
Có một Văn bản thành cột hữu ích tính năng mà chúng tôi có thể sử dụng sẽ giúp chúng tôi phân chia các văn bản này.
Các bước:
- Trước tiên, hãy chọn phạm vi bạn muốn tách văn bản.
- Sau đó, chuyển đến Dữ liệu trên ruy-băng của bạn.
- Sau đó, từ Công cụ dữ liệu nhóm, chọn Văn bản thành cột .
- Tiếp theo, chọn Được phân cách trong hộp tiếp theo.
- Sau khi nhấp vào Tiếp theo , một hộp khác sẽ xuất hiện.
- Chọn Dấu cách tại đây trong Dấu phân cách tùy chọn khi văn bản được chia theo dấu cách.
- Sau đó nhấp vào Tiếp theo .
- Sau đó, nhấp vào Hoàn tất .
- Cuối cùng, văn bản sẽ được tách ra.
- Bây giờ chúng ta hãy thực hiện một số sửa đổi để làm cho nó dễ nhìn hơn.
Đây là cách chúng tôi có thể chia văn bản thành các ô khác nhau cùng với các kỹ thuật làm sạch dữ liệu khác trong Excel.
4. Chuyển đổi dữ liệu bằng công thức
Đôi khi, bạn có thể muốn rằng trường hợp văn bản của bạn sẽ giống nhau. Excel không cung cấp bất kỳ cách trực tiếp nào để thay đổi trường hợp văn bản, nhưng bạn có thể thực hiện dễ dàng bằng cách sử dụng một số công thức. Ba công thức để thay đổi văn bản là:
LÊN LÊN :Công thức này chuyển đổi văn bản thành TẤT CẢ TẤT CẢ.
THẤP HƠN :Công thức này chuyển đổi văn bản thành tất cả chữ thường.
CHUYÊN GIA :Cái này chuyển văn bản thành Chữ hoa phù hợp (chữ cái đầu tiên trong mỗi từ sẽ được viết hoa, giống như tên riêng).
Chúng tôi sẽ sử dụng tập dữ liệu sau cho tất cả các bản trình bày.
Thay đổi thành chữ hoa
Như đã đề cập trước đó, chúng tôi sẽ sử dụng chức năng LÊN LÊN vì mục đích này. Hãy làm theo các bước sau để xem cách chúng tôi có thể thực hiện điều đó.
Các bước:
- Đầu tiên, chọn ô C5 .
- Sau đó, hãy viết công thức sau vào đó.
=UPPER(B5)
- Sau đó, nhấn Enter .
- Bây giờ, hãy chọn lại ô, nhấp và kéo biểu tượng chốt điền để điền công thức vào các ô còn lại.
Thay đổi thành chữ thường
Tiếp theo, chúng tôi sẽ trình bày cách chuyển tất cả văn bản thành chữ thường.
Chúng tôi sẽ làm theo các bước sau cho việc này.
Các bước:
- Đầu tiên, chọn ô D5 .
- Sau đó, hãy viết công thức sau vào đó.
=LOWER(B5)
- Sau đó, nhấn Enter .
- Bây giờ, hãy chọn lại ô, nhấp và kéo biểu tượng chốt điền để điền công thức vào các ô còn lại.
Thay đổi thành trường hợp thích hợp
Trường hợp thích hợp chỉ ra các chữ in hoa ở những nơi thích hợp và viết thường ở những nơi khác. Đối với tập dữ liệu này, chúng tôi đang sử dụng phương pháp sau cho việc này.
Các bước:
- Đầu tiên, chọn ô E5 .
- Sau đó, hãy viết công thức sau vào đó.
=PROPER(B5)
- Sau đó, nhấn Enter .
- Cuối cùng, chọn lại ô, nhấp và kéo biểu tượng chốt điền để điền vào các ô còn lại.
5. Xóa các khoảng trắng phụ
Luôn luôn là một vấn đề rất quan trọng để đảm bảo rằng văn bản không có khoảng trắng thừa. Không thể tìm ra ký tự khoảng trắng ở cuối chuỗi văn bản. Khoảng trắng thừa tạo ra rất nhiều vấn đề, đặc biệt là khi bạn cần so sánh các chuỗi văn bản. Ví dụ:"Tháng 7" không giống như "Tháng 7". Đầu tiên dài bốn ký tự và cái thứ hai dài năm ký tự.
Chúng ta có thể làm theo hai kỹ thuật làm sạch dữ liệu này trong Excel để loại bỏ khoảng trắng thừa.
Chúng tôi sẽ sử dụng cả hai trong số chúng trong tập dữ liệu sau.
Sử dụng hàm TRIM
Chúng tôi sử dụng hàm TRIM để xóa tất cả các khoảng trắng ở đầu và cuối khỏi văn bản. TRIM hàm cũng thay thế nhiều dấu cách bằng một dấu cách.
Các bước:
- Trước tiên, hãy thêm một cột có tên Dữ liệu được cập nhật để hiển thị kết quả.
- Sau đó nhấp vào Ô D5 .
- Nhập =TRIM và chọn Ô C5 trong đối số đầu tiên. Đóng dấu ngoặc đơn.
- Vì vậy, công thức trở thành:
=TRIM(C5)
- Bây giờ, nhấn Enter .
- Bây giờ, hãy kéo biểu tượng Fill Handel đến ô cuối cùng.
Chúng ta có thể thấy rằng những khoảng trống không cần thiết bị loại bỏ; chỉ tồn tại những khoảng trắng sau mỗi từ.
Sử dụng Tính năng Tìm &Thay thế:
Trong phần này, chúng ta sẽ thảo luận về cách sử dụng lệnh Tìm &Thay thế để xóa tất cả các khoảng trắng.
Các bước:
- Trước tiên, hãy chọn dữ liệu từ nơi chúng tôi muốn xóa các khoảng trắng thừa.
- Sau đó, chuyển đến Trang chủ tab.
- Từ Chỉnh sửa , đi tới Tìm &Chọn tính năng.
- Chọn Thay thế từ danh sách thả xuống.
- Khi chúng tôi chọn Thay thế , chúng ta sẽ nhận được một hộp thoại.
- Nhập một khoảng trống trong hộp Tìm gì lĩnh vực này.
- Giữ nút Thay thế bằng hộp trống.
- Sau đó, nhấp vào Thay thế tất cả .
- Sau khi nhấp vào Thay thế tất cả , chúng tôi sẽ nhận được Cửa sổ bật lên hiển thị số lượng thay thế.
- Bây giờ hãy nhấp vào OK trên Cửa sổ bật lên.
- Sau đó, nhấp vào Đóng của hộp thoại.
- Cuối cùng, chúng tôi nhận được kết quả.
6. Xóa các ký tự lạ
Thông thường, sau khi nhập dữ liệu vào trang tính Excel, bạn sẽ thấy có một số ký tự lạ (đôi khi không thể in được). Bạn có thể sử dụng chức năng CLEAN để xóa tất cả các ký tự không in được khỏi văn bản. Nếu dữ liệu nằm trong ô A2, bạn có thể sử dụng để thực hiện công việc của mình:
=CLEAN(A2)
CLEAN đôi khi chức năng có thể bỏ sót một số ký tự Unicode không in được. Nó được lập trình để xóa 32 ký tự không in đầu tiên trong mã ASCII 7-bit. Duyệt qua hệ thống Trợ giúp Excel để biết thông tin về cách xóa các ký tự Unicode không in được.
7. Chuyển đổi giá trị
Một quan trọng khác trong kỹ thuật làm sạch dữ liệu là chuyển đổi một giá trị trong tập dữ liệu sang một giá trị khác. Đôi khi bạn có thể cần phải chuyển đổi các giá trị từ một hệ thống số liệu này sang một hệ thống số liệu khác. Ví dụ:bạn có thể nhập một tệp có giá trị tính bằng ounce (fl oz) chất lỏng và chúng cần được thể hiện bằng mililit. Hàm CONVERT của Excel có thể thực hiện loại này và nhiều loại chuyển đổi khác.
Chức năng này cực kỳ hữu ích và có thể xử lý các đơn vị đo lường phổ biến nhất trong các danh mục sau:trọng lượng và khối lượng, khoảng cách, thời gian, áp suất, lực, năng lượng, công suất, từ tính, nhiệt độ, thể tích, chất lỏng, diện tích, bit và byte và tốc độ .
Để có phần trình diễn, hãy xem tập dữ liệu và áp dụng điều này cho tập dữ liệu đó. Đây là tập dữ liệu.
Làm theo các bước sau để thay đổi nhiệt độ thành độ F.
Các bước:
- Đầu tiên, chọn ô đầu ra D5
- Sau đó, nhập công thức sau
=CONVERT(C5,"C","F")
- Sau đó, nhấn Enter .
- Bây giờ, hãy nhấp và kéo biểu tượng chốt điền vào cuối cột để chuyển đổi tất cả các giá trị.
Đây là cách chúng ta có thể chuyển đổi các giá trị thành các đơn vị khác. Đối với các chuyển đổi đơn vị khác, hãy xem bài viết này .
8. Đánh dấu lỗi
Do thiếu công thức hoặc chuỗi hoặc giá trị thích hợp, bảng tính sẽ hiển thị lỗi. Nếu bạn bắt đầu tìm kiếm từng cái một thì sẽ mất rất nhiều thời gian. Dưới đây, tôi đã giải thích một kỹ thuật dễ dàng để tìm lỗi và đánh dấu chúng để bạn có thể tìm thấy chúng dễ dàng.
Giả sử chúng ta có một tập dữ liệu với một số lỗi trong một số ô. Bây giờ, chúng tôi sẽ đánh dấu chúng bằng định dạng có điều kiện và nút Chuyển đến Đặc biệt tính năng của excel.
Hãy xem bảng tính sau.
Làm theo các bước sau để đánh dấu các lỗi tại đây.
Các bước:
- Trên hết, hãy chọn toàn bộ tập dữ liệu và nhấp vào Quy tắc mới từ Định dạng có điều kiện tùy chọn.
- Sau đó, trong loại quy tắc, hãy chọn Chỉ định dạng các ô chứa rồi chọn Lỗi từ danh sách thả xuống bên dưới.
- Sau đó nhấp vào OK .
Đây là kết quả cuối cùng của tập dữ liệu. Như bạn có thể thấy, Excel đã đánh dấu các giá trị lỗi.
Đây là cách bạn có thể đánh dấu các lỗi như một trong những kỹ thuật làm sạch dữ liệu trong Excel.
9. Nối các cột
Nối các cột là một trong những kỹ thuật khác mà chúng tôi có thể cần một chút thời gian trong khi dọn dẹp hoặc sắp xếp lại dữ liệu. Đối với phần trình diễn này, hãy xem tập dữ liệu sau.
Làm theo các bước sau để xem cách chúng tôi có thể nối các văn bản này từ các ô riêng lẻ trong Excel.
Các bước:
- Đầu tiên, chọn ô E5 .
- Sau đó, hãy viết ra công thức sau.
=B5&" "&D5&" "&C5
- Sau đó, nhấn Enter .
- Bây giờ, hãy nhấp và kéo biểu tượng chốt điền vào cuối cột để sao chép công thức cho các ô còn lại.
Đây là cách chúng tôi có thể nối hai hoặc nhiều cột trong Excel.
10. Sắp xếp lại các cột
Đôi khi bạn có thể cần phải sắp xếp lại các cột trong một trang tính. Nếu bạn suy nghĩ trong một vài phút, bạn có thể giải quyết vấn đề này theo cách này:bạn có thể tạo một cột trống và sau đó kéo một cột khác vào cột trống mới. Cột di chuyển sẽ để lại một khoảng trống và bạn phải xóa cột đó sau khi hoàn thành công việc của mình.
Đây là một cách dễ dàng hơn bạn có thể sử dụng để sắp xếp lại cột một cách dễ dàng mà chúng tôi đang thực hiện trên tập dữ liệu sau.
Các bước:
- Trước tiên, hãy chọn cột bạn muốn di chuyển bằng cách nhấp vào tiêu đề cột.
- Sau đó, cắt cột bằng cách sử dụng trình đơn ngữ cảnh hoặc nhấn Ctrl + X . Tại thời điểm này, bạn sẽ thấy đường chấm chấm ở đường viền của vùng chọn.
- Sau đó, nhấp chuột phải vào tiêu đề cột mà bạn muốn đặt cột trước đó.
- Bây giờ chọn Chèn các ô đã cắt từ menu ngữ cảnh.
- Thao tác này sẽ chèn cột trước đó vào đây.
Đây là cách chúng tôi có thể sắp xếp lại các cột như một phần của kỹ thuật làm sạch dữ liệu trong Excel.
11. Xếp hàng ngẫu nhiên
Trong phần này, chúng ta sẽ thảo luận về việc sắp xếp các hàng một cách ngẫu nhiên. Mặc dù không đặc biệt hữu ích trong hầu hết các trường hợp, nhưng đây vẫn là một thủ thuật hữu ích nếu bạn muốn sắp xếp lại tập dữ liệu. Hoặc nếu bạn muốn thay đổi thứ tự của danh sách thông qua Excel.
Chúng tôi sẽ thực hiện nhiệm vụ như vậy cho tập dữ liệu sau.
Làm theo các bước sau để sắp xếp các hàng một cách ngẫu nhiên và thay đổi thứ tự trong Excel.
Các bước:
- Trước hết, chúng ta phải tạo một cột mới sau cột B . Đối với điều này, hãy nhấp vào chữ cái tiêu đề của cột C và toàn bộ C cột sẽ được chọn.
- Sau đó, nhấp chuột phải và chọn lệnh Chèn và một cột mới sẽ được tạo.
- Bây giờ, chỉ định tên cho cột mới tạo (tức là Số Ngẫu nhiên). Chọn cột ô đầu tiên (tức là C5) và nhập công thức sau vào ô.
=RAND()
- Sau đó, nhấn ENTER và một số ngẫu nhiên sẽ được hiển thị trong ô C5 . Số lượng nhỏ hơn 1.
- Tiếp theo, chọn lại ô, nhấp và kéo biểu tượng chốt điền vào cuối cột để sao chép công thức cho các ô còn lại.
- Trong khi ô C5 được chọn, đi tới Trang chủ tab.
- Sau đó, chuyển đến Chỉnh sửa nhóm và nhấp vào Sắp xếp &Bộ lọc .
- Bây giờ chọn Sắp xếp Nhỏ nhất đến Lớn nhất từ menu thả xuống.
- Ngay sau khi bạn nhấp vào lệnh, các hàng của bạn sẽ tự ngẫu nhiên hóa. Điều đáng chú ý là không chỉ cột C , nhưng tất cả các cột cũng đã được thay đổi ngẫu nhiên dọc theo các hàng.
Vì vậy, đây là các bước bạn có thể làm theo khi bạn có một tập dữ liệu có nhiều cột và bạn muốn ngẫu nhiên hóa các hàng của mình theo cách mà tất cả các cột dọc theo một hàng sẽ ngẫu nhiên hóa các giá trị của chúng.
12. Trích xuất tên tệp từ URL
Đôi khi bạn có thể có một danh sách các URL và bạn chỉ cần trích xuất tên tệp. Bằng cách sau, bạn có thể trích xuất tên tệp từ một URL. Giả sử ô B5 chứa URL này: https://example.com/assets/images/horse.jpg .
Chúng ta sẽ cần một công thức bao gồm rất nhiều hàm ở đây. Đừng lo lắng, sẽ có phần giải thích được thêm vào sau để hiểu cách hoạt động của nó. Công thức này chứa RIGHT , LEN , TÌM HIỂU và SUBSTITUTE các chức năng trong đó.
Bây giờ, hãy làm theo các bước sau để trích xuất tên tệp (horse.jpg) từ liên kết.
Các bước:
- Đầu tiên, hãy chọn ô mà bạn muốn đặt tên tệp. Trong trường hợp này, đó là ô C5 .
- Sau đó, hãy viết công thức sau vào đó.
=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/","")))))
- After that, press Enter .
🔎 Breakdown of the Formula
👉 SUBSTITUTE(B5,”/”,””) removes all the “/” from the URL. It returns http:www.example.comassetsimageshorse.jpg.
👉 LEN(SUBSTITUTE(B5,”/”,””)) returns the length of the previous string.
👉 LEN(B5) returns the length of the string in cell B4 which is 46 here.
👉 LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””))) indicates the difference between the two length values. This in turn indicates the number of “/” were removed.
👉 SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””))) substitutes all the slashes (/) is cell B5 with star sign (*) with the instance number from the result of the previous function.
👉 FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””)))) finds the position “*” is in the string up until now.
👉 LEN(B5)-FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””)))) is the difference between the original string length and the previous position.
👉 Finally, RIGHT(B5,LEN(B5)-FIND(“*”,SUBSTITUTE(B5,”/”,”*”,LEN(B5)-LEN(SUBSTITUTE(B5,”/”,””))))) extracts that number of characters from the right side of the cell.
13. Match Text in List
Let’s take a look at the following figure. We are going to find out the persons who have resigned from the left side of our example. There is a list on the right side of the resigned numbers.
The above one shows a simple example. The data is in the range B5:D133 . The goal is to identify the rows in the data zone which are appearing in the Resigned Members list, in column G . You can delete these unnecessary rows later. We are going to need a combination of the IF and COUNTIF functions for the purpose.
Follow these steps to see how we can achieve the resigned status in our dataset.
Các bước:
- First, select cell E5 .
- Then write down the following formula.
=IF(COUNTIF($G$5:$G$25,C5),"Resigned","")
- After that, press Enter .
- Now double-click the fill handle icon on the bottom-right corner of the cell border. You can also click and drag. But as the dataset is large, we are opting for the previous method.
- Once you have done that, the spreadsheet will look like this.
This is how we can match text as part of the data analysis techniques in Excel.
14. Change Vertical Data to Horizontal
Transposing or changing vertical to horizontal and vice versa in a dataset is another common one in data analysis. So we will be discussing changing vertical data to horizontal now in the series of data cleaning techniques in Excel.
For that, we will be using the following dataset.
Using Paste Special Option
The easiest way to change a vertical column to a horizontal row is to use the Paste Special option of Excel. It also keeps the exact formatting while changing the vertical column. So, you don’t need to apply any formatting later. Let’s follow the steps below to see how we can use the Paste Special option to transpose the columns.
- First of all, select the range that you want to change to horizontal rows. Here, we have selected the range B4:C10 .
- Secondly, press Ctrl+C to copy the range.
- Thirdly, select a cell where you want to paste the range horizontally. In our case, we have selected Cell B12 .
This will change vertical columns into horizontal rows in the Excel spreadsheet.
Using TRANSPOSE Function:
We can use some Excel Functions to convert a vertical column to a horizontal row. Here, we will use the TRANSPOSE function for that purpose. The main advantage of using functions is that you will get dynamic updates in the horizontal rows if you change anything in the main dataset. But, the horizontal rows will not have the same formatting as the vertical columns. You need to add formatting after applying the method.
Let’s follow the steps below to learn more.
Các bước:
- First, select the cell where you want to place the formula.
- Then write down the following formula in it.
=TRANSPOSE(B4:C10)
- Now press Enter .
This is how we can change vertical data to horizontal using the TRANSPOSE function.
15. Fill Blank Cells
One of the most important data cleaning techniques in any form of data analysis is filling blank values, either with average in numeric cases or with values with the previous or later rows.
Sometimes we keep some blank cells to understand that they will be the first filled ones, have a look at our dataset to understand it. But sometimes it creates problems like while we are filtering or sorting data. So it’s better to fill them and there are easy ways to o it.
This is our dataset.
We can fill them up using these two simple methods.
Using Fill Command
If your data is small, you can enter the missing cell values from above manually or by using this command. Follow these steps to see how you can do that.
- Select Cell B6 .
- Then go to the Home
- From the Editing group, click on Fill and then Down .
- The dataset will look like this now.
- Now repeat the process for all the blank cells and fill out all the cells.
This is one way to fill up blank cells in a dataset.
Using Keyboard Shortcut:
We can achieve the same result using a keyboard shortcut. The shortcut is Ctrl+D . You just have to select the blank cell and press the shortcut. It will fill the cell with the value from the previous cell in the same column. Follow these steps for a more detailed guide.
- First, select cell B6 .
- Then press Ctrl+D on your keyboard.
- Now selecting cell B9 and pressing Ctrl+D will yield the following result.
- Now fill out all the blank values by selecting them and pressing the shortcut.
16. Check Spelling
When you use Microsoft Word Document or another word processor, you use its spell checker feature most of the time. Spelling mistakes in a text document are embarrassing. In Excel, spelling mistakes can also occur serious problems. Say, for example, if you tabulate data by month, a misspelled month name will make that a year has 13 months.
Here we have made a dataset for the demonstration.
We are going to correct the range C5:C7 . Follow these steps to see how we can do that.
Các bước:
- First, select the range C5:C7 .
- Then go to the Review tab and select Spelling from the Proofing You can also skip this step and press F7 on your keyboard.
- Now select the correct suggestion and click on Change to change the value of a particular cell. The errors come in the selection order here.
- This will change the first error in the dataset.
- Now repeat this process for all the errors and you will have correct spellings this way.
17. Replace or Remove Text
Removing and replacing data, especially outliers is another important task in any data cleaning process. So as a part of our data cleaning techniques compilation, we are going to demonstrate replacing and removing text in Excel. We can do that using three methods.
For all of the methods, we are using the following dataset.
Using Find &Replace Feature:
Excel has a direct feature that can help find, replace and remove certain values from another. Follow these steps to see how we can use this feature to remove or replace in Excel.
- First, select the range which you want to modify. In this case, it is C5:C13 .
- Then go to the Home
- After that, select Find &Replace from the Editing group section.
- Next, select Replace from the drop-down.
- Now, go to the Replace tab in the pop-up box.
- Then insert the value you want to replace in the Find what field and the value you want to replace it with in the Replace with
- Finally, click Replace All to replace all of them in the dataset.
This will be the final product after removing all of the hyphens from the dataset.
Utilizing Flash Fill:
Another feature we can use to replace or remove values while using data cleaning techniques is to utilize the flash fill feature. For the same dataset, follow these steps to see how that works.
- First, write down the final product after removing or replacing the desired value in the first cell. We are replacing the second hyphen in the string with a colon.
- Now start writing the second one manually. As soon as you start writing Excel will recognize the pattern and start suggesting output for the rest.
Note
If the suggestion doesn’t occur in the second entry, try it out manually. It sometimes suggests the output at the third or fourth entry if the pattern isn’t that obvious to Excel.
- Now press Enter .
Thus, you can replace or remove values using this handy feature of Excel while data cleaning.
Using SUBSTITUTE Function:
Another way we can replace a value, or remove them in Excel is to use the SUBSTITUTE function . This function takes three values- a string, the value to replace, and the value to replace it with. Follow these steps to see how we can use this function.
- First, select cell C5 .
- Then write down the following formula in it.
=SUBSTITUTE(B5,"-",":",2)
- After that, press Enter .
- Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.
18. Add Text to Cells
Adding text is not only common as one of the data cleaning techniques but also in general Excel operations. We can add text to cells in various ways. For demonstrations of those, we will use the following dataset.
Now you can use one of the following methods to add text to cells.
Using Ampersand (&) Symbol:
The ampersand (&) symbol in a formula works as the string connector. It connects the previous and the following string it is in between. Follow these steps to see how we can easily add text to cells using this.
- First of all, select cell C5 .
- Secondly, put down the formula below.
=B5&".mp3"
- Next, press Enter .
- Finally, click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.
Applying Flash Fill Feature:
Follow these steps to add text using the flash fill feature easily in Excel.
- First, write down the first intended value manually in cell C5 .
- Then start filling out the second one in the dataset. Once you start filling, Excel will suggest the pattern.
- As soon as the suggestion appears, press Enter .
This is another way to add text in Excel.
Using CONCATENATE Function:
The CONCATENATE function in Excel, as the name suggests, concatenates two or more strings together. It takes those strings as its arguments. It works the same way as the ampersand sign method described earlier. Follow these steps to see how we can use this function.
- First, select cell C5 .
- Then write down the following formula in it.
=CONCATENATE(B5,".mp3")
- After that, press Enter .
- Finally, select the cell again and click and drag the fill handle icon to the end of the column to replicate the formula for the rest of the cells.
19. Fix Trailing Minus Sign
Sometimes while taking data from other sources, the negative values do have a trailing minus sign. Take a look at the following figure.
Excel doesn’t consider the values with trailing negative signs as numbers. Instead, it treats them as texts. This may cause problems, not only in the data cleaning techniques but also in general operations like using formulas in Excel. So we need to address the problem and convert them into negative numbers.
Follow these simple steps to do that.
Các bước:
- First, select the range you want to convert. We have copied it to compare it with the previous one.
- Then go to the Data tab on your ribbon.
- Then select Text to Columns from the Data Tools group section.
- Finally, directly click on Finish in the wizard.
This will automatically change all the negative values with trailing minus signs to Excel’s negative sign format.
This procedure works because there is a default setting in the Advanced Text Import Settings hộp thoại.
Kết luận
So these were some of the most useful data cleaning techniques in Excel used for data analysis. Hopefully, you can use these techniques while using data cleaning in Excel with ease. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
For more guides like this, visit Exceldemy.com .