Consolidate data in Excel - javatpoint - Free Printable

It looks like you've uploaded a screenshot of an Excel workbook with sales data for three different years (Year 1, Year 2, and Year 3), each on its own worksheet. The data includes quarterly sales figures for three products: Tea, Coffee, and Cold Drink.

However, the image shows that:

- Year 1: All four quarters (Q1 to Q4) are present.
- Year 2: Only Q1, Q3, and Q4 are filled; Q2 is missing.
- Year 3: Only Q1, Q2, and Q3 are filled; Q4 is missing.

There’s no specific problem stated in your message, but based on common tasks involving such data, here are likely objectives and solutions:

---

🔍 Possible Task Interpretation

You may be asked to:
1. Complete missing quarters by estimating or interpolating values.
2. Summarize total annual sales for each product per year.
3. Compare trends across years.
4. Identify patterns (e.g., seasonal spikes).
5. Create a consolidated report combining all years.

Let me assume the task is to calculate total annual sales for each product per year, even though some quarters are missing.

---

✔ Step-by-Step Solution

#### 📌 Step 1: Total Sales Per Year (with missing data)

We'll sum up available quarters and note where data is missing.

---

#### Year 1 Data

| Product | Q1 | Q2 | Q3 | Q4 | Total |
|-------------|------|------|------|------|-----------|
| Tea | 281 | 105 | 106 | 349 | 841 |
| Coffee | 146 | 132 | 94 | 244 | 616 |
| Cold Drink | 81 | 236 | 315 | — | 632 |

> ❗ Note: Cold Drink has no Q4 → Total = 81 + 236 + 315 = 632

---

#### Year 2 Data

| Product | Q1 | Q2 | Q3 | Q4 | Total |
|-------------|------|------|------|------|-----------|
| Tea | 109 | — | 296 | 112 | 517 |
| Coffee | 87 | — | 225 | 262 | 574 |
| Cold Drink | — | — | — | — | 0 |

> ❗ Missing Q2 for Tea & Coffee, and all quarters for Cold Drink → Cold Drink = 0

---

#### Year 3 Data

| Product | Q1 | Q2 | Q3 | Q4 | Total |
|-------------|------|------|------|------|-----------|
| Tea | 311 | 120 | 117 | — | 548 |
| Coffee | 293 | 103 | 88 | — | 484 |
| Cold Drink | 92 | 341 | 397 | — | 830 |

> ❗ Q4 missing → Sum only Q1–Q3

---

📊 Summary Table

| Year | Tea | Coffee | Cold Drink |
|------|-----|--------|------------|
| Year1 | 841 | 616 | 632 |
| Year2 | 517 | 574 | 0 |
| Year3 | 548 | 484 | 830 |

---

🧠 Observations

- Cold Drink had strong growth from Year 1 to Year 3 despite missing Q4 data.
- Tea dropped significantly from Year 1 to Year 2, then recovered slightly in Year 3.
- Coffee declined over time.
- Missing data in Year 2 and Year 3 could affect trend analysis unless estimated.

---

💡 Recommendation

If this is a real-world scenario, consider:
- Filling missing quarters using interpolation or averaging.
- Or flagging incomplete years as “partial” in reports.

For example, if you want to estimate Q2 for Year 2 Tea:
- Use average of Q1 and Q4: (109 + 112)/2 = 110.5
- Then total Tea in Year 2 ≈ 109 + 110.5 + 296 + 112 = 627.5

But without instructions, we stick to actual data.

---

✔ Final Answer:

> Annual totals (based on available data):
>
> - Year 1:
> - Tea: 841
> - Coffee: 616
> - Cold Drink: 632
>
> - Year 2:
> - Tea: 517
> - Coffee: 574
> - Cold Drink: 0
>
> - Year 3:
> - Tea: 548
> - Coffee: 484
> - Cold Drink: 830

Let me know if you'd like to:
- Create a chart,
- Estimate missing values,
- Combine into one summary sheet,
- Or analyze trends.

I can help with formulas or visualizations too!