Compare two tables in Excel

02 Oct, 2025

Overview

This is for Excel.

Have two source tables, one original, and one changed. This will compare the two, "cell by cell", and show the fields that are different.

Tables

Make sure the original and changed tables are exactly the same schema, and that there is a key column that can correlate rows one-to-one.

Name them originalBase and changedBase.

Define a global variable/query called keyColumnName.

Original

let
    
    keyColumnGeneric = "Key",

    base = Table.RenameColumns(originalBase, {{keyColumnName, keyColumnGeneric}}),   

    group = Table.Group(
        base, 
        {keyColumnGeneric},
        {
            {
                "Fields", 
                each 
                    let
                        record = _{0},
                        cols = Table.ColumnNames(_),
                        aslist = List.Transform(
                            cols, 
                            each [
                                Field = _,
                                Value = Record.Field(record, _)
                            ]
                        )
                    in
                        aslist
            }
        }
    ),
    #"Expanded Original" = Table.ExpandListColumn(group, "Fields"),
    #"Expanded Original1" = Table.ExpandRecordColumn(#"Expanded Original", "Fields", {"Field", "Value"}, {"Field", "Value"})

in
    #"Expanded Original1"

Changed

let
    keyColumnGeneric = "Key",

    base = Table.RenameColumns(changedBase, {{keyColumnName, keyColumnGeneric}}),   

    group = Table.Group(
        base, 
        {keyColumnGeneric},
        {
            {
                "Fields", 
                each 
                    let
                        record = _{0},
                        cols = Table.ColumnNames(_),
                        aslist = List.Transform(
                            cols, 
                            each [
                                Field = _,
                                Value = Record.Field(record, _)
                            ]
                        )
                    in
                        aslist
            }
        }
    ),
    #"Expanded Original" = Table.ExpandListColumn(group, "Fields"),
    #"Expanded Original1" = Table.ExpandRecordColumn(#"Expanded Original", "Fields", {"Field", "Value"}, {"Field", "Value"})

in
    #"Expanded Original1"

Comparison

let
    join = Table.NestedJoin(
        original,
        {"Key", "Field"},
        changed,
        {"Key", "Field"},
        "Changed"
        
    ),
    #"Renamed Columns" = Table.RenameColumns(join,{{"Value", "Original.Value"}}),
    #"Expanded Changed" = Table.ExpandTableColumn(#"Renamed Columns", "Changed", {"Value"}, {"Changed.Value"}),

    different = Table.SelectRows(
        #"Expanded Changed", 
        each [#"Original.Value"] <> [#"Changed.Value"]
    )

in
    different

Absent in Original

let
    Source = Table.NestedJoin(
        changedBase, 
        keyColumnName,
        originalBase, 
        keyColumnName, 
        "Join", 
        JoinKind.LeftAnti
    )
in
    Source

Absent in Changed

let
    Source = Table.NestedJoin(
        originalBase, 
        keyColumnName,
        changedBase, 
        keyColumnName, 
        "Join", 
        JoinKind.LeftAnti
    )
in
    Source

#excel #powerquery