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:
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.
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.
3 - After selecting the "Microsoft Query" option will open a new window, where you should choose the "Excel Files *" option and click OK.
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.
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.
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:
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!