Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Compare And Merge Workbooks In Excel

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am using Microsoft Excel 2002.

I want to Compare and Merge two Workbooks, but when I go to the Tools Menu, the option is shaded. What should I do?

View Answers     

Similar Excel Tutorials

How to Arrange Data within Cells in Excel
In this tutorial I am going to look at cell alignment / arrangement. These features allow you to change how data lo ...
Change the Default Number of Worksheets Created in a New Excel Workbook
Learn how to change the number of worksheets that are in new Excel workbooks. Excel workbooks always have 3 worksh ...
Import a Worksheet from One Workbook to Another in Excel
In Excel, you can quickly copy an entire worksheet from one workbook to another workbook.  This allows you to impor ...
Easily Compare Duplicate Values in Excel
Here, I'll show you a simple technique to quickly and easily compare large lists of duplicate values.  This allows ...

Helpful Excel Macros

Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Filter Data in Excel to Display Results that Contain 1 of 2 Possible Values - AutoFilter
- This Excel macro filters data in Excel to display results that contain 1 of 2 possible values. This macro uses the xlor
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
- Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a great little macro that allows yo
Sort Worksheet Tabs - Ascending or Descending Order
- This macro will sort all of the worksheets in the current workbook. It can sort in ascending or descending order. This

Similar Topics

I am trying to merge 2 workbooks, again something I do on a daily basis
almost. I have followed the directions given by Excel Help, but I can not
click on the Compare and Merge Workbooks option on the Tools menu. Is there
another way that I can do this without having to move back and forth between
the 2 workbooks?

I want to merge 2 excel workbooks, there is no compare and merge workbooks on
the tools menu. I tried to add the command to the category - no luck. I can
drag the command to the top of the page but it is 'grayed out' and i can't
use it.
Is there another way to get to the 'select files to merge into current

I have several folders with about 30 workbooks in each that I want to merge and so far have managed to work out how to do one at a time. In my workbooks my Tools, Compare and Merge Workbooks is greyed out. I'm using 2003. Is there any other way I can merge a quantity of workbooks in one go?
Many thanks for your continuing support & help,

I want to Compare two Excel Spreadsheets to highlight the changes that have
been made between one and the other.

Unfortunately the "Compare and Merge Workbooks" function in the Tools menu
is greyed-out. It's not an Add-In, and I've tried installing the
functionality from the CD, but can't find anything.

any ideas?


Paul Wilson

"My Compare and merge workbooks" is not on the list under "tool" on my toolbar.

any suggestions?


correction: My compare and merge workbooks" is not enabled on my "tools" list...


Merge Work Books-Excel 2003 (Help) explains how to Merge Work Books.It states-Go to Tools and click Compare and Merge Workbooks.
However,when the drop down wind appears the Merge Workbooks icon is disabled. Question is Why -Do I need to download an update?
Please HelpMerge Workbooks

I'm trying to use the Tools menu / Compare and Merge Workbook feature, but it's greyed out and don't understand why. Has anyone used this feature?
What am I doing wrong?

Hello! Some copies of excel workbooks are made using save as option for data entry purpose. Each copy has same file structure because they are created using save as option. Is it possible to merge them (preferably preserving the macros) ? The workbooks are not shared because they were protected sheets and so macros wont run.

I am getting the following message when attempting to Merge spreadsheets in
Excel 2002...

{Spredasheet Name} cannot be merged into the current workbook. Workbooks to
be merged must be copies made from the same shared workbook, and must all
maintain the change history for a sufficient amount of time.

Let's see...
1.) I properly created a "Copy" of each spreadsheet.
2.) I went into Tools ==> Share Workbook and ensured the Allow changes by
more than one user at the same time was checked for both copies of the
workbooks I'm trying to Merge.
3.) I went into the Advanced tab within Tools ==> Share Workbook and ensured
the radio for Keep Change History for was checked

Now my column headings don't match and I want to merge based on a key...An
Application Number. My good file has close to 15,000 rows. My sub-set file
has 5,000 rows. So I want to see where the 5,000 rows match-up with my big
file of 15,000.

I am all wet thinking that the Compare and Merge function within Excel will
do this for me???

Thanks in advance for your help.

Dear all,

I sent out an excel file to other colleagues of my in different countries to
update some figure. after getting the revised version, i try to used the
Compare and Merge Workbooks function in Excel to consolidate the data. But
while doin it on some file, I been kick out from Excel and "Send Error
Report" dialog box been prompt.

Had anyone have any idea to solve this error. Thank.


Good Morning;

I have 2 large excel documents; approximately 3,000 rows each; they are similar, but not exact duplicates. (1 is from corporate; 1 is from a branch); I need to create 1 file with both sets of data in it; and there are multiple duplicates. Compare and Merge can't do this because they are not shared workbooks. When I share them I get an error message stating that there hasn't been enough history to compare and merge. Import is messy; and I like the idea of creating an Access database, however this is something that needs to be merged and updated monthly; and personnel will not know how to pull this information from Access.

Any suggestions?

P.S. I know basic Visual Basic;

Thank you.


I want to merge two workbooks. Compare and Merge is grayed out? What do I
need to do?

"into the current workbook."
Yes, trying to Merge and Compare workbooks. Sent out exact copies all shared. It seems a user has done something.
I think if I read this error too many times my head will crack.

I thought I found the issue, someone changed the format. Deleted that file. Try to merge again. Still get the error. Only clean copy I had was the email I sent to them which was the exact one. Could somehow this have messed up? What does this mean? I have 30 workbooks to merge.

I have a folder that contains about 1700 workbooks, each workbook containing one worksheet. There are 1-2 workbooks for each group and I need to merge those workbooks together. I have seen code that will merge every book in the folder, but I need to only merge ones for each group.



should become


Not all of the locations have both workbooks, but none of them have more than 2 workbooks.

Can anybody help me to automate this with either VBA or .Net?


Hi, I'm using Excel 2003. I'm comparing a list of names in 2 different workbooks. I had to take the first list of names from workbook1 that had last name in one cell and first name in the other cell and merge the data so in one cell I had last name first name. The other workbook[2] has last name first name in one cell.

When I try to do a =MATCH(A1,[Book2]Sheet1!$A:$A,0), I get an error. I believe it's b/c of the merge of last name first name in workbook1. It's like it's not reading the data that was merged into one cell.

Is there another way to do this so I can do the compare and return the matching names? Any advice would be greatly appreciated!!

Hey everyone,

I've trying to sort something out.

My team has got 5 workbooks, all pretty much the same layout, but all with different data as they cover different areas of the country.

However i need to merge all the data into one spreadsheet. None of the worksheets/workbooks are shared documents as if they were i would just use the 'Merge Workbooks' option.

Does anyone know any quick way of merging them into one or am i going to need to set up a whole load of formulas for each cell (there are about 2500-300 rows in each worksheet so would take me for ever)
The main thing I'm worried about also is that any duplicates will be deleted, i need all the data including any duplicate as someone of the information might be different.

Can anyone point me in the right direction.

Just started using Office 2003 upgrade in a merge. Not sure I am liking it.
Question 1: The merge fields show up with a shaded background. I have gone
to the tools and under options, view, field codes, field shading and have
changed it to never but it still comes up shaded and even prints as shaded.
I can stand looking at it shaded but not printing as such. How do I get to
turn the shading off? I have even put the shading in borders to none also.

Questions 2: Also the font is a 12 in the letter and in the database is a 10
but when I merge it is now a 12 for the letter but the merge fields are a 10.
I want all 12. If I change the database to 12 it still is 10. If I change
the form letter all to 12 it still is a 10 in the merge fields. How do I get
everything to change to all 12 and stay that way. If I do the merge and
change the mergered letter to 12 it will only do the first record and the
other records are gone.

Question 3: In older versions of word when I merged it asked me if I wanted
to save as a new document. Now it just saves over the template and will not
leave me with a record of the old one. Any ideas. If I do a save as then
when I open it up it leaves me with a merge again. If I say no to the SQl
command it opens only one record not as many as the merge should have. Any
ideas on how to save to a new document? Thanks

I have two workbooks, each with identical worksheets , but they have been updated by different people. Each person will only update their specific cells, and there is no overlap - ie an individual cell is not updated by different people.

can anyone please suggest a way to compare the worksheets and identify the differences betrween these worksheets, then merge the worksheets so that all changes appear in the merged document?

Here's my problem. I have a column populated with values. I want the
macro to compare a1 with a2 and if they have same value, to merge them.
Then it would check a3 and compare it with the previous cell and merge
if the same value. If it does not have the same value, then go the
next cell and compare. The result would be multiple merged cells in
the column with the same value. Does anyone have a macro or know how
to do this?

Hi Guys!

I'm looking for some help with my excel workbooks.

I would like to be able to compare two workbooks, and display the discrepancies between them.

The two workbooks contain two columns - Surname & Date of Birth.

I want the macro to remove all people that match in the two workbooks, and leave the ones that don't.

I have provided two example workbooks with example details.

If anyone can help me it would be much appreciated.


I have two unrelated workbooks, each with multiple sheets with multi sheet
references. I like these references and in creating a new workbook, would
like to combine the unrelated workbooks. The combine (merge and compare)
option is not allowed by excel.

If I copy the sheets, one by one, the new sheets references to one another
will be lost. What do I do?


can anyone just help me giving information regarding merging the different workbooks in one sheet but in different tabs.
i have 20 workbooks which carries thousands of data i need to merge all those 20 workbooks in one excel sheets but in different tabs. please help me out.

I am trying to merge 365 separate workbooks to one workbook by tab. Each workbook represents one day, so I need to open one workbook with 365 tabs. Each workbook is the same template as the previous, but just saved as the date completed. PLEASE does anyone know if this is possible?

The purpose of this, which leads to my next question is this: Once I merge all the workbooks, can I get subtotals without having to equal each line?

Thank you so much for your help! Any suggestions are soooo appreciated!

i need to create a micro that can compare the value of the cells and if it is the same, it will merge. currently my code is


Sub Compare_Merge()

    Dim rng As Range
    Set rng = Intersect(Selection, ActiveSheet.UsedRange)
    If rng Is Nothing Then
        MsgBox "nothing in usedrange to be merged"
        GoTo done
    End If

    Dim x As Long, y As Long, i As Long
    For i = 1 To Selection.Areas.Count
        For x = 1 To Selection.Columns.Count
            For y = 1 To Selection.Rows.Count
                If Cells(y, x).Value = Cells(y + 1, x).Value Then
                    Range(Cells(y, x), Cells(y + 1, x)).Merge
                End If
            Next y
        Next x
Next i
End Sub

This help me compare and merge... but as my one cell contain like abt 4cells... my compare code will compare will a null value cell... thus will not combine it... anyone can help? thanks...

I have 21 excel workbooks, some have up to 100 worksheets inside.

I'd like to merge them all into one big doc. Can this be done?

I have looked around but only found how to merge shared workbooks.

Every worksheet has a common theme where they all have information that starts on line 1, 50 & 100.