How to Add Text Conditional Formatting

Possible Usage Scenarios

Using text-based conditional formatting in spreadsheets is useful for highlighting cells that meet specific textual criteria. This can improve data analysis and make it easier to find key information in a large dataset. Here are some reasons to use text conditional formatting:

1. Highlight Specific Text: You can apply formatting based on specific words, phrases, or characters. For example, you might want to highlight all cells that contain the word “Urgent” or “Completed” to easily differentiate tasks in a project.
2. Identify Patterns or Trends: If you’re working with categories or statuses (like “High”, “Medium”, “Low”), text conditional formatting can visually distinguish between them, making it easier to track progress or prioritize tasks.
3. Error or Data Entry Alerts: Text formatting can flag inconsistent or erroneous entries, such as misspelled words, incomplete text, or incorrect values. This is particularly useful in datasets with a lot of textual input.
4. Enhanced Readability: Color-coding text or changing its style (bold, italics, etc.) helps make important information stand out, improving the overall readability of your sheet.
5. Dynamic Feedback: You can set up rules that automatically adjust the formatting when text matches certain conditions. This means you don’t have to manually update the formatting as the data changes.

In essence, text conditional formatting helps you quickly spot relevant information, errors, and trends, making it a powerful tool for managing and interpreting textual data.

How to Add Text Conditional Formatting Using Excel

To add text-based conditional formatting in Excel, follow these steps:

1. Select the Range of Cells: Highlight the cells where you want to apply the conditional formatting.
2. Open the Conditional Formatting Menu: Go to the Home tab in the Excel ribbon. Click on Conditional Formatting in the “Styles” group.
3. Choose “New Rule”: From the drop-down menu, select New Rule.
4. Select “Format only cells that contain”: In the New Formatting Rule dialog, choose Format only cells that contain under the “Select a Rule Type” section.
5. Set the Rule Criteria: In the “Format cells with” section, choose Specific Text from the drop-down. Select either containing, begins with, or ends with, depending on the condition you want to apply. Enter the text you want to format (e.g., a specific word like “Urgent” or “Completed”).
6. Choose the Formatting: Click on the Format button. In the Format Cells dialog, you can select the font color, fill color, or any other formatting options you prefer.
7. Apply the Rule: Once you’ve set your desired format, click OK to apply the rule. Click OK again in the New Formatting Rule dialog to close it.
8. View the Results: The cells containing the text you specified will now have the formatting applied, making it easy to spot relevant information.

How to Add Text 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 examples demonstrate an exercise for advanced conditional formatting types including BeginsWith, ContainsBlank, ContainsText and so on.

Format Cell When the Value Starts With Specified Text

// This method implements the BeginsWith conditional formatting type.
private void AddBeginWith()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];
    FormatConditionCollection conds = GetFormatCondition("E15:G16", Color.LightGoldenrodYellow, _sheet);
    int idx = conds.AddCondition(FormatConditionType.BeginsWith);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.Text = "ab";
    Cell c = _sheet.Cells["E15"];
    c.PutValue("abc");
    c = _sheet.Cells["G16"];
    c.PutValue("babx");
    book.Save("BeginsWith.xlsx");
}

// 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;
}

Format Cell When the Value Contains Blank

// This method implements the ContainsBlank conditional formatting type.
private void AddContainsBlank()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];

    FormatConditionCollection conds = GetFormatCondition("E9:G10", Color.LightBlue, _sheet);
    int idx = conds.AddCondition(FormatConditionType.ContainsBlanks);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Yellow;
    cond.Style.Pattern = BackgroundType.Solid;
    Cell c = _sheet.Cells["E9"];
    c.PutValue("  ");
    c = _sheet.Cells["G10"];
    c.PutValue("  ");
    book.Save("ContainsBlank.xlsx");
}

Format Cell When the Value Contains Errors

// This method implements the ContainsErrors conditional formatting type.
private void AddContainsErrors()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];
    FormatConditionCollection conds = GetFormatCondition("E17:G18", Color.LightSkyBlue, _sheet);
    int idx = conds.AddCondition(FormatConditionType.ContainsErrors);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Yellow;
    cond.Style.Pattern = BackgroundType.Solid;
    Cell c = _sheet.Cells["E17"];
    c.PutValue("  ");
    c = _sheet.Cells["G18"];
    c.PutValue("  ");
    book.Save("ContainsErrors.xlsx");
}

Format Cell When the Value Contains Specified Text

// This method implements the ContainsText conditional formatting type.
private void AddContainsText()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];
    FormatConditionCollection conds = GetFormatCondition("E5:G6", Color.LightBlue, _sheet);
    int idx = conds.AddCondition(FormatConditionType.ContainsText);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Yellow;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.Text = "1";
    book.Save("ContainsText.xlsx");
}
        

Format Cell When the Value Contains Duplicate Values

// This method implements the DuplicateValues conditional formatting type.
private void AddDuplicateValues()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];

    FormatConditionCollection conds = GetFormatCondition("E23:G24", Color.LightSlateGray, _sheet);
    int idx = conds.AddCondition(FormatConditionType.DuplicateValues);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    Cell c = _sheet.Cells["E23"];
    c.PutValue("bb");
    c = _sheet.Cells["G24"];
    c.PutValue("bb");
    book.Save("DuplicateValues.xlsx");
}

Format Cell When the Value Ends With Specified Text

// This method implements the EndsWith conditional formatting type.
private void AddEndWith()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];

    FormatConditionCollection conds = GetFormatCondition("E13:G14", Color.LightGray, _sheet);
    int idx = conds.AddCondition(FormatConditionType.EndsWith);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Yellow;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.Text = "ab";
    Cell c = _sheet.Cells["E13"];
    c.PutValue("nnnab");
    c = _sheet.Cells["G14"];
    c.PutValue("mmmabc");
    book.Save("EndsWith.xlsx");
}

Format Cell When the Value Not Contains Blank

// This method implements the NotContainsBlank conditional formatting type.
private void AddNotContainsBlank()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];

    FormatConditionCollection conds = GetFormatCondition("E11:G12", Color.LightCoral, _sheet);
    int idx = conds.AddCondition(FormatConditionType.NotContainsBlanks);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    Cell c = _sheet.Cells["E11"];
    c.PutValue("abc");
    c = _sheet.Cells["G12"];
    c.PutValue("  ");
    book.Save("NotContainsBlank.xlsx");
}

Format Cell When the Value Not Contains Errors

// This method implements the NotContainsErrors conditional formatting type.
private void AddNotContainsErrors()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];

    FormatConditionCollection conds = GetFormatCondition("E19:G20", Color.LightSeaGreen, _sheet);
    int idx = conds.AddCondition(FormatConditionType.NotContainsErrors);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    Cell c = _sheet.Cells["E19"];
    c.PutValue("  ");
    c = _sheet.Cells["G20"];
    c.PutValue("  ");
    book.Save("NotContainsErrors.xlsx");
}

Format Cell When the Value Not Contains Specified Text

// This method implements the NotContainsText conditional formatting type.
private void AddNotContainsText()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];

    FormatConditionCollection conds = GetFormatCondition("E7:G8", Color.LightCoral, _sheet);
    int idx = conds.AddCondition(FormatConditionType.NotContainsText);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.Text = "3";
    book.Save("NotContainsText.xlsx");
}

Format Cell When the Value Contains Unique Values

// This method implements the UniqueValues conditional formatting type.
private void AddUniqueValues()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];
    FormatConditionCollection conds = GetFormatCondition("E21:G22", Color.LightSalmon, _sheet);
    int idx = conds.AddCondition(FormatConditionType.UniqueValues);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Yellow;
    cond.Style.Pattern = BackgroundType.Solid;
    Cell c = _sheet.Cells["E21"];
    c.PutValue("aa");
    c = _sheet.Cells["G22"];
    c.PutValue("aa");
    book.Save("UniqueValues.xlsx");
}