We have a lot of ways to fix duplicates in google sheet. Here we are discussing how to use the pivot table to identify duplicates in google sheet.

We already discussed how to make a pivot table in google sheet.

How to Create Pivot Table in Google Sheet

This discussion, we are considering a bank statement as an example and focussing how to identify a duplicate entry by a firm’s accountant(it may be by mistake or intentional) in a Bank statement.

Bank statements are the books of entry of all the transactions relating to banks. So the reconciliation of these entries with the Account Statement provided by the bank is very familiar and routine job of accountants and finance professionals.

Before doing the bank reconciliation, checking and finding the duplicate values also the important in case the number of entries in the statement is large. It may help us to find double payments or any other entries passed more than once.

Using a pivot table, we can also find duplicates in any other data based on a unique column in the data. This is useful for accountants or finance professionals. It may increase the efficiency of the internal auditors in identifying fraud in transactions in case they are not using any Computer-Aided Audit Tool (CAAT).

Using pivot table is very simple and easy method to identify duplicate in google sheet.

How to use Pivot Table in identifying duplicates in a bank statement.

Let us start now

Normally, in any organisation, while passing an entry, there will a unique transaction number for all the entries. Here also we need a unique number to identify the duplicates.

In below table, you can see a bank statement of a company. There we can see a unique transaction Reference No. along with Date, Description, Withdrawal, Deposit and Balance

Bank Statement

Just for an example, we have taken some transactions entered by an accountant of the firm into their bank book.

To start, we need to create a pivot table in the Google sheet.

1. Click on the ‘Bank Statement’ and select ‘Pivot table’ from the ‘Data’ tab.

2. ADD ‘Reference No’ as Rows.

3. ADD ‘Withdrawal’ and ‘Deposit’ as ‘Values’.

4. Both these values must be ‘summarized by’ count

After the fourth step, we can see the table as given below.

5. Select the Pivot Table and click on ‘Format’ on the menu tab.

6. Select ‘Conditional formatting’

7. Define the condition as ”Greater than” and value as “1” from ‘Conditional format rules’ appearing on the right side of the sheet.

8 Select the ‘formatting style’ also from the same tab and click on

You can refer the table below for details

Now we can see the table as shown below. Cells with the value greater than 1 have highlighted. All these cells indicating that these entries with the corresponding ‘Reference No’ have entered more than once.

If the cell is showing 2, means the entry made two times in connection with the ‘Reference No’ and three times if the cell is showing 3.

Now, if we need to get more details about the duplication, just double click on the cell highlighted with the number. It will open another sheet with the details of the duplication.

Duplication

From this table, we can have a clear idea that the “Allowance to Mr Bravo” entered two times in the firm’s Bank Account by the accountant.

From this example, it is very easy to find duplication in google sheet . Practically, it is useful in finding duplicate payment and receipt entries in all kind of accounts like Receivables, Payables, Cash Book etc.

Recommended for Reading