Compare Two Columns in LibreOffice Calc – Steps and Examples


This guide explains how to compare two columns of texts in the LibreOffice Calc spreadsheet programme.

LibreOffice Calc offers several methods to help you compare two lists or two columns and find out their differences. You can try the methods explained below to find out the mismatched items.

Compare two columns in LibreOffice Calc

Using EXACT Formula Method

The easiest way to compare is to use the EXACT function. The EXACT function takes two arguments, compares them, and returns TRUE or FALSE based on whether they match.

EXACT("Text1"; "Text2")
Text1 refers to the first text to compare.
Text2 is the second text to compare.


=EXACT("libreoffice";"Libreoffice") returns FALSE
=EXACT("libreoffice";"libreoffice") returns TRUE

You can apply this function to a cell or an entire column to compare texts in cells in those columns. Let’s take a look. We have the following test data, which has two columns. Not all the rows are identical; there are differences.

Let’s create a helper column C. In this column, in the first data row, enter the EXACT function.

Compare two columns using EXACT function in Calc
Compare two columns using EXACT function in Calc

And hit enter. The result is TRUE for the above test data. Now select the cell handle and drag it to the bottom. Or, double click on the cell handle to autofill.

Result after comparing two columns using EXACT function in Calc
Result after comparing two columns using EXACT function in Calc

You can see the result column filled with TRUE or FALSE.

Those cells are identical, they are shown as TRUE, and the rest are false.

Now you can use the Menu > Data > Auto Filter or press CTRL+SHIFT+L to filter out the relevant fields based on TRUE or FALSE.

Using MATCH function

The above method only checks the mismatched values in two parallel rows. But it didn’t give you the cells which are entirely missing in another column.

For example, we have these simple two columns with data.

data for mismatch highlight
data for mismatch highlight

As you can see, the above EXACT method will not work if you want to see the data that are not present in column B. The value Mars and Venus is not present in column B, but how you can find that out.

So, there are various ways to do it. You can use the MATCH function or conditional formatting to view the differences.

Let’s add a helper column C with the below function to demonstrate.

MATCH function showing the differences
MATCH function showing the differences

So, as you can see, Mars and Venus are not present in column B and its shown as #N/A in column C.

Using Conditional Formatting

Furthermore, there is a way to highlight this via conditional formatting because it becomes easier to analyse data visually.

So, select col A and Col B. Then add the name Mylist1 and Mylist2 as named ranges, as shown below.

Then click on A1 and choose Menu > Format > Conditional > Condition. Choose the condition as “Formula is“, then apply style as BAD on the formula type. And add the Range as A1:A6. Press ok.

Compare two columns in LibreOffice and showing the differences
Conditional formatting – column differences

And you will see the values that are different showing in RED colour.


Finally, you can easily compare two columns and see the differences using the above methods. I hope this article gives you some idea of how you can work out your use cases. Do let me know in the comment section down below whether it helps.


Join our Telegram channel and stay informed on the move.

Notify of

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Newest Most Voted
Inline Feedbacks
View all comments

Irish work


In my LibreOffice Version on Ubuntu i had to use a semikolon instead of a comma in the COUNTIF condition.


Useless functions. Conditional doesn’t work in LibreOffice, all values are red! Excel works properly.