Microsoft Excel sheets are used vastly for database. If you want to consolidate data from various worksheets you can do this easily from same workbook or from different workbooks. When you will use consolidating data; you can easily compare data, view results and create project summaries. You can do it in three ways such as: by position, category or formula. There are different advantages of doing it in different ways like if you are consolidating data by position then it will help you to transfer the data into the same position. If you are consolidating by category, then it will allow you to reorganize information while using pre-existing worksheet formats such as column and row titles. And if you are doing it by formula then it will allow you to transfer data using the formulas already used in the various worksheets. In this article we will discuss Consolidate Data in Microsoft Excel 2003.
Follow the instruction given below.
Consolidate data with position
First step is to open the excel worksheet that you want to consolidate. Now open a new worksheet, now copy and paste the range from each worksheet that you want to consolidate into the new worksheet.
Now you will have to select each range one at a time. To do so, click named cell located on the toolbar, then click formula, click name a range to specify name from a range. Now you will see a pop-up box, in that box type the range name into the “Name” field. After doing this, click save and like this specify name for each range.
Now the next step is to open the master worksheet where you are going to consolidate the data. Now to place the consolidated data in the master worksheet, click on the cell in the upper left corner of the area. Try to make sure about the selected space. You will have to select enough space for consolidating data otherwise you will lose data in the process.
Now go to data tools and click. Again click data then consolidate, after this step click the function you want to use in function box which will appear.
Now if you want to browse the worksheets you want to locate are in a different workbook the click browse. Just choose the location and click OK.
Now you will have to type the name in the range dialogue box that you gave the range of data you wish to consolidate. Now you just have to click add button. Repeat this step for all of the ranges that you want to consolidate.
if you want the master worksheet to be updated automatically when you add data to your worksheet in a different workbook then click “create links to source data”. This function will work when you are consolidating data from different workbooks. If you want to update the data manually then just uncheck the box. Also, try to make sure that you do not fill out the boxes in the “Use Labels” categories. At last, click on save option to save all the work done in the master worksheet.