Explanation
Excel can consolidate data based on how the source ranges are structured.
By Position
Use when corresponding values are in the same positions in each range.
Example:
|
Product |
Sales |
|
Keyboard |
4000 |
|
Mouse |
5000 |
|
Monitor |
25500 |
All branch sheets have the products in the same order.
By Category
Useful when labels identify the items and their order may differ.
Example:
Sirsa
|
Product |
Sales |
|
Keyboard |
4000 |
|
Monitor |
25500 |
|
Mouse |
5000 |
Hisar
|
Product |
Sales |
|
Mouse |
4500 |
|
Keyboard |
6000 |
|
Monitor |
17000 |
The product names allow Excel to match corresponding categories.
Practice
Rearrange the products in one branch and consolidate the data based on labels.