Excel의 19가지 실용적인 데이터 정리 기술

데이터 입력 및 구성을 통해 Microsoft Excel은 오늘날 초급에서 중급 수준의 데이터 분석도 쉽게 수행할 수 있습니다. 일상적인 사용을 위해 먼 길을 가는 기능적인 도구를 제공할 수 있습니다. 데이터 정리는 모든 데이터 분석 방법의 기본 단계입니다. 여기에는 원하지 않거나 불규칙한 값을 제거, 준비 또는 사용 가능한 값으로 변환하는 작업이 포함됩니다. 이 자습서에서는 다양한 데이터 정리 기술과 Microsoft Excel에서 이를 수행하는 방법에 대해 설명합니다.

아래 링크에서 데모에 사용된 워크북을 다운로드할 수 있습니다.

편리한 Excel의 19가지 데이터 정리 기술

이 기사에서는 Excel의 총 19가지 데이터 정리 기술에 대해 논의할 것입니다. 그들 모두는 다소 다른 경우에 중요합니다. 각각의 작동 방식을 확인하거나 위의 목차에서 필요한 것을 찾으십시오.

1. 중복 행 제거

이유가 무엇이든 데이터에 중복 행이 있을 수 있습니다. 대부분의 경우 중복 행을 제거해야 합니다. 예전에는 중복 데이터를 제거하는 것이 수동 작업이었지만 제거 작업은 고급 기술로 수행할 수 있었습니다. 그러나 중복 항목 제거를 사용하면 명령, 중복 제거는 이제 쉬운 작업입니다. 중복 제거 명령은 Excel 2007에서 도입되었습니다.

옆에 있는 열을 복제한 다음 나중에 비교하기 위해 중복을 제거해 보겠습니다.

중복을 제거하려면 다음 단계를 따르십시오.

단계:

• 먼저 중복을 제거할 열을 선택합니다.
• 이제 데이터 로 이동합니다. 리본의 탭입니다.
• 그런 다음 중복 항목 제거 를 선택하십시오. 데이터 도구 에서 그룹으로 이동합니다.

• 그런 다음 현재 선택 항목으로 계속 을 선택합니다. 팝업 상자에서 옵션을 선택하고 중복 항목 제거를 클릭합니다. .

• 다음으로 확인을 클릭합니다. .

이렇게 하면 선택 항목에서 모든 중복 항목이 제거됩니다.

2. 중복 값 강조 표시

때로는 중복 값을 완전히 제거하는 대신 강조 표시하는 것이 도움이 됩니다. 여기에서는 중복 값을 강조 표시하는 다양한 시나리오와 방법을 보여줍니다.

조건부 서식 활용

첫 번째 부분에서는 조건부 서식 중복 값을 강조 표시하는 기능입니다. 다음 데이터세트에서 이를 수행합니다.

다음 단계에 따라 이를 수행하는 방법을 확인하십시오.

단계:

• 먼저 복제할 셀을 선택합니다. 이 경우 D5:D18입니다.
• 둘째, 홈으로 이동합니다.> 조건부 서식 선택> 셀 강조 표시 규칙으로 이동> 중복 값 선택 .

• 결국 중복된 값 창이 나타납니다.
• 셋째, 복제를 선택합니다. .
• 넷째, 값에서 색상 옵션을 선택합니다. 이 경우 진한 빨간색 텍스트로 밝은 빨간색 채우기를 선택했습니다. .

• 결과적으로 D 열이 강조 표시됩니다.

첫 번째 항목을 포함하여 중복 강조 표시

이제 동일한 데이터 세트에 대해 첫 번째 항목을 포함하여 중복 값을 강조 표시합니다. 이것은 앞서 언급한 것과 동일합니다. 하지만 여기서는 COUNITF 기능과 함께 다른 경로를 따릅니다. . 이 방법이 어떻게 작동하는지 보려면 다음 단계를 따르세요.

단계:

• 먼저, 새 서식 규칙으로 이동합니다. 조건부 서식으로 이동하여 창

• 둘째, 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다. .
• 셋째, 수식 상자에 다음 수식을 작성합니다.

=COUNTIF($D$5:$E$16,$D5)>1

• 결과적으로 D 열은 첫 번째 항목을 포함하여 중복 항목에 대해 강조표시됩니다.

첫 번째 항목을 제외한 중복 강조 표시

이제 우리는 중복 값을 식별하는 가장 유용한 기술 중 하나에 대해 논의할 것입니다. 첫 번째 항목을 제외하고 강조 표시되지 않습니다. 이는 모든 중복 값 대신 반복되는 값만 관찰하려는 경우에 유용합니다. 이전과 마찬가지로 COUNTIF 함수를 활용합니다. 여기.

다음 단계에 따라 이를 수행하는 방법을 확인하십시오.

단계:

• 이렇게 하려면 먼저 새 서식 규칙으로 이동합니다. 조건부 서식을 통해 이전에 논의한 옵션입니다.

• 둘째, 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다. 이전과 같은 방식으로.
• 셋째, 수식 상자에 다음 수식을 작성합니다.

=COUNTIF($D$5:$D5,$D5)>1

• 확인을 클릭한 후 , 첫 번째 항목을 제외한 모든 중복 항목을 강조 표시합니다.

3. 텍스트를 여러 셀로 분할

다른 소스에서 데이터를 가져올 때 여러 값을 단일 열로 가져올 수 있습니다. 다음 이미지는 이러한 유형의 수입 사고의 예를 보여줍니다.

유용한 텍스트를 열로 이 텍스트를 분할하는 데 도움이 되는 기능을 사용할 수 있습니다.

단계:

• 먼저 텍스트를 분할할 범위를 선택합니다.
• 그런 다음 데이터 로 이동합니다. 리본의 탭입니다.
• 그 후 데이터 도구 그룹에서 텍스트를 열로를 선택합니다. .

• 다음으로 구분된 을 선택합니다. 다음 상자에 있습니다.

• 다음을 클릭한 후 , 다른 상자가 나타납니다.
• 스페이스바 선택 여기 구분자 아래 텍스트는 공백으로 구분되므로 옵션을 선택하세요.
• 그런 다음 다음을 클릭합니다. .

• 그런 다음 마침을 클릭합니다. .

• 마지막으로 텍스트가 분할됩니다.

• 이제 좀 더 보기 쉽게 수정해 보겠습니다.

이것이 Excel의 다른 데이터 정리 기술과 함께 텍스트를 다른 셀로 분할하는 방법입니다.

4. 수식으로 데이터 변환

때로는 텍스트의 대소문자가 동일하기를 원할 수 있습니다. Excel은 텍스트의 대소문자를 변경하는 직접적인 방법을 제공하지 않지만 일부 수식을 사용하여 쉽게 변경할 수 있습니다. 텍스트를 변경하는 세 가지 공식은 다음과 같습니다.

상단 :이 수식은 텍스트를 모두 대문자로 변환합니다.

낮음 :이 수식은 텍스트를 모두 소문자로 변환합니다.

적당 :이것은 텍스트를 적절한 대소문자로 변환합니다(각 단어의 첫 글자는 고유명에서와 같이 대문자로 표시됩니다).

모든 프레젠테이션에 다음 데이터세트를 사용합니다.

대문자로 변경

앞서 언급했듯이 UPPER 함수를 사용합니다. 이 목적을 위해. 다음 단계에 따라 이를 수행하는 방법을 확인하십시오.

단계:

• 먼저 C5 셀을 선택합니다. .
• 그런 다음 그 안에 다음 공식을 적으세요.

=UPPER(B5)

• 그런 다음 Enter 키를 누릅니다. .

• 이제 셀을 다시 선택하고 채우기 핸들 아이콘을 클릭하고 끌어 나머지 셀을 공식으로 채웁니다.

소문자로 변경

다음으로 모든 텍스트를 소문자로 변환하는 방법을 보여드리겠습니다.

이를 위해 다음 단계를 따릅니다.

단계:

• 먼저 D5 셀을 선택합니다. .
• 그런 다음 그 안에 다음 공식을 적으세요.

=LOWER(B5)

• 그런 다음 Enter 키를 누릅니다. .

• 이제 셀을 다시 선택하고 채우기 핸들 아이콘을 클릭하고 끌어 나머지 셀을 공식으로 채웁니다.

적절한 대소문자 변경

대소문자가 맞는 곳은 대문자, 다른 곳은 소문자를 가리킵니다. 이 데이터 세트의 경우 이를 위해 다음 방법을 사용합니다.

단계:

• 먼저 E5 셀을 선택합니다. .
• 그런 다음 그 안에 다음 공식을 적으세요.

=PROPER(B5)

• 그런 다음 Enter 키를 누릅니다. .

• 마지막으로 셀을 다시 선택하고 채우기 핸들 아이콘을 클릭하고 끌어 나머지 셀을 채웁니다.

5. 여분의 공백 제거

텍스트에 추가 공백이 없도록 하는 것은 항상 매우 중요한 문제입니다. 문자열 끝에서 공백 문자를 찾을 수 없습니다. 여분의 공백은 특히 텍스트 문자열을 비교해야 할 때 많은 문제를 야기합니다. 예를 들어 "7월"은 "7월"과 같지 않습니다. 첫 번째는 4자이고 두 번째는 5자입니다.

Excel에서 이 두 가지 데이터 정리 기술에 따라 추가 공백을 제거할 수 있습니다.

다음 데이터세트에서 둘 다 사용할 것입니다.

TRIM 기능 사용

TRIM 기능을 사용합니다. 텍스트에서 모든 선행 및 후행 공백을 제거합니다. 트림 함수는 또한 여러 공백을 하나의 공백으로 바꿉니다.

단계:

• 먼저 업데이트된 데이터라는 열을 추가합니다. 결과를 보여줍니다.
• 그런 다음 셀 D5를 클릭합니다. .
• =TRIM을 입력하고 C5 셀을 선택합니다. 첫 번째 인수에서. 괄호를 닫습니다.
• 따라서 공식은 다음과 같습니다.

=TRIM(C5)

• 이제 Enter 키를 누릅니다. .

• 이제 Fill Handel 아이콘을 마지막 셀로 드래그합니다.

불필요한 공간이 제거된 것을 볼 수 있습니다. 각 단어 뒤에 공백이 하나만 있습니다.

찾기 및 바꾸기 기능 사용:

이 섹션에서는 찾기 및 바꾸기 명령을 사용하여 모든 공백을 제거하는 방법에 대해 설명합니다.

단계:

• 먼저, 여분의 공백을 제거할 데이터를 선택합니다.
• 그런 다음 홈 으로 이동합니다. 탭.
• 편집 에서 명령을 사용하여 찾기 및 선택 으로 이동합니다. 기능.
• 바꾸기 선택 드롭다운 목록에서.

• 바꾸기를 선택하면 , 대화 상자가 나타납니다.
• 찾을 내용에 공백을 입력합니다. 필드.
• 유지 다음으로 대체 상자가 비어 있습니다.
• 그런 다음 모두 바꾸기를 클릭합니다. .

• 모두 바꾸기를 클릭한 후 , 교체 횟수를 보여주는 팝업이 표시됩니다.
• 이제 확인 을 클릭하십시오. 팝업에서.
• 그런 다음 닫기 를 클릭합니다. 대화 상자.
• 드디어 결과를 얻었습니다.

6. 이상한 문자 제거

데이터를 Excel 워크시트로 가져온 후 이상한(때로는 인쇄할 수 없는) 문자가 있음을 알게 되는 경우가 많습니다. CLEAN 기능을 사용할 수 있습니다. 텍스트에서 인쇄되지 않는 모든 문자를 제거합니다. 데이터가 A2 셀에 있는 경우 작업을 수행하는 데 사용할 수 있습니다.

=CLEAN(A2)

깨끗한 함수는 때때로 인쇄되지 않는 일부 유니코드 문자를 누락할 수 있습니다. 7비트 ASCII 코드에서 처음 32개의 인쇄되지 않는 문자를 제거하도록 프로그래밍되었습니다. 인쇄되지 않는 유니코드 문자를 제거하는 방법에 대한 정보는 Excel 도움말 시스템을 참조하십시오.

7. 값 변환

데이터 정리 기술에서 또 다른 중요한 것은 데이터 세트의 한 값을 다른 값으로 변환하는 것입니다. 때로는 한 미터법에서 다른 미터법으로 값을 변환해야 할 수도 있습니다. 예를 들어, 유체 온스(fl oz) 값이 있는 파일을 가져올 수 있으며 이 값은 밀리리터로 표시해야 합니다. Excel의 CONVERT 기능 이 유형 및 기타 여러 유형의 전환을 수행할 수 있습니다.

이 기능은 매우 유용하며 다음 범주에서 가장 일반적인 측정 단위를 처리할 수 있습니다. 무게 및 질량, 거리, 시간, 압력, 힘, 에너지, 전력, 자기, 온도, 부피, 액체, 면적, 비트 및 바이트, 속도 .

데모를 위해 데이터 세트를 보고 이를 적용해 보겠습니다. 데이터세트입니다.

다음 단계에 따라 온도를 화씨로 변경하십시오.

단계:

• 먼저 출력 셀 D5를 선택합니다.
• 다음 수식을 입력하세요.

=CONVERT(C5,"C","F")

• 그런 다음 Enter 키를 누릅니다. .

• 이제 채우기 핸들 아이콘을 클릭하고 열 끝으로 끌어 모든 값을 변환합니다.

이것이 값을 다른 단위로 변환하는 방법입니다. 다른 단위 변환에 대해서는 이 도움말을 확인하세요. .

8. 하이라이트 오류

적절한 수식이나 문자열 또는 값이 없기 때문에 스프레드시트에 오류가 표시됩니다. 일일이 검색을 시작하면 시간이 많이 걸립니다. 아래에서 오류를 찾고 쉽게 찾을 수 있도록 강조 표시하는 쉬운 기술을 설명했습니다.

일부 셀에 오류가 있는 데이터 세트가 있다고 가정합니다. 이제 조건부 서식으로 강조표시하고 특수로 이동 엑셀의 기능입니다.

다음 스프레드시트를 살펴보겠습니다.

여기에서 오류를 강조 표시하려면 다음 단계를 따르세요.

단계:

• 무엇보다도 전체 데이터 세트를 선택하고 새 규칙 을 클릭하십시오. 조건부 서식 에서 옵션.

• 그런 다음 규칙 유형에서 다음을 포함하는 셀만 서식을 선택합니다. 오류를 선택합니다. 아래 드롭다운 목록에서.

• 그런 다음 확인을 클릭합니다. .

데이터세트의 최종 결과입니다. 보시다시피 Excel은 오류 값을 강조 표시했습니다.

Excel에서 데이터 정리 기술 중 하나로 오류를 강조 표시하는 방법입니다.

9. 열 가입

열 결합은 데이터를 정리하거나 재정렬하는 동안 시간이 필요할 수 있는 또 다른 기술 중 하나입니다. 이 데모에서는 다음 데이터세트를 사용하겠습니다.

Excel의 개별 셀에서 이러한 텍스트를 결합하는 방법을 보려면 다음 단계를 따르세요.

단계:

• 먼저 E5 셀을 선택합니다. .
• 다음 공식을 적으세요.

=B5&" "&D5&" "&C5

• 그런 다음 Enter 키를 누릅니다. .

• 이제 채우기 핸들 아이콘을 클릭하고 열 끝으로 끌어 나머지 셀에 대한 수식을 복제합니다.

이것이 Excel에서 두 개 이상의 열을 결합하는 방법입니다.

10. 열 재정렬

경우에 따라 워크시트의 열을 다시 정렬해야 할 수 있습니다. 몇 분 동안 생각하면 이 문제를 다음과 같이 해결할 수 있습니다. 빈 열을 만든 다음 다른 열을 새 빈 열로 끌어다 놓을 수 있습니다. 이동하는 열은 공백을 남기므로 작업이 완료된 후 해당 열을 삭제해야 합니다.

다음 데이터세트에서 쉽게 열을 재배열하는 데 사용할 수 있는 더 쉬운 방법이 있습니다.

단계:

• 먼저 열 머리글을 클릭하여 이동할 열을 선택합니다.
• 그런 다음 컨텍스트 메뉴를 사용하거나 Ctrl+X를 눌러 열을 잘라냅니다. . 이 지점에서 선택 영역의 경계에 점선이 표시됩니다.

• 그런 다음 이전 열을 배치할 열 머리글을 마우스 오른쪽 버튼으로 클릭합니다.
• 이제 절단 세포 삽입 을 선택하십시오. 컨텍스트 메뉴에서.

• 여기에 이전 열이 삽입됩니다.

이것이 Excel에서 데이터 정리 기술의 일부로 열을 재정렬하는 방법입니다.

11. 행 무작위화

이 섹션에서는 행을 무작위화하는 방법에 대해 설명합니다. 대부분의 경우 특히 유용하지는 않지만 데이터 세트를 재정렬하려는 경우 여전히 유용한 트릭입니다. 또는 Excel을 통해 목록의 순서를 변경하려는 경우.

다음 데이터세트에 대해 이러한 작업을 수행할 것입니다.

Excel에서 행을 무작위로 지정하고 순서를 변경하려면 다음 단계를 따르세요.

단계:

• 우선 B 열 다음에 새 열을 만들어야 합니다. . 이를 위해 C 열의 헤더 문자를 클릭합니다. 및 전체 C 열이 선택됩니다.
• 그런 다음 마우스 오른쪽 버튼을 클릭하고 삽입 명령을 선택하면 새 열이 생성됩니다.

• 이제 새로 생성된 열에 이름을 지정합니다(즉, 난수). 첫 번째 셀(예:C5) 열을 선택하고 셀에 다음 수식을 입력합니다.

=RAND()

• 그런 다음 Enter 키를 누릅니다. 및 임의의 숫자가 C5 셀에 표시됩니다. . 숫자가 1보다 작습니다.

• 다음으로 셀을 다시 선택하고 채우기 핸들 아이콘을 클릭하고 열의 끝까지 끌어 나머지 셀에 대한 수식을 복제합니다.

• C5 셀 동안 홈으로 이동합니다. 탭.
• 그런 다음 수정 으로 이동합니다. 그룹화하고 정렬 및 필터를 클릭합니다. .
• 이제 가장 작은 것부터 큰 것을 선택합니다. 드롭다운 메뉴에서.

• 명령을 클릭하는 즉시 행이 무작위로 생성됩니다. C 열뿐만 아니라 , 그러나 모든 열도 행을 따라 무작위로 변경되었습니다.

따라서 다음은 여러 열이 포함된 데이터세트가 있고 행을 따라 있는 모든 열이 값을 임의화하는 방식으로 행을 임의화하려는 경우 수행할 수 있는 단계입니다.

12. URL에서 파일 이름 추출

때로는 URL 목록이 있을 수 있으며 파일 이름만 추출해야 합니다. 다음과 같은 방법으로 URL에서 파일 이름을 추출할 수 있습니다. B5 셀을 가정합니다. 다음 URL 포함:https://example.com/assets/images/horse.jpg .

여기에는 수많은 함수로 구성된 공식이 필요합니다. 걱정하지 마십시오. 작동 방식을 이해하기 위해 나중에 추가된 설명이 있습니다. 이 수식에는 RIGHT이(가) 포함됩니다. , LEN , FIND and SUBSTITUTE functions in it.

Now follow these steps to extract the file name (horse.jpg) from the link.

Steps:

• First, select the cell where you want to put the file name. In this case, it is cell C5 .
• Then write down the following formula in it.

=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.

Steps:

• 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.

Steps:

• 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.

Steps:

• 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.

Steps:

• 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 대화 상자.

결론

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 .