Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Deleting Duplicate Columns.

Deleting Duplicate Columns

by Allen Wyatt
(last updated October 18, 2018)


Dror has a worksheet that contains quite a bit of data. It is possible that the data in one column will be exactly the same as the data in another column, so he wonders if there is an easy way to delete any duplicate columns within the worksheet.

The first step, of course, is to figure out if two columns are identical or not. This can be determined rather easily with an array formula such as the following:


(Remember that an array formula is entered by using Shift+Ctrl+Enter.) The formula compares all the values in the first 100 rows of columns A and B. If they are all the same, then the formula returns TRUE. If any of the cells don't match, then the formula returns FALSE. If the result is TRUE you could then delete one of the columns because they are the same.

If you want something that is a bit more automatic, meaning that the duplicate column is deleted, then you'll need to use a macro. The following steps through all the columns in the worksheet and, starting with the right-most column, compares all the columns. If any are the same—regardless of their order in the worksheet—then the macro asks if you want the duplicate column deleted.

Sub DeleteDuplicateColumns()
    Dim rngData As Range
    Dim arr1, arr2
    Dim i As Integer, j As Integer, n As Integer

    On Error Resume Next
    Set rngData = ActiveSheet.UsedRange
    If rngData Is Nothing Then Exit Sub

    n = rngData.Columns.Count

    For i = n To 2 Step -1
        For j = i - 1 To 1 Step -1
            If WorksheetFunction.CountA(rngData.Columns(i)) <> 0 And _
              WorksheetFunction.CountA(rngData.Columns(j)) <> 0 Then
                arr1 = rngData.Columns(i)
                arr2 = rngData.Columns(j)
                If AreEqualArr(arr1, arr2) Then
                    With rngData.Columns(j)
                        'mark column to be deleted
                        If MsgBox("Delete marked column?", vbYesNo) _
                          = vbYes Then
                            'remove mark
                            Application.CutCopyMode = False
                        End If
                    End With
                End If
            End If
        Next j
    Next i

End Sub
Function AreEqualArr(arr1, arr2) As Boolean
    Dim i As Long, n As Long
    AreEqualArr = False
    For n = LBound(arr1) To UBound(arr1)
        If arr1(n, 1) <> arr2(n, 1) Then
            Exit Function
        End If
    Next n
    AreEqualArr = True
End Function


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5674) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Deleting Duplicate Columns.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Cleaning Up Lists

When you have huge amounts of data you need to check for matches, Excel may not be the best tool to use. If you can fit ...

Discover More

Standardizing Note Reference Placement

Want to modify where an endnote or footnote reference appears in relation to the punctuation in a sentence? Here's a way ...

Discover More

Countering Compressed Columns

If you open a workbook and find that the width of some of your columns has been changed, the discovery can be ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Pasting Excel Data within Word's Page Margins

The programs in the Microsoft Office suite are designed to work with each other easily. Sometimes there can be hiccups ...

Discover More

Scroll Wheel Doesn't Work when Editing

Using the mouse's scroll wheel can help improve how you edit information in a worksheet. Here's how to make sure that the ...

Discover More

Forcing Editing to Be Done in a Cell

Excel allows you to edit your cell contents in two places. What if you want to limit where editing occurs, so it can only ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 2?

2017-07-21 04:43:31

Willy Vanhaelen

@Dave Bonin
Indeed if you produce workbooks to use by so many people you have a point.
I am the only user of the workbooks I create so I am free to do what I like :-)

2017-07-17 10:30:57

Dave Bonin

@Willy Vanhalen

Unfortunately, the average, everyday user of Excel is not familiar with array formulae,
much less the Ctrl+Shift+Enter sequence used to complete an array formula.

This leads to issues when users "examine" a formula and then inadvertently re-enter
the formula without the Ctrl+Shift+Enter sequence. The newly-non-array formula
no longer produces the desired result and computational pandemonium ensues.

I produce workbooks to use as tools in our little 95,000 employee company. Once
released to the wild, I never know who might choose to adapt my workbook to suit
some special, parochial purpose. Many of the adopting adapters are not familiar
with array formulae. This leads to much frustration and taking of my name in vain
through no fault of my own.

The SUMPRODUCT() function, while acting much like an array formula, has no
special entry syntax. In that way, by mimicking a normal formula, they are much
safer for use around typical users.

In my humble opinion, Microsoft did a disservice to users when it introduced and
required the special array formula entry sequence, especially while doing nothing
much further to aid the user to identify an array formula. The brackets, while
helpful, are insufficient.

While working with array formulae, it's very easy to forget to Ctrl+Shift+Enter.
I've done it countless times and I suspect you have, too. If we're really good
with Excel and we mess up, imagine how much harder it is for more average

2017-07-15 09:28:04

Willy Vanhaelen

@Dave Binin
When the steam engine was invented one proclaimed that the cows would't give no milk anymore :-). If you detest array formulae simply don't use them.

I use them a lot for many years now and had no problem with them at all. They are very robust, extremely reliable, usefull and efficient, but of course you must understand there working and know how to use them.

BTW: the SUMPRODUCT() function is in fact an array formula "in disguise".

2017-07-14 10:59:39

Dave Bonin

I detest array formulae. They are fragile, fussy creatures, prone to birth defects
and premature mortality whenever anyone looks at them the wrong way --
particularly your average, everyday pedestrian user.

Instead, I highly recommend using the far more robust SUMPRODUCT() function.

2013-07-22 14:40:24

Glenn Case

Thanks, Mike, that cleared it up for me.

2013-07-20 19:14:26

Michael (micky) Avidan

Please ignore my TWO previous posts.
(too tired at this hour: UTC+02:00)

It must be an Array Formula where ever typed.

Without the AND it returns only an array of 100 TRUE/FALSE and therefor the AND mut be there in order to return TRUE only if all 100 Cells are identiacal.

Michael Avidan
“Microsoft®” MVP – Excel (2009-2014)

2013-07-20 18:42:44

Michael (micky) Avidan

Sorry about the "TIPO".

It should read: A1:A100=B1:B100

Michael Avidan
“Microsoft®” MVP – Excel (2009-2014)

2013-07-20 18:41:01

Michael (micky) Avidan

@Glenn Case,

You are, absolutely, right about the superfluous "AND".

However - you can use a regular formula (non Array):


only if it is typed within any cell but between rows 1-100.

If you want to type the formula in cell C101 - it must be an "Array formula".

Michael Avidan
“Microsoft®” MVP – Excel (2009-2014)

2013-07-19 12:05:29

Glenn Case

I'm curiouis about the syntax of the array formula. Why is AND necessary to make this work? There's only one condition. What does AND do here?

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.