엑셀 중복값 찾기 (1) - 조건부 서식이용.

엑셀 중복 값 찾기 (1) - 조건부 서식 이용.

엑셀 중복 값 찾기를 한 번 해 보도록 하겠습니다. 저도 엑셀을 자주 이용하는 편인데, 엑셀은 오랜 시간 동안 많은 사람들에게 사랑받고 있는 프로그램이지요. 이런 엑셀을 이용해 중복 값 찾기는 엑셀 업무에서 필수적인 느낌이기도 합니다. 업무를 보기 위해서는 필수적으로 엑셀 활용능력이 보장되거나, 업무를 복는 와중에서 자연스럽게 습득하게 되는 프로그램이 엑셀이 아닐까 합니다. 무엇보다 데이터를 정리 확인하기 위해서는 엑셀 중복값 찾는 방법은 꼭 필요하게 되는데요. 이런 엑셀 중복값 찾는 방법은 여러 방법이 존재한다고 할 수 있습니다.

엑셀 중복값 찾기, 업무 효율 높이기. 사진출처 pixabay

엑셀 프로그램에서 기본적으로 제공하고 있는 기능으로 함수, 조건부 서식을 활용하여 엑셀 내 데이터의 중복 값을 쉽게 찾을 수도 있지만, 보다 원하는 방법으로 활용하기 위해서는 엑셀 매크로, VBA를 활용하는 방법도 있습니다. 단순하게 데이터의 중복 값을 찾고 싶다면 함수 혹은 조건부서식을 활용하는 것이 엑셀 중복값 찾는 방법으로는 손쉬운 방법이라 할 수 있습니다. 함수, 조건부 서식을 이용한 엑셀 중복값 찾기 방법을 먼저 살펴보도록 하겠습니다.

---

1. 조건부서식을 활용한 엑셀 중복값 찾기.

가장 먼저 엑셀 중복값을 찾기 위해 많이 사용되는 방법인 조건부서식을 활용한 엑셀 중복값 찾기를 살펴볼게요. 생각보다 간단하게 중복값을 표시할 수 있는 기능을 제공하고 있습니다. 우선을 엑셀을 열어서 간단한 샘플을 활용하여 중복값을 찾아보도록 하겠습니다.

엑셀 중복값 찾기 샘플.

이렇게 번호별 과일 이름을 샘플로 활용해보도록 하겠습니다. 데이터가 얼마 없다면 수작업을 통해 서식을 지정해줌으로써 원하는 데이터가 있는 셀을 표시할 수 있으나, 데이터가 많다면 일일이 수작업하기에는 귀찮기도 하고 시간도 오래 걸리는 번거로움이 있습니다. 이 단순한 과일 데이터를 통해 중복 값을 찾아보고 큰 데이터에서의 적용을 생각해보면 좋을 것 같습니다. 우선은 우리가 원하는 기능인 '조건부 서식' 버튼을 찾아볼까요.

엑셀 홈 탭에 잇는 조건부 서식 버튼.

엑셀 홈 탭의 메뉴 중, 조건부 서식 버튼을 확인할 수 있습니다. 조건부 서식을 활용하기 위해서는 '조건부 서식'이 적용될 셀을 먼저 선택해야 합니다. 즉, 원하는 데이터가 한 개 혹은 다수일 경우, 해당하는 셀을 선택한 후 조건부 서식을 설정해주면 되겠습니다. 과일의 이름에 대한 중복 값을 찾을 것이므로 과일 이름 부분을 모두 선택해 주겠습니다.

원하는 셀 선택 후 조건부 서식 선택.

그럼 본격적으로 조건부 서식을 활용하여 엑셀 중복값을 찾기 위해 적용해 보도록 하겠습니다. 조건부 서식 메뉴를 선택하면 아래 사진과 같은 화면을 볼 수 있습니다. 단순하게 조건부 서식은 설정된 서식이 적용되는 셀이 일정 조건 (사용자가 지정한)에 만족하면 서식이 적용되는 기능이라고 생각하면 되겠습니다. 여러 기능을 확인할 수 있는데요. 그중 '새 규칙'을 선택하여 주시면 됩니다.

엑셀 중복값 찾기, 조건부 서식 새규칙 선택.

엑셀 중복값 찾기, 조건부 서식 중복값 설정

엑셀 중복 값을 찾기 위해 조건부 서식을 선택하여 새 규칙을 클릭하면 위와 같은 화면을 만날 수 있습니다. 이 화면에서 빨간 네모 표시처럼 '고유 또는 중복 값만 서식 지정'이라는 메뉴를 선택하세요. 그럼, 아래와 같은 화면으로 전환되는 것을 보실 수 있습니다. 아래 사진에서 고윳값 혹은 중복 값을 설정하는 1번에서 중복을 선택하시고, 해당되는 중복 값의 셀에 들어갈 서식을 지정해주시면 됩니다. (2번). 

엑셀 중복값 찾기, 조건부 서식 설정

서식의 경우 개인의 취향 및 사용 목적에 맞는 형태의 서식을 설정해주면 되는데요. 저는 굵은 글씨, 글씨 색 검정, 배경색 빨강으로 설정해 보겠습니다.

엑셀 조건부 서식 설정, 글씨색, 배경색.

서식 설정이 끝나면 확인 버튼을 통해 설정한 서식을 저장하면 되겠습니다. 설정창에서 지정한 내용이 확인 버튼을 통해 바로 적용되는 것은 아닙니다. 아래 사진과 같이 다시금 조건부 서식 설정 화면으로 넘어가, 최종적인 확인을 할 수 있습니다. 아래 사진의 '미리 보기'에 설정한 서식이 표시되는 방식을 확인하시고, 마음에 드신다면 그 아래 '확인' 버튼을 클릭해 최종적으로 적용해 주시면 됩니다.

조건부 서식 설정 후 적용 전 확인 단계.

엑셀 중복값 찾기, 조건부 서식 활용, 최종 적용

위 사진은 엑셀 데이터의 중복 값을 찾기 위해 사용한 조건부 서식이 최종적으로 적용된 모습입니다. 가져온 과일 이름 샘플을 통해 눈으로 확인한 결과 사과라는 과일이 중복으로 데이터가 있다는 사실을 쉽게 알 수 있었는데요. 전체 데이터에 조건부 서식을 적용한 결과 중복적으로 들어있는 '사과' 데이터에만 서식이 적용된 모습을 확인할 수 있습니다. 

서식의 내용은 제가 임의로 정한 것이어서 보기 좋은 모습은 아니네요. 실제로 활용하실 때는 취향, 목적 등을 고려하여 서식을 지정해주시면 보다 보기 좋은 엑셀 중복 값 데이터가 정리된 모습을 보실 수 있습니다.

엑셀 중복값 찾기, 조건부 서식 활용 전(좌), 후(우).

몇 번의 클릭으로 아주 쉽게 엑셀 데이터중 중복 값 찾기가 가능하다는 것을 알 수 있습니다. 위 사진의 왼쪽은 조건부 서식을 적용하기 전, 오른쪽은 조건부 서식을 적용한 후의 모습을 담아 봤습니다. 대충 이런 식으로 엑셀 중복 값 찾기에 조건부 서식을 활용한 경우, 결괏값이 보인다고 예상하시면서 서식을 적용하시면 조금 더 쾌적한 엑셀 작업이 될 수 있으리라 생각이 되네요.

---

2. 조건부 서식을 활용한 엑셀 중복값 찾기. 특정 문자가 포함된 값의 경우.

엑셀 데이터 중 셀 값 자체가 같은 경우가 아닌, 셀 값 중 특정 값을 포함한 경우 조건부 서식을 통한 표시가 가능한데요. 위의 과일 이름을 예로 들면, '사과'는 '사' + '과'라는 데이터의 합성이라고 할 수 있습니다. 이 경우, '사' 혹은 '과'를 포함한 데이터를 조건부 서식으로 표현할 수 있다는 의미를 나타냅니다. 엑셀에서 특정 중복 값을 찾고자 할 때, 활용할 수 있는 방법 중 하나라고 할 수 있겠네요. 조건부 서식을 이용한 엑셀 중복 값 찾기인 위의 순서와 비슷한데, 조건부 서식에서의 메뉴 중 '다음을 포함하는 셀만 서식 지정'을 선택하여 활용하면 되겠습니다. 같이 한 번 해볼게요.

엑셀 중복값 찾기.

앞서 말한 순서대로 우선 서식이 설정될 특정 셀 혹은 셀 영역을 선택합니다. 다음으로 홈 탭에 있는 조건부 서식 메뉴를 선택한 후, 나오는 하위 메뉴 중 새 규칙을 선택합니다. 앞서 설명한 순서와 동일합니다.

조건부 서식 선택 메뉴 창

조건부 서식 선택 메뉴창 설정 순서.

조건부 서식의 새 규칙을 통해 들어간 창입니다. 이 창에서 '다음을 포함하는 셀만 서식 지정'을 선택하시고, 아래 세부 사항 조절에서 셀 값이라고 되어있는 드롭박스의 오른쪽 화살표 모양을 클릭합니다. 드롭박스에서 나오는 메뉴 중 '특정 텍스트'를 클릭하시면 됩니다.

조건부 서식 세부 설정 메뉴 창.

특정 텍스트를 선택하신 후, 오른쪽에서 세부 설정이 가능합니다. 포함, 포함하지 않음, 시작 문자, 끝 문자로 기능을 선택할 수 있습니다. 엑셀 중복 값 찾기에 해당되는 내용에 적절한 기능을 선택하셔서 활용하시면 됩니다. 기능은 이름 그대로라고 생각하시면 되겠습니다. 저는 '사과'의 '과'를 포함하는 셀 값이 표현되도록 설정해보겠습니다.

조건부 서식 '과' 입력 후 서식 설정 및 확인

포함될 문자는 위 사진 왼쪽 편과 같이 세 번째 영역에 입력하면 되겠습니다. 그 후, 앞서 연습한 것처럼 서식을 설정하여 지정하면 미리보기를 통해 설정된 서식이 어떻게 보일지 화인 할 수 있습니다. 확인 후 이상 혹은 수정사항이 없다면 가장 아래 있는 '확인' 버튼을 클릭하여 적용시키면 되겠습니다.

특정 문자를 포함한 조건부 서식 설정 및 적용 후 결과 화면.

최종적으로 적용을 시키면, 앞서 확인했던 엑셀 중복 값 찾기 조건부 서식 활용에서 조건부 서식 기본 기능인 중복 값에 서식이 적용되었던 것과 똑같은 결과를 얻을 수 있습니다. 상황에 따라 적절한 방법을 활용하시면 되겠습니다.

---

3. 조건부 서식을 활용한 엑셀 중복 값 찾기. 특정 셀 값과 같은 셀에 서식 적용.

이번에는 선택한 데이터들 중에서 특정 값과 같은(중복인) 셀에만 서식을 적용시키는 방법을 알아보겠습니다. 이 방법을 활용하면, 선정된 특정 셀에 찾고자 하는 데이터를 입력하여 엑셀 중복 값을 찾을 수 있습니다. 기준이 되는 셀의 데이터 값을 변동하면서 그때그때 찾고자 하는 중복된 값의 위치를 찾을 수 있는 방법입니다. 샘플은 계속 해돈 과일 이름으로 활용해 보겠습니다.

 

먼저 중복 값을 찾기 위한 기준이 되는 셀을 정하도록 합니다. 어떤 곳이든 크게 상관은 없습니다. 기존 데이터를 훼손시키지만 않으면 가능하며, 사용자가 임의로 지정하는 것이니 편한 곳으로 설정하면 되겠습니다. 저는 'C1' 셀을 기준값 데티 어를 입력할 수 있는 셀로 지정하도록 하겠습니다. 더불어, 적당한 표시를 해두면 사용하기 불편함이 없을 것이란 생각이 드네요.

엑셀 중복값 찾기 3, 기준값과 같은 값에 서식 지정.

자, 이제는 앞서 언급한 조건부 서식 메뉴로 들어가는 순서와 같습니다. 다만, 이번에는 적용할 전체 데이터를 선택하는 것은 아닙니다. 적용할 전체 데이터 중 1개를 선택하여 조건부 서식 메뉴로 넘어가면 되겠습니다. 앞서 언급한 방법과의 큰 차이점이라 할 수 있습니다.

(여기서 의문점이 생길 수 있는데요. 조건부 서식의 메뉴 중 함수 조건을 충족시키는 메뉴를 사용하기 위해 개별 값에 대한 조건부 서식을 지정하기 위해서랍니다. 즉, 데이터 중 한 개의 셀에 적용한 후, 채우기 및 서식 복사를 이용하여 전체 데이터에 중복 값 찾기를 적용할 수 있습니다.)

엑셀 중복값 찾기를 위한 조건부 서식 메뉴를 불러온 후, 새 규칙 메뉴 창에서 이번에는 '수식을 사용하여 서식을 지정할 셀 결정'이라는 메뉴를 선택하도록 하겠습니다. 아래 사진에서 확인할 수 있듯이 간단한 함수를 사용하도록 하겠습니다. 크게 어렵지 않으니 차근차근 순서대로 진행해 보도록 하겠습니다.

조건부 서식 메뉴 선택.

사진에서 '함수 활용'이라고 적혀있는 부분을 클릭합니다. 먼저 '='을 입력한 후, 엑셀 창에서 기준값으로 정해둔 셀을 클릭합니다. 다음으로 '='을 입력한 후 비교할 데이터 (서식이 적용될 데이터 셀) 셀을 클릭합니다. (물론, 키보드로 입력해되 들입니다. 또한, 방향키를 사용하면 안 되는데요. 엑셀 창의 셀 이동이 되어 해당 셀의 주소가 입력됩니다. 마우스로 클릭 후 수정 및 입력하셔야 된답니다. ※ 함수 활용 입력 시 방향키 금지 ※)

조건부 서식에 함수 적용.

저와 같은 위치로 셀을 선택하셨다면 위 사진처럼 '함수 활용'란에 적히는 것을 확인할 수 있습니다. '=$C$1=$C$4'라고 적혀 있다면 이제는 수정을 좀 해 줄 필요가 있습니다. $C$4의 경우 적용될 데이터의 셀 주소이며, '$'표시가 되어있으면 참조되는 주소가 고정이 되기에 서식 복사를 해도 변동이 없답니다. 따라서 기준값인 '$C$1'은 그대로 두고 '$C$4' 부분의 내용에서 '$' 표시를 지워주도록 합니다.

조건부 서식 함수 설정.

그다음으로 적용될 서식을 설정해 줍니다. 저는 앞서 연습한 것과 같이 굵은 글씨, 붉은 바탕색을 선택하도록 하겠습니다. 이상 혹은 수정사항이 없다면 아래에 있는 확인을 눌러 최종 적용을 시켜주도록 합니다.

조건부 서식 함수 설정 적용.

조건부 서식 함수 설정 적용 후 화면.

앞에서 언급한 두 가지의 엑셀 중복 값 찾기 방법은 조건부 서식을 설정하면 바로 데이터에 표현이 되었습니다. 하지만, 이번 방법은 큰 변화가 없다는 것을 알 수 있는데, 먼저, 기준값이 비어있기 때문입니다. 기준값에 사과를 입력해 보도록 하겠습니다.

기준값 C1에 사과를 입력한 모습

기준값인 C1셀에 '사과'를 입력하면 우리가 적용하고자 했던 위치의 데이터인 C4 셀이 사과이기에 서식이 적용된 것을 확인할 수 있습니다. 만약, '사과'가 아닌 바나나를 입력하면 어떻게 될까요. 올바르게 설정되었다면 기준값과 C4데이터 값이 다르기에 아무 변화가 없어야 합니다.

적용된 셀 값과 기준값이 다름.

'바나나'를 입력해 봤습니다. 앞서 C4 셀의 서식이 사라지고, 서식 적용이 안되어 있는 화면을 볼 수 있습니다. 이렇게 되면 조건부 서식 설정은 올바르게 된 것을 의미하며, 이제는 전체 데이터에 적용하도록 해보겠습니다. C4 셀을 선택한 후, 복사를 합니다. (마우스를 우클릭으로 해도 되며, 'Ctrl + C' 단축키를 이용해도 됩니다.) 

전체 데이터에 적용하기 위한 복사과정.

이렇게 복사를 하면 데이터 값과 설정된 서식 값이 모두 복사되게 됩니다. 이제는 남아있는 데이터에 적용을 해야겠지요. 적용하고자 하는 데이터 영역을 선택하도록 한 후, 마우스 오른쪽 버튼을 클릭하도록 합니다.

붙여넣기 기능중 서식을 선택.

오른쪽 버튼을 누르면 '붙여 넣기 옵션'을 확인할 수 있습니다. 이 메뉴 중 사진의 붉은 표시처럼 되어있는 아이콘을 선택하셔도 되며, 단축키 'R'을 누르셔도 적용되는 모습을 볼 수 있습니다. 만약 올바르게 적용이 되었다면 전체 데이터 영역에서 '바나나'의 경우만 서식이 적용된 화면을 볼 수 있습니다. 기준값이 '바나나'이기 때문이죠.

서식 아이콘 (붉은 네모칸) 마우스를 올리면 이렇게 보여요.

붙여 넣기 옵션 중 서식 아이콘에 마우스를 올려두면 위 사진과 같은 화면을 볼 수 있습니다. 뒤 엑셀 창은 서식을 붙여 넣었을 시 보일 '미리 보기'라고 생각하시면 되겠는데요. 보시는 것처럼 바나나에 서식이 적용된 것을 확인할 수 있습니다. 데이터 영역 전체에 서식 붙여 넣기를 했다면 이제 엑셀 중복 값 찾기, 기준값과 같은 셀에 서식 적용 설정이 끝났습니다. 그럼 테스트를 해봐야겠죠. 정해둥 기준값에 과일 이름을 입력해 보시면 되겠습니다.

기준값에 맞는 중복 데이터 표시.

올바르게 적용되었다면 기준값에 중복 값으로 찾고 싶은 데이터를 입력하면, 서식이 적용된 데이터 영역에 중복된 데이터를 가진 셀에는 지정된 서식으로 표시가 되는 것을 확인할 수 있습니다. 조금 번거로운 과정이 있지만, 엑셀 중복 값을 유동적으로 찾고자 할 때, 활용하기 좋은 방법일 것입니다. 또한, 한 가지 더 추가해서 찾을 때마다 입력하기 번거롭다면 기준값에 '드롭박스'를 설정할 수 있습니다. 

엑셀 중복값 찾기 조건부 서식 활용. 사진 출처 pixabay

이것으로 엑셀 중복 값 찾기 방법 중 조건부 서식을 활용한 중복 값 찾기 방법을 알아봤습니다. 이런 다양한 기능과 활용도로 오랜시간 엑셀은 업무 영역 및 개인의 활용에서 많은 사랑을 받는 프로그램이 아닐까 생각해 봅니다. 여러분의 엑셀 활용에 작게나마 도움이 되었으면 좋겠네요. 다음에는 '함수를 이용한 엑셀 중복값 찾기', 'VBA를 이용한 엑셀 중복값 찾기'에 대해서도 포스팅 해 보도록 하겠습니다.

2020/03/27 - [Life Information] - 엑셀 중복값 찾기 (2) - 함수 이용, Countif

엑셀 중복값 찾기 (2) - 함수 이용, Countif

2020/07/12 - [Life Information/IT 이야기] - 엑셀 중복 값 찾기 (3) - VBA 이용

엑셀 중복값 찾기 (3) - VBA 이용

---

방문해 주셔서 감사합니다. 

유익한 내용이라고 생각하시면, 하단의 "공감.💖", 을 부탁드려요.

'좋아요'는 글쓴이에게 큰 힘이 된답니다.

---