Online PC Support

OPS Technical Solutions : +1(833)-522-1003


How to Consolidate Data in Microsoft Excel 2003

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.

How to create colored worksheet tabs in MS Excel

Microsoft Office Excel is a database application which is most widely used for keeping data in offices and organizations. It is the first thing which would come in anybody’s mind when one has to keep some data in tabular format and may or may not perform some analysis on it. Files which get created in Microsoft Excel are called as workbooks and each workbook can have multiple worksheets in it. Worksheets are like pages in any document file, which separate different between different sets of data. For example we can have one file for keeping date of birth of students of every class in your school and you can have multiple worksheets for individual classes. Worksheets are though different entities altogether but the data saved in them can be used in any other worksheet or at times by any other workbook as well. Depending upon the purpose and need of the file the number of worksheets may increase. By default there are 3 worksheets available in a new workbook and this default number can be changed by making alterations in the options of Microsoft Excel. Different sheets can be accessed by the small tabs which are present on the bottom left corner of the excel window. These sheets are by default named as Sheet1, Sheet2, Sheet3 and so on but the names of these sheets can be changed for better understanding and reviewing. Renaming of these sheets can be easily done by right clicking on the sheet tab and then selecting Rename. Renaming sheet is a good idea but if you have to categorize some sheets together then you can even use some colors for these tabs. For example in the same school scenario we can color all the tabs containing personal information in Blue and we can color all the sheets containing academic information in Red. In this post now we will discuss how to change the color of worksheet tabs in Microsoft Excel.

Steps for coloring the worksheet tabs in excel are as follows:

Step1: Open the Workbook in which you want to do the same.

Step 2: Click on the tab which you want to color. If you want to give same color to multiple tabs then press the CTRL key while you make the selection of the tabs. This will allow you to select multiple tabs at the same time.

Step 3: Once you have selected the tabs then right click on the tab or on the selection of tabs and then click on Tab Color option in the context menu.

Step 4: This action will present you a small window displaying the default color palette from which you can select a color. If you want more color choices then click on the more color button where you can create your own custom colors.

Step 5: After you get the color of your choice select it and click on OK to close all the color windows.

By following the steps given above you can easily color the worksheet tabs in any Microsoft Excel Workbook.

Call Now: +1 833-522-1003
Call Now: +1 833-522-1003
Call Now: +1 833-522-1003