The consolidate function in **Excel** allows an analyst to combine information from **multiple** workbooks into one place.The **Excel** consolidate function lets you select data from its various locations and creates a table to summarizes the information for you.

## Consolidating multiple excel files

Last week, we learned how to use SQL and query data inside *Excel*. Once we have found the last row we then select the next empty cell below that and paste our data then.

This week, lets talk about how we can use VBA to consolidate **multiple** data sheets from different workbooks into one single worksheet. Additional things that may be used to enhance this code 1.

Step 4: Select the method of consolidation (in our example it’s Sum).

Step 5: Select the data including the labels and click Add Step 6: Repeat step 5 for each worksheet or workbook that contains the data Step 7: Check boxes “top row”, “left column” and “create links to data source” (note you don’t have to tick these boxes if you don’t want labels or don’t want live links) and click the OK button.

We’ve created a step by step guide to help you create your own consolidation by combining similarly organized data across *multiple* sheets and books.

The screenshots below will help you see an example of how to use the **Excel** consolidate function: Step 1: Open all **files** (workbooks) that contain the data you want to consolidate.Finally you click the Extract Metadata button, and the Spreadsheet does the rest. That is a great tip, however I wanted to consolidate **multiple** **Excel** **files** Create a new folder in your sharepoint site. Once you have the data the way you want it in Power Query, turn the query into a function. Add the code as shown below Save the Power Query Create a new query to import all the **files** located in your Share Point folder Once you have the **files** you need, create a custom column that uses your function produced in the first step above. You should end up with your consolidated table of data something like this.Add all the *files* you want to consolidate into this folder. First rename the Query to something like f Get My *Files* Share Point. 1429 x 8896 = ...3746 x 6721 = ...1045 x 9633 = ...1076 x 8143 = ...2187 x 6247 = ...7633 x 3084 = ...2078 x 6477 = ...7453 x 7521 = ...3575 x 5875 = ...5129 x 9606 = ...9496 x 1886 = ...4405 x 7860 = ...6969 x 4263 = ...3869 x 3341 = ...4366 x 1590 = ...6149 x 5623 = ...7184 x 9799 = ...9306 x 9666 = ...9578 x 5131 = ...6004 x 1418 = ...1617 x 3851 = ...7196 x 4164 = ...2914 x 9025 = ...2854 x 1270 = ...8738 x 7316 = ...5782 x 4131 = ...7108 x 3767 = ...8144 x 9998 = ...7378 x 3487 = ...9237 x 5952 = ...1952 x 9669 = ...9459 x 8649 = ...3281 x 5473 = ...5563 x 7139 = ...4821 x 4275 = ...3710 x 4269 = ...9731 x 1000 = ...5934 x 4709 = ...1064 x 6368 = ...5663 x 8187 = ...2825 x 3936 = ...8948 x 2473 = ...7707 x 5342 = ...7989 x 1235 = ...1299 x 5199 = ...9415 x 9751 = ...8342 x 3385 = ...6748 x 8069 = ...7258 x 8098 = ...3243 x 8682 = ...6626 x 6784 = ...3937 x 9387 = ...2571 x 5301 = ...1900 x 5556 = ...2635 x 8958 = ...6597 x 2345 = ...4658 x 2645 = ...5678 x 9794 = ...5924 x 1271 = ...7638 x 2578 = ...7373 x 3594 = ...3019 x 2620 = ...4603 x 8927 = ...3252 x 7793 = ...1529 x 5339 = ...8899 x 9837 = ...1522 x 9176 = ...4495 x 9587 = ...5326 x 5490 = ...1585 x 9865 = ...1272 x 3978 = ...6854 x 9677 = ...6648 x 1661 = ...1419 x 3307 = ...7865 x 8225 = ...5986 x 6603 = ...1060 x 2649 = ...5994 x 8369 = ...5634 x 8629 = ...7421 x 8914 = ...3081 x 3999 = ...8499 x 9930 = ...1829 x 6460 = ...1487 x 5469 = ...7037 x 9661 = ...2514 x 3434 = ...4870 x 6617 = ...6723 x 9840 = ...2029 x 5398 = ...6582 x 1742 = ...4320 x 6226 = ...4279 x 6883 = ...6195 x 7685 = ...8318 x 1551 = ...6196 x 7302 = ...1665 x 5013 = ...1884 x 9822 = ...1385 x 2870 = ...4470 x 1542 = ...8503 x 2780 = ...6474 x 9766 = ...2684 x 1007 = ...1690 x 9609 = ...8994 x 7958 = ...9601 x 2884 = ...1628 x 1381 = ...4426 x 6199 = ...5869 x 1165 = ...5764 x 6688 = ...6739 x 3231 = ...6439 x 4620 = ...9872 x 6830 = ...1536 x 8184 = ...7618 x 1996 = ...1023 x 4932 = ...4925 x 1798 = ...2506 x 4478 = ...3338 x 5898 = ...6198 x 4808 = ...5844 x 2350 = ...1150 x 7459 = ...3175 x 4250 = ...7855 x 2153 = ...9018 x 6086 = ...3859 x 7900 = ...6197 x 8296 = ...3577 x 9812 = ...2697 x 9751 = ...5168 x 6713 = ...1151 x 3165 = ...4980 x 5599 = ...5017 x 2357 = ...3422 x 7002 = ...1967 x 4155 = ...5496 x 2948 = ...8427 x 9881 = ...2452 x 5219 = ...7617 x 6675 = ...3569 x 1819 = ...5430 x 1349 = ...9966 x 4768 = ...1844 x 8269 = ...9055 x 6496 = ...7305 x 4999 = ...2224 x 1194 = ...2138 x 9673 = ...1975 x 2270 = ...1083 x 5300 = ...9991 x 2509 = ...8012 x 5362 = ...6815 x 2402 = ...2459 x 9405 = ...3899 x 9321 = ...6578 x 5676 = ...4143 x 1543 = ...8340 x 1274 = ...4517 x 4725 = ...9559 x 2860 = ...1050 x 3722 = ...1198 x 8453 = ...7072 x 7857 = ...8593 x 5786 = ...1898 x 9143 = ...7288 x 7193 = ...3554 x 4093 = ...4850 x 9434 = ...1981 x 7023 = ...5070 x 3579 = ...8443 x 5112 = ...4367 x 9114 = ...1880 x 8586 = ...5550 x 4933 = ...3891 x 9345 = ...5027 x 1467 = ...3595 x 2729 = ...4023 x 8393 = ...6534 x 1177 = ...1955 x 3217 = ...7768 x 5744 = ...6106 x 5606 = ...3963 x 5221 = ...5385 x 4800 = ...3352 x 6094 = ...5479 x 3206 = ...2884 x 9656 = ...7794 x 2041 = ...7239 x 2457 = ...8306 x 9076 = ...1255 x 9435 = ...8436 x 1978 = ...5585 x 7091 = ...8336 x 5942 = ...3225 x 9916 = ...9970 x 5382 = ...8437 x 4999 = ...2498 x 1234 = ...5528 x 3088 = ...4390 x 5640 = ...4767 x 4581 = ...7249 x 5559 = ..multiplication worksheet generatorfun maths worksheetvlookup *multiple* worksheetssolving *multiple* step equations worksheetworksheets on dividing fractionsfree maths worksheets for year 13rd grade math review worksheetssimple sentences worksheets for kindergartenfraction of amount worksheetadding decimal worksheets2015 | Any content, trademarks, or other material that might be found on the website that is not property remains the copyright of its respective owners.In no way does claim ownership or responsibility for such items, and you should seek legal consent for any use of such materials from its owner..To merge data from all workbooks in a folder, type or paste the following code in standard module in the Visual Basic Editor.