Consolidate in Excel
Microsoft Office Excel comes with several features for customizing tables and charts full of important data. The program also offers efficient ways to combine and summarize data from multiple files, also known as worksheets. Common methods to consolidate in Excel include consolidating by position, by category, by formula or by using Excel’s Pivot Table feature. Scroll past the jump to learn how to consolidate in Excel so that your information appears in a master worksheet as a reference whenever you need to generate reports.
Contents
Steps
Consolidate According to the Position in an Excel Worksheet
- Verify that data in each worksheet appear in list format. Make sure you have removed any blank columns and rows, and that each column is labeled with similar information.
- Add and layout each column range to a separate worksheet. However, do not add the ranges to the master worksheet that you plan to consolidate.
- Highlight each range, and name them by picking the Formulas tab, followed by the arrow located near Name a Range. Enter a name for the range in the Name box.
- Prepare to consolidate Excel data. Click on the upper-left cell where you want to place your consolidated data from your master worksheet.
- Go to the Data tab from the master worksheet, and then select the Data Tools group. Choose Consolidate.
- Access the summary function feature from the Function box to create the settings for consolidating data.
- Enter the names of your ranges in the Summary Function feature. Click Add to begin the consolidation process.
- Update the consolidation. Choose the Create Links to Source Data box if you want to update source data automatically. Leave the box unchecked if you prefer to update consolidation data manually.
Identify Categories to Consolidate Excel Data
- Repeat the tasks in the first step above for setting up the data in list format. In the master worksheet, click on the upper-left cell where you want to place the consolidated data.
- Go to the Data Tools Group. Find the Data tab, and then click Consolidate. Use the summary function feature in the Function box to choose the settings for consolidating data. Name each range, and then click Add to finish consolidating your data. Repeat the process for updating your consolidated data described above.
Use Formulas to Consolidate Excel Data
- Start with the master worksheet. Type or copy the row and column labels that you want to use to consolidate Excel data.
- Select the cell where you want to consolidate your results. On each worksheet, enter a formula that references the cells that you want to consolidate. In the first cell where you want to include the information, enter a formula such as: =SUM (Department A!B2, Department B!D4, Department C!F8). To consolidate Excel data inclusively from all cells, enter a formula such as: =SUM (Department A:Department C!F8)
Access the PivotTable function
- Create a PivotTable report. This feature allows you to consolidate Excel data from multiple ranges with the capability of reorganizing categories when necessary.
- Start the PivotTable and PivotChart wizard by pressing Alt+D+P on your keyboard. Choose Multiple Consolidation Ranges, then Next.
- Select the command, I Will Create the Page Fields, and press Next.
- Go to Collapse Dialog to hide the dialog box on the worksheet. On the worksheet, select the cell ranges, Expand Dialog, then Add. Under the page field option, enter 0 and pick Next.
- Pick a location on the worksheet to generate the PivotTable report, and press Finish.
Tips
- With the PivotTable option, you can also use the wizard to consolidate Excel worksheet data using a single page, multiple page or no page fields.
Related Articles
- Convert Measurements Easily in Microsoft Excel
- Sort Microsoft Excel Columns Alphabetically
- Edit Data in Microsoft Excel
- Sort a List in Microsoft Excel
- Use Summation Formulas in Microsoft Excel
