How to Consolidate Data in Microsoft Excel 2003

Consolidate Data by Position
1. Open the Excel worksheets that you want to consolidate. Check to make sure that there are no blank spaces within each range.
2. Open a new worksheet. Copy and paste the range from each worksheet that you wish to consolidate that you wish to consolidate into the new worksheet.
3. Select each range one at a time. Click “Named Cells” located on the toolbar. Click “Formulas.” Click “Name a Range.” A pop-up box will open. Type the range name into the “Name” field. Click “Save.” Create a name for each range.
4. Open the master worksheet where you plan to consolidate the data.
5. Click onto the cell in the upper left hand corner of the area where you want to place the consolidated data in the master worksheet, making sure to choose an area with enough room on all sides of the worksheet for all of the data you will be consolidating or you will lose data in the consolidation process.
6. Click “Data Tools” on the master worksheet. Click “Data.” Click “Consolidate.” Click on the function you wish to use in the “Function” box which opens.
7. Click “Browse” if the worksheets you want to locate are in a different workbook. Choose the location from the drop-down menu and click “Okay.”
8. Type into the range dialog box the name that you gave the range of data that you wish to consolidate. Click “Add.” Do this step for all of the ranges that you wish to consolidate.
9. Click to check the “Create links to source data” box if you want the master worksheet to be updated automatically when you add data to the worksheets in a different workbook. This function only works if you are consolidating data from different workbooks. To update the data manually, uncheck this box. Do not fill out the boxes in the “Use Labels” categories.
10. Click “Save.”
Consolidate Data by Category
11. Open the Excel worksheets that you want to consolidate. Check to make sure that there are no blank spaces within each range and that each range is formatted with label titles located in the top row, left column, or both.
12. Open a new worksheet. Copy and paste the range from each worksheet that you wish to consolidate that you wish to consolidate into the new worksheet.
13. Select each range one at a time. Click “Named Cells” located on the toolbar. Click “Formulas.” Click “Name a Range.” A pop-up box will open. Type the range name into the “Name” field. Click “Save.” Create a name for each range.
14. Open the master worksheet where you plan to consolidate the data.
15. Click onto the cell in the upper left hand corner of the area where you want to place the consolidated data in the master worksheet, making sure to choose an area with enough room on all sides of the worksheet for all of the data you will be consolidating or you will lose data in the consolidation process.
16. Click “Data Tools” on the master worksheet. Click “Data.” Click “Consolidate.” Click on the function you wish to use in the “Function” box which opens.
17. Click “Browse” if the worksheets you want to locate are in a different workbook. Choose the location from the drop-down menu and click “Okay.”
18. Type into the range dialog box the name that you gave the range of data that you wish to consolidate. Click “Add.” Do this step for all of the ranges that you wish to consolidate.
19. Click to check the “Create links to source data” box if you want the master worksheet to be updated automatically when you add data to the worksheets in a different workbook. This function only works if you are consolidating data from different workbooks. To update the data manually, uncheck this box.
20. Click the boxes in the “Use labels in” section. Click “Top row” if the category titles are located in the row of your data ranges, click “Left column “if the category titles are located in the left column, or choose both boxes if titles are located in both areas.
21. Click “Save.”
Consolidate Data by Formula
22. Open the Excel worksheets that you want to consolidate. Locate the reference codes of the cells you will be consolidating on the master worksheet.
23. Open the master worksheet where you plan to consolidate the data.
24. Type in the titles of the column and/or rows onto the master worksheet. You can also cut and paste the titles from the original worksheets.
25. Click onto the cell in the upper left hand corner of the area where you want to place the consolidated data in the master worksheet, making sure to choose an area with enough room on all sides of the worksheet for all of the data you will be consolidating or you will lose data in the consolidation process.
26. Type in the formula, which contains the reference codes of the cells you are consolidating into the cell you chose. To consolidate formulas located in different areas on separate worksheets, type an equal sign followed by the word SUM. Next, within parentheses, type the name of the worksheet followed by an exclamation point. Next, type the location of the formula. Type all of the formulas you want to consolidate separating each formula with a comma. For example, = SUM (Inventory!J8, Stock!A3, Tax!B7). If the formulas are located in the same areas of different worksheets, create the using the worksheet names and the area. For example, SUM (Inventory:Stock:Tax!B7)
27. Click “Save.”