How to Add Top10 Conditional Formatting

Possible Usage Scenarios

Using Top 10 conditional formatting in Excel helps quickly highlight the highest-performing values in a dataset — not just the literal top 10 values, but often the top N values or top N% (you can choose!).

1. Spot Trends and Outliers: Instantly identify the top performers (e.g., top 10 sales reps, best grades, highest revenue months).Makes it easy to analyze without sorting data.
2. Data Visualization: Adds color cues that make important data points stand out visually.Helps viewers of the spreadsheet understand the key values at a glance.
3. Quick Comparisons: Useful in dashboards and reports where you want to highlight excellence or peaks.
4. Dynamic Updates: If your data changes, the conditional formatting updates automatically to reflect the new top values.

How to Add Top10 Conditional Formatting Using Excel

Here’s how you can add Top10 conditional formatting in Excel, step by step:

1. Select the range of cells you want to analyze. For example: Select B2:B100, if you’re working with scores or sales numbers.
2. Go to the Home tab on the Excel ribbon.
3. Click on Conditional Formatting in the Styles group.
4. Hover over Top/Bottom Rules in the dropdown.
5. Click on Top 10 Items…
6. A dialog box will pop up: It will say: Format cells that rank in the top 10. You can change the number (e.g., Top 5, Top 3, etc.). Choose a format (like a light red fill, bold text, or click Custom Format for more options).
7. Click OK

How to Add Top10 Conditional Formatting Using Aspose.Cells for .NET

Aspose.Cells fully supports the conditional formatting provided by Microsoft Excel 2007 and later versions in XLSX format on cells at runtime. This example demonstrates an exercise for Top 10 conditional formatting with different sets of attributes.

private void TestTop10()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];
    AddTop10_1(_sheet);
    AddTop10_2(_sheet);
    AddTop10_3(_sheet);
    AddTop10_4(_sheet);
    book.Save(filePath + "Top10.xlsx");
}
// This method implements a simple Top10 conditional formatting type.
private void AddTop10_1(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("A17:C20", Color.Gray, _sheet);
    int idx = conds.AddCondition(FormatConditionType.Top10);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Yellow;
    cond.Style.Pattern = BackgroundType.Solid;
}

// This method implements another Top10 conditional formatting type.
private void AddTop10_2(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("A21:C24", Color.Green, _sheet);
    int idx = conds.AddCondition(FormatConditionType.Top10);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;

    cond.Top10.IsBottom = true;
}

// This method implements another Top10 conditional formatting type with some custom attributes.
private void AddTop10_3(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("A25:C28", Color.Orange, _sheet);
    int idx = conds.AddCondition(FormatConditionType.Top10);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Blue;
    cond.Style.Pattern = BackgroundType.Solid;

    cond.Top10.IsPercent = true;
}

// This method implements another Top10 conditional formatting type with some custom attributes.
private void AddTop10_4(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("A29:C32", Color.Gold, _sheet);
    int idx = conds.AddCondition(FormatConditionType.Top10);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Green;
    cond.Style.Pattern = BackgroundType.Solid;

    cond.Top10.Rank = 3;
}

// This method adds formatted conditions.
private FormatConditionCollection GetFormatCondition(string cellAreaName, Color color, Worksheet _sheet)
{
    // Adds an empty conditional formattings
    int index = _sheet.ConditionalFormattings.Add();
    // Get the formatted conditions
    FormatConditionCollection formatConditions = _sheet.ConditionalFormattings[index];
    // Get the cell area calling the custom GetCellAreaByName method
    CellArea area = GetCellAreaByName(cellAreaName);
    // Add the formatted conditions cell area.
    formatConditions.AddArea(area);
    // Call the custom FillCell method
    FillCell(cellAreaName, color, _sheet);
    // Return the formatted conditions
    return formatConditions;
}

// This method specifies the cell shading color for the conditional formattings cellarea range.
private void FillCell(string cellAreaName, Color color, Worksheet _sheet)
{
    CellArea area = GetCellAreaByName(cellAreaName);
    int k = 0;
    for (int i = area.StartColumn; i <= area.EndColumn; i++)
    {
        for (int j = area.StartRow; j <= area.EndRow; j++)
        {
            Cell c = _sheet.Cells[j, i];
            if (!color.IsEmpty)
            {
                Style s = c.GetStyle();
                s.ForegroundColor = color;
                s.Pattern = BackgroundType.Solid;
                c.SetStyle(s);
            }
            // Set some random values to the cells in the cellarea range
            int value = j + i + k;
            c.PutValue(value);
            k++;
        }
    }

}
// This method specifies the CellArea range (start row, start col, end row, end col etc.)
// For the conditional formatting
internal static CellArea GetCellAreaByName(string s)
{
    CellArea area = new CellArea();
    string[] strCellRange = s.Replace("$", "").Split(':');
    int column;
    CellsHelper.CellNameToIndex(strCellRange[0], out area.StartRow, out column);
    area.StartColumn = column;
    if (strCellRange.Length == 1)
    {
        area.EndRow = area.StartRow;
        area.EndColumn = area.StartColumn;
    }
    else
    {
        CellsHelper.CellNameToIndex(strCellRange[1], out area.EndRow, out column);
        area.EndColumn = column;
    }
    return area;
}