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:
Consolidating data meaning
|PT 1 Marks|
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
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
Steps to Consolidating Data in OO Calc:
- Click on Data ⇢ Consolidate option. A consolidate dialog box will appear.
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:
- 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
- Type the range in Name box. Do not use space in range names.
- The Add button will active click on that.
- Click on OK.