Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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

Center Titles Across Multiple Cells in Excel
How to center a title across multiple cells in Excel in order to make good looking titles for your spreadsheets. S ...
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 ...

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.


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.

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!!

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?

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?

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.


Hi. I am trying to merge sheets in workbooks which would have been copied and modified by my peers. All the sheets to be modified are named "Info for PICA" and so i would like if a code to merge workbooks could use some GetFile function to merge all these sheets together into one sheet in one workbook.

Thanks in advance guys.

I have two Excel workbooks with several sheets in each. The workbooks
are essentially the same with the exception of a few equation buried
somewhere within a particular sheet. The changes in the equations are
minor, for example a '+' changed to a '-', but they impact the entire
calculation so all of the cell values are all different between the two

I've found a some comparison programs (e.g. 'Excel Compare', '4Tops
Compare'), but they seem to compare the cells values not the
underlying equations. I'm trying to track down all of the equations
that have been modified but I'm missing at least one.

Can anyone suggest a program or method to compare the actual equations
in the cells rather than just the values?


We versioned up to using "microsoft office excel 2007 - SP1 MSO". We are getting used to the new menus slowley but progressing. One thing that we have all noticed and cannot figure out, is how to unmerge cells once we merge them.

It seems like Excel will allow you to create a spreadsheet and merge/unmerge as needed. But it seems once you have closed and saved it, when re-opening it, and attempt to unmerge a cell, the Merge Cells menu has a box that is greyed out (protected) with a checkmark in it. And the menu will not allow you to unmerge.

How can I change this so that I again have the ability to unmerge these cells.

How to compare and merge cells?

Cell 1 Cell2

merge to

I have 3 different sharred workbooks with one master sheet that I am trying
to merge the others into. When I try to merge the workbooks I get the error
that excel needs to close. Any ideas of a bug patch or fix for this problem
or am I sharring incorrectly?



I want to select a folder and then merge the selected .xls files to one workbook (must include all sheets). I need this to be available from a menu so as I believe it has to be insertable as a module. Or can you call a "this workbook" function from a menu ?

I have three regional spreadsheets (that all use VLOOKUPs) - Called North, London, and SouthWest. I then have a master spreadsheet called National.

What I need to do is 3 seperate users look after the regional spreadsheets, but the National Spreadsheet needs to extract data from all 3 of these when column A contains data. (column B will always hold data as will be a reference number) so the compare/merge workbooks doesn't really provide what I need it to (plus this needs all 3 users to exit the workbooks for the national one to be updated - not the solution I really need.)

I am guessing there is no automatic way that is easy and will need some code. Can anyone help me/point me in the right direction?

Many thanks