Microsoft Office Excel offers many features for customizing tables and charts that contain important data. Using the Consolidate tool, you can merge and summarize data from multiple files or worksheets. This guide explains how to consolidate data from various files to Excel.
Steps
Step 1. Open the worksheets you want to consolidate
Once Microsoft Excel has started, you can click on the recent files you worked on on the main page. If you don't see the files you are looking for, click on You open in the left sidebar, then up Browse. Navigate to the location of the files that contain the data to be consolidated and open them.
Step 2. Make sure all sheet data is in the same format
Also check that the labels of all the sheets have the same format. For example, if the date is indicated in the first row, while the products are in the left column, make sure that the same structure is adopted in all sheets. Verify that the rows and columns are in the same positions, then delete all empty rows and columns.
Step 3. Open a new Excel sheet
This sheet will contain all consolidated information. If you want to consolidate the data contained in several different files, click on File, then on New one to open a new worksheet. If you are consolidating sheets in the same file, click the plus (+) icon to the right of the sheet name in the lower left corner to create a new one.
Step 4. To get started, click on the cell where you want to insert the consolidated columns and / or rows
Click on the row and / or column that is in the same position as the tables in the other sheets.
Step 5. Click on Data
You will see this button in the menu bar at the top of Excel. Press it and the Data section will appear at the top.
Step 6. Click the Consolidate icon
This is the button that looks like two sheets with a blue arrow between them pointing to a third blue sheet. Press it and the consolidation menu will open.
In earlier versions of Excel, this icon looks like a column of cells with a blue arrow pointing to a new cell
Step 7. Select a function
Use the menu under "Function" to select the data consolidation method. For example, if you want to add all the data, select Sum. You can also select Count, Average, Min, Max and other functions.
Step 8. Select a reference source
This is the first sheet to be consolidated. If the sheets to be grouped are in the same file, click the arrow icon next to the bar under "References". If you want to consolidate data from a different file instead, click on Browse, then select the file that contains the data to be consolidated and click on You open.
Step 9. Select the data to consolidate
If you want to group the data contained in separate sheets in the same file, click and drag the mouse pointer to select the data and labels that interest you. If, on the other hand, the data is in different files, open them all and repeat the same selection operation. In this way, the name of the file or sheet will appear in the reference field followed by an exclamation point and the range of rows and columns of data (for example "Sales SheetQ1! $ A $ 2: $ F $ 5").
Step 10. Click Add
Look for this button to the right of the "All References" field. Press it to add the selected reference and range to the reference list. Repeat steps 6-10 for all sheets and files to be consolidated.
Step 11. Select the row or column for the labels
Click on one or both of the boxes next to "First Row" and "Left Column" to select which row or column contains the labels.
If you don't check one of these boxes, Excel will consolidate all cells in the same position
Step 12. Click on the box
next to "Create links to references" (optional).
By checking this option, the consolidated data will be updated automatically if one of the reference sources is modified.
Step 13. Click Ok
This way, you will add the consolidated data to the master sheet. You can see the source data of the consolidated ones by clicking on the plus (+) icons to the left of the cell numbers on the left side.
