Excel 2016 consolidating data

If each sheet contains that month's data and if it is laid out in the same arrangement of columns and rows then you can summarize this full year of data into a single sheet using the Consolidate tool.

To do this select an empty sheet in the workbook - add a new one if necessary - and click in it.

For our data we want to add the values so we'll set the Function to Sum.

However we could have chosen any of these 11 functions: Sum, Count, Average, Max, Min, Product, Count Numbers, Std Dev, Std Devp, Var and Varp.

Provided you have the same range of data on each of your worksheets and they are all in the same position all you will need to do is to click the Worksheet tab and the range will already be selected for you so it's not as big a job as it might seem.

Here you will select the function to analyze your data and the references or ranges that you want to consolidate.In this post, I'll show you how to use the Consolidate data feature in Excel to take data from a series of worksheets - or workbooks - and summarize them into a single worksheet.Consider the situation where you have data for twelve months of your business stored one month per sheet in an Excel workbook.To ensure that every year’s workbook has a similar appearance, you can create a workbook with the characteristics you want, and save it as a pattern, or template, for similar workbooks you will create in the future.A consequence of organizing your data into different workbooks and worksheets is that you need ways to manage, combine, and summarize data from more than one Excel document.In addition to creating a workbook template, you can add a worksheet based on a worksheet template to your workbook by using the Insert dialog box.

793

Leave a Reply