Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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 July 14, 2017)


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

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5674) applies to Microsoft Excel 2007, 2010, and 2013. 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. ...


Changing Built-in Word Commands

Want to replace Word's internal commands with your own macros? It's easy to do if you know the key discussed in this tip.

Discover More

Creating Multiple Blank Documents in One Step

Word makes it easy to create a new, blank document. What if you want to create more than one document at a time, however? ...

Discover More

Changing Text Case Many Times

You can use the built-in Word shortcut to change the case of a text selection. You may have quite a few items in a document ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Displaying Row and Column Labels

When you create a worksheet, it is common to place headings at the top of each column and the left of each row so you can ...

Discover More

Ignoring Paragraph Marks when Pasting

Paste information in a worksheet, and you may end up with Excel placing it into lots of different cells. If you want it to ...

Discover More

Checking for an Entry in a Cell

You may be looking for a way to have a formula determine if a particular cell has anything in it. Here's how you can find the ...

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 6 + 0?

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.