How to Consolidate Data in Excel 2013 - 2003

Note. The updated version of this tutorial can be found here: How to consolidate data from multiple worksheets into a single sheet

There are many ways to consolidate data in Microsoft Excel. You can use the Table feature and its associated filters to filter a list so it shows only those items you are interested in seeing and you can create PivotTables to look at your data in different ways. You can also consolidate large amounts of data into a summary form which can show you as much or as little of your data as you want to see. In this post, I'll show you how to use the Consolidate data feature in Excel to take data from a series of worksheets - or workbooks - and summarize them into a single worksheet.

Work with multiple data sets

Consider the situation where you have data for twelve months of your business stored one month per sheet in an Excel workbook. If each sheet contains that month's data and if it is laid out in the same arrangement of columns and rows then you can summarize this full year of data into a single sheet using the Consolidate tool.

With a series of data like one sheet for every month, you can consolidate it into a summary worksheet  using the Consolidate tool.

To do this select an empty sheet in the workbook - add a new one if necessary - and click in it. Choose Data > Consolidate to view the Consolidate dialog. Here you will select the function to analyze your data and the references or ranges that you want to consolidate. For our data we want to add the values so we'll set the Function to Sum. However we could have chosen any of these 11 functions: Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var and Varp.

For the summary to total all the data, select Sum as the function.

Click in the Reference area and click the Expand/Collapse button to its immediate right. Select over the first data range to consolidate - to do this you will need to click the Sheet tab and then drag over the data including the row and column headings.
Select the first sheet with data to consolidate and drag over the data area including row and column headings.

Back in the Consolidate dialog, click Add to add this first set of data to the consolidation dialog. Continue and add the second and each additional set of data to the References section of the dialog. Provided you have the same range of data on each of your worksheets and they are all in the same position all you will need to do is to click the Worksheet tab and the range will already be selected for you so it's not as big a job as it might seem.

The ranges you consolidate do not necessarily have to be of the same size in each worksheet. So the number of rows or the number of columns or the number of rows and columns might be different from sheet to sheet. For example, if we had opened a new office in a September, we would need to expand the range selected in September to account for the additional rows of data for that new office and we would then continue to do that for every worksheet that contained those extra rows.

If you have extra rows or columns in subsequent worksheets you can select a different range to consolidate on these sheets.

Top Tip

You can name your ranges before you start the Consolidation process. To do this, select a range and type a name for it into the Name box at the far left of the Formula bar. If you name each range then, when you create the consolidation, place your cursor in the Reference field, press F3 and then choose the range from the list in the Paste Name dialog. This way you can give each range a descriptive name and you won't need to remember later what you had entered in Sheet1, cells A3:F40.

You'll see that the worksheets appear in the All References panel in alphabetical order. Check that you have all the references there before you continue. Select the Use labels in: Top Row and Left Column checkboxes. Select the Create links to source data check box and click Ok.

Add references to each of the ranges with data to consolidate

The consolidated data

When you click Ok, Excel summarizes all the data into your new sheet. You will see grouping tools down the left of the screen which you can use to display and hide the data.

The consolidation shows a summary of the data you had selected with Grouping buttons down the left of the screen.

If you chose "Create links to source data" then the data is linked to the original cell containing it. To see this, click in a cell with the data (not a sum function) and you will see a reference to the sheet and cell that contains that data.

Choose to link to the source data and each cell will contain a formula linking back to the original data.

If you did not choose "Create links to source data" the consolidation is simply a summary of the data without any details so there is no grouping applied and the results are summarized only.

Without linking to the source data you get a summary with no indication of where the data came from.

Top Tip

As this data is linked you can use the Trace Precedents option to go to the cell containing that data. To do this, click in the cell that contains the data you want to view. Click Formulas tab > Trace Precedents. Because the precedent cell is on a different worksheet hold your mouse over the black arrow head till the mouse cursor turns into a hollow white arrow. Double click to open the Go To dialog - the cell reference will be automatically listed there. Click it and click Ok to go to that cell.

To view a linked cell on another sheet, use Trace Precedents

Format the data

You can format the data in the summary worksheet as you would in any regular Excel file. You'll find that the second column of data shows the name of the workbook that contains the data. You can hide this column, if desired, by right clicking it and choosing Hide. This simply hides the column so the data is there should you need to refer to it later on.

You can format the data to display it more effectively

Different workbooks

One of the benefits of using this Consolidation tool is that the data does not need to be in a single workbook. Instead, if you have a series of workbooks each of which contains a similar arrangement of data for different periods of time, you can consolidate all these using this tool.

To do this you would use the same process as before and select an empty worksheet or click Insert > Insert Sheet to include a new worksheet in a workbook. Choose the Consolidate tool and this time instead of selecting a sheet in the current workbook, click the Browse button to open a different workbook.

Now select the references to use in that particular workbook. Repeat for each workbook containing the data you are interested in viewing. I find it easier to switch between open workbooks when I add the Switch Windows button to the QAT (Quick Access Toolbar).

Add the Switch Windows button to your QAT if your data are in different workbooks

If you select the Create Links to Source Data checkbox then, when you create your consolidation, the connection will be live so that changes in the underlying worksheets and workbooks will be reflected in the consolidated data. In addition, the second column of the summary will display the workbook name. The tip about using Trace Precedents also works to quickly take you to the workbook if it is open already, but will fail if it is not.
>Update the consolidation

If the data in your sheets changes, for example, you add another office after you have created your consolidation, first add the data to the appropriate sheets and save the workbook. Then go to the sheet containing the consolidation and select and remove all the rows containing the consolidated data.

Click Data > Consolidate and you'll find the references are all still in place. Make changes to the data by adding or removing ranges or changing the range size and click Ok to recreate the consolidation.

Warning tip!

If you have chosen to link back to the source data, you cannot update the consolidation by choosing Data > Consolidate without having first removed the earlier results. The reason is that this will embed one consolidation inside another rather than updating it making a nonsense of the worksheet and the results. You can update the ranges manually by changing the formulas but it's probably best to start over.

If, however, you did not select to link back to the source data, you will find the consolidation is simply a summary of the data without any details. In this case you can update the consolidation by clicking in the top cell containing the results and choosing Data > Consolidate, make the changes and click Ok.

The benefit of this Consolidate option is that it allows you to keep your data where it was originally located even across multiple worksheets and multiple workbooks and still consolidate it for analysis. While you cannot reorder the data as you could in a PivotTable if you don't need that functionality and if all you need is a simple summary worksheet laid out in a similar way to the original data then this is a quick and easy solution.

See also

33 Responses to "How to Consolidate Data in Excel 2013 - 2003"

  1. Aditya says:

    This really nice example to explain

    also need some more example on some other topic

  2. Sean says:

    Hi,

    I'm trying to consolidate information between different workbooks. I have followed the instructions, however am getting the following error;

    Cannot open consolidation source file 'workbook.xlsx'

    I'm also noticing that the switch windows function does not show anything other than the workbook I am in (I tried when I had two workbooks open, and six, to no avail).

    For reference I'm using Excel 2007 professional on a Windows 7 machine.

  3. fildenaonline.blogspot.com says:

    Hey there, You've done a fantastic job. I will surely delicious that plus our view propose in order to my buddies. More than likely are going to took advantage of this web site.

  4. Anonymous says:

    Hi, is there a formula that can be used to replace the consolidate function? thank you.

  5. David says:

    Thanks for the help. This was useful.

  6. nuzhat fatima says:

    i am trying to consolidate data but the result was shows that "source refrence overlap destination area" now what should i do?

    • Alexander says:

      Hello Fatima,
      Try inserting the result onto a new sheet. It looks like you are trying to add it on the same sheet where your source data are located.

  7. Noam says:

    Hi,
    Thanks for a great explanation!
    Is there a way to show the sheet name instead of the workbook name when creating link to source data?
    Thanks,
    Noam

    • Alexander says:

      Hello Noam,
      A sheet name and workbook name should always be specified in the path. If you create a reference link to a spreadsheet inside the same workbook, you can omit the workbook name. Did I answer your question? Or, were you asking about something else?

  8. ANAND says:

    Hi,
    Extremely Thankful for a neat and easy to understand explanation!

  9. ANAND says:

    Required more clarification on how to update changes in Data Consolidation if the changes in original input data is done

  10. Neeraj kumar says:

    good example.

  11. Prashant says:

    Nice Clarification

  12. mano says:

    Thanks for helpful tips.
    Here I have a problem
    for example
    in cell A1, I had a text in font Bodoni
    in cell B1, a text in font Arial Black

    while I combine these two cells, font change. I want to keep the same fonts.

  13. Rupali Piane says:

    Thanks.
    its very useful information.simple steps and easy to understand.

  14. Tarafa says:

    Thanks much! this was helpful.

  15. Andrew says:

    Great info, thanks!
    I do have a question: I have 2 workbooks; I am entering data on workbook A over a weekly basis and collating into monthly on that workbook. I then have another workbook, B, which I am transferring data from monthly results into yearly. My problem is that I have monthly tabs in workbook B (Jan - Dec.). Can I enter data into workbook A, have it transfer into workbook B (January), then clear data from workbook A to create new month. This would then mean I will have to change the tab in workbook B to the next month (Feb), while retaining the Jan. data.I know that the cell references in workbook A will have to be changed to reflect the new tab (Feb) in workbook B.... Can I do that at all?
    Thanks. Andrew.

  16. danjuma says:

    It was really helpful

  17. Ana says:

    How do you administer an Excel based datasheet that contains information from multiple business units?

  18. Ellen1J says:

    Keep in mind, when you need to expand a data range (such as in the example below of adding another office) for the consolidate function to work, additional rows must ONLY be added to the bottom of the data range -- thereby keeping all the rows above in the same order. In other words, you cannot insert additional rows in the middle of pre-existing data...

    <>

    Does anyone know a way to consolidate data for multiple ranges when the data is a consistent column, across worksheets, but NOT in a consistent row (for example, if an individual is added to a list, in alphabetical order)?

  19. Bn_Islaam says:

    Hello,

    How do i let excel auto-assign positions in a workbook of different sheets without having to copy their averages elsewhere before transferring them back?

    Regards

  20. Zondra says:

    I am trying to add up data collected from every day of the year. I have a sheet for each day (there are multiple things were are collecting data on each day, so it's a pretty full sheet), and a workbook for each month, with a summary sheet for that month, with a simple SUM function for each item. So, I have 12 workbooks and am working on creating another 13th workbook that is a summary of all the data. I started by doing a simple SUM function, going into the summary page from each month and picking the corresponding cell (every sheet is laid out the same). Now that works just fine, but I can't get the formula to copy down to the next cell down. For example, in A1, I want to total all the A1's for all 365 sheets. But when I copy that formula down to the next cell, A2, it doesn't update to A2, it keeps it as A1. Well, I want to copy it down all the way to A266, since that's how many rows of data I have. So, I looked into using the consolidate feature, since I am not about to retype that formula, with info from 12 different workbooks 266 times! Anyway, when using the consolidate feature, it doesn't seem to let me pick just one sheet from the other workbooks. I get "Consolidation reference is not valid" error message. Is there a way to get a sum on all 266 of my items, from 365 sheets? I would prefer to have a different workbook for each month, as I do not want to have more than 32 sheets per workbook.

  21. Zondra says:

    Nevermind. I think I figured out the answer to my own question. I'm going to stick with the original SUM formula. It had a $ in it, so that's why it wouldn't copy down to the next cell. Silly simple mistake and it had me all confused.

  22. Shane says:

    Hello Alexander

    Your instructions for this including links to source data worked perfectly. Thank you for helping me learn something new.

    However I decided a different approach was more suited for what I was trying to accomplish and now I am unable to reverse what I have done. I deleted everything via the original dialogue box but still have hidden rows and the additional columns prior to the A column.

    Is there another way around this?

    Regards

  23. Rhodri says:

    I manually checked to see if the data in Consolidate format was correct. The sum and average were both the same, but the standard deviation was different any suggestion why this maybe the case ?

  24. Mink says:

    I have a consolidated file and I need to copy from that file and paste it to another worksheet, however when I do it, the figures change to 0. I need to just copy paste from the file. How to do it?

  25. kirti says:

    Hello..

    I am trying to distribute data from one file source to other file having common reference id..

    eg.
    1121 dmadk
    1122 kekekqk
    1123 wqwwkq this data to 1121 dmadk
    1121 dmadk
    1121 dmadk
    1122 kekekqk
    1122 kekekqk
    1123 wqwwkq
    1123 wqwwkq
    1123 wqwwkq
    1123 wqwwkq
    1123 wqwwkq

    like that.. Is there a command that could ease this data copy paste task having common reference id...many thanks..

  26. someone says:

    When combining multiple workbooks, how do you keep make a column of the worksheet name. So when they all go into one workbook, all the data would have which sheet the data came from. Thanks

  27. Deepak Sureana says:

    Hi,

    I am looking to make a consolidated sheet, I have a data sheet where date-wise data of different items is mentioned. for E.g. on 21/10/2016 various items have been distributed in five different places, similarly daily wise report sheet have already been created. I need to make a consolidated sheet where in i can get a drop down option of choosing the date and once selected the data of the particular date is shown.

  28. Sweety says:

    Can u plzz tell me consolidate and compiling the data are both same thing?:

  29. Awdhesh says:

    Bank A/C No. Compliance Diff
    S00095204661 12043 11207
    but next sheet i want to these data to same id on diffrent way
    for exp :-
    S00095204661 12043 (I want to these type)
    S00095204661 11207 (I want to these type)

    please help me urgent on tommorow morning
    my mob no 8802059786

  30. Awdhesh says:

    please tell me date 27/03/2018 before 9:00 am

  31. Jerry George Cobbina says:

    to consolidate data, write a formula that will add the values of cell A1 from sheet 1 in two workbooks (named Region1 and Region2.
    Please any help?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard