As you work in Excel, you may find yourself using data spread across multiple worksheets. It’s often useful to bring this data together. For example, you may have multiple columns of numbers on multiple sheets. You may want to find the average of all these values in a single column on a different sheet. Fortunately, Excel lets you do exactly that. Let’s learn how to Consolidate in Microsoft Excel.
How to Consolidate in Excel
Consider the following scenario. Sheet 1 has numbers in the range A1:A4.Sheet 2 also has numbers in the range A1:A4. On Sheet 3, you want to display the average of the values in each of the four rows in column B. You can use Excel’s Consolidate feature to do this.
To get started, click into cell B1 on Sheet 3. Then, on Excel’s Data tab, click Consolidate. The Data tab is found on the ribbon up at the top.
Excel launches the Consolidate menu. The Function setting defaults to Sum, but you can change it to Average by clicking in the dropdown. Then, click into the Reference box.
Go to Sheet 1, then click and drag to select your range of data in A1:A4. Click the + icon on the Consolidate menu, and the reference will be added to the All References list.
Then, repeat these steps for Sheet 2, selecting the data and clicking +. Back on Sheet 3, click OK. Excel will calculate the average of the values in cells A1:A4 on both sheets, and display the results in Column B of Sheet 3.
As you can see, Excel’s Consolidate Sheets feature is a powerful tool to work with data spread across multiple spreadsheets. It avoids the need to duplicate data, by referencing values instead of copying them. Try it out next time you’re working with complex data in several locations.
RATE in Excel