Advanced Spreadsheet Concepts

 Data Consolidation

Consolidation in Excel refers to the process of combining data from multiple worksheets or workbooks into a single worksheet. It is useful when  data is spread across different sources and want to summarize or analyze it in one place.

• Data Consolidation allows to gather data from separate worksheets into a master worksheet. 
• In other words, the Data Consolidation function takes data from a series of worksheets or workbooks and summaries it into a single worksheet that can be updated easily. 
• The data from the consolidation ranges and target range are saved when the worksheet is saved.

Steps to Perform Data Consolidation in Excel

1. Prepare Your Data:

    One must ensure that the data to be consolidated is organized in a consistent format across all
    sheets or workbooks. The data ranges should have identical labels or headers.

2. Open a New Worksheet:

    Start with a new worksheet where one wants to consolidate the data.

3. Go to the Data Tab:

    Click on the Data tab in the Excel ribbon.

4. Select Consolidate:

    In the Data Tools group, click on Consolidate. This opens the Consolidate dialog box.

5. Choose the Function:

    In the Function box, select the function that one want to use for consolidation (e.g., SUM,
    AVERAGE, COUNT, etc.).

6. Add Data Ranges:

    Click on the Add button to select the data ranges  to be consolidate.

    If the data is in different worksheets or workbooks, click Browse to find and select them.

7. Use Labels:

    If the data ranges include labels, check the appropriate boxes for Top row, Left column, or
    Create links to source data if one wants Excel to automatically update the consolidated data
    when the source data changes.

8. Consolidate:

    Once one has added all the data ranges, click OK. Excel will consolidate the data into the new
    worksheet.

Example Scenario

Let's imagine that sales data for different regions are in separate worksheets. One can consolidate all the regional data into a single worksheet to get a total or average sales figure.

 Using Excel Consolidation with Example Data

Sheet1: North Region Sales

Sheet2: South Region Sales

Sheet3: East Region Sales

Sheet4: West Region Sales

 Consolidate using SUM: One has to select SUM as the function and add data ranges from each sheet.

Result: Excel will display the total sales from all regions combined.

Try yourself:

Here’s a sample layout for each department's quarterly budget. You can recreate these in Excel and then practice the consolidation.

Worksheet 1: "HR"

Category	Q1	Q2	Q3	Q4
Salaries	50000	52000	54000	56000
Office Supplies	2000	2200	2100	2300
Travel	3000	3500	3200	3700
Training & Development	4000	4500	4300	4800

Worksheet 2: "Marketing"

Category	Q1	Q2	Q3	Q4
Salaries	60000	62000	64000	66000
Office Supplies	3000	3200	3100	3300
Travel	4000	4500	4200	4700
Advertising	5000	5500	5300	5800

Worksheet 3: "IT"

Category	Q1	Q2	Q3	Q4
Salaries	55000	57000	59000	61000
Office Supplies	2500	2700	2600	2800
Travel	3500	4000	3700	4200
Software & Equipment	6000	6500	6300	6800

Worksheet 4: "Sales"

Category	Q1	Q2	Q3	Q4
Salaries	58000	60000	62000	64000
Office Supplies	2800	3000	2900	3100
Travel	4500	5000	4700	5200
Client Entertainment	7000	7500	7300	7800

Steps to Consolidate the Data

1.      Create a new worksheet called "Total Budget" in the same workbook.

2.      Go to the Data tab in the Excel ribbon and click on Consolidate.

3.      In the Consolidate dialog box, choose SUM as the function.

4.      Add the data ranges for each department by clicking Add:

For HR: Select the range from Q1 to Q4 for all categories.

Repeat this for the Marketing, IT, and Sales sheets.

5.      Ensure that both Top row and Left column options are checked so that Excel recognizes the labels for the categories and quarters.

6.      Click OK, and Excel will create a consolidated table showing the total budget for each category across all departments.

Consolidated Worksheet: "Total Budget"

Category	Q1	Q2	Q3	Q4
Salaries	223000	231000	239000	247000
Office Supplies	10300	11100	10700	11500
Travel	15000	17000	15800	17800
Training & Development	4000	4500	4300	4800
Advertising	5000	5500	5300	5800
Software & Equipment	6000	6500	6300	6800
Client Entertainment	7000	7500	7300	7800

Note: First try yourself. In case of any problem, please contact us or request solution.

Subtotals in Excel:

Subtotals in Excel are a powerful feature that allows one to calculate intermediate totals for different groups of data within a list or table. This feature is especially useful for summarizing data by categories, such as finding the total sales for each region or department.

The subtotal command is used automatically to create groups and apply built-in functions like sum, count and average to summarize available data. In Excel, the Subtotal command is used to quickly insert subtotal functions into a list or table. It's particularly useful for summarizing data in large datasets. 

Steps to Create Subtotals in Excel

1. Organize Your Data:

Ensure that  data is in a structured format, typically in a table or list.

The data should be sorted by the column one wants to subtotal (e.g., sort by "Region" or "Department").

2. Select the Data:

Highlight the range of data where one wants to apply subtotals.

3. Use the Subtotal Feature:

Go to the Data tab on the Ribbon.

Click on the Subtotal button in the Outline group.

4. Configure Subtotal Options:

In the Subtotal dialog box, choose the column by which one wants to group one's data in the At each change in dropdown.

Select the function one wants to use for the subtotal (e.g., Sum, Count, Average).

Check the column(s) for which one wants to calculate subtotals.

Choose whether to replace current subtotals, include a summary below data, or add subtotals to existing ones.

5. Click OK:

Excel will insert subtotal rows with the specified calculations in  dataset.

Example Scenario:

The Monthly Fees Installments received from students is summarized in excel as follows:

Roll No.	Installment Amount
1	3000
2	5000
3	8000
1	3000
4	2000
2	4000
3	10000

The aim is to calculate total amount paid by each student and also find the total amount received till now. Let's watch the following video:

Try Yourself:

Suppose you have a list of sales data with columns for "Region," "Salesperson," and "Sales Amount." By applying subtotals, Calculate the total sales for each region.

1.      Sort by Region: First, sort  data by the "Region" column.

2.      Apply Subtotals:

Choose "Region" in the "At each change in" dropdown.

Select "Sum" as the function.

Check the "Sales Amount" column to subtotal the sales for each region.

Sample Data:

Region	Salesperson	Sales Amount
North	John	5000
North	Jane	7000
North	Steve	6000
South	Mark	8000
South	Emma	7500
East	Chris	9000
East	Linda	8500
West	Luke	4000
West	Mike	4500

Result After Subtotals:

Using the subtotal feature, your worksheet should look as follows:

Region	Salesperson	Sales Amount
North	John	5000
North	Jane	7000
North	Steve	6000
Total for North		18000
South	Mark	8000
South	Emma	7500
Total for South		15500
East	Chris	9000
East	Linda	8500
Total for East		17500
West	Luke	4000
West	Mike	4500
Total for West		8500
Grand Total		59500

Note: First try yourself. In case of any problem, please contact us or request solution.