How to Consolidate in Excel
Verify that data in each worksheet appear in list format., Prepare to consolidate Excel data., Enter the names of your ranges in the Summary Function feature., Update the consolidation.
Step-by-Step Guide
-
Step 1: 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. -
Step 2: 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. , Click Add to begin the consolidation process. , 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. -
Step 3: Enter the names of your ranges in the Summary Function feature.
-
Step 4: Update the consolidation.
Detailed Guide
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.
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. , Click Add to begin the consolidation process. , 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.
About the Author
Melissa Ortiz
Enthusiastic about teaching cooking techniques through clear, step-by-step guides.
Rate This Guide
How helpful was this guide? Click to rate: