인수인계 받은 파일, 전임자가 쓰던 관리대장, 협력사에서 보내온 양식. 남이 만든 엑셀 파일을 이어받아 쓰는 일은 생각보다 자주 있습니다. 이때 바로 수정부터 시작하면 나중에 곤란해집니다. 보이지 않는 곳에 무엇이 들어 있는지 모르는 상태 이기 때문입니다. 숨겨진 시트, 깨진 외부 링크, 수식 자리에 박혀 있는 숫자 같은 것들입니다. 고치기 전에 5분만 들여 확인하는 순서를 정리했습니다. 0단계. 복사본부터 만들기 점검이든 수정이든 원본은 건드리지 않습니다. 파일을 복제해두고 복사본에서 작업하세요. 시트 단위로 작업할 때도 마찬가지입니다. 시트 탭 우클릭 → 이동/복사 → 복사본 만들기 . 5초면 됩니다. 1단계. 실제 데이터 범위 확인 — Ctrl + End 커서가 데이터 마지막 셀로 가면 정상입니다. 엉뚱하게 먼 곳으로 튄다면 사용 범위가 오염된 상태입니다. 이건 단순히 파일이 무겁다는 문제가 아닙니다. 나중에 피벗테이블이나 필터를 만들 때 빈 행 수십만 개가 범위에 포함되어 계산이 어긋납니다. 작업을 시작하기 전에 정리하는 편이 낫습니다. 2단계. 하드코딩된 값 찾기 — Ctrl + ` Ctrl + ` (숫자 1 왼쪽 백틱)를 누르면 모든 셀이 수식 상태로 표시 됩니다. 다시 누르면 원래대로 돌아옵니다. 이 상태에서 수식이어야 할 자리에 숫자가 그냥 박혀 있는 셀 을 찾으세요. 합계 행인데 =SUM(...) 이 아니라 1250000 이 들어 있는 식입니다. 전임자가 급하게 값을 덮어썼거나, 수식이 깨져서 임시로 숫자를 넣은 흔적입니다. 데이터를 갱신해도 이 값은 바뀌지 않습니다. 인수인계 파일에서 가장 자주 사고가 나는 지점입니다. 3단계. 오류 셀 위치 확인 — F5 F5 → 옵션 → 수식 선택 → 오류만 체크 → 확인. #REF! , #N/A , #VALUE! 가 있는 셀이 한 번에 선택됩니다. 시트 아래쪽에 숨어 있어서 스크롤로는 못 찾는 오류를 잡아냅니다. 특히 ...

데이터는 몇백 행뿐인데 파일이 20MB가 넘습니다. 열 때마다 한참 걸리고, 스크롤만 해도 버벅입니다. 메일로 보내면 용량 제한에 걸립니다. 이 경우 용량의 대부분은 데이터가 아니라 빈 껍데기 입니다. 원인을 찾으면 대개 몇 분 안에 10분의 1로 줄어듭니다. 1단계. 사용 범위 확인 (가장 먼저) Ctrl + End 를 눌러보세요. 커서가 데이터 마지막 셀로 가야 정상입니다. 만약 커서가 XFD1048576 근처나 한참 아래로 튄다면 사용 범위가 오염된 상태입니다. 데이터를 지웠어도 엑셀은 "여기까지 썼던 영역"을 기억하고 그만큼의 빈 셀 정보를 파일에 저장합니다. 해결 방법 실제 데이터 아래 첫 번째 빈 행의 행 번호 를 클릭 Ctrl + Shift + ↓ 로 아래 끝까지 선택 우클릭 → 삭제 (Delete 키가 아니라 '행 삭제') 오른쪽 빈 열도 같은 방식으로 Ctrl + Shift + → 후 열 삭제 저장하고 파일을 닫았다 다시 엽니다 5번이 빠지면 사용 범위가 갱신되지 않습니다. 닫았다 열어야 반영됩니다. 이 작업만으로 용량이 크게 줄어드는 경우가 많습니다. Delete 키로 지우는 것과 '행 삭제'는 다릅니다. Delete는 내용만 지우고 셀의 서식 정보는 남깁니다. 2단계. 열·행 전체 서식 정리 A열 머리글을 클릭해서 열 전체에 색이나 테두리를 지정한 적이 있다면, 그 열의 100만 개 셀 전부에 서식 정보가 붙습니다. Ctrl + A 로 전체 선택 후 홈 → 지우기 → 서식 지우기 를 하면 정리되지만, 필요한 서식까지 전부 사라집니다. 데이터 영역을 제외한 바깥쪽만 선택해서 하는 편이 안전합니다. 3단계. 조건부 서식 규칙 확인 홈 → 조건부 서식 → 규칙 관리 → 표시 대상을 이 워크시트 로 변경. 같은 규칙이 수십 개로 쪼개져 있다면 복사·붙여넣기 때문입니다. 전부 지우고 하나로 다시 만드세요. 규칙이 수...

마감일이 지난 건, 사흘 안에 처리해야 할 건, 안전재고에 미달한 품목. 이런 건 매번 눈으로 찾을 게 아니라 파일을 열면 저절로 눈에 띄게 만들어두는 편이 낫습니다. 조건부 서식의 기본 기능은 대부분 알고 계실 겁니다. 이 글은 수식을 직접 넣어 행 전체에 색을 입히는 방법 과 실무에서 어긋나는 지점을 다룹니다. 기본 구조 — $ 위치가 전부다 조건부 서식에서 수식을 쓸 때 결과를 좌우하는 건 $ 의 위치입니다. =$D2<TODAY() → 열만 고정. 행 전체에 색이 칠해집니다 =$D$2<TODAY() → 둘 다 고정. D2 하나만 보고 판단해서 전체가 칠해지거나 아무것도 안 칠해집니다 =D2<TODAY() → 고정 없음. 셀마다 조건이 옆으로 밀려서 엉뚱한 칸이 칠해집니다 행 전체를 칠하려면 열 문자 앞에만 $ 를 붙입니다. 이것만 기억하면 나머지는 응용입니다. 적용 순서 색을 칠할 전체 범위 를 선택합니다 (머리글 제외, 예: A2:F500) 홈 → 조건부 서식 → 새 규칙 수식을 사용하여 서식을 지정할 셀 결정 선택 수식 입력 후 서식 에서 채우기 색 지정 1번에서 선택한 범위의 첫 행 기준 으로 수식을 쓴다는 점이 중요합니다. A2부터 선택했다면 수식도 2행 기준( $D2 )으로 써야 합니다. 마감일 관리 규칙 세 개 마감일이 D열에 있다고 가정합니다. 규칙은 아래 순서대로 만드세요. ① 기한 초과 (빨강) =AND($D2<>"", $D2<TODAY()) ② 3일 이내 임박 (주황) =AND($D2<>"", $D2-TODAY()>=0, $D2-TODAY()<=3) ③ 완료된 건 제외 (회색) 상태가 E열에 있다면, =$E2="완료" 완료 규칙은 목록에서 맨 위로 올리고 '참일 경우 중지'에 체크 하세요. 그래야 완...

VLOOKUP은 배우기는 쉬운데 실무에서 자주 어긋납니다. 어제까지 잘 되던 수식이 오늘 #N/A 를 뱉기도 하고, 아래로 채우니 값이 밀리기도 합니다. 원인은 거의 정해져 있습니다. 아래 순서대로 확인하면 대부분 처음 두 가지에서 해결됩니다. 확인 1. 네 번째 인수를 넣었는가 가장 흔한 원인이자 가장 위험한 원인입니다. =VLOOKUP(A2, 범위, 2, 0) 마지막 0 (또는 FALSE )은 정확히 일치하는 값만 찾으라 는 뜻입니다. 이걸 생략하면 엑셀은 TRUE 로 간주하고 근사값 을 찾습니다. 근사값 모드는 데이터가 오름차순으로 정렬돼 있다고 가정합니다. 정렬돼 있지 않으면 #N/A 가 나오거나, 더 나쁘게는 엉뚱한 값을 오류 없이 가져옵니다. 오류가 안 나기 때문에 발견이 늦어지고, 그대로 보고에 들어갑니다. 정확히 일치를 원한다면 마지막 인수는 항상 0으로 쓰세요. 예외는 구간별 등급표처럼 근사 조회가 목적일 때뿐입니다. 확인 2. 찾는 값의 형식이 같은가 한쪽은 숫자 1001 , 다른 쪽은 텍스트 "1001" 인 경우입니다. 화면에는 똑같이 보이지만 VLOOKUP은 다른 값으로 봅니다. 확인은 =ISNUMBER(A2) 로 양쪽을 비교하면 됩니다. 다르다면 한쪽을 맞춰야 합니다. 공백도 같은 문제를 일으킵니다. 대성물산 과 대성물산 (뒤에 공백)은 다른 값입니다. =VLOOKUP(TRIM(A2), 범위, 2, 0) 다만 이건 찾는 쪽만 정제합니다. 조회 대상 표에 공백이 있다면 그쪽도 정제한 열을 따로 만들어야 합니다. 확인 3. 범위를 절대참조로 고정했는가 수식을 아래로 채웠더니 위쪽은 맞고 아래로 갈수록 #N/A 가 늘어난다면 이 경우입니다. =VLOOKUP(A2, $B$2:$D$500, 2, 0) 범위에 $ 가 없으면 아래로 채울 때 조회 범위도 함께 내려갑니다. 500행짜리 표가 점점 잘려나가는 셈입니다. 범위를 표( Ctrl + T...

합계를 냈는데 0이 나옵니다. VLOOKUP은 #N/A 를 뱉고, COUNTIF는 분명히 있는 값을 못 찾습니다. 화면에는 아무 문제 없어 보이는 숫자인데도 그렇습니다. 대부분 숫자가 텍스트로 저장된 경우입니다. 다른 시스템에서 내려받은 파일, 웹페이지에서 복사한 표, ERP에서 뽑은 자료에서 거의 항상 발생합니다. 1단계. 텍스트인지 확인하기 세 가지 방법이 있고, 위로 갈수록 확실합니다. 가장 확실한 방법 =ISNUMBER(A2) FALSE 가 나오면 텍스트입니다. 셀 하나만 확인하지 말고 아래로 채워서 전체를 보세요. 일부 행만 텍스트인 경우가 가장 찾기 어렵습니다. 눈으로 확인하는 방법 엑셀은 숫자를 오른쪽, 텍스트를 왼쪽으로 정렬합니다. 정렬을 따로 지정하지 않았는데 값이 셀 왼쪽에 붙어 있다면 텍스트입니다. 상태표시줄로 확인하는 방법 숫자 열을 드래그 선택했을 때 화면 아래 상태표시줄에 합계가 표시되지 않고 개수만 나온다면 텍스트입니다. 2단계. 숫자로 바꾸기 상황에 따라 네 가지 방법이 있습니다. 방법 1. 곱하기 1 (가장 확실) 빈 셀에 1 입력 후 복사 ( Ctrl + C ) 문제가 되는 열 전체 선택 우클릭 → 선택하여 붙여넣기 연산에서 곱하기 선택 후 확인 모든 값에 1을 곱하면서 강제로 숫자 연산을 시키는 방식입니다. 값은 그대로 두고 형태만 바꿉니다. 열이 여러 개여도 한 번에 처리됩니다. 방법 2. 오류 검사 표시 이용 셀 왼쪽 위에 초록색 삼각형이 있다면, 열 전체를 선택하고 나타나는 느낌표 아이콘 → 숫자로 변환 을 누르면 됩니다. 가장 간단하지만 초록 삼각형이 항상 나타나는 건 아닙니다. 안 보인다면 다른 방법을 쓰세요. 방법 3. 텍스트 나누기 (한 열씩, 매우 빠름) 열 하나 선택 데이터 → 텍스트 나누기 아무것도 바꾸지 말고 마침 클릭 나누는 게 목적이 아니라, 엑셀이 데이터를 다시 해석하게 만드는 게 목적...