IronXL How-Tos Set Cell Data Formats How to Set Cell Data Formats Chaknith Bin Updated:July 28, 2025 Data formatting and number formatting in Excel allow you to control how numbers, dates, times, and other data are displayed in cells, enhancing readability and ensuring data accuracy. With data formats, you can present information in specific formats like percentages or currencies. Similarly, number formats let you customize decimal places and display options. The IronXL library allows you to set data formats or number formats in C#. This library simplifies the process of creating, formatting, and manipulating Excel files programmatically, making it a valuable tool for data handling and presentation tasks in C# applications. How to Set Cell Data Formats Download the C# library to set data format Open existing or create a new Excel spreadsheet Set the FormatString property of the cell, column, row, or range to the desired data format Use the built-in data format with BuiltinFormats class Export the edited spreadsheet in various file types Get started with IronXL Start using IronXL in your project today with a free trial. First Step: Start for Free Set Cell Data Formats Example The FormatString property can be accessed through both cells and ranges. Therefore, it is possible to set data formats for individual cells, columns, rows, and any selected range. :path=/static-assets/excel/content-code-examples/how-to/set-cell-data-format.cs using IronXL; using IronXL.Formatting; using System; using System.Linq; // Create a new workbook WorkBook workBook = WorkBook.Create(); WorkSheet workSheet = workBook.DefaultWorkSheet; // Set the data format to 12300.00% workSheet["A1"].Value = 123; workSheet["A1"].FormatString = BuiltinFormats.Percent2; // Set the data format to 123.0000 workSheet["A2"].Value = 123; workSheet["A2"].FormatString = "0.0000"; // Set data display format to range DateTime dateValue = new DateTime(2020, 1, 1, 12, 12, 12); workSheet["A3"].Value = dateValue; workSheet["A4"].Value = new DateTime(2022, 3, 3, 10, 10, 10); workSheet["A5"].Value = new DateTime(2021, 2, 2, 11, 11, 11); IronXL.Range range = workSheet["A3:A5"]; // Set the data format to 1/1/2020 12:12:12 range.FormatString = "MM/dd/yy h:mm:ss"; workBook.SaveAs("dataFormats.xlsx"); Imports IronXL Imports IronXL.Formatting Imports System Imports System.Linq ' Create a new workbook Private workBook As WorkBook = WorkBook.Create() Private workSheet As WorkSheet = workBook.DefaultWorkSheet ' Set the data format to 12300.00% Private workSheet("A1").Value = 123 Private workSheet("A1").FormatString = BuiltinFormats.Percent2 ' Set the data format to 123.0000 Private workSheet("A2").Value = 123 Private workSheet("A2").FormatString = "0.0000" ' Set data display format to range Private dateValue As New DateTime(2020, 1, 1, 12, 12, 12) Private workSheet("A3").Value = dateValue Private workSheet("A4").Value = New DateTime(2022, 3, 3, 10, 10, 10) Private workSheet("A5").Value = New DateTime(2021, 2, 2, 11, 11, 11) Private range As IronXL.Range = workSheet("A3:A5") ' Set the data format to 1/1/2020 12:12:12 range.FormatString = "MM/dd/yy h:mm:ss" workBook.SaveAs("dataFormats.xlsx") $vbLabelText $csharpLabel Set Cell Value as String When setting the value in IronXL, use StringValue instead of Value to directly assign the exact value to the cell without automatic conversion. This usage is like placing an apostrophe before the cell value in Excel. :path=/static-assets/excel/content-code-examples/how-to/write-excel-net-assign-stringvalue.cs // Assign value as string workSheet["A1"].StringValue = "4402-12"; ' Assign value as string workSheet("A1").StringValue = "4402-12" $vbLabelText $csharpLabel Use Builtin Formats Example The IronXL library offers a variety of predefined format strings that can be accessed through the IronXL.Formatting.BuiltinFormats class for formatting Excel cells. This allows you to customize how data is displayed in your Excel spreadsheets. :path=/static-assets/excel/content-code-examples/how-to/set-cell-data-format-builtin-formats.cs using IronXL; using IronXL.Formatting; // Create a new workbook WorkBook workBook = WorkBook.Create(); WorkSheet workSheet = workBook.DefaultWorkSheet; // Use builtin formats workSheet["A1"].Value = 123; workSheet["A1"].FormatString = BuiltinFormats.Accounting0; workBook.SaveAs("builtinDataFormats.xlsx"); Imports IronXL Imports IronXL.Formatting ' Create a new workbook Private workBook As WorkBook = WorkBook.Create() Private workSheet As WorkSheet = workBook.DefaultWorkSheet ' Use builtin formats Private workSheet("A1").Value = 123 Private workSheet("A1").FormatString = BuiltinFormats.Accounting0 workBook.SaveAs("builtinDataFormats.xlsx") $vbLabelText $csharpLabel Available Builtin Data Formats For the Duration type, input values are represented in days. For example, 1 day is equal to 24 hours, and 1.05 days is equivalent to 25 hours and 12 minutes. Here are the available format types: Description General: The default format that displays numbers as they are without any specific formatting. Duration1: Displays duration values in minutes and seconds, like "mm:ss." Number input into the Value property will be considered as days. Duration2: Displays duration values in hours, minutes, and seconds, like "[h]:mm:ss." Duration3: Displays duration values in minutes, seconds, and milliseconds, like "mm:ss.0." Accounting0: Formats accounting values without decimal digits, using parentheses for negative values, like "#,##0_);(#,##0)." Accounting0Red: Formats accounting values without decimal digits, using red color for negative values, like "#,##0_);Red." Accounting2: Formats accounting values with two decimal digits, using parentheses for negative values, like "#,##0.00;(#,##0.00)." Accounting2Red: Formats accounting values with two decimal digits, using red color for negative values, like "#,##0.00_);Red." Time1: Formats time values in 12-hour clock format with AM/PM, like "h:mm AM/PM." Time2: Formats time values in 12-hour clock format with seconds and AM/PM, like "h:mm:ss AM/PM." Time3: Formats time values in 24-hour clock format without seconds, like "h:mm." Time4: Formats time values in 24-hour clock format with seconds, like "h:mm:ss." ShortDate: Formats date values in a short format, like "m/d/yy." ShortDateAndTime: Formats date and time values in a short format, like "m/d/yy h:mm." LongDate1: Formats date values in a long format with month, day, and year, like "d-mmm-yy." LongDate2: Formats date values in a long format with day and abbreviated month, like "d-mmm." LongDate3: Formats date values in a long format with abbreviated month and year, like "mmm-yy." Fraction1: Formats fractional values with one digit, like "# ?/?." Fraction2: Formats fractional values with two digits, like "# ??/??." Scientific1: Formats numbers in scientific notation with one digit after the dot, like "##0.0E+0." Scientific2: Formats numbers in scientific notation with two digits after the dot, like "0.00E+00." Percent: Formats percentage values without decimal digits, like "0%." Percent2: Formats percentage values with two decimal digits, like "0.00%." Currency0: Formats currency values without decimal digits, using parentheses for negative values, like "$#,##0_);($#,##0)." Currency0Red: Formats currency values without decimal digits, using red color for negative values, like "$#,##0_);Red." Currency2: Formats currency values with two decimal digits, using parentheses for negative values, like "$#,##0.00;($#,##0.00)." Currency2Red: Formats currency values with two decimal digits, using red color for negative values, like "$#,##0.00_);Red." Thousands0: Formats numbers with thousands separator and no decimal digits, like "#,##0." Thousands2: Formats numbers with thousands separator and two decimal digits, like "#,##0.00." Number0: Formats numbers without decimal digits, like "0." Number2: Formats numbers with two decimal digits, like "0.00." Text: Formats text values as plain text, like "@" (no specific formatting applied). Frequently Asked Questions What is the purpose of setting cell data formats in Excel? Setting cell data formats in Excel allows you to control how numbers, dates, times, and other data are displayed, enhancing readability and ensuring data accuracy. It enables presenting information in specific formats like percentages or currencies and customizing decimal places and display options. How can a C# library help in setting cell data formats without using Interop? IronXL simplifies the process of creating, formatting, and manipulating Excel files programmatically in C#. It allows setting data formats or number formats without using Interop, making it a valuable tool for data handling and presentation tasks in C# applications. How do you set a custom data format for a cell using a C# library? To set a custom data format for a cell using IronXL, load an Excel workbook, access the desired worksheet, and set the FormatString property of the cell to the desired format. For example, to format a number as a percentage with two decimal places: sheet["A1"].FormatString = "0.00%". What are BuiltinFormats in a C# library for Excel? BuiltinFormats in IronXL are predefined format strings that can be used to format Excel cells. They offer a variety of formats such as accounting, time, date, scientific, and percentage formats, which simplify the customization of data display in Excel spreadsheets. How do you preserve leading zeros in a cell using a C# library? To preserve leading zeros in a cell using IronXL, use the StringValue property instead of Value when setting the cell's value. This approach prevents automatic conversion, allowing the leading zeros to be retained, similar to placing an apostrophe before the cell value in Excel. Can you use a C# library to format cells with accounting styles? Yes, IronXL can format cells with accounting styles using the BuiltinFormats class. For instance, you can set a cell to an accounting format with two decimal places using: sheet["C3"].FormatString = IronXL.Formatting.BuiltinFormats.Accounting2. What are some examples of duration formats available in a C# library for Excel? IronXL offers several duration formats, such as Duration1 for minutes and seconds ("mm:ss"), Duration2 for hours, minutes, and seconds ("[h]:mm:ss"), and Duration3 for minutes, seconds, and milliseconds ("mm:ss.0"). How can you set a cell to display currency values with red color for negatives using a C# library? To display currency values with red color for negatives, use the BuiltinFormats class and set the FormatString property to a currency format like Currency0Red or Currency2Red. For example: sheet["B4"].FormatString = IronXL.Formatting.BuiltinFormats.Currency2Red. What is the difference between Number0 and Number2 formats in a C# library for Excel? The Number0 format displays numbers without decimal digits ("0"), while the Number2 format displays numbers with two decimal digits ("0.00"). These formats help in customizing the precision of numerical data in Excel sheets. How do you save changes in an Excel workbook after formatting cells using a C# library? After formatting cells in an Excel workbook using IronXL, save the changes by calling the SaveAs method on the WorkBook object, specifying the file name and format to save the updated workbook. Chaknith Bin Chat with engineering team now Software Engineer Chaknith is the Sherlock Holmes of developers. It first occurred to him he might have a future in software engineering, when he was doing code challenges for fun. His focus is on IronXL and IronBarcode, but he takes pride in helping customers with every product. Chaknith leverages his knowledge from talking directly with customers, to help further improve the products themselves. His anecdotal feedback goes beyond Jira tickets and supports product development, documentation and marketing, to improve customer’s overall experience.When he isn’t in the office, he can be found learning about machine learning, coding and hiking. Ready to Get Started? Free NuGet Download Total downloads: 1,487,525 View Licenses