The Excel formula to convert a number to a Roman numeral is =ROMAN(number), and to convert a Roman numeral back to a number, use =ARABIC("MMXXIV"). For example, =ROMAN(2024) returns MMXXIV, and =ARABIC("MMXXIV") returns 2024.
What the ROMAN Function Does
The ROMAN function is built into Excel and converts an Arabic numeral (0 to 3999) into a Roman numeral text string. It takes two arguments: the number and an optional form argument that controls the simplification level of the output. The syntax is =ROMAN(number, [form]).
The form argument can be 0 (classic, most concise) through 4 (simplified), or TRUE/FALSE. A form of 0 or omitted gives the standard classic form (e.g., ROMAN(499) returns CDXCIX). Higher forms produce more simplified, less traditional versions, such as ROMAN(499,4) returning LDVLIV—a form that is rarely used but demonstrates the flexibility. For most purposes, you should use ROMAN(number) without the second argument.
How to Use ROMAN and ARABIC in Excel
To convert a number in cell A1 to a Roman numeral, enter =ROMAN(A1) in another cell. To convert a Roman numeral in cell B1 (e.g., “MMXXIV”) back to a number, use =ARABIC(B1). The ARABIC function is available in Excel 2013 and later, including Microsoft 365.
Here are a few practical examples:
=ROMAN(2024)→MMXXIV=ROMAN(1999)→MCMXCIX=ARABIC("MCMXCIX")→1999=ARABIC("MMXXIV")→2024
If you have a list of numbers in A2:A10, you can drag the formula down from B2 to B10 to get the Roman numerals for all of them. Similarly, if you have Roman numerals in B2:B10, use =ARABIC(B2) in C2 and copy down.
Choosing the Right Form Argument
The optional form argument in ROMAN lets you choose how “simplified” the output is. The default (0) uses the classic Roman numeral rules, which are the most recognizable. Forms 1 through 4 relax the subtractive notation rules, producing longer but sometimes more readable strings. For example:
=ROMAN(1999,0)→MCMXCIX(classic)=ROMAN(1999,1)→MLMVLIV(simplified)=ROMAN(1999,2)→MVMIV(more simplified)=ROMAN(1999,3)→MVMIV(same as form 2 for this number)=ROMAN(1999,4)→MIM(very simplified)
In practice, you will almost always want the default form 0, because it matches the standard Roman numeral system taught in schools. The higher forms are included for historical or specialized use, but they can produce results that are not considered correct by purists.
Common Problems and Limitations
The ROMAN function only works for numbers between 0 and 3999. If you try =ROMAN(4000), Excel returns a #VALUE! error. This is because traditional Roman numerals do not have a standard representation for numbers beyond 3999 (though some use a vinculum or overline to indicate multiplication by 1000). Similarly, ARABIC only accepts valid Roman numeral strings; if you pass an invalid one like “IIII” (which is non-standard), it will return #VALUE!.
Another common issue is that ARABIC is case-insensitive, so it accepts both uppercase and lowercase letters. For example, =ARABIC("mmxxiv") returns 2024. However, it does not accept spaces or other characters, so a string like “M M” will error.
Comparison with Other Methods
If you are using an older version of Excel that does not have ARABIC (pre-2013), you can still convert Roman numerals to numbers using a custom VBA function or a complex array formula, but these are error-prone. The built-in functions are the cleanest solution. For Google Sheets, the ROMAN function exists but ARABIC does not. In Google Sheets, you can use a custom function or a script, but the easiest cross-platform approach is to use Excel’s ARABIC if you are in Excel, and for Sheets, consider using a formula like =ARRAYFORMULA(SUM(...)) with a lookup table, though that is messy.
For users who need to generate Roman numerals for a sequence (e.g., 1 to 10), you can combine ROMAN with ROW(): =ROMAN(ROW()) in row 1 returns I, in row 2 returns II, and so on. This is handy for creating outlines or lists.
Practical Use Cases
Roman numerals are often used in outlines, movie titles, book chapters, and clock faces. In Excel, you might use ROMAN to format a column of chapter numbers, or to create a list of legislative items. The ARABIC function is useful when you import data that contains Roman numerals and need to sort or calculate with them. For example, if you have a column of movie titles with release years in Roman numerals, you can convert them to numbers for sorting.
Here is a step-by-step example:
- Put the number 2024 in cell A1.
- In B1, enter
=ROMAN(A1). The result isMMXXIV. - In C1, enter
=ARABIC(B1). The result is2024.
This round-trip conversion works for any number 0-3999.
Frequently Asked Questions
Why does my ROMAN formula return #VALUE!?
This usually happens when the number is outside the valid range (0 to 3999) or if the number is not a numeric value. Check that the cell contains a number, not text. If you need to convert a number stored as text, use =ROMAN(VALUE(A1)).
Can I convert a Roman numeral to a number in Google Sheets?
Google Sheets does not have a built-in ARABIC function. However, you can use a script or a custom formula. One workaround is to use a helper column with a lookup table for each Roman numeral symbol, but that is complex. For most users, it is easier to do the conversion in Excel or use an online converter.
How do I convert a whole column of numbers to Roman numerals?
Enter =ROMAN(A1) in the first cell of the adjacent column, then double-click the fill handle (the small square at the bottom-right of the cell) to copy the formula down. This works for both Windows and Mac.
Does ROMAN work with decimal numbers?
No. The ROMAN function truncates decimals to integers. For example, =ROMAN(3.9) returns IV (because 3.9 becomes 3). If you need to round, use =ROMAN(ROUND(A1,0)).
Is there a way to use Roman numerals in Excel for dates?
Yes, you can combine ROMAN with date functions. For instance, to display the month as a Roman numeral, use =ROMAN(MONTH(A1)) where A1 contains a date. For full dates, you might concatenate: =ROMAN(DAY(A1))&"/"&ROMAN(MONTH(A1))&"/"&ROMAN(YEAR(A1)), but note that years beyond 3999 will error.
Can I use ROMAN in conditional formatting?
Yes, you can use ROMAN in a conditional formatting rule. For example, to highlight cells that convert to a Roman numeral containing “X”, you could use a formula like =ISNUMBER(SEARCH("X",ROMAN(A1))). This works in Excel for Microsoft 365.
Related
- Excel Bullet Journal Template: Setup & Free Options
- Excel Built-In Repair Tool: What It Does and How to Use It
- Excel Budget Audit Template: How to Build One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter A-Z in Excel: Sort a List the Right WayTo put Excel entries in alphabetical order, select a cell in the column and choose Home > Sort…
- How to Filter Greater Than in Excel: Step-by-Step GuideTo show only values above a threshold in Excel, open the column’s filter menu and choose Number Filters…
- Excel Table Filter by Multiple Colors: What WorksIn Excel for Microsoft 365 on Windows, a table’s color filter can show rows for one selected fill…
- How to Add a New Line in Excel CONCATENATETo add a new line between text values joined with Excel’s CONCATENATE function, insert CHAR(10) between the cell…
- Excel Min Date Formula: Find Earliest DateThe simplest Excel formula to find the minimum (earliest) date in a range is =MIN(A2:A100), assuming your dates…
- C++ String Functions Cheat Sheet: Uses and ExamplesA C++ string functions cheat sheet centers on std::string: use size() or length() to count bytes, find() to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.