Aggregate data from multiple Sheets in Excel to save you time when working. In this article, ThuThuatPhanMem.vn will show you how to aggregate data from multiple Sheets with the same structure.
Step 1: In Excel, you open the Data and select item Consolidate to enable aggregation of data from multiple columns into one.
Step 2: In the window of Consolidateyou will have to set up Function (Calculation function) for the system to apply a formula that aggregates data from multiple sheets.
Calculation functions in Consolidate are similar to functions on Excel:
- Sum: Total
- Average: Averaged
- Count: Count all
- Max: Get the maximum value
- Min: Get the minimum value
- Product: Calculation
- Count Number: Only count columns with numeric values
- StdDev: Estimate the standard deviation of the sample
- StdDevp: Estimate the standard deviation of the entire set
- Var: Calculate the variance on the sample
- Varp: Calculate the variance over the entire set
For this example, Thuthuatphanmem will aggregate data from multiple Sheets by summing, so choose the Sum function (sum function).
Các bài viết liên quan:
Step 3: Next, you will have to select the data area to be aggregated from many different Sheets in Excel.
Please put your mouse pointer on a blank line Reference.
Then, you drag the mouse to create a selection on the Excel data table to zone the data to be aggregated from the Sheets.
In case you don’t like creating a selection of data in Excel with your mouse, you can also enter a selection using basic syntax (it takes a long time).
Step 4: Once you have selected the data area to be aggregated from a Sheet, you switch to the remaining Sheet tabs to check if the selected data area is correct or not.
If the aggregated zoning data from multiple Sheets is correct, select OK to start data aggregation.
Step 5: Immediately, data will be aggregated from the sheets you have selected at the position you place the mouse pointer to activate the feature. Consolidate.
Let’s check the results after aggregating data from multiple Sheets in Excel with the Consolidate tool (SUM function)!
Some note:
- Columns and rows when aggregating data from multiple Sheets in Excel must have the same structure.
- The position of columns and rows when aggregating data from multiple Sheets in Excel must be commensurate – symmetrical with each other.
- Between columns, rows when aggregating data must not have blank cells.
In this article, Thuthuatphanmem shared with you how to aggregate data from multiple Sheets in Excel. Good luck!
