Skip to content

Data validation applied to multiple range areas is not correctly updated when inserting/deleting rows/columns #2571

Open
Open
Data validation applied to multiple range areas is not correctly updated when inserting/deleting rows/columns#2571
Labels
bugSomething isn't working

Description

@craigbrown
craigbrown
opened on Oct 10, 2026

EPPlus usage

Commercial use (I have a commercial license)

Environment

Windows

Epplus version

8.7.1

Spreadsheet application

Excel

Description

A data validation can be applied to multiple areas, like A1:A10 C1:C10.

However, when rows or columns are inserted or deleted, only the first area is updated correctly. Every other area is either not updated at all (when inserting/deleting between the areas) or removed entirely (when inserting/deleting before or within the areas).

To reproduce:

  1. Create a new data validation list applying to A1:A10 C1:C10.
  2. Insert a single row at row 5.
  3. Check the data validation list - it now applies to A1:A11, but it should apply to A1:A11 C1:C11.

There are some more unit test cases below (full disclosure - these were generated by Claude):

[TestMethod]
public void InsertRow_BetweenAreasOfMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas, D16:D20 and D28
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["D16:D20 D28"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN 4 rows are inserted between the two areas
        sheet1.InsertRow(24, 4);

        // THEN the second area is shifted down by 4 rows
        Assert.AreEqual("D16:D20 D32", dv.Address.Address);
    }
}

[TestMethod]
public void InsertRow_AboveMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas, D16:D20 and D28
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["D16:D20 D28"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN 2 rows are inserted above both areas
        sheet1.InsertRow(10, 2);

        // THEN both areas are shifted down by 2 rows
        Assert.AreEqual("D18:D22 D30", dv.Address.Address);
    }
}

[TestMethod]
public void DeleteRow_BetweenAreasOfMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas, D16:D20 and D28
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["D16:D20 D28"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN 2 rows are deleted between the two areas
        sheet1.DeleteRow(24, 2);

        // THEN the second area is shifted up by 2 rows
        Assert.AreEqual("D16:D20 D26", dv.Address.Address);
    }
}

[TestMethod]
public void DeleteRow_AboveMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas, D16:D20 and D28
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["D16:D20 D28"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN 2 rows are deleted above both areas
        sheet1.DeleteRow(10, 2);

        // THEN both areas are shifted up by 2 rows
        Assert.AreEqual("D14:D18 D26", dv.Address.Address);
    }
}

[TestMethod]
public void InsertRow_AboveSideBySideAreasOfMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas on the same row, D17 and L17
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["D17 L17"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN 2 rows are inserted above both areas
        sheet1.InsertRow(10, 2);

        // THEN both areas are shifted down by 2 rows
        Assert.AreEqual("D19 L19", dv.Address.Address);
    }
}

[TestMethod]
public void DeleteRow_AboveSideBySideAreasOfMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas on the same row, D17 and L17
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["D17 L17"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN 2 rows are deleted above both areas
        sheet1.DeleteRow(10, 2);

        // THEN both areas are shifted up by 2 rows
        Assert.AreEqual("D15 L15", dv.Address.Address);
    }
}

[TestMethod]
public void InsertRow_WithinSideBySideAreasOfMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas on the same rows, G17:G26 and L17:L26
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["G17:G26 L17:L26"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN 2 rows are inserted within both areas
        sheet1.InsertRow(20, 2);

        // THEN both areas are extended by 2 rows
        Assert.AreEqual("G17:G28 L17:L28", dv.Address.Address);
    }
}

[TestMethod]
public void DeleteRow_WithinSideBySideAreasOfMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas on the same rows, G17:G26 and L17:L26
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["G17:G26 L17:L26"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN all but the first row of both areas are deleted
        sheet1.DeleteRow(18, 9);

        // THEN both areas are reduced to the first row
        Assert.AreEqual("G17 L17", dv.Address.Address);
    }
}

[TestMethod]
public void InsertColumn_BetweenAreasOfMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas, D16:D20 and H16:H20
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["D16:D20 H16:H20"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN 2 columns are inserted between the two areas
        sheet1.InsertColumn(6, 2);

        // THEN the second area is shifted right by 2 columns
        Assert.AreEqual("D16:D20 J16:J20", dv.Address.Address);
    }
}

[TestMethod]
public void InsertColumn_LeftOfMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas, D16:D20 and H16:H20
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["D16:D20 H16:H20"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN 2 columns are inserted to the left of both areas
        sheet1.InsertColumn(2, 2);

        // THEN both areas are shifted right by 2 columns
        Assert.AreEqual("F16:F20 J16:J20", dv.Address.Address);
    }
}

[TestMethod]
public void DeleteColumn_BetweenAreasOfMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas, D16:D20 and H16:H20
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["D16:D20 H16:H20"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN 1 column is deleted between the two areas
        sheet1.DeleteColumn(6, 1);

        // THEN the second area is shifted left by 1 column
        Assert.AreEqual("D16:D20 G16:G20", dv.Address.Address);
    }
}

[TestMethod]
public void DeleteColumn_LeftOfMultiAddressDataValidation()
{
    using (var pck = new ExcelPackage())
    {
        // GIVEN a data validation over two areas, D16:D20 and H16:H20
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");
        var dv = sheet1.Cells["D16:D20 H16:H20"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // WHEN 1 column is deleted to the left of both areas
        sheet1.DeleteColumn(2, 1);

        // THEN both areas are shifted left by 1 column
        Assert.AreEqual("C16:C20 G16:G20", dv.Address.Address);
    }
}

Activity

  1. craigbrown
    added
    bugSomething isn't working
    on Oct 10, 2026
  2. github-project-automation
    added this to Bug Trackeron Oct 10, 2026

  3. OssianEPPlus commented on Oct 12, 2026

    @OssianEPPlus
    OssianEPPlus
    on Oct 12, 2026
    Contributor

    Hmm. This is a bug. But also a bit deeper.
    Referring to cells with space separation such as "sheet1.Cells["D16:D20 D28"]" is technically incorrect by itself. As referring to the address of cells in Epplus (and in excel) is generally done with ',' rather than space. In order for Epplus to work correctly all multi-addresses in sheet1.cells[] are expected to be comma-separated.

    This is since Space-separation means different things in different places in Excel. In DataValidations and ConditionalFormatting space-separation IS the correct ooxml-format. In formulas space-separation implies an Intersect operation.

    Epplus currently throws when applying comma-separation to Data Validations yet also struggles to handle space-separation as e.g. insert and delete operations Expect comma-separation. We're looking into the issue but it is deceptively complex due to the way Excel operates.

    We'll get back to you with a fix or workaround shortly.

  4. OssianEPPlus commented on Oct 13, 2026

    @OssianEPPlus
    OssianEPPlus
    on Oct 13, 2026
    Contributor

    We've created a fix for this internally. (See #2573) And will let you know when it is ready for you to test and verify.

    After internal discussion we have verified that an ExcelAddress class is Expected to be Comma-separated. Due to legacy and us previously throwing on this for DataValidations we will be allowing space-separation for Input-Only.

    Accessing the address of a Data Validation from now on will give a Correct Comma-separated ExcelAddress.
    Your tests would need to be ammended so that e.g.
    Assert.AreEqual("C16:C20 G16:G20", dv.Address.Address); is instead
    Assert.AreEqual("C16:C20,G16:G20", dv.Address.Address);

    We really appreciate the full disclosure and apologize for the confusion.

  5. craigbrown commented on Oct 14, 2026

    @craigbrown
    craigbrown
    on Oct 14, 2026
    Author

    Thanks, I think that should work.

    In practice, we aren't actually looking at the address - we're just taking an existing workbook and inserting rows. So that change to commas shouldn't affect us.

  6. OssianEPPlus commented on Oct 15, 2026

    @OssianEPPlus
    OssianEPPlus
    on Oct 15, 2026
    Contributor

    That's good to hear!
    However Note that with this fix we'll start allowing
    "sheet1.Cells["D16:D20,H16:H20"].DataValidation.AddListDataValidation();"

    and this will be the preferred way of adding Data Validations. We'll likely be throwing on space-separated addresses for Data Validations from Epplus9 see #2158

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions