I Combine the Same Report

Excel for Microsoft 365 for Mac Excel 2021 for Mac Excel 2019 for Mac Excel 2016 for Mac Excel for Mac 2011 More...Less

If you receive information in multiple sheets or workbooks that you want to summarize, the Consolidate command can help you pull data together onto 1 sheet. For case, if you have a sheet of expense figures from each of your regional offices, y'all might use a consolidation to curl upwardly these figures into a corporate expense sail. That canvass might contain sales totals and averages, current inventory levels, and highest selling products for the whole enterprise.

To make up one's mind which type of consolidation to utilise, wait at the sheets y'all are combining. If the sheets have data in inconsistent positions, fifty-fifty if their row and column labels are not identical, consolidate by position. If the sheets utilise the aforementioned row and cavalcade labels for their categories, fifty-fifty if the data is non in consistent positions, consolidate by category.

Combine by position

For consolidation past position to piece of work, the range of data on each source sheet must be in list format, without blank rows or blank columns in the list.

1. Open each source sheet and brand sure that your data is in the same position on each canvas.

2. In your destination sheet, click the upper-left cell of the surface area where yous want the consolidated data to announced.

   Note:Make certain that you leave enough cells to the right and underneath for your consolidated data.

3. On the Data tab, in the Data Tools group, click Consolidate.

   

4. In the Function box, click the function that you desire Excel to utilise to consolidate the data.

5. In each source canvass, select your data.

   The file path is entered in All references.

6. When you have added the data from each source sail and workbook, click OK.

Combine by category

For consolidation by category to work, the range of data on each source canvass must exist in list format, without bare rows or blank columns in the list. Besides the categories must be consistently labeled. For instance, if 1 column is labeled Avg. and some other is labeled Average, the Consolidate command volition not sum the two columns together.

1. Open each source canvas.

2. In your destination canvas, click the upper-left cell of the area where you desire the consolidated information to appear.

   Note:Brand certain that you exit enough cells to the right and underneath for your consolidated information.

3. On the Information tab, in the Data Tools group, click Consolidate.

   

4. In the Function box, click the function that you want Excel to use to consolidate the data.

5. To indicate where the labels are located in the source ranges, select the check boxes nether Apply labels in: either the Top row, the Left column, or both.

6. In each source sheet, select your data. Make sure to include either the top row or left column data that y'all previously selected.

   The file path is entered in All references.

7. When you take added the information from each source sheet and workbook, click OK.

   Annotation:Any labels that don't match labels in the other source areas cause separate rows or columns in the consolidation.

Combine by position

For consolidation by position to work, the range of information on each source canvass must be in list format, without blank rows or blank columns in the list.

1. Open each source sail and make sure that your information is in the same position on each sail.

2. In your destination canvas, click the upper-left jail cell of the area where y'all desire the consolidated data to appear.

   Note:Make sure that you leave plenty cells to the right and underneath for your consolidated data.

3. On the Data tab, under Tools, click Consolidate.

   

4. In the Part box, click the function that you desire Excel to utilise to consolidate the data.

5. In each source sail, select your data, and then click Add.

   The file path is entered in All references.

6. When you accept added the data from each source canvass and workbook, click OK.

Combine by category

For consolidation by category to piece of work, the range of data on each source sheet must be in list format, without bare rows or blank columns in the listing. Also the categories must be consistently labeled. For example, if ane column is labeled Avg. and another is labeled Boilerplate, the Consolidate command will non sum the ii columns together.

1. Open each source sail.

2. In your destination sheet, click the upper-left cell of the expanse where yous want the consolidated data to appear.

   Note:Make sure that y'all exit enough cells to the right and underneath for your consolidated data.

3. On the Data tab, under Tools, click Consolidate.

   

4. In the Function box, click the part that you want Excel to use to consolidate the data.

5. To betoken where the labels are located in the source ranges, select the check boxes under Utilise labels in: either the Superlative row, the Left cavalcade, or both.

6. In each source canvass, select your data. Brand sure to include either the top row or left cavalcade data that you lot previously selected, and then click Add.

   The file path is entered in All references.

7. When you have added the data from each source canvass and workbook, click OK.

   Notation:Whatsoever labels that don't match labels in the other source areas cause separate rows or columns in the consolidation.

Need more aid?

serranomaidest.blogspot.com

Source: https://support.microsoft.com/en-us/office/combine-data-from-multiple-sheets-dd7c7a2a-4648-4dbe-9a11-eedeba1546b4

Share this post