Display Excel Values in Korean 100 Million Won Units: Show the Remainder as Decimals (12.3456억)
When Korean won amounts in a report appear as long numbers such as 1,234,567,890, they can quickly become tiring to read. In practice, it is very common to display values only in 100 million won units (억), with the remainder shown as decimals of that unit, such as 12.3456억. This guide covers everything from displaying values in 억 units to using them in PivotTables and charts.
Quick Fix — Finish in 3 Minutes (Display Excel Values in 억 Units)
Goal: Keep the original Korean won values in column A and display values such as 12.3456억 in column B.
- Create a display column (column B) (recommended)
B2: =A2/100000000 - Apply a custom format to column B
Select column B →Ctrl + 1→ Number → Custom- To the nearest 10,000 won (4 decimal places):
0.0000"억" - To the nearest 1 million won (2 decimal places):
0.00"억"
- To the nearest 10,000 won (4 decimal places):
- If you do not want rounding (round the actual value down)
=ROUNDDOWN(A2/100000000,4)
Why Formatting Alone Cannot Cleanly Display 100 Million Won Units
In Excel custom number formats, commas (,) can scale down values, but they work only in multiples of 1,000. Therefore, a unit such as 100 million won (100,000,000), which is not an exact power of 1,000, is difficult to scale accurately with formatting alone. The safest approach is to divide by 100 million in a display column, then use the 억 suffix and the required number of decimal places.
Practical Example — Create and Verify 12.3456억
Sample Data (Copy and Paste)
| Item | Amount (KRW) |
|---|---|
| Revenue | 1234567890 |
| Expenses | 450000000 |
| Profit | 784567890 |
| Refund (Negative) | -125670000 |
| Zero Value | 0 |
| Blank |
1) Divide by 100 Million (Display Column)
If column A contains the original values, enter the following formula in column B.
=IF(A2="","",A2/100000000)2) How to Choose Decimal Places (10,000 Won / 1 Million Won)
| Decimal Places in 억 Units | Value of One Increment | Recommended Use |
|---|---|---|
| 1 digit | 10 million KRW | High-level summaries |
| 2 digits | 1 million KRW | Management or summary reports |
| 3 digits | 100,000 KRW | Settlement statements |
| 4 digits | 10,000 KRW | Practical standard (recommended) |
3) Set a Rounding, Round-Down, or Truncation Policy
- Round:
=ROUND(A2/100000000,4) - Round down:
=ROUNDDOWN(A2/100000000,4) - Truncate:
=TRUNC(A2/100000000,4)
4) Formats for Zero Values, Blanks, and Negative Values (4 Sections)
Example custom format (positive;negative;zero;text):
0.0000"억";(0.0000"억");"-";@How to Display 억 Units in PivotTables and Charts
PivotTable: Add an 억 Helper Column to the Source Data
- Add an
Amount_억column to the source table:=[@금액]/100000000 - Add
Amount_억to the Values area of the PivotTable. - In Value Field Settings → Number Format, specify
0.0000"억".
Charts: Format Only When Data Is Already in 억 Units; Otherwise, Use a Helper Column
To use exact 100 million won units in a chart, the safest approach is to prepare the data in 억 units with a helper column.
Alternative Method and Precautions Checklist
Alternative 1) Display as “억 + 만” Text
=IF(A2="","", INT(A2/100000000)&"억 "&TEXT(INT(MOD(A2,100000000)/10000),"0")&"만")Note: The result is text, so totals and sorting will not work as they do for numbers.
Troubleshooting
| Issue | Cause | Solution |
|---|---|---|
| Totals look wrong | You changed the display but assumed the value changed | Keep separate source (KRW) and display (억) columns |
| Decimal meaning is unclear | The unit for each decimal place is not clearly defined | Use a decimal-place reference table to set the policy |
| Negative round-down results differ from expectations | INT and TRUNC are used interchangeably | Use ROUNDDOWN and TRUNC according to their distinct purposes |
| PivotTable formatting is reset | The format was not fixed in Value Field Settings | Set it in Value Field Settings → Number Format |
| Division does not work | The number is stored as text | Convert it with VALUE or NUMBERVALUE |
Related Posts (Internal Links)
- Complete Guide to Excel Custom Number Formats: Cleanly Display Numbers, Dates, and Text
- Calculate Integers, Truncation, and Remainders with INT, TRUNC, MOD, and QUOTIENT
- Complete Guide to PivotTables, PivotCharts, and Slicers
Subscribe for the next post, which will cover practical ways to display even larger values using both 조 and 억 units.
Wonderful goods from you, man. I have understand your stuff previous to and you are just extremely fantastic.
I really like what you’ve acquired here, certainly like what you are stating and the way in which
you say it. You make it enjoyable and you still take care of to keep it smart.
I can not wait to read far more from you. This is actually a wonderful web site.
We stumbled over here coming from a different page and thought I
might check things out. I like what I see so i
am just following you. Look forward to checking out your web page for a second time.