How To Combine Excel Spreadsheets – To summarize and report results from separate worksheets, you can consolidate data from each into a master worksheet. The worksheets can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data, you are assembling data so that you can more easily update and aggregate as needed.
For example, if you have an expense worksheet for each of your regional offices, you might use consolidation to roll these figures into a master corporate expense worksheet. This master worksheet could also contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.
TIPS: If you frequently consolidate data, it might help you to base your worksheets on a worksheet template that uses a consistent layout. To learn more about Templates, see: Create a template. This is also an ideal time to set up your template with Excel tables.
There are two ways to consolidate data: by Category or by Position.
Consolidation by position: When the data in the source are arranged in the same order and use the same labels. Use this method to consolidate data from a series of worksheets, such as departmental budget worksheets that have been created from the same template.
Consolidation by category: When the data in the source are not arranged in the same order but use the same labels. Use this method to consolidate data from a series of worksheets that have different layouts but have the same data labels.
Consolidating data by category is similar to creating a PivotTable. With a PivotTable, however, you can easily rearrange the categories. If you want a more flexible consolidation by category, consider creating a PivotTable instead.
NOTE: The examples in this article were created with Excel 2016, so your view might differ depending on which version you’re using. The steps are the same however.
If you have not already, then in each worksheet that contains the data that you want to consolidate, set up your data by doing the following:
Make sure that each range of data is in list format, so that each column has a label in the first row and contains similar data, and there are no blank rows or columns within the list.
Put each range on a separate worksheet, but do not enter anything in the master worksheet where you plan to put the consolidation – Excel will fill this in for you.
Make sure that each range has the same layout.
In the master worksheet, click the upper-left cell of the area where you want the consolidated data to appear.
NOTE: To avoid overwriting existing data in the destination worksheet with the data you are consolidating, make sure you leave enough cells to the right and below this cell for the consolidated data.
- On the Data tab, in the Data Tools group, click Consolidate.
- Data Tools group on the Data tab
- In the Function box, click the summary function that you want Excel to use to consolidate the data. The default function is SUM.
- Following is an example with three worksheet ranges selected.
- Data Consolidation dialog
- Select your date
If the worksheet that contains the data that you want to consolidate is in another workbook, first click Browse to locate that workbook, and then click OK to close the Browse dialog box. Excel will enter the file path in the Reference box followed by an exclamation point, and you can continue to select your data.
- Next, in the Reference box, click the Collapse Dialog button to select the date in the worksheet.
- Data Consolidation Collapse dialog
- Click the worksheet that contains the data you want to consolidate, select the data, and then click the Expand Dialog button on the right to return to the Consolidate dialog.
- In the Consolidate dialog box, click Add, and then repeat to add all of the ranges that you want.
Automatic vs. Manual updates: If you want Excel to update your consolidation table automatically when the source data changes, select the Create links to source data check box. If unchecked, you can update the consolidation manually.
You can not create links when source and destination areas are on the same sheet.
If you need to change ranges once you’ve added them, you can click each one in the Consolidate dialog and update them when they show up in the Reference box, then click Add. This will create a new range reference, so you’ll need to delete the previous one before you consolidate again. Just select the old reference and press Delete.
Press OK, and Excel will generate the consolidation for you. It will be unformatted, so it’s up to you to format, but you only need to do that once, unless you rerun the consolidation.
Any labels that do not match up with labels in the other source areas result in separate rows or columns in the consolidation.
Make sure that any categories that you do not want to consolidate have unique labels that appear in only one source range.
Use the formula to consolidate data
If the data to consolidate is in different cells on different worksheets
Enter the formula with cell references to the other worksheets, one for each separate worksheet. For example, to consolidate data from worksheets named Sales (in cell B4), HR (in cell F5), and Marketing (in cell B9), in cell A2 of the master worksheet, you would enter the following:
Excel multi-sheet formula reference
TIP: To enter a cell reference, like Sales! B4, in a formula without typing, type the formula to the point where you need the reference, click the worksheet tab, and then click the cell – Excel will fill in the sheet name And cell address for you. Just note that formulas like this can be prone error, because it’s very easy to accidentally select the wrong cell. It is also difficult to spot a mistake once a formula has been entered.
If the data to consolidate is in the same cells on different worksheets
Enter the formula with a 3-D reference that uses a reference to a range of worksheet names. For example, to consolidate data in cells A2 from Sales through Marketing inclusive, in cell E5of the master worksheet you would enter the following:
Excel 3D Sheet reference formula, example :
How To Combine Excel Spreadsheets
You may also like
Spreadsheet Tools For Engineers Using Excel 2007Spreadsheet Tools For Engineers Using Excel 2007 – Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not
How To Create a Spreadsheet in Excel 2010How To Create a Spreadsheet in Excel 2010 – Then save a trip through the Excel 2010 workspace interface so that you learn basic concepts before
Retirement Calculator SpreadsheetRetirement Calculator Spreadsheet – What pension will I have? How much will my salary after retirement be reduced? Find out with the Retirement Pension Calculator Is
Courses On Excel SpreadsheetsCourses On Excel Spreadsheets – The Excel Sheets course provides a complete overview of the most useful features of Microsoft ®Excel. You will learn to create your own