![how to consolidate data in excel how to consolidate data in excel](https://cdn.corporatefinanceinstitute.com/assets/merge-cells-step-1.png)
- #HOW TO CONSOLIDATE DATA IN EXCEL HOW TO#
- #HOW TO CONSOLIDATE DATA IN EXCEL CODE#
- #HOW TO CONSOLIDATE DATA IN EXCEL FREE#
In the figure below, three worksheets in a workbook show a chart of accounts in column A. While this is a limiting factor, there are a lot of data sets that follow this shape.Ĭonsolidate easily handles differing numbers of rows or columns. Found on the Data tab, it has a peculiar requirement: It only works if your data has exactly one column of labels and one row of headings.
![how to consolidate data in excel how to consolidate data in excel](https://2.bp.blogspot.com/-CwXriFUkdS8/VqS7SR0tm7I/AAAAAAAACFA/DUwSV5R-iJI/s1600/consolidate%2Bstart%2Bstep%2B1.jpg)
#HOW TO CONSOLIDATE DATA IN EXCEL FREE#
If you have any suggestions or questions, feel free to share them with us.The Consolidate command isn’t new. In this article, we have tried to cover the easiest ways to combine sheets in Excel effectively. Here, we have provided a blank sheet named Practice where you can try the ways to combine the sheets here.
#HOW TO CONSOLIDATE DATA IN EXCEL HOW TO#
Related Content: How to Merge Multiple Sheets into One Sheet with VBA in Excel (2 Ways) So, we have selected the headers from the March (3) sheet.Īfterward, you will get the combined data in the Combined Sheet LR and LC will determine the last row and column of each sheet and then a range is formed using the firstRow, firstCol, LR, LC in this way, this loop copies the data of each sheet for this range and pastes it into the Combined sheet.įinally, we will activate this sheet to see the result.Īfter that, the Input wizard will open up where you have to select the headers from any of the sheets. Here, we have declared firstRow, firstCol, LR, LC as Long and set the sheet name Combined as x.įirstRow and firstCol are set as the first row and column of the data tables and then we have run the FOR loop for the other sheets except for the Combined sheet as we will combine our data in this sheet. Range(Cells(firstRow, firstCol), Cells(LR, LC)).Copy _ LC = Cells(firstRow, Columns.Count).End(xlToLeft).Column LR = Cells(Rows.Count, firstCol).End(xlUp).Row Set headers = Application.InputBox("Select the Headers", Type:=8)
#HOW TO CONSOLIDATE DATA IN EXCEL CODE#
➤Write the following code Sub combinesheets() Then, the Visual Basic Editor will open up.
![how to consolidate data in excel how to consolidate data in excel](https://www.wallstreetmojo.com/wp-content/uploads/2019/01/Consolidate-Data-in-Excel-1.png)
➤ Go to Developer Tab > Visual Basic Option For this purpose, we have created a sheet named Combined where we will have the combined data. You can use a VBA code for combining multiple sheets into one. Related Content: How to Merge Columns in Excel (4 Ways) In this way, you will be able to combine the data of multiple sheets into one sheet-like below. ➤ Go to Home Tab > Close & Load Dropdown > Close & Load Option Now, the task is completed and you can close this editor. ➤ Type _month (the common portion of each table’s name) in the box besides ends with Option ➤ Follow Text Filters Group > Ends With OptionĪfter that, the Filter Rows wizard will open. ➤ Select the filter sign of the Name column If you don’t want to change the combined data of this table after refreshing then you have to follow some more steps. ➤ Select the columns you want to have in your sheet and unclick the option Use original column name as prefixįinally, you will get the combined data of the three sheets in the following table. ➤ Select the double arrow sign in the Content column Here, to combine the data of the three sheets we have created a new sheet named VLOOKUP where we have pasted the common data of the Product and Sales Person column and now we will combine the sales data from the three sheets by using the VLOOKUP function. Method-2: Using Excel VLOOKUP Function to Combine Sheets Similarly, repeat this referencing task for the other two sheets February and Marchįinally, you will get the sales values summed up for each product. In this way, the reference will be added up in the All references box ➤ Select Sum Option (or any other option as per your needs) in the Function boxĪfter that, we have selected the data of the Sales column of the January sheet in the Reference box ➤ Go to Data Tab > Data Tools Group > Consolidate Option ➤ Select the cell D5 (where you want to have the consolidated data) Here, we will use the Consolidate option, to sum up, the sales value of the three months of the three sheets January, February, and March for each product. Method-1: Using Consolidate Option in Excel to Combine Sheets In these data tables, the first two columns are the same for all of the sheets but the only difference is in the value of sales.įor creating the article, we have used Microsoft Excel 365 version, you can use any other versions according to your convenience. Here, we have three sheets named January, February, and March containing sales records of these months.