4 ways to consolidate data with Excel

4 ways to consolidate data with Excel
4 ways to consolidate data with Excel
Anonim

You can use Microsoft Excel to perform various data processing and tracking. Then you have the option of presenting them in the form of tables or graphs. Functionality consolidate is very useful in this spreadsheet to group data from several sheets onto one. It is possible to do this in several ways, such as using the function to consolidate by category or by position, but also by creating a pivot table or using the formula SUM.

Steps

Method 1 of 4: Consolidate by position

Consolidate in Excel Step 1

Step 1. Check the location of the data

Check that all your data is positioned in the same place in each of the worksheets with the same titles. Your data will thus be arranged in the form of a list. Also remember to remove all empty rows and columns.

  • Prepare your spreadsheet which will be used to consolidate your data spread over several sheets. Create exactly the same format for the sheet, but without taking over the data.
  • Name each data range. Select the data range, then click the tab Formula. Then press Define a name in category Defined names. Finally, enter a name for the data range you selected.
Consolidate in Excel Step 2

Step 2. Organize data consolidation

Select the cell at the top left, where you have planned to display your consolidated data in the sheet provided for this purpose.

  • Click on the tab Data of your data summary sheet, then press Consolidate which is in the section Data tools.
  • In the dialog box, select Sum in the drop-down list of the named field Function. This will allow you to consolidate your data.
Consolidate in Excel Step 3

Step 3. Enter the names of the tracks

In the field Reference, write the name of one of the ranges you want to consolidate, then click the Add button. Repeat the operation for all the ranges to be consolidated.

Consolidate in Excel Step 4

Step 4. Set up the consolidation

In the dialog box, check the box Link to source data, if you want the update to be done automatically. If, on the contrary, you prefer to do it manually, do not check the box. Link to source data.

Method 2 of 4: Consolidate by category

Consolidate in Excel Step 5

Step 1. Apply the same process

To perform a consolidation by category, carry out the same procedure as for that by position. Organize your data in the form of a list. Then, select, in the worksheet provided for the consolidation, the upper left cell where you want to display the consolidation of your data.

Consolidate in Excel Step 6

Step 2. Create the consolidation

Click on the tab Data, then in the section Data tools select Consolidate. In the dialog box that opened, choose Sum in the field Function. Then enter the name of the data ranges in the field Reference and press the Add button. Finally, automate the data update, if you want. Note that the difference with the previous method is just in the fact that here the data ranges are not positioned in the same places in all the data sheets and that the consolidation is therefore done by data category.

Method 3 of 4: Use the SUM formula to consolidate data

Consolidate in Excel Step 7

Step 1. Organize your consolidation sheet

In the spreadsheet that will present the consolidation of data from other Excel sheets, enter the different column and row headers that you want to appear in the presentation of your consolidation.

Consolidate in Excel Step 8

Step 2. Perform the consolidation

In each of the sheets containing data, add the data which will then be consolidated in the sheet provided for this purpose. You can write, for example = SUM (sale) in the cell A1 of the leaf region1, if you have named a data range sale. Finally, in the sheet that presents the consolidation, write a formula with SUM, like = SUM ('region1'! sale; 'region2'! sale; 'region3'! sale) to consolidate sales from different regions.

Method 4 of 4: Create a PivotTable

Consolidate in Excel Step 9

Step 1. Make a pivot table

Use a pivot table to consolidate data from different data ranges. Note that these can be moved, depending on your need.

  • Start the creation of the table. From the 2007 version of Excel, press the alt = "Image" + S + UA keys to start the creation process. Select the data ranges that you plan to consolidate, and then click OK.
  • In the window on the right, check the boxes in front of the titles of the data ranges you selected previously.
  • You can modify the operating parameters of your data by adjusting the latter in the pane to the right of your spreadsheet. Once you are done, click in a blank part of your sheet to make the right panel disappear.
  • Place the elements that present the consolidation of your data by selecting in the tab PivotTable Tools tab To analyse, then click on the drop-down menu Actions, in the drop-down menu that appears choose Move the pivot table. Then follow the procedure.

Advice

  • Using the features of Pivot table, you can consolidate data whose sources can come from multiple worksheets. To do this, you will need to add the pivot table wizard.

Popular by topic