Consolidate Tool: Learn how to consolidate data from multiple worksheets

58
46809
tool-consolidate-excel-8
Excel Spreadsheets

What is it: Data consolidation is basically the union of information contained in different worksheets so that they are grouped into a single worksheet. In this way, the consolidation tool comes up with the practicality in several cases, where you work with several spreadsheets and if you want to get information from them in a single spreadsheet.

Example of use:

To better understand this tool and its implementation, we will use the example of a company that sells computer components (computer parts). This company has three units in which they make random sales and each one feeds a sales spreadsheet containing information about the name of the seller, the product sold and the date of the sale. As shown in the figure below:

tool-consolidate-excel-1

These worksheets are saved on a company server in a specific folder for sales control, and at a certain point there is a need to group the information in these three worksheets to get all the information in a single worksheet.

Thus, without the knowledge of the consolidation tool, a user will have to update daily the worksheet that will receive the information of the three units of the company, thus generating an unnecessary work. On the other hand, consolidating this information into a single worksheet will automatically update it when there are changes in the other worksheets.

Consolidate spreadsheet data:

After we understand the use of the consolidation tool, we will use the example above to show step by step how to consolidate spreadsheets. See below:

1 - Identify the worksheets to be consolidated. In the case of the example cited, the worksheets are in a single folder but they can be in different places without any problems.

tool-consolidate-excel-2

2 - Open a new spreadsheet and go to the "Data" tab by choosing the "From Other Sources" option and choose the "Microsoft Query" option again.

tool-consolidate-excel-3

3 - After selecting the "Microsoft Query" option will open a new window, where you should choose the "Excel Files *" option and click OK.

tool-consolidate-excel-4

4 - It will open a new window where you should point the path of the files to be consolidated. In the example cited, we will point to the spreadsheets of the three units of the company.

tool-consolidate-excel-5

Select the first worksheet and click OK. In the next step, add the tables you want in the "Query Columns" field and continue to finish.

tool-consolidate-excel-6

6 - When you click Finish, note that the first worksheet is already consolidated. Repeat the steps from 2 to 4 to add the remaining worksheets to be consolidated. At the end of the procedure, we will have all the worksheets grouped into a single worksheet as shown below:

tool-consolidate-excel-7

In this way, each change made in any of the three worksheets will also be modified in the spreadsheet that we consolidate. Just in this case, access the "Data" tab and click the "Update All" button.

With the example quoted, one can conclude that the consolidation tool is critical for grouping information from multiple worksheets into a single file. Regardless of the type of data that these spreadsheets carry, data consolidation brings the benefit of the practicality in linking information for reports and other analyzes as well as getting them updated according to the modifications in each of the spreadsheets.

Did you have any questions about this content? Leave a comment below that we can help you with!

Excel Spreadsheets

58 COMMENTS

  1. hello, I liked the information a lot… but I'm in doubt how to proceed…
    I have 02 extensive spreadsheets from 02 branches, both of which have the same customers and their respective orders, are in the same file, but in different spreadsheets (type: plan1; plan2), one spreadsheet has some types of information and the other types information; I wanted to create a third spreadsheet, where I could unite both, where there was the possibility to unite the information of equal orders and unite all its columns, for a better visualization. can you help me.

  2. Hi Deborah, I don't know. As I do not use this tool often, I could not say why this functionality is unavailable

  3. Can you consolidate data from online spreadsheets, for example from outlook?

  4. Hello, When I repeat the process to insert the other tables, the microsoft query option is unavailable.

    Can you tell me why?

  5. Hi Gilson, I would try for the consolidate tool anyway. If not, just creating a VBA code to make a blank cell fill routine

  6. Rafael. In my work we have a spreadsheet with thousands of lines containing cells that say “RECEIPT DATE” “TITLE” “SACADOR” “PAYER” “VALUE”. We search for the security value and feed the corresponding cell with the date of receipt. As the volume is very large, is it possible for one person to use this spreadsheet on the network and another to use the same spreadsheet in another environment outside the network and later concatenate those spreadsheets into one?

    Thanks in advance.

  7. Hi Cristiano, it is possible that your spreadsheet only has data ranges and not tables (this is a structure recognized by Excel) - to insert, just select the range and on the INSERT tab choose the TABLE option

  8. I'm trying to consolidate the spreadsheets, but when selecting the query, it shows the message "this data source does not contain visible tables"

    What could be wrong?

  9. Hi Augusto, just create a list with validation of data in a cell by placing the desired products and then use a PROCV to automatically pick up the value of the chosen one

  10. Hello Rafael, I would like to create a spreadsheet where each cell has several products so that I can choose which I am working with at the moment and that already gave me the value of this product per unit!
    I do not know if I understand Anything send me an email and I explain better !!!

    Thank you for your attention and information. I have learned and taken many doubts with you !!

    Augusto !!

  11. Hi Rafael, how are you?
    I need help on a spreadsheet. I need to create a spreadsheet with the companies that I work with, the input of the products and the output of these, generating an inventory, that is, a final stock of what came out and entered

  12. I loved this tool that I read here, I'll apply. to then gather all in only one header, you can do a pivot table, right?

  13. Hi Sérgio, in this spreadsheet you choose the person, you must have the INDIRECT function to fetch all the data. Using a PROCV you can bind the person's name to a default name, and all the tabs in the worksheet will need to have names where you can use that pattern. For example:
    1 - the tabs are called plan1, plan2, plan3 etc.
    2 - when choosing a person, the VLOOKUP searches for plan1, plan2 etc. according to the person chosen in cell A1 for example
    3 - in the formulas you have = INDIRECT (A1 & "!" & C2) when reading this formula, Excel will understand this here = plan1! C2 which is the value in C2 in the chosen person's plan

  14. Hi Gimena, this is a very chatty process, it is important to understand the structure and see how potential modifications affect the consolidated results

  15. I did it all !!! But when I changed the information in the spreadsheets that fed the main the information was gone.

    The worksheet that consolidates was with the structure of the tables but without information !!

  16. Hi Rafael, see if you can help me. I have a spreadsheet in which the content is text, it will be sent to several people to evaluate each item and fill in with comments and suggestions (all in rows and columns of this spreadsheet). I wanted a way to consolidate these spreadsheets, but in the following way: as they are all the same spreadsheets, I wanted a “master” spreadsheet to display what was filled out by one of those people who filled it out, but so that when I chose someone else, it would be displayed in that same spreadsheet what that other person filled out (the content displayed in the spreadsheet would consolidate in relation to who I chose to display). To clarify, in your example it would be as if you created a cell in which you could choose the Unit, and the spreadsheet would bring the data from that unit, so you could switch to another unit, and the data in the spreadsheet would change.

  17. Hi Silene, for this you would probably need all the spreadsheets saved in the same folder and could use the INDIRECT function to fetch the values ​​of the cells in the corresponding workbooks.

  18. Good morning, Rafael.
    It would be possible in a single worksheet, to gather 3 to 4 cells from 300 worksheets written in different files but from the same excel. Each worksheet file has the same structure but with a different name.

  19. Hi Cris, at first I do not know what could be happening. It would be good to understand a little more of the problem that is occurring to try to come up with a solution. If you can explain this mistake a little more, maybe I can give you some idea.

  20. Rafa, I did exactly the process you explained to unify the worksheets. In case my files are not in the same document, different names are saved, because they are files of three users, but the worksheet has the same structure. Well the process has brought me the information of all of them, but when I do testing impute information and I return to update the consolidated basis does not bring me the new information. Can you help me?

  21. Hi Amanda, I'm not sure how this procedure can happen. The ideal is to have spreadsheets with equal structures, but anything is just testing and trying to chaste in the result

  22. Hi, I am consolidating data from registering other spreadsheets, but if I have 12 items to be consolidated from a spreadsheet, I can only consolidate 8 items into the spreadsheet I am grouping. Is there a limit to items that I can consolidate? Thank you!

  23. Hi Wlademir, I've never had a similar problem, so I'm not sure how to help. Maybe if you lock your entire worksheet with password? Or create a general protection procedure after using Consolidate?

  24. Hello, I have a doubt, when consolidating a spreadsheet I realized that the process provides the return of all the data of the source worksheet, regardless of the password being protected by password, I would like to know how do I prevent the consolidation accessing data that should be protected in the source worksheet ?.
    Now appreciate the attention.

  25. Hi Glaucia,

    perhaps your spreadsheet has some formatting or data with some kind of incompatibility with the process that we quote here. It pays to try a very simple spreadsheet to see if the whole process is working properly and what may have gone wrong in relation to what you are doing. Another possible incompatibility may be your version of Excel compared to what was used here when we made this post, which seems to me to have been the 2007 version

  26. Rodrigo from the sky! You solved my problem in one second !!!
    Too much!
    Thank you!

  27. Hello… I don't know much about Excel and I'm using the step by step to create a spreadsheet with consolidated data. However, when I reach the 4th step, clicking “OK” returns me a box with the message “This data source does not contain visible tables.”. What could be wrong? It would be possible to give me some instructions to complete my spreadsheet.

    Thank you!
    Att

  28. Hi Romulus, I do not know if I understood very well what you wanted to say there. To put a number and fill in automatically you would need to use search functions such as PROCV but I do not know if after consolidating you would have some problem with that kind of functionality

  29. Hi Rafael. All right?

    Where I work I fill in the equivalent of 100 weekly with the same number of rows and columns, only with data related to date and different values. I would like to consolidate all the data into a single worksheet.

    Eg: There are four columns, being: Number, Start Date, End Date, Value.
    I would like to know if it is possible to fill in the “Number” column (which would be part of the spreadsheet name), collecting the remaining data automatically.

    Grateful for the attention. Great week.

  30. Hi Bruno, how are you?

    I did not understand your doubt very well. Can you explain a little more? Anyway, I would advise you to do the test using SOMASE instead of the subtotal to see if it works normally with other functions.

  31. Hello Rafael, I'm having trouble applying formulas to the consolidated worksheet, eg. When using the SUBTOTAL formula, it always returns me zero, as if there were no numbers to be added, other than the separate worksheets.

  32. Hi Luis, you can do this procedure using the remove duplicates feature (DATA tab)

  33. Good evening! I have two spreadsheets with 1000 part numbers each, some part numbers repeating themselves. How to make a single worksheet with all the part numbers of the two worksheets, but eliminating the repeated PNs, that is, leaving a part number of each?
    I meant to eliminate only the PN repeated, for example if the PN xyz repeats once, it eliminates this repeated and leaves only once the PN xyz in the spreadsheet.

  34. I do not quite understand Bruno, can you explain a little more? If you need, please attach a spreadsheet image here

  35. Good afternoon!! I'm with several spreadsheets that I'm trying to put together in just one time!
    It is as follows, each employee (there are 4) fills out a standard spreadsheet in a dynamic table format, and in this spreadsheet the tasks are distributed to each employee, that is, each one receives a task to be performed and in the spreadsheet he fills with the name the responsible employee; client name; request number; start date; delivery date; product code and etc …………. however, I need a master spreadsheet that brings together all the information in the spreadsheets filled in by the employees and that the information is classified in ascending order number, eg order 0001.16 would have a total of 20 lines filled by the employees and in each line would have the random names of them, so I can see which one would be late in their tasks ……… .. as explained above, I managed to gather the information in a master worksheet, but it is not possible to classify the information in its total, only for each consolidated worksheet individually .

    I tried to be as clear as possible in the explanations, if in doubt I send the template of the worksheet.

    Hugs

  36. Hi Bruno,

    I imagine that consolidating 300 spreadsheets is a job that will leave the final spreadsheet heavy and difficult to move.

    Other than that, unfortunately I'm not visualizing any way to do this job no.

  37. Hello Rafael, my doubt is. I have a folder on disk C: with 200 worksheets, all the same but with different products, I needed to do a search for a specific product on all of these worksheets. Example: search ref A300 and return the data from this ref.

  38. Yes, I would have Adriana, but I do not know if it will be easy to explain. You have some ways.

    1 - Create a spreadsheet with customers in rows and products in columns
    2 - Now you can use a VLOOKUP with SE for each cell doing a customer search for the product. IF you have purchased, 1 appears, if not, it appears 0. So you would already be able to see

    3 - another solution would be to use a pivot table and summarize the table for customers and products purchased, so you would see exactly the same analysis dictated above

  39. Good evening,
    I have three spreadsheets, one with all my registered customers, another with the products I sell and another with what each customer has bought, I would like to make a spreadsheet to know which product each customer has not yet bought

  40. Hi Pricila, how are you?

    I really do not know what the problem might be. This may be a problem with the version of Excel that you are using.

    Either way, you can do this in a more manual way using simple reference to other spreadsheets.

  41. Good afternoon,

    I am not managing to add the other spreadsheets, returning to 2 to 5, since the button from other sources is disabled after the first query, how should I proceed?

  42. Glad you got Rodrigo and I'm glad to have helped in some way. Always good to have content available here for the other readers who might need it. Thanks for sharing! Hugs

  43. Rafael,

    Thanks a lot for the tip, while reading the Microsoft article, gave me an insight and I ended up using another macro that served perfectly for me, because after applying it, I just made a simple filter and got everything I wanted.

    The Macro I used was this:

    Sub Consolidate ()
    Sheets (1). Range (“A2: G10000”). Clear
    plans = Sheets.Count
    line = 2

    For n = 2 To plans
    lin = 5
    Do Until Sheets (n) .Cells (lin, 1) = “”

    Sheets (1) .Cells (row, 1) = Sheets (n) .Cells (lin, 1)
    Sheets (1) .Cells (row, 2) = Sheets (n) .Cells (lin, 2)
    Sheets (1) .Cells (row, 3) = Sheets (n) .Cells (lin, 3)
    Sheets (1) .Cells (row, 4) = Sheets (n) .Cells (lin, 4)
    Sheets (1) .Cells (row, 5) = Sheets (n) .Cells (lin, 5)
    Sheets (1) .Cells (row, 6) = Sheets (n) .Cells (lin, 6)
    Sheets (1) .Cells (row, 7) = Sheets (n) .Cells (lin, 7)

    lin = lin + 1

    line = line + 1

    loop
    Next
    End Sub

    Hugs

  44. Hi Rodrigo,

    you can use 3D references in a summary tab to tell you when the status changes, but I imagine it to be work, since you would have to create a function referencing all existing tabs.

    I never had to consolidate multiple tabs from a single spreadsheet, but I found this Microsoft article, see if it helps - https://support.office.com/pt-br/article/Consolidar-dados-de-v%C3%A1rias-planilhas-em-uma-%C3%BAnica-planilha-69c84929-5f67-48cf-b48e-e55b20cda2ef?ui=pt-BR&rs=pt-BR&ad=BR

    Hugs

  45. Good afternoon.

    How do I gather all the data from multiple tabs into a single tab?

    I have a spreadsheet that on each tab is a client, with their data and payments.

    Do I need to gather all of the open payments from these clients in a single tab or spreadsheet, would I have some formula or some macro that would do this?

    And that as the person goes paying is already coming out of that new sheet or tab?

    I thank

  46. Good evening.

    I need someone in excel to make a consolidated worksheet for membership repayments.

    I need this urgently.

    If someone has the knowledge and is willing to join this project.

    Send me an e-mail and I'll give you more details.

    [email protected]

  47. Thanks Rafael, in fact the procedure is daily and consolidates 6 spreadsheets into 3 that are the basis of some managerial views… even in this model that you presented I had problems with the update, but I haven't had time to stop and test to try to find one problem. News I post here… abs

  48. Hi, Jeferson, is everything okay?

    I do not know a procedure that allows this, but I do not think it's impossible not to. Perhaps by searching a little more, you will find more information.

    In the example below, you would easily solve by deleting unneeded header lines to straighten the worksheet. If your procedure is sporadic, it is a very small job.

    I would only spend time looking for this solution if you have a spreadsheet with a huge amount of data or something you do every day

    if I can help in other ways, just tell me

  49. can I merge the two worksheets into one table with a single header?

LEAVE AN ANSWER

Please, write your comment
Please enter your name