# Apply Styles, Formatting, and Conditional Formatting

Learn how to programmatically apply styles, formatting, and conditional formatting in C# .NET. Get started and see more from Document Solutions today.

## Content

This tutorial demonstrates how to programmatically control the formatting and visual presentation of Excel (.xslx) workbooks using C# and the .NET Excel API library, **Document Solutions for Excel .NET (DsExcel .NET)**.
You’ll learn how to:

* Create and reuse custom named styles.
* Apply currency and percentage number formats.
* Configure fonts, alignment, borders, and background fills.
* Add conditional formatting using data bars.
* Adjust column widths and row heights.
* Hide worksheet gridlines for a cleaner, report-ready layout.
* Save the formatted workbook as an Excel file.

## Prerequisites

Before you begin, make sure you have:

* **.NET SDK**
* **Document Solutions for Excel .NET (DsExcel .NET)**
* The **[DS.Documents.Excel](https://www.nuget.org/packages/DS.Documents.Excel)**[ NuGet package](https://www.nuget.org/packages/DS.Documents.Excel) installed in your project

Import the required namespace:

```csharp
using GrapeCity.Documents.Excel;
```

## 1) Create a Workbook and Add Sample Data

Start by creating a workbook and renaming the default worksheet.

```csharp
Workbook workbook = new Workbook();

IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Formatting";
```

Next, add sample income and expense data. The worksheet will also include formulas that calculate total income, total expenses, and the percentage of income spent.

```csharp
object[,] incomeData =
{
    { "ITEM", "AMOUNT" },
    { "Income 1", 2500 },
    { "Income 2", 1000 },
    { "Income 3", 250 },
    { "Other", 250 }
};

object[,] expenseData =
{
    { "ITEM", "AMOUNT" },
    { "Rent/mortgage", 800 },
    { "Electricity", 120 },
    { "Gas", 50 },
    { "Cell phone", 45 },
    { "Groceries", 500 },
    { "Car payment", 273 }
};

// Add section labels and data.
worksheet.Range["B2"].Value = "Income";
worksheet.Range["B9"].Value = "Expenses";

worksheet.Range["B3:C7"].Value = incomeData;
worksheet.Range["B10:C16"].Value = expenseData;

// Add total formulas.
worksheet.Range["B8"].Value = "Total";
worksheet.Range["C8"].Formula = "=SUM(C4:C7)";

worksheet.Range["B17"].Value = "Total";
worksheet.Range["C17"].Formula = "=SUM(C11:C16)";

// Add a summary section.
worksheet.Range["F2"].Value = "Income Spent";
worksheet.Range["F2:H2"].Merge();
worksheet.Range["F3:H3"].Merge();
```

Using range-based formulas such as `=SUM(C4:C7)` makes calculations easier to read and maintain than listing each cell separately.

## 2) Create and Reuse Custom Named Styles

DsExcel .NET allows you to create custom named styles and store them in the workbook’s **[Styles](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/Features/ApplyStyle#site_main_content-doc-content_title "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/Features/ApplyStyle#site_main_content-doc-content_title")**[ collection](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/Features/ApplyStyle#site_main_content-doc-content_title "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/Features/ApplyStyle#site_main_content-doc-content_title"). You can then apply the same style to multiple cells or ranges.

### Create a Currency Style

The currency style applies a currency number format and aligns values to the left.

```csharp
IStyle currencyStyle = workbook.Styles.Add("CustomCurrency");

currencyStyle.HorizontalAlignment = HorizontalAlignment.Left;
currencyStyle.VerticalAlignment = VerticalAlignment.Bottom;
currencyStyle.NumberFormat = "$#,##0.00";

currencyStyle.IncludeAlignment = true;
currencyStyle.IncludeNumber = true;
```

### Create a Heading Style

The heading style applies font settings, alignment, and a background fill.

```csharp
IStyle headingStyle = workbook.Styles.Add("CustomHeading");

headingStyle.HorizontalAlignment = HorizontalAlignment.Center;
headingStyle.VerticalAlignment = VerticalAlignment.Center;

headingStyle.Font.Name = "Century Gothic";
headingStyle.Font.Bold = true;
headingStyle.Font.Size = 11;
headingStyle.Font.Color = Color.White;

headingStyle.Interior.Color = Color.FromArgb(32, 61, 64);

headingStyle.IncludeAlignment = true;
headingStyle.IncludeFont = true;
headingStyle.IncludePatterns = true;
```

### Create a Percentage Style

The percentage style displays the calculated percentage using a larger, bold font and a percentage number format.

```csharp
IStyle percentStyle = workbook.Styles.Add("CustomPercent");

percentStyle.HorizontalAlignment = HorizontalAlignment.Center;
percentStyle.VerticalAlignment = VerticalAlignment.Center;

percentStyle.Font.Name = "Century Gothic";
percentStyle.Font.Bold = true;
percentStyle.Font.Size = 14;
percentStyle.Font.Color = Color.FromArgb(32, 61, 64);

percentStyle.NumberFormat = "0.00%";

percentStyle.IncludeAlignment = true;
percentStyle.IncludeFont = true;
percentStyle.IncludeNumber = true;
```

## 3) Apply Number Formats and Styles

Apply the custom currency style to the cells containing income and expense amounts.

```csharp
worksheet.Range["C4:C8,C11:C17"].Style = currencyStyle;
```

Apply the heading style to the income and expense table headers.

```csharp
worksheet.Range["B3,C3,B10,C10"].Style = headingStyle;
```

Next, calculate the percentage of total income that was spent and apply the percentage style.

```csharp
worksheet.Range["F3"].Formula = "=C17/C8";
worksheet.Range["F3"].Style = percentStyle;
```

Because the percentage number format is included in `percentStyle`, a separate assignment to the **[NumberFormat](https://developer.mescius.com/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IStyle.NumberFormat.html#GrapeCity_Documents_Excel_IStyle_NumberFormat_ "https://developer.mescius.com/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IStyle.NumberFormat.html#GrapeCity_Documents_Excel_IStyle_NumberFormat_")**[ property](https://developer.mescius.com/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IStyle.NumberFormat.html#GrapeCity_Documents_Excel_IStyle_NumberFormat_ "https://developer.mescius.com/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IStyle.NumberFormat.html#GrapeCity_Documents_Excel_IStyle_NumberFormat_") is not required.

## 4) Add Borders and Background Fills

You can also format individual ranges directly when creating a reusable named style is not necessary.
Add a bottom border below the income total row:

```csharp
IBorder incomeBorder =
    worksheet.Range["A8:D8"].Borders[BordersIndex.EdgeBottom];

incomeBorder.LineStyle = BorderLineStyle.Medium;
incomeBorder.Color = Color.FromArgb(32, 61, 64);
```

Add a bottom border below the expense total row:

```csharp
IBorder expenseBorder =
    worksheet.Range["A17:D17"].Borders[BordersIndex.EdgeBottom];

expenseBorder.LineStyle = BorderLineStyle.Medium;
expenseBorder.Color = Color.FromArgb(32, 61, 64);
```

Apply a dark background fill and white, bold text to the section headings:

```csharp
IRange sectionHeadings = worksheet.Range["B2,B9,F2"];

sectionHeadings.Interior.Color = Color.FromArgb(32, 61, 64);
sectionHeadings.Font.Color = Color.White;
sectionHeadings.Font.Bold = true;
sectionHeadings.HorizontalAlignment = HorizontalAlignment.Center;
sectionHeadings.VerticalAlignment = VerticalAlignment.Center;
```

## 5) Add Conditional Formatting with Data Bars

Conditional formatting helps readers compare values visually. In this example, a data bar rule is applied to the expense amounts.

```csharp
IDataBar dataBar =
    worksheet.Range["C11:C16"]
        .FormatConditions
        .AddDatabar();

dataBar.MinPoint.Type = ConditionValueTypes.LowestValue;
dataBar.MinPoint.Value = null;

dataBar.MaxPoint.Type = ConditionValueTypes.HighestValue;
dataBar.MaxPoint.Value = null;

dataBar.BarFillType = DataBarFillType.Gradient;
dataBar.BarColor.Color = Color.Red;
dataBar.ShowValue = true;
```

The length of each data bar is based on the corresponding expense value. Setting the **[ShowValue](https://developer.mescius.com/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IDataBar.ShowValue.html#GrapeCity_Documents_Excel_IDataBar_ShowValue "https://developer.mescius.com/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IDataBar.ShowValue.html#GrapeCity_Documents_Excel_IDataBar_ShowValue")** [property](https://developer.mescius.com/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IDataBar.ShowValue.html#GrapeCity_Documents_Excel_IDataBar_ShowValue "https://developer.mescius.com/document-solutions/dot-net-excel-api/api/online/DS.Documents.Excel/GrapeCity.Documents.Excel.IDataBar.ShowValue.html#GrapeCity_Documents_Excel_IDataBar_ShowValue") property to **true** displays both the original numeric value and the data bar in each cell.

## 6) Adjust the Worksheet Layout

Set column widths and row heights to make the report easier to read.

```csharp
worksheet.Range["B:B"].ColumnWidth = 22;
worksheet.Range["C:C"].ColumnWidth = 15;
worksheet.Range["F:H"].ColumnWidth = 14;

worksheet.Range["2:2"].RowHeight = 24;
worksheet.Range["9:9"].RowHeight = 24;
```

You can also hide worksheet gridlines to create a cleaner, report-style layout.

```csharp
worksheet.SheetView.DisplayGridlines = false;
```

## 7) Save the Workbook

Save the completed workbook as an `.xlsx` file.

```csharp
workbook.Save("FormattingAndConditionalFormatting.xlsx");
```

When you open the generated workbook in Excel, you’ll see:

* Formatted income and expense sections.
* Reusable currency, heading, and percentage styles.
* Summary rows with colored borders.
* Section headings with dark background fills and white text.
* Data bars that visually compare expense values.
* Adjusted column widths and row heights.
* A clean worksheet without gridlines.

The final worksheet looks as follows:
![Formatted Excel Table](https://cdn.mescius.io/document-site-files/images/6c089d65-4816-4591-8c0d-bb0bfb8c43df/image-20260710-162627-20260728.721de8.png)

## Next Steps

* Read the full documentation on [styles](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/Features/ApplyStyle "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/Features/ApplyStyle") and [conditional formatting](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/Features/ApplyConditionalFormatting "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/Features/ApplyConditionalFormatting").
* Explore the demos:
    * [Apply Style to a Range](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/applystyle "https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/applystyle")
    * [Conditional Formatting](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/conditionalformatting "https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/conditionalformatting")