엑셀 수식 복사, 참조 고정과 채우기 방법

엑셀 수식 복사는 채우기 핸들을 끌거나 Ctrl + D, Ctrl + C/V로 하지만, 결과가 틀리는 원인은 대부분 복사 방법이 아니라 참조가 함께 움직이는 데 있습니다. 할인율이나 환율처럼 모든 행이 같은 칸을 봐야 하는 수식을 아래로 복사하면 엑셀은 그 칸 주소까지 한 줄씩 내립니다. 견적 파일에서 이 한 칸이 밀리면 할인 금액이 0으로 찍힌 견적서가 거래처로 나갑니다. 수식을 빠르게 복사하는 법, 참조를 고정하는 법, 복사했는데 값이 바뀌지 않을 때 확인할 곳까지 순서대로 따라 할 수 있습니다.

엑셀 수식 복사하는 방법 다섯 가지

채우기 핸들 끌기와 두 번 클릭. 수식이 든 셀을 선택하면 오른쪽 아래에 작은 네모(채우기 핸들)가 보입니다. 이것을 아래나 옆으로 끌면 수식이 복사됩니다. 두 번 클릭하면 옆 열에 데이터가 이어진 마지막 행까지 한 번에 채워집니다. 옆 열 중간에 빈 칸이 있으면 거기서 멈추므로, 수천 행짜리 표에서는 마지막 행까지 채워졌는지 확인합니다.

Ctrl + D와 Ctrl + R. 수식이 든 셀부터 채울 범위까지 선택하고 Ctrl + D를 누르면 아래로, Ctrl + R을 누르면 오른쪽으로 복사됩니다. 리본에서는 홈 탭 편집 그룹의 채우기 → 아래쪽/오른쪽입니다. 끌기보다 범위가 정확해서 긴 표에 좋습니다.

범위를 먼저 잡고 Ctrl + Enter. D3:D500을 선택한 상태에서 수식을 입력하고 Enter 대신 Ctrl + Enter를 누르면, 선택한 모든 셀에 같은 수식이 상대참조 규칙대로 들어갑니다.

복사해서 수식만 붙여 넣기. Ctrl + C 후 붙일 곳에서 Ctrl + V를 누르면 수식과 서식이 함께 들어갑니다. 서식은 두고 수식만 넣으려면 붙인 뒤 나타나는 붙여넣기 옵션 단추에서 수식을 고르거나, Ctrl + Alt + V로 선택하여 붙여넣기를 열어 수식을 고릅니다.

표(Ctrl + T)의 계산된 열. 범위를 표로 바꿔 두면 한 칸에 수식을 넣는 순간 열 전체에 자동으로 채워지고, 새 행을 추가해도 수식이 따라 들어갑니다. 매달 행이 늘어나는 매출표나 입고 기록에 가장 손이 덜 가는 방법입니다.

Microsoft의 수식 이동 또는 복사 도움말은 수식을 이동하면 셀 참조가 바뀌지 않고, 복사하면 상대참조가 새 위치에 맞게 바뀐다고 설명합니다. 수식 복사에서 생기는 문제는 거의 이 차이에서 나옵니다.

수식을 복사하면 결과가 달라지는 이유

엑셀은 기본으로 상대참조를 씁니다. D3에 있는 =B3*C3은 엑셀 입장에서 "왼쪽으로 두 칸 셀 곱하기 왼쪽으로 한 칸 셀"이라는 뜻이라, 한 줄 아래로 복사하면 =B4*C4가 됩니다. 행마다 다른 단가와 수량을 곱할 때는 이것이 정확히 원하는 동작입니다. 문제는 모든 행이 B3 하나를 봐야 하는 경우입니다. B4가 빈 칸이면 할인 금액은 0이 됩니다.

B3을 고정하려면 열 문자와 행 번호 앞에 $를 붙여 =$B$3*C3으로 씁니다. 이렇게 복사하면 아래 모든 행이 B3의 할인율과 자기 행의 정가를 곱합니다.

절대참조, 혼합참조와 F4

$는 행과 열에 따로 붙일 수 있습니다. 고정할 쪽에만 붙인다고 기억하면 됩니다.

혼합참조는 가로와 세로로 모두 복사하는 표에서 씁니다. 아래는 사람별 매출액(C열)에 5%, 10%, 15% 인센티브율(13행)을 곱하는 표입니다. D14에 =$C14*D$13을 넣고 오른쪽과 아래로 채우면, 매출액은 항상 C열에서, 비율은 항상 13행에서 가져옵니다. 매출 450에 5%를 곱한 22.5, 15%를 곱한 67.5까지 수식 하나로 채워집니다.

$를 직접 타이핑하지 않아도 됩니다. 수식을 입력하거나 고치는 중에 셀 주소에 커서를 두고 F4를 누르면 A1 → $A$1 → A$1 → $A1 → A1 순서로 바뀝니다. Microsoft의 상대/절대/혼합 참조 도움말은 수식을 두 칸 아래, 두 칸 오른쪽으로 복사할 때 $A$1은 그대로, A$1은 C$1, $A1은 $A3, A1은 C3이 된다고 정리해 둡니다. 노트북에서 F4가 화면 밝기 같은 기능으로 잡혀 있으면 Fn + F4를 누릅니다.

참조를 바꾸지 않고 그대로 복사하기

같은 수식을 다른 자리에 주소 그대로 옮기고 싶을 때가 있습니다. 셀이 하나라면 셀을 선택하고 수식 입력줄에서 수식 글자를 드래그해 복사한 뒤, Esc를 누르고 붙일 셀의 수식 입력줄에 붙여 넣습니다. 셀 단위가 아니라 글자로 복사했으므로 참조가 바뀌지 않습니다.

여러 셀을 한꺼번에 옮길 때는 찾기 및 바꾸기를 씁니다. 범위를 선택하고 Ctrl + H로 =를 #=처럼 수식에 없는 글자로 바꾸면 수식이 글자가 됩니다. 이 범위를 복사해 붙인 뒤 양쪽에서 #=를 다시 =로 바꾸면 원래 주소 그대로 수식이 살아납니다. 아예 위치를 옮기는 것이라면 잘라내기(Ctrl + X)와 붙여넣기를 쓰면 참조가 바뀌지 않습니다.

따라 하기: 견적 할인표를 복사해 거래처별로 쓰기

대영식품 영업 담당자가 한일리테일에 보낼 견적을 만드는 경우입니다. 견적 시트에는 B3에 거래처 할인율 20%, C열에 정가(2,000원, 3,000원, 4,000원, 10,000원), D열에 할인 금액, E열에 판매가가 있습니다.

첫 행만 만들어 둔 상태에서 D3에 =B3*C3, E3에 =C3-D3을 넣고 아래로 끌면 첫 줄만 400원 할인, 나머지는 0원 할인으로 나옵니다. D3의 B3에 커서를 두고 F4를 한 번 눌러 =$B$3*C3으로 고친 뒤 다시 채우면 할인 금액이 400, 600, 800, 2,000원, 판매가가 1,600, 2,400, 3,200, 8,000원으로 맞게 나옵니다. E열은 같은 행끼리 빼는 계산이라 고정할 필요가 없습니다.

다음으로 성우유통용 견적을 같은 양식으로 만들려고 시트를 복사합니다. 시트 탭을 Ctrl을 누른 채 끌어 복사하면 시트 안의 참조는 새 시트 기준으로 따라오므로, B3의 할인율만 15%로 바꾸면 됩니다. 반면 D열 수식만 다른 통합 문서에 복사해 붙이면 ='[견적_한일리테일.xlsx]견적'!$B$3처럼 원래 파일을 가리키는 외부 참조가 생깁니다. 거래처에 보낼 파일이라면 마지막에 할인 금액과 판매가 열을 복사해 값으로 붙여 넣어 연결을 끊습니다. 외부 참조가 남았는지는 데이터 탭의 통합 문서 링크(이전 버전은 연결 편집)에서 확인합니다.

보내기 전 마지막으로 수식 탭의 수식 표시를 누르면(단축키는 Ctrl과 숫자 1 왼쪽의 억음 부호 키), 모든 셀의 수식이 한눈에 보여 한 줄만 다른 수식이 섞였는지 바로 찾을 수 있습니다. 한 번 더 누르면 결과 화면으로 돌아옵니다.

👉 엑셀 다른 시트 참조, 값 가져오기 수식 총정리

Microsoft 365에서는 복사 없이 한 번에

Microsoft 365와 Excel 2021 이후 버전은 동적 배열 수식을 지원합니다. 견적 예시라면 D3에 =$B$3*C3:C6을 한 번만 입력해도 결과가 D3부터 D6까지 자동으로 펼쳐집니다. 수식은 D3 한 칸에만 있으므로 복사하다 한 줄을 빠뜨리거나 중간에 다른 수식이 섞일 일이 없습니다. 펼쳐질 자리에 이미 값이 있으면 #SPILL! 오류가 나니 아래 칸을 비워 둡니다. 다만 Excel 2019 이전 버전으로 이 파일을 열면 배열 수식으로 바뀌어 고치기 어려우므로, 거래처와 주고받는 파일에는 행마다 복사한 일반 수식이 무난합니다.

웹용 Excel에서도 채우기 핸들, Ctrl + D, F4가 같은 방식으로 동작합니다. 붙여넣기 옵션은 홈 탭의 붙여넣기 아래 화살표에서 수식 붙여넣기를 고르면 됩니다. Mac용 Excel에서는 단축키의 Ctrl이 대부분 Command로 바뀌고, 참조 형식 전환은 Command + T입니다.

수식을 복사했는데 이상할 때

복사했는데 모든 행 값이 첫 행과 같습니다. 계산 옵션이 수동으로 바뀐 경우입니다. 수식 탭의 계산 옵션을 자동으로 바꾸거나 F9를 누릅니다. 계산 옵션은 그 세션에서 처음 연 통합 문서의 설정을 따르기 때문에, 수동으로 저장된 파일을 먼저 열면 나중에 연 파일도 수동이 됩니다.

수식이 계산되지 않고 글자 그대로 보입니다. 셀 서식이 텍스트인 칸에 수식을 붙여 넣은 경우입니다. 서식을 일반으로 바꾸고 셀을 두 번 클릭해 Enter를 누르면 계산됩니다.

#REF! 오류가 납니다. 위쪽이나 왼쪽 끝에서 수식을 복사해 참조가 시트 바깥으로 밀려났거나, 참조하던 행/열을 지운 경우입니다. 수식을 원래 위치에서 다시 복사하고 기준 칸을 $로 고정합니다.

채우기 핸들 두 번 클릭이 중간에서 멈춥니다. 옆 열에 빈 칸이 있기 때문입니다. 끝까지 Ctrl + D로 채우거나 범위를 표로 바꿉니다.

VLOOKUP을 아래로 복사했더니 중간부터 #N/A가 납니다. 찾을 범위를 고정하지 않아 범위가 한 줄씩 내려간 경우입니다. 첫 행의 =VLOOKUP(A2,단가표!A2:C200,3,FALSE)가 열 번째 행에서는 단가표의 A11:C209를 보게 되어, 위쪽 품목을 찾지 못합니다. 범위를 단가표!$A$2:$C$200으로 고정하면 모든 행이 같은 단가표 전체를 봅니다. SUMIF, COUNTIF, INDEX/MATCH처럼 범위를 받는 함수는 모두 같은 원리라, 범위 인수에는 기본으로 $를 붙이는 습관을 들이면 복사 오류가 크게 줄어듭니다.

표에 수식을 넣었는데 열 전체로 채워지지 않습니다. 계산된 열 자동 채우기가 꺼져 있는 경우입니다. 파일 → 옵션 → 언어 교정 → 자동 고침 옵션의 입력할 때 자동 서식 탭에서 표에 수식을 채워 계산된 열 만들기를 켭니다. 이미 넣은 수식은 수식 칸 옆에 나타나는 자동 고침 옵션 단추에서 열 전체에 덮어쓰기를 고르면 됩니다. 매달 행이 늘어나는 표라면 이 옵션을 켜 두는 것만으로 복사할 일이 사라집니다.

엑셀 수식 복사 자주 묻는 질문 (FAQ)

Q. 수식을 복사하면 값이 0이나 엉뚱한 숫자로 바뀌는 이유는 무엇인가요?

상대참조 때문에 기준값 칸 주소까지 함께 이동했기 때문입니다. 고정해야 하는 칸 주소에 커서를 두고 F4를 눌러 $B$3처럼 절대참조로 바꾼 뒤 다시 복사합니다.

Q. 서식은 빼고 수식만 복사할 수 있나요?

있습니다. 붙여 넣은 뒤 붙여넣기 옵션에서 수식을 고르거나, Ctrl + Alt + V로 선택하여 붙여넣기를 열고 수식을 고릅니다. 결과 숫자만 필요하면 값을 고릅니다.

Q. Mac에서는 F4 대신 무엇을 누르나요?

Mac용 Excel에서는 Command + T로 참조 형식을 바꿉니다.

Q. 수식을 복사하면 다른 파일 이름이 수식에 들어갑니다.

다른 통합 문서의 셀을 참조하는 수식을 복사해 왔기 때문입니다. 필요 없는 연결이면 값으로 붙여 넣거나, 데이터 탭의 통합 문서 링크에서 그 파일의 연결을 끊습니다.

inline AI로 견적 수식 맞추기

inline AI는 열어 둔 견적 파일에서 기준값을 봐야 하는데 밀려 있는 참조를 찾아 고정하고, 고친 칸을 표시합니다. ARP를 함께 쓰면 거래처 메일로 들어온 견적 요청에 견적을 내고 발주서가 오면 주문으로 등록하므로, 거래처마다 견적 시트를 복사해 고칠 일이 줄어듭니다.