Top/Bottom in Conditional Formatting

24 Jul 202622 minutes to read

Top/Bottom rules are conditional formatting rules that help you quickly identify and emphasize the highest or lowest values within a range of cells in a worksheet.

Format Top or Bottom Values

The Top/Bottom rule highlights the top or bottom ranked cells in a data range. A Top/Bottom conditional formatting rule is created and customized using the ITopBottom interface in XlsIO.

The properties of the ITopBottom interface are:

  • Type – Specifies whether the rank is evaluated from the top or bottom, using the ExcelCFTopBottomType enumerator (Top or Bottom).
  • Percent – Specifies whether the Rank value is treated as a percentage of the range (true) or as an absolute number of cells (false).
  • Rank – Specifies the maximum number (or percentage) of cells to be highlighted.

Top/Bottom ‘n’ Rank Values

The following code example illustrates how to format the top 10 rank values from the given data range using the ITopBottom Type and Rank properties in XlsIO.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	//Applying conditional formatting to "N6:N35".
	IConditionalFormats formats = worksheet.Range["N6:N35"].ConditionalFormats;
	IConditionalFormat format = formats.AddCondition();

	//Applying top or bottom rule in the conditional formatting.
	format.FormatType = ExcelCFType.TopBottom;
	ITopBottom topBottom = format.TopBottom;

	//Set type as Top for TopBottom rule.
	topBottom.Type = ExcelCFTopBottomType.Top;

	//Set rank value for the TopBottom rule.
	topBottom.Rank = 10;

	//Set color for Conditional Formattting.
	format.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(51, 153, 102);

	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/TopToBottomRank.xlsx"));
	#endregion
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
   
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];
  
  //Applying conditional formatting to "N6:N35".
  IConditionalFormats conditionalFormats1 = worksheet.Range["N6:N35"].ConditionalFormats;
  IConditionalFormat conditionalFormat1 = conditionalFormats1.AddCondition();

  //Applying top or bottom rule in the conditional formatting.
  conditionalFormat1.FormatType = ExcelCFType.TopBottom;
  ITopBottom topBottom1 = conditionalFormat1.TopBottom;

  //Set type as Top for TopBottom rule.
  topBottom1.Type = ExcelCFTopBottomType.Top;

  //Set rank value for the TopBottom rule.
  topBottom1.Rank = 10;

  //Set solid color conditional formatting for TopBottom rule.
  conditionalFormat1.FillPattern = ExcelPattern.Solid;
  conditionalFormat1.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(51, 153, 102);


  //Applying conditional formatting to "M6:M35".
  IConditionalFormats conditionalFormats2 = worksheet.Range["M6:M35"].ConditionalFormats;
  IConditionalFormat conditionalFormat2 = conditionalFormats2.AddCondition();

  //Applying top or bottom rule in the conditional formatting.
  conditionalFormat2.FormatType = ExcelCFType.TopBottom;
  ITopBottom topBottom2 = conditionalFormat2.TopBottom;

  //Set type as Top for TopBottom rule.
  topBottom2.Type = ExcelCFTopBottomType.Top;

  //Set rank value for the TopBottom rule.
  topBottom2.Rank = 10;

  //Set gradient color conditional formatting for TopBottom rule.
  conditionalFormat2.FillPattern = ExcelPattern.Gradient;
  conditionalFormat2.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(130, 60, 12);
  conditionalFormat2.ColorRGB = Syncfusion.Drawing.Color.FromArgb(255, 255, 0);
  conditionalFormat2.GradientStyle = ExcelGradientStyle.Horizontal;
  conditionalFormat2.GradientVariant = ExcelGradientVariants.ShadingVariants_1;

  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
   
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)
  
  ' Applying conditional formatting to "N6:N35".
  Dim conditionalFormats1 As IConditionalFormats = worksheet.Range("N6:N35").ConditionalFormats
  Dim conditionalFormat1 As IConditionalFormat = conditionalFormats1.AddCondition()

  ' Applying top or bottom rule in the conditional formatting.
  conditionalFormat1.FormatType = ExcelCFType.TopBottom
  Dim topBottom1 As ITopBottom = conditionalFormat1.TopBottom

  ' Set type as Top for TopBottom rule.
  topBottom1.Type = ExcelCFTopBottomType.Top

  ' Set rank value for the TopBottom rule.
  topBottom1.Rank = 10

  ' Set solid color conditional formatting for TopBottom rule.
  conditionalFormat1.FillPattern = ExcelPattern.Solid
  conditionalFormat1.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(51, 153, 102)


  ' Applying conditional formatting to "M6:M35".
  Dim conditionalFormats2 As IConditionalFormats = worksheet.Range("M6:M35").ConditionalFormats
  Dim conditionalFormat2 As IConditionalFormat = conditionalFormats2.AddCondition()

  ' Applying top or bottom rule in the conditional formatting.
  conditionalFormat2.FormatType = ExcelCFType.TopBottom
  Dim topBottom2 As ITopBottom = conditionalFormat2.TopBottom

  ' Set type as Top for TopBottom rule.
  topBottom2.Type = ExcelCFTopBottomType.Top

  ' Set rank value for the TopBottom rule.
  topBottom2.Rank = 10

  ' Set gradient color conditional formatting for TopBottom rule.
  conditionalFormat2.FillPattern = ExcelPattern.Gradient
  conditionalFormat2.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(130, 60, 12)
  conditionalFormat2.ColorRGB = Syncfusion.Drawing.Color.FromArgb(255, 255, 0)
  conditionalFormat2.GradientStyle = ExcelGradientStyle.Horizontal
  conditionalFormat2.GradientVariant = ExcelGradientVariants.ShadingVariants_1

  ' Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to format top and bottom rank values in C# is present on this GitHub page.

By executing the program, you will get the Excel file as shown below.

Top or Bottom conditional format

NOTE

The ITopBottom Rank value should be in a range between 1 and 1000.

Top/Bottom ‘n’% Rank Values

The following code example illustrates how to format the bottom 50 percent of rank values from the given data range using the ITopBottom Type, Rank, and Percent properties in XlsIO.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	//Applying conditional formatting to "N6:N35".
	IConditionalFormats formats = worksheet.Range["N6:N35"].ConditionalFormats;
	IConditionalFormat format = formats.AddCondition();

	//Applying top or bottom rule in the conditional formatting.
	format.FormatType = ExcelCFType.TopBottom;
	ITopBottom topBottom = format.TopBottom;

	//Set type as Bottom for TopBottom rule.
	topBottom.Type = ExcelCFTopBottomType.Bottom;

	//Set true to Percent property for TopBottom rule.
	topBottom.Percent = true;

	//Set rank value for the TopBottom rule.
	topBottom.Rank = 50;

	//Set color for Conditional Formattting.
	format.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(51, 153, 102);

	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/Chart.xlsx"));
	#endregion
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];

  //Applying conditional formatting to "N6:N35".
  IConditionalFormats conditionalFormats1 = worksheet.Range["N6:N35"].ConditionalFormats;
  IConditionalFormat conditionalFormat1 = conditionalFormats1.AddCondition();

  //Applying top or bottom rule in the conditional formatting.
  conditionalFormat1.FormatType = ExcelCFType.TopBottom;
  ITopBottom topBottom1 = conditionalFormat1.TopBottom;

  //Set type as Bottom for TopBottom rule.
  topBottom1.Type = ExcelCFTopBottomType.Bottom;

  //Set true to Percent property for TopBottom rule.
  topBottom1.Percent = true;

  //Set rank value for the TopBottom rule.
  topBottom1.Rank = 50;

  //Set solid color conditional formatting for TopBottom rule.
  conditionalFormat1.FillPattern = ExcelPattern.Solid;
  conditionalFormat1.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(51, 153, 102);

  //Applying conditional formatting to "M6:M35".
  IConditionalFormats conditionalFormats2 = worksheet.Range["M6:M35"].ConditionalFormats;
  IConditionalFormat conditionalFormat2 = conditionalFormats2.AddCondition();

  //Applying top or bottom rule in the conditional formatting.
  conditionalFormat2.FormatType = ExcelCFType.TopBottom;
  ITopBottom topBottom2 = conditionalFormat2.TopBottom;

  //Set type as Top for TopBottom rule.
  topBottom2.Type = ExcelCFTopBottomType.Bottom;

  //Set true to Percent property for TopBottom rule.
  topBottom2.Percent = true;

  //Set rank value for the TopBottom rule.
  topBottom2.Rank = 20;

  //Set gradient color conditional formatting for TopBottom rule.
  conditionalFormat2.FillPattern = ExcelPattern.Gradient;
  conditionalFormat2.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(130, 60, 12);
  conditionalFormat2.ColorRGB = Syncfusion.Drawing.Color.FromArgb(255, 255, 0);
  conditionalFormat2.GradientStyle = ExcelGradientStyle.Horizontal;
  conditionalFormat2.GradientVariant = ExcelGradientVariants.ShadingVariants_1;

  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  ' Applying conditional formatting to "N6:N35".
  Dim conditionalFormats1 As IConditionalFormats = worksheet.Range("N6:N35").ConditionalFormats
  Dim conditionalFormat1 As IConditionalFormat = conditionalFormats1.AddCondition()

  ' Applying top or bottom rule in the conditional formatting.
  conditionalFormat1.FormatType = ExcelCFType.TopBottom
  Dim topBottom1 As ITopBottom = conditionalFormat1.TopBottom

  ' Set type as Bottom for TopBottom rule.
  topBottom1.Type = ExcelCFTopBottomType.Bottom

  ' Set true to Percent property for TopBottom rule.
  topBottom1.Percent = True

  ' Set rank value for the TopBottom rule.
  topBottom1.Rank = 50

  ' Set solid color conditional formatting for TopBottom rule.
  conditionalFormat1.FillPattern = ExcelPattern.Solid
  conditionalFormat1.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(51, 153, 102)

  ' Applying conditional formatting to "M6:M35".
  Dim conditionalFormats2 As IConditionalFormats = worksheet.Range("M6:M35").ConditionalFormats
  Dim conditionalFormat2 As IConditionalFormat = conditionalFormats2.AddCondition()

  ' Applying top or bottom rule in the conditional formatting.
  conditionalFormat2.FormatType = ExcelCFType.TopBottom
  Dim topBottom2 As ITopBottom = conditionalFormat2.TopBottom

  ' Set type as Bottom for TopBottom rule.
  topBottom2.Type = ExcelCFTopBottomType.Bottom

  ' Set true to Percent property for TopBottom rule.
  topBottom2.Percent = True

  ' Set rank value for the TopBottom rule.
  topBottom2.Rank = 20

  ' Set gradient color conditional formatting for TopBottom rule.
  conditionalFormat2.FillPattern = ExcelPattern.Gradient
  conditionalFormat2.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(130, 60, 12)
  conditionalFormat2.ColorRGB = Syncfusion.Drawing.Color.FromArgb(255, 255, 0)
  conditionalFormat2.GradientStyle = ExcelGradientStyle.Horizontal
  conditionalFormat2.GradientVariant = ExcelGradientVariants.ShadingVariants_1

  ' Saving the workbook
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to format top and bottom rank percentage in C# is present on this GitHub page.

By executing the program, you will get the Excel file as below

Top or Bottom conditional format

NOTE

The ITopBottom Rank value should be in a range between 1 and 100 when Percent is set to true.

Format Above or Below Average Values

The Above/Below Average rule highlights cells that contain values above or below the average of a data range. An Above/Below Average conditional formatting rule is created and customized using the IAboveBelowAverage interface in XlsIO.

The properties of IAboveBelowAverage are:

  • AverageType – Specifies the type of rule, using the ExcelCFAverageType enumerator: Above, Below, AboveStdDev, BelowStdDev, EqualOrAboveStdDev, or EqualOrBelowStdDev.
  • StdDevValue – Specifies the standard deviation number used when AverageType is one of the standard deviation values.

The following code example illustrates how to format a range with values that are below average using the IAboveBelowAverage AverageType property in XlsIO.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	//Applying conditional formatting to "M6:M35"
	IConditionalFormats formats = worksheet.Range["M6:M35"].ConditionalFormats;
	IConditionalFormat format = formats.AddCondition();

	//Applying above or below average rule in the conditional formatting
	format.FormatType = ExcelCFType.AboveBelowAverage;
	IAboveBelowAverage aboveBelowAverage = format.AboveBelowAverage;

	//Set AverageType as Below for AboveBelowAverage rule.
	aboveBelowAverage.AverageType = ExcelCFAverageType.Below;

	//Set color for Conditional Formattting.
	format.FontColorRGB = Syncfusion.Drawing.Color.FromArgb(255, 255, 255);
	format.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(166, 59, 38);

	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/AboveAndBelowAverage.xlsx"));
	#endregion
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Open("CFTemplate.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];

  //Applying conditional formatting to "M6:M35"
  IConditionalFormats formats = worksheet.Range["M6:M35"].ConditionalFormats;
  IConditionalFormat format = formats.AddCondition();

  //Applying above or below average rule in the conditional formatting
  format.FormatType = ExcelCFType.AboveBelowAverage;
  IAboveBelowAverage aboveBelowAverage = format.AboveBelowAverage;

  //Set AverageType as Below for AboveBelowAverage rule.
  aboveBelowAverage.AverageType = ExcelCFAverageType.Below;

  //Set color for Conditional Formattting.
  format.FontColorRGB = System.Drawing.Color.FromArgb(255, 255, 255);
  format.BackColorRGB = System.Drawing.Color.FromArgb(166, 59, 38);

  //Saves the Excel
  workbook.SaveAs("AboveBelowAverage.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Open("CFTemplate.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Applying conditional formatting to "M6:M35"
  Dim formats As IConditionalFormats = worksheet.Range("M6:M35").ConditionalFormats
  Dim format As IConditionalFormat = formats.AddCondition()

  'Applying above or below average rule in the conditional formatting
  format.FormatType = ExcelCFType.AboveBelowAverage
  Dim aboveBelowAverage As IAboveBelowAverage = format.AboveBelowAverage

  'Set AverageType as Below for AboveBelowAverage rule.
  aboveBelowAverage.AverageType = ExcelCFAverageType.Below

  'Set color for Conditional Formatting.
  format.FontColorRGB = Color.FromArgb(255, 255, 255)
  format.BackColorRGB = Color.FromArgb(166, 59, 38)

  'Saves the Excel
  workbook.SaveAs("AboveBelowAverage.xlsx")
End Using

A complete working example to format above and below average values in C# is present on this GitHub page.

By executing the program, you will get the Excel file as shown below.

Above or Below Average conditional format

Above or Below Standard Deviation Values

The following code example illustrates how to format a range with values above the standard deviation, using the IAboveBelowAverage AverageType and StdDevValue properties in XlsIO.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
	IWorksheet worksheet = workbook.Worksheets[0];

	//Applying conditional formatting to "M6:M35"
	IConditionalFormats formats = worksheet.Range["M6:M35"].ConditionalFormats;
	IConditionalFormat format = formats.AddCondition();

	//Applying above or below average rule in the conditional formatting
	format.FormatType = ExcelCFType.AboveBelowAverage;
	IAboveBelowAverage aboveBelowAverage = format.AboveBelowAverage;

	//Set AverageType as AboveStdDev for AboveBelowAverage rule.
	aboveBelowAverage.AverageType = ExcelCFAverageType.AboveStdDev;

	//Set value to StdDevValue property for AboveBelowAverage rule.
	aboveBelowAverage.StdDevValue = 1;

	//Set color for Conditional Formattting.
	format.FontColorRGB = Syncfusion.Drawing.Color.FromArgb(255, 255, 255);
	format.BackColorRGB = Syncfusion.Drawing.Color.FromArgb(166, 59, 38);

	#region Save
	//Saving the workbook
	workbook.SaveAs(Path.GetFullPath("Output/AboveAndBelowStandardDeviation.xlsx"));
	#endregion
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Open("CFTemplate.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];

  //Applying conditional formatting to "M6:M35"
  IConditionalFormats formats = worksheet.Range["M6:M35"].ConditionalFormats;
  IConditionalFormat format = formats.AddCondition();

  //Applying above or below average rule in the conditional formatting
  format.FormatType = ExcelCFType.AboveBelowAverage;
  IAboveBelowAverage aboveBelowAverage = format.AboveBelowAverage;

  //Set AverageType as AboveStdDev for AboveBelowAverage rule.
  aboveBelowAverage.AverageType = ExcelCFAverageType.AboveStdDev;

  //Set value to StdDevValue property for AboveBelowAverage rule.
  aboveBelowAverage.StdDevValue = 1;

  //Set color for Conditional Formattting.
  format.FontColorRGB = System.Drawing.Color.FromArgb(255, 255, 255);
  format.BackColorRGB = System.Drawing.Color.FromArgb(166, 59, 38);

  //Saves the Excel
  workbook.SaveAs("AboveBelowAverage.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Open("CFTemplate.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)
	
  'Applying conditional formatting to "M6:M35"
  Dim formats As IConditionalFormats = worksheet.Range("M6:M35").ConditionalFormats
  Dim format As IConditionalFormat = formats.AddCondition()

  'Applying above or below average rule in the conditional formatting
  format.FormatType = ExcelCFType.AboveBelowAverage
  Dim aboveBelowAverage As IAboveBelowAverage = format.AboveBelowAverage

  'Set AverageType as AboveStdDev for AboveBelowAverage rule.
  aboveBelowAverage.AverageType = ExcelCFAverageType.AboveStdDev

  'Set value to StdDevValue property for AboveBelowAverage rule.
  aboveBelowAverage.StdDevValue = 1

  'Set color for Conditional Formatting.
  format.FontColorRGB = Color.FromArgb(255, 255, 255)
  format.BackColorRGB = Color.FromArgb(166, 59, 38)

  'Saves the Excel
  workbook.SaveAs("AboveBelowAverage.xlsx")
End Using

A complete working example to format above and below standard deviation values in C# is present on this GitHub page.

By executing the program, you will get the Excel file as shown below.

Above or Below Standard Deviation conditional format

NOTE

The IAboveBelowAverage StdDevValue can be applied only if the AverageType is AboveStdDev or BelowStdDev. The StdDevValue value should be in a range between 1 and 3.