Add source ranges any of the method, which was above-described.To create of the merged range open «Consolidation» menu and select the function «Sum» (for example).Some designations are repeated, and some ones are not. For example, in shops are implemented to different products. The consolidating data by category is used when the source ranges have different structure. The comment: show for the program the path to the source range you may with helping the «Browse» button, or by switching to the opening book. Links to files of other Workbooks Excel on the disk D: Select alternately the names of the files, allocated to ranges in open workbooks – click on «Add».On the tab «VIEW», click the «Switch to another window». To show the way to books with the original ranges, put the cursor in the field «Link».Prepare the consolidated report, using the function «Average».In the blank workbook, choose the upper-left corner of the blank slate.
HOW TO CONSOLIDATE DATA IN EXCEL USING MACRO PLUS
The consolidating of data in Excel: practical work Press the «+» in the left field – there will be values, on the basis of which are formed the totals by quantity and revenue. The consolidated statement represents a structured table. For exiting from the «Consolidation» menu and creating the PivotTable, click OK. To be able to enter value by manually, uncheck the «Create links to source data». For automatic updating of the joint report when entering new data into the original tables – in front «create links to source data».Īttention!!! If you make in the original tables to the new values over the selected for consolidation range, they will not display in the merged report. To automatically make the header for the columns of the consolidated table, put a check next to « signature top of the line» - for summarizing of the team all of the values for each unique entry of the leftmost column opposite «the values of the left column». The tables for consolidation are shown in the field «The list of ranges». In the «Link» field will be the first range of consolidating and click «Add».Īlternately open the second, the third and the fourth quarter – allocated ranges. Put the cursor in the field and open the worksheet «1-st quarter», allocated the table together with the cap. Proceed to filling of the next field «Link». Then to choose «Amount» (the values in the source ranges will be added). The team «Consolidation» when working with data may be performed these types of calculations. There dialog box «Consolidate» is opened: In the group «Data Tools», click the button «Consolidate». The team « Consolidation» will fill as many rows and columns as you need.Īfter that, go to the tab «DATA». Put the cursor in the first cell of the merged range.Īttention!!! To the right and below of this cell should be free. The ranges with the source value need to open.įor consolidated data you need to ascribe and open a new worksheet or a new workbook. the names of columns – are identical (it is allowed to rearrange of columns).the layouts of all the tables will be the same.You need to make the General report by using the «data consolidation». There are 4 sheets with the same structure - quarterly results of sales of furniture, for example.