Top/Bottom in Conditional Formatting

14 Oct 202423 minutes to read

Top/Bottom Rules are powerful tools for data analysis and presentation, enhancing the ability to quickly identify and emphasize the highest or lowest values within a range of cells in a worksheet.

Format Top or Bottom Values

Top/Bottom rule in conditional formatting is used to highlight the top or bottom ranked cells in a data range. Top/Bottom conditional formatting rule can be created and customized using the ITopBottom interface in XlsIO.

The properties of ITopBottom interface are:

  • Type - Specifies whether the rank is evaluated from the top or bottom.
  • Percent - Specifies whether the rank is determined by a percentage value.
  • 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 top 10 rank values from the given data range using ITopBottom Type and Rank properties in XlsIO.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	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
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/TopToBottomRank.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
   
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = 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 below

Top or Bottom conditional format

NOTE

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 top 50 percentage rank values from the given data range using ITopBottom Type, Rank and Percent properties in XlsIO

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	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
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Chart.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}
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

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

Format Above or Below Average Values

Above/Below average rule in conditional formatting is used to highlight the cells which contains above/below the average values in a data range. Top/Bottom conditional formatting rule can be created and customized using the IAboveBelowAverage interface in XlsIO.

The properties of IAboveBelowAverage are:

  • AverageType - Specifies whether the conditional formatting rule looks for cell values that are above average or below average or standard deviation.
  • StdDevValue - Specifies standard deviation number for AboveBelowAverage conditional formatting rule.

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

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	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
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/AboveAndBelowAverage.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = 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"
  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")
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 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 standard deviation, using IAboveBelowAverage AverageType and StdDevValue properties in XlsIO.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	FileStream inputStream = new FileStream(Path.GetFullPath(@"Data/InputTemplate.xlsx"), FileMode.Open, FileAccess.Read);
	IWorkbook workbook = application.Workbooks.Open(inputStream);
	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
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/AboveAndBelowStandardDeviation.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
	inputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = 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"
  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")
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 below

Above or Below Average conditional format

NOTE

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.