For our example let us consider several Microsoft Excel worksheets say for the various months from January till December containing sales data spread across an array of cells ranging from B5 to E16. Now suppose we wish to do a sum function just on only the data for the months of January and February, i.e. summing across worksheets we can do so with a neat trick. Open a new Excel spreadsheet. As a tip, is will be easier if a blank sheet of the same Excel template is used. Also using Excel templates for the spreadsheet of the various months also maintains consistency of the labels and data across all the worksheets which is a vital thing to bear in mind while performing Excel data consolidations. Now in this new sheet at B5 we need to insert the SUM function. So begin by entering =SUM(
Now move on to the January sheet and click on the B5 cell, then holding down the shift key and select the February sheet. You’ll notice that B5 is automatically selected in this sheet. The sum function should look like this = SUM(JanuarySales, FebruarySales:B5) and the value of the sum is instantly reflected in the B5 cell of the new sheet where we are doing the sum across spreadsheets. Now in this new sheet use the auto fill feature and fill horizontally and vertically till you have covered the entire range i.e from B5 to E16 and you’ll notice that the entire range for the two sheets in January and February has been summed.
In an earlier Excel help article we discussed consolidation by position. Here we’ll discuss data consolidation by category or label, to consolidate data in multiple Excel worksheets where the number of cells that contain values varies. In some of the Excel worksheets there are more values to report. On top of it all, in some of the worksheets there are additional columns (fields) with values to consolidate.
When we consolidate Excel data by category we are consolidating data that is not symmetrical. For example for a particular company we may have salespersons’ sales, which include quantity sold and price for a particular month. The very next month an additional column called commissions is added to the existing columns. In the subsequent month i.e. March the positions of the columns may vary. For instance in January it was Salespersons, Quantity, Price and in March it could Salespersons, Price and then followed by Quantity.
To consolidate such types of worksheets first we open a blank, new Microsoft Excel workbook. Here click on Data and then on Consolidate. The Consolidate dialog opens. Select the Sum function. Now before we select the references ensure you check both the checkboxes, Top Row and Left Column under User Labels in. Now click the References field, and point to the January sheet. Here click and drag the entire array that includes the top header (containing the heading for Quantity and Price), the left column (of the salespersons) and the data. Next point to the February sheet and this time do as you did with the sheet of January, by capturing all data, the left column and the header. Finally do the same for the March spreadsheet too. Now instantly the blank sheet where we consolidated the data will have all the data from the three sheets summed up nicely.
We could go another step further and perform a link to source just as we did for the consolidation by position. For this in the Consolidate dialog apart from checking the checkboxes for Top Row and Left Column check the Create Links to Source Data checkbox. Click OK and your consolidated sheet now has outlines and expandable and collapsible buttons to view the data.
Try modifying the data in any of the January or February sheets and the totals are instantly changed and reflected in the Sub totaling is also easy and less cumbersome while consolidating by category or label in that that if you were to subtotal a column the total will comprise only of the detail items which is the actual total and not the detail and the subtotals as is the case when you consolidate using position.
We hope this free Excel help article helped. If not we have other articles, videos, and downloads. Still, if those do not help, and if you want paid help, from our professional staff of Excel consultants, Access programmers, and Macro programmers, please simply contact Christopher out of the corporate office in the Irvine area of Orange COunty California, near Los Angeles, at 714-262-6893, or toll-free at 877-392-3539 and we will discuss your needs with you, in as much detail as you would like. We can even help you with Excel 2013 (Excel 15). The call is free, the consultation is free, the estimate is free, and the results will exceed your expectations. Please call or e-mail now.