Use Consolidating Data

Use Consolidating Data

Use consolidating data help users to automate data and save time where data used frequently in different sheets. If you are working with spreadsheet software, you may use different worksheets. Sometimes you need to use the same data for different sheets but with different values. For example, data of students of one class who appeared in different exams like periodic test 1, periodic test 2 etc. Data on sales of various salesmen of a company like quarterly, monthly, or and so on. So to achieve a common business goal or specific goal you need to analyze and summarise data. So in this post,, I will discuss using consolidating data. So let us start! I will cover the following topics in this post:

    1. Consolidating data meaning
    2. Consolidating data
    3. Define a range

Consolidating data meaning

Consolidate means that to combine a number of things into a single unit. Consolidating of data means that the process of combining the number of data organized into different sheets into one worksheet or cell. Let consider one of the above examples that student data are given in a screenshot.

PT 1 Marks


In the above example, I have marks of two periodic tests. Screenshot 1 is having PT 1 marks and Screenshot 2 is having PT 2 marks. Now in sheet 3, I need to use formulas on data to compute results. This process is known as consolidating data. Prepare your data into different worksheets to perform consolidation and follow these steps:

Steps to Consolidating Data in MS Excel:

  • Open the workbook in which you have prepared data.
  • Now click and place the cursor in the cell in the new worksheet where you want to display consolidated data. If the worksheet is not available then insert a new worksheet.
  • Click on Data ⇢ Consolidate. 
  • The consolidate dialog box will appear. 
    Data Consolidate  dialog box MS Excel 2013
    Data Consolidate  dialog box MS Excel 2013

    As you can see in the above screenshot consolidate dialog box contains 5 options highlighted.

    • Function: In this option, you can select your desired function to display consolidated results.
    • Reference: This option allows you to select the reference from another sheet having data. Click on the circled button to select a reference. Click on the Add button to use the reference. Repeat the same step to add all the references.
    • All references: Display the added references from the workbook.
    • Use labels in: This option allows us to use the row headers and column headers of worksheets. 
    • Create links to source data: When you select this option data will automatically update the values when any change happens to referenced cells. Finally, click on OK button.
  • Observe the following screenshot I have added references for the above-mentioned sheets. 
    Data consolidate dialog with referenced data
    Data consolidate dialog with referenced data

Steps to Consolidating Data in OO Calc:

Prepare your worksheets for data consolidation and follow these steps:
  • Click on Data ⇢ Consolidate option. A consolidate dialog box will appear. 
    Data Consolidate dialog box in OO Calc
    Data Consolidate dialog box in OO Calc
  • The dialog is almost similar like MS Excel. Select your desired function and add references to the cells by selecting ranges. 
  • Click on more button to add row labels and column labels or to link source data. 

Define a range:

This option is useful for consolidate data in OO calc. So follow these steps to define a range in OO Calc.
  • Select the cells for which you want to create a range.
  • Click on Insert ➝ Names ➝ Define or press Ctrl + F3. A Define Names Dialog box will appear on the screen.
    Define Names dialog box in OO Calc
    Define Names dialog box in OO Calc
  • Type the range in Name box. Do not use space in range names. 
  • The Add button will active click on that.
  • Click on OK.

2 thoughts on “Use Consolidating Data”

Comment Your Views

%d bloggers like this: