Search code examples
excelconditional-formattinglibreoffice-calc

Highlight new/different items in a list Excel/LibreOffice Calc

I have a column of data (Version with list of reports), and I have a column of newer data (new version with mostly the same, but some different reports), and I would like to highlight the new/different items in the second column. I think conditional formatting is what I am looking for. I'm using LibreOffice Calc at the moment, but I've been looking up Excel help.

This formula COUNTIF(H3:H30,I3)<1 does what I want, but I can't figure out how to make it scale in LibreOffice Calc. I think it would scale well in Excel, but I'm not sure. By scale, I mean copy to other cells.

How can I make this work in LibreOffice Calc? Can I just copy/paste conditional formatting? Do I have to switch to Excel to get this to work?

I'm working with a set of ~30 items. However, the list grows with new versions, and if I have to copy it manually, I might as well manually assign the highlighting too.

Solution

  • It sounds like only one condition is needed, so it should be easy to maintain.

    1. Select cells I3 to I50 and go to Format -> Conditional Formatting -> Condition...
    2. Formula is AND(I3<>"",COUNTIF(H$3:H$50,I3)<1). Press OK.

    conditional formatting

    This is the result:

    result

    If the list grows, then modify the condition using Format -> Conditional Formatting -> Manage.