이 글의 목적은 엑셀에서 사용자 정의 서식(숫자·날짜·텍스트 서식)이 갑자기 목록에서 보이지 않거나 다른 PC·통합 문서에서 사라질 때, 원인별로 빠르게 복구하고 재발을 예방하도록 실무 중심의 절차와 자동화 스크립트를 제공하는 것이다.
1. 핵심 요약: 무엇이 사라졌고 어디에 저장되는가
엑셀의 사용자 정의 서식은 통합 문서 내부 스타일과 서식 개체에 저장되는 특성이 있으므로 파일 유형, 저장 방식, 시트 복구 과정, 호환성 변환에 따라 손실될 수 있다. 사용자 정의 서식은 전역 옵션이 아니라 문서 단위로 유지되므로 문서를 바꾸면 목록이 비어 보이는 현상이 발생한다. 재발 방지를 위해 템플릿화, 스타일화, 코드 백업을 병행하는 것이 최선이다.
2. 빠른 복구 체크리스트
- 현재 파일 형식이 서식을 보존하는지 확인한다(.xlsx, .xlsm, .xltx 권장)한다.
- 다른 통합 문서에서 서식이 보이지 않으면 서식을 포함한 템플릿 또는 스타일을 복사한다.
- 파일 용량 대비 서식 개수 초과 여부를 진단하고 과도한 고유 서식을 정리한다.
- 날짜·통화 등 로캘 의존 서식은 명시적 언어 태그([$-409], [$-412] 등)를 사용해 강제 고정한다.
- VBA로 서식 리스트를 추출·재주입하여 즉시 복원한다.
주의 : CSV, TXT, HTML 등 데이터 전용 형식은 셀 서식을 아예 저장하지 않는다. 서식 손실을 방지하려면 반드시 .xlsx 또는 .xlsm로 최종 저장한다.
3. 증상별 원인 매핑
| 증상 | 가능 원인 | 즉시 해결 |
|---|---|---|
| 서식 목록의 사용자 정의가 비어 보임 | 다른 통합 문서로 전환, 새 통합 문서에는 해당 서식 미포함 | 서식을 포함한 원본 파일에서 서식이 적용된 셀 또는 스타일을 복사하여 대상 파일에 붙여넣기(서식만)한다 |
| 특정 날짜/통화 서식이 갑자기 다른 형태로 보임 | 로캘 변경, 시스템 지역 설정 상이, 자동 언어 종속 서식 | 서식 코드에 [$-언어코드]를 명시해 고정한다(예: [$-412]yyyy-mm-dd) |
| 저장 후 다시 열면 사용자 정의 서식이 사라짐 | CSV/텍스트로 저장, 외부 도구 저장, 호환성 변환(.xls), 공동편집 중 단순화 | .xlsx/.xlsm로 저장하고 원본에서 서식을 재복사한다 |
| 서식 추가가 안 되거나 추가 직후 사라짐 | 고유 서식(Unique Formats) 또는 스타일 개수 과다 | 불필요한 서식을 정리하고 표준 스타일로 통합한다 |
| 특정 시트만 서식 소실 | 시트 복구/복사 과정에서 서식 테이블 미동반 | 원본 시트에서 서식을 가진 범위를 함께 복사하거나 서식 파일을 사용한다 |
4. 파일 형식별 서식 보존성
| 파일 형식 | 서식 보존 | 설명 | 권장 사용 |
|---|---|---|---|
| .xlsx | 보존 | 표준 통합 문서 | 일반 문서 |
| .xlsm | 보존 | 매크로 포함 | VBA 사용 시 |
| .xltx/.xltm | 보존 | 템플릿 | 재사용 표준 서식 배포 |
| .xls | 제한적 | 호환성 변환 중 단순화 가능 | 불가피할 때만 |
| .csv/.txt | 미보존 | 데이터만 저장 | 최종 산출물로 사용 금지 |
| .htm/.html | 제한적 | 표현은 가능하나 재개방 시 구조 변경 | 보고용 내보내기만 |
주의 : 공동 편집(클라우드) 환경에서 외부 앱이 파일을 중간 저장하면서 서식을 단순화할 수 있다. 원본 마스터 파일은 오프라인 사본(.xlsx)으로 별도 보관한다.
5. 즉시 복구 절차(무코드→저코드→자동화)
5.1 무코드 복구
- 원본 파일에서 서식이 적용된 셀 범위를 복사한다.
- 대상 파일에서 홈 > 붙여넣기 > 선택하여 붙여넣기 > 서식을 사용한다.
- 서식이 사용자 지정 목록에 다시 나타나는지 확인한다.
5.2 스타일 복구
- 원본 파일에서 셀 스타일을 열고 사용자 정의 스타일을 마우스 오른쪽 클릭하여 병합 또는 스타일 복사를 수행한다.
- 대상 파일에 붙여넣는다. 스타일은 사용자 정의 서식과 연결되어 재생성된다.
5.3 로캘 강제 고정
날짜 서식 예시이다.
[$-412]yyyy-mm-dd ; 한국어(대한민국) [$-409]mm/dd/yyyy ; 영어(미국) [$-804]yyyy"年"m"月"d"日" ; 중국어(간체) 통화 서식 예시이다.
[$₩-412]#,##0_);[빨강]\([$₩-412]#,##0\) [$$-409]#,##0.00;[Red]\-[$$-409]#,##0.00 주의 :
NumberFormatLocal은 PC 언어에 따라 다르게 기록되므로 공유 목적의 자동화에는 NumberFormat(영문 기반)을 우선 사용한다.5.4 저코드 복구(VBA로 서식 일괄 재주입)
현재 통합 문서 내에서 사용된 고유 서식 코드를 수집해 사용자 정의 서식 목록을 강제로 재구성하는 매크로 예시이다.
Option Explicit
Sub RebuildCustomNumberFormats()
Dim sh As Worksheet, rng As Range, c As Range
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
Dim fmt As String, i As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' 사용된 셀 범위에서 서식 수집
For Each sh In ThisWorkbook.Worksheets
On Error Resume Next
Set rng = sh.UsedRange
On Error GoTo 0
If Not rng Is Nothing Then
For Each c In rng.Cells
fmt = c.NumberFormat
If Len(fmt) > 0 Then
If Not dict.Exists(fmt) Then dict.Add fmt, 1
End If
Next c
End If
Next sh
' 더미 셀에 모든 서식을 일시 주입하여 사용자 정의 목록 복원
Dim wsTmp As Worksheet
Set wsTmp = ThisWorkbook.Worksheets.Add(After:=Sheets(Sheets.Count))
wsTmp.Name = "Tmp_Rebuild_Formats"
i = 1
Dim k As Variant
For Each k In dict.Keys
wsTmp.Cells(i, 1).Value = 0
wsTmp.Cells(i, 1).NumberFormat = k
i = i + 1
Next k
' 완료 안내
MsgBox dict.Count & " 개의 사용자 정의 서식을 재주입했다.", vbInformation
' 필요 시 임시 시트 유지 후 수동 삭제
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
주의 : 위 코드는
NumberFormat을 기준으로 동작한다. 다국어 환경에서 NumberFormatLocal 기반으로 교체하려면 c.NumberFormatLocal을 사용한다.5.5 고급: 즐겨찾는 서식 일괄 적용 유틸
자주 쓰는 서식을 버튼 한 번으로 대상 통합 문서에 투입하는 매크로이다.
Sub InjectFavoriteFormats() Dim fav As Variant, i As Long fav = Array( _ "0;[Red]\-0", _ "#,##0;-#,##0;""-""", _ "#,##0.00;[Red]\-#,##0.00", _ "[$-412]yyyy-mm-dd", _ "[$₩-412]#,##0_);[Red]\([$₩-412]#,##0\)" _ ) Dim ws As Worksheet: Set ws = ActiveSheet For i = LBound(fav) To UBound(fav) ws.Cells(i + 1, 1).Value = 1234.56 ws.Cells(i + 1, 1).NumberFormat = fav(i) Next i MsgBox "즐겨찾는 서식을 주입했다.", vbInformation End Sub 6. 진단과 정리: 서식 폭증과 손실의 상관관계
워크북 내 고유 셀 서식과 스타일이 폭증하면 신규 사용자 정의 서식 등록이 실패하거나 저장 후 목록에서 사라질 수 있다. 다음 절차로 정리한다.
- 중복 스타일 합치기한다. 동일 목적의 서식을 하나의 스타일로 통합한다.
- 불필요한 빈/숨김 시트 제거한다. 숨은 시트에 잔존 서식이 쌓여 있다.
- 범위 정리한다. 실제 데이터 영역을 벗어나 서식만 남은 열/행을 지우고 통합 문서를 축소 저장한다.
- 외부 복붙 최소화한다. 외부 파일에서 가져온 서식은 새로운 고유 서식을 계속 만든다.
간단한 진단 매크로로 고유 수를 측정한다.
Sub CountUniqueNumberFormats() Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary") Dim sh As Worksheet, c As Range For Each sh In ThisWorkbook.Worksheets For Each c In sh.UsedRange.Cells If Not dict.Exists(c.NumberFormat) Then dict.Add c.NumberFormat, 1 Next c Next sh MsgBox "고유 NumberFormat 개수: " & dict.Count End Sub 주의 : 고유 서식과 고유 스타일 모두 과도하면 저장/열기 시 서식이 일부 누락될 수 있다. 대용량 파일은 피벗 캐시, 조건부 서식, 테마 글꼴 등도 함께 점검한다.
7. 영구 예방 설계: 템플릿과 배포 전략
7.1 Book.xltx 템플릿
- 빈 통합 문서에서 필요한 사용자 정의 서식을 모두 적용한 샘플 범위를 만든다.
- 셀 스타일로 승격하여 이름을 부여한다(예: KR_Date_yyyy_mm_dd, KR_Currency_NoDecimal 등).
- 파일 > 다른 이름으로 저장 > Excel 템플릿(.xltx)으로 저장 후
XLSTART폴더에 Book.xltx로 배치한다. - 이후 새 통합 문서는 자동으로 해당 서식을 기본 포함한다.
7.2 Sheet.xltx 템플릿
시트 추가 시마다 표준 서식이 따라오도록 Sheet.xltx를 같은 위치에 배치한다.
7.3 스타일 명명 규칙
- 접두사로 도메인 구분:
KR_(국내),EN_(영문 보고),ACC_(회계) 등으로 관리한다. - 숫자 서식은 소수점/음수/단위 정보를 코드에 반영한다(예: ACC_Num_Comma2_RedNeg).
7.4 배포
- 템플릿 파일을 공용 네트워크에 배치하고, 사용자 PC의
XLSTART경로를 동일하게 맞춘다. - 버전 관리를 위해 YYYYMMDD를 파일 설명에 기록한다.
8. 로캘 안전 서식 레시피 모음
현업에서 잦은 변형을 막는 안전 레시피이다.
-- 날짜(ISO) -- [$-412]yyyy-mm-dd [$-409]yyyy-mm-ddThh:mm:ss
-- 숫자(천단위, 음수 붉은색, 블랭크 대시) --
#,##0;-#,##0;"-"
-- 백분율(소수 2자리, 음수 괄호) --
0.00%_);[Red]
0.00
0.00
-- 원화(소수 0자리, 음수 괄호) --
[$₩-412]#,##0_);[Red][$₩-412]#,##0
-- 가변 단위(백만 표시) --
[>=1000000]0,,"M";[>=1000]0,"K";0
-- 텍스트와 숫자 혼합 --
"주문번호 "000000
주의 : 조건부 구간 서식은 표시만 바뀐다. 계산 정확도를 변경하지 않는다. 수식의 반올림 정책은
ROUND, ROUNDUP, ROUNDDOWN 등으로 별도 관리한다.9. 사고 대응 시나리오: 이미 손실된 경우
- 가장 최근의 자동 복구 파일을 우선 확인한다(임시 폴더). 동일 서식이 살아 있을 수 있다.
- 백업 히스토리(버전 기록, 클라우드 버전)를 열어 서식을 복사해 현행 파일에 붙여넣는다.
- 복구가 불가하면 본문 5.4의 RebuildCustomNumberFormats로 목록을 재생성한다.
- 필요한 레이아웃만 남기고 새 통합 문서로 콘텐츠를 복사해 구조를 초기화한다.
10. 조건부 서식과의 충돌 최소화
- 표시 목적은 셀 서식, 논리 강조는 조건부 서식으로 역할을 분리한다.
- 조건부 서식 규칙은 범위를 최소화하고, 우선순위를 명시적으로 정한다.
- 반복 규칙과 충돌 규칙을 정리하면 사용자 정의 서식이 리스트에서 사라진 것처럼 보이는 착시를 줄일 수 있다.
11. 서식 코드 품질 기준
- 단위·언어 고정: 보고 대상의 언어와 통화에 맞춰
[$-]접두를 명시한다. - 음수 정책 명시: 괄호/부호/색상을 일관 적용한다.
- 결측값 표현: 빈값과 0을 구분하여
""또는"-"로 시각 일관성 확보한다. - 소수 자릿수 정의: 데이터 출처별 소수 자리 통일로 혼선을 방지한다.
12. 표준 운영절차(SOP) 예시
- 새 프로젝트 착수 시 템플릿(Book.xltx)로 시작한다.
- 필요 사용자 정의 서식은 즉시 스타일로 승격하고 명명 규칙을 적용한다.
- 외부 데이터는 먼저 값만 붙여넣고, 서식은 템플릿 스타일로 재적용한다.
- 월 1회 고유 서식 개수 진단 매크로 실행 및 정리한다.
13. 자주 쓰는 사용자 정의 서식 샘플
| 목적 | 서식 코드 | 비고 |
|---|---|---|
| ISO 날짜 | [$-412]yyyy-mm-dd | 언어 고정 |
| 시분초 | hh:mm:ss | 24시간 |
| 천단위 정수 | #,##0;-#,##0;"-" | 0은 대시 |
| 소수 2자리 | #,##0.00;[Red]\-#,##0.00 | 음수 붉은색 |
| 원화 | [$₩-412]#,##0_);[Red]\([$₩-412]#,##0\) | 괄호 음수 |
| 백만/천 접미 | [>=1000000]0,,"M";[>=1000]0,"K";0 | 지표 대시보드 |
| 품목번호 | "ITEM-"000000 | 선행 0 유지 |
14. 문제 해결 QRG(Quick Reference Guide)
- 저장 형식 확인 → 데이터 전용 형식이면 .xlsx로 다시 저장한다.
- 원본 문서에서 서식을 복사해 대상에 붙여 서식 목록을 되살린다.
- 로캘 고정 접두 사용으로 표시 흔들림을 차단한다.
- VBA로 서식 재주입 및 즐겨찾기 투입으로 즉시 정상화한다.
- 템플릿·스타일 체계로 재발을 근본 예방한다.
FAQ
사용자 정의 서식은 PC에 저장되는가, 파일에 저장되는가?
주로 통합 문서에 저장된다. 따라서 다른 파일을 열면 목록이 비어 보일 수 있다. 전역 배포가 필요하면 템플릿을 사용한다.
CSV로 저장했다가 다시 .xlsx로 바꾸면 서식이 돌아오는가?
아니다. CSV 단계에서 서식은 이미 손실된다. 원본 .xlsx에서 다시 복사해야 한다.
다른 PC에서 날짜 표시가 달라진다. 왜인가?
시스템 로캘과 언어 종속 서식 차이 때문이다. [$-412] 등 로캘 고정 접두를 사용해 동일 표시를 강제한다.
고유 서식이 많으면 왜 문제가 되는가?
워크북 메타데이터가 비대해지고 일부 서식이 저장/로드 과정에서 누락될 수 있다. 중복 스타일을 통합하고 불필요 서식을 정리해야 한다.
조건부 서식이 사용자 정의 서식을 덮어쓰는가?
조건부 서식이 우선한다. 규칙 범위와 우선순위를 정리해 충돌을 줄인다.