What are the four sections of a custom number format?

You can define up to four sections in the order positive; negative; zero; text, separated by semicolons. You can specify different display rules for each section.

How can I hide zero values only on screen?

Set the format code to 0;-0;;@ to keep the value as 0 while hiding only its display.

How can I display negative numbers in red and parentheses?

Use a code such as #,##0;[Red](#,##0);0;@ to display only negative numbers in red parentheses.

How can I correctly display accumulated time over 24 hours?

Use the [h]:mm:ss format to display total accumulated time even when it exceeds 24 hours.

CONTENTS

Excel Custom Number Formats: Keep Values Intact, Make Displays Clean

In Excel, you can use cell formatting and custom number formats to keep values unchanged while changing only how they appear. This guide covers the four-section structure (positive; negative; zero; text), number, date, and text codes, and practical reporting patterns with examples.

Quick Fix — 1-Minute Tip

Select a range → Ctrl+1 → Number → Custom as needed.

How It Works

Formatting does not affect calculations; it only changes what appears on screen.

Custom Number Format Structure

positive_format;negative_format;zero_format;text_format

Example: #,##0;[Red](#,##0);0;@
Example: [>=1000000]0.0,,"M";[>=1000]0,"K";0
Example: 0;-0;;@ (hides zeros)

Number Examples

#,##0" won"
#,##0;[Red](#,##0);0;@
[>=1000000]#,##0,,"M";[>=1000]#,##0,"K";0

Date/Time Examples

yyyy-mm-dd
yyyy" year" m" month" d" day"
[h]:mm:ss

Text/Code Examples

00000           'fixed number of digits (ZIP Code)
0" items"       'suffix
"No. "0000      'prefix
*.-              'fill character

Troubleshooting

Issue	Cause	Solution
Hiding zeros affects totals	Changed to “”	Use `0;-0;;@` to hide only the display
Date/time confusion	Different interpretations of m/mm/mmm	Check the preview and context
K/M abbreviation misunderstanding	Unclear basis	State 1K=1000 in the legend

Reference: Microsoft Support — Create a custom number format, Format numbers as dates or times

VLOOKUP 두 값 연결: 도우미열·CHOOSE·XLOOKUP 완전 정복

Excel Custom Number Formats: Display Numbers, Dates, and Text Cleanly