How to Highlight Every Other Row in Excel

Microsoft Excel offers an inbuilt functionality to highlight rows as per the user's needs. To highlight rows in Excel, you can use the Highlight Cells action. The highlight rows in Excel feature is a simple way to make the document more interesting to look at. This tool comes in handy for showing the difference between rows of data points. But, highlighting the odd-numbered rows in Microsoft Excel can be tricky and not so easy for the beginner. However, it can become very easy for you to shade alternate rows. We will explain it step by step.

Let's get started:

1. Select the rows you want to format.
2. Click Home from the main menu and then click on Format as Table.
3. Select a table style that has alternate row shading from the Table dialog box.
4. Now, if you want to change the shading from rows to columns, select the table and click on Design from the table style options group. Then, deselect the Banded Rows box from the drop-down menu and select the Banded Columns box.

Navigate to Banded Columns feature in Excel

If you want to keep your table layout, but don't need its functionality, you can convert it to a range of data. If you're adding color rows/columns and have banding, this won't be replicated automatically. However, you can re-create the formatting by copying rows or columns with alternate formatting with Format Painter.

Use conditional formatting to apply banded rows or columns

You can also use a conditional formatting rule to apply different formatting to specific rows or columns. Here's how we can do it:

• On the worksheet, do one of the following:
  • If you want to apply formatting on some cells, then select the range of cells or rows. And if you want to apply the formatting to the whole document, then select the whole document by pressing Ctrl+A.
• Go to Home > Conditional Formatting > New Rule. It will open the formatting rule dialog box.

Setup a new rule for Conditional Formatting

• In the "Select a Rule Type" section, select the "Use a formula to determine which cells to format" option.
• Write the following formula in the "Edit the Rule Description" section to apply color to alternate rows.

=MOD(ROW(),2)=0

To apply color to alternate columns, type this formula: =MOD(COLUMN(),2)=0.

These formulas determine whether a row or column is even or odd-numbered and accordingly apply the desired formatting.

New Formatting Rule Dialog

1. Click Format.
2. In the Format Cells box, click Fill.
3. Pick a color and click OK.
4. You can preview your choice under Sample and click OK or pick another color.

By implementing the following steps, you will see the alternate rows highlighted.

IronXL: C# Excel Library

IronXL is a .NET Excel library that provides developers with a set of tools to work with Excel. The library can be used to read, write, and modify Excel files and sheets. It can also be used to convert between different file formats like XML, JSON, HTML, and CSV. The IronXL library has been developed in C# programming language and it is open-source, which means developers are free to use it in their applications. The IronXL Excel library can be used in any .NET project that needs access to Microsoft Office Excel. Developers can use IronXL in their projects without the need to install Office on their computer or set up a developer license from Microsoft.

The IronXL Excel library provides the following features:

• A broad set of functions, including data manipulation, data export, data import, and importing from other sources.
• Support for all the latest versions of Microsoft Excel.
• Support for Excel's most popular file formats (.xlsx).
• Support for cell formatting such as text alignment, font size, color, borders, etc.
• Ability to control the way that cells are displayed in an Excel workbook (e.g. gridlines).

We can do conditional formatting using IronXL. To use IronXL, you have to install the IronXL library in your C# project. After installing the library, you have to add the IronXL namespace. Write the following line of code on top of your program file:

using IronXL;
using IronXL.Formatting;
using IronXL.Formatting.Enums;
using IronXL.Styles;

using IronXL;
using IronXL.Formatting;
using IronXL.Formatting.Enums;
using IronXL.Styles;

Imports IronXL
Imports IronXL.Formatting
Imports IronXL.Formatting.Enums
Imports IronXL.Styles

After that, add the following lines of code to your main function:

// Load the Excel workbook
WorkBook workbook = WorkBook.Load("test.xlsx");

// Access the default worksheet
WorkSheet sheet = workbook.DefaultWorkSheet;

// Create a specific conditional formatting rule for values less than 8
ConditionalFormattingRule rule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8");

// Set different style options for the rule
rule.FontFormatting.IsBold = true;
rule.FontFormatting.FontColor = "#123456";
rule.BorderFormatting.RightBorderColor = "#ffffff";
rule.BorderFormatting.RightBorderType = BorderType.Thick;
rule.PatternFormatting.BackgroundColor = "#54bdd9";
rule.PatternFormatting.FillPattern = FillPattern.Diamonds;

// Add the formatting rule to a specific region in the worksheet
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule);

// Create another conditional formatting rule for values between 7 and 10
ConditionalFormattingRule rule1 = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10");

// Set additional style options for the new rule
rule1.FontFormatting.IsItalic = true;
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single;

// Add the second formatting rule to another region in the worksheet
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1);

// Save the updated workbook
workbook.SaveAs("ApplyConditionalFormatting.xlsx");

// Load the Excel workbook
WorkBook workbook = WorkBook.Load("test.xlsx");

// Access the default worksheet
WorkSheet sheet = workbook.DefaultWorkSheet;

// Create a specific conditional formatting rule for values less than 8
ConditionalFormattingRule rule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8");

// Set different style options for the rule
rule.FontFormatting.IsBold = true;
rule.FontFormatting.FontColor = "#123456";
rule.BorderFormatting.RightBorderColor = "#ffffff";
rule.BorderFormatting.RightBorderType = BorderType.Thick;
rule.PatternFormatting.BackgroundColor = "#54bdd9";
rule.PatternFormatting.FillPattern = FillPattern.Diamonds;

// Add the formatting rule to a specific region in the worksheet
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule);

// Create another conditional formatting rule for values between 7 and 10
ConditionalFormattingRule rule1 = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10");

// Set additional style options for the new rule
rule1.FontFormatting.IsItalic = true;
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single;

// Add the second formatting rule to another region in the worksheet
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1);

// Save the updated workbook
workbook.SaveAs("ApplyConditionalFormatting.xlsx");

' Load the Excel workbook
Dim workbook As WorkBook = WorkBook.Load("test.xlsx")

' Access the default worksheet
Dim sheet As WorkSheet = workbook.DefaultWorkSheet

' Create a specific conditional formatting rule for values less than 8
Dim rule As ConditionalFormattingRule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8")

' Set different style options for the rule
rule.FontFormatting.IsBold = True
rule.FontFormatting.FontColor = "#123456"
rule.BorderFormatting.RightBorderColor = "#ffffff"
rule.BorderFormatting.RightBorderType = BorderType.Thick
rule.PatternFormatting.BackgroundColor = "#54bdd9"
rule.PatternFormatting.FillPattern = FillPattern.Diamonds

' Add the formatting rule to a specific region in the worksheet
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule)

' Create another conditional formatting rule for values between 7 and 10
Dim rule1 As ConditionalFormattingRule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10")

' Set additional style options for the new rule
rule1.FontFormatting.IsItalic = True
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single

' Add the second formatting rule to another region in the worksheet
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1)

' Save the updated workbook
workbook.SaveAs("ApplyConditionalFormatting.xlsx")

We can define conditional formatting rules using the CreateConditionalFormattingRule() function. It is very easy to edit the font styles and border styling using IronXL. You can format the rows by using the row function. And, in the parameter, you will give the row number and formatting you want to apply. You can select highlight every nth row. You can see more details in our code example about conditional formatting support.

IronXL is free for development. However, there will be a watermark in the development phase. You can try it for free in production. You can activate the IronXL free trial without any payment or card information. After that, you can purchase it. There are different price plans which you can choose according to your needs, visit this licensing page for more information.