How to automate consolidation in Microsoft Excel
By
SpreadCheaters
In this tutorial, we will learn how to automate consolidation in Microsoft Excel. In Excel automatic consolidation is commonly utilized as it is efficient and makes it easier to work with the data. For this, we can utilize the built-in Consolidate command in the Data tab.For instance, we have two sample data sets in distinct sheets i.e. Data1 and Data2 that represent category, product, and sales of various items of a store. We aim to consolidate data from both sheets into a single sheet labeled “Automatic Consolidation”.
The process of consolidating data from multiple sheets or workbooks into a single sheet or workbook using Excel’s built-in features is known as automatic consolidation. This process is automated, which means that any changes made to the source data will automatically be updated in the consolidated data, without requiring any manual effort in Excel.
Step 1 – Organize the Data
– Organize the data into rows and columns and make sure the data has suitable row and column headers.
Step 2 – Choose the Destination
– Choose the destination i.e. the location where the consolidated data will be returned.
Step 3 – Locate the Data Tab
– Locate the Data tab in the menu bar.
Step 4 – Perform a Click on the Consolidate Button
– Perform a click on the consolidate button in the “Data Tools” section in the Data tab.
Step 5 – Choose the Function
– Choose the suitable function from the drop-down list i.e. we have selected the SUM function.
Step 6 – Enter the Range of the First Data Set
– Enter the reference range of the first data set located in the sheet “Data1” in the “Reference” field.
Step 7 – Perform a Click on the Add Button
– Perform a click on the Add button.
Step 8 – Enter the Range of the Second Data Set
– Enter the reference range of the second data set located in the sheet “Data2” in the “Reference” field.
Step 9 – Perform a Click on the Add Button
– Perform a click on the Add button.
Step 10 – Enable the Following Options
– Enable the following options in the dialog box by checking the box of each:
– “Top Row”
– “Left Column”
– “Create links to source data.
Step 11 – Hit the OK Button
– Hit the OK button in the dialog box, the data from both the sheets will be consolidated.
Step 12 – Viewing the Detailed Consolidated Data
– Perform a click on the headers that appear at the left of the sheet.
