Consolidate data
- Thread starter GN0001
- Start date
It's used when you have data on multiple worksheets, of similar layout, that you want to group all together on one sheet. So...not exactly a replacement for formulas per se, but it might help in other ways.
Copied from help file:
Consolidate data
To summarize and report results from separate worksheets, you can consolidate data from each separate worksheet into a master worksheet. The worksheets can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data, you are assembling data so that you can more easily update and aggregate it on a regular or ad hoc basis.
For example, if you have a worksheet of expense figures for each of your regional offices, you might use a consolidation to roll up these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.
To consolidate data, use the Consolidate command on the Data menu.
About consolidating data
Show AllHide AllTo summarize and report results from multiple worksheets, you can consolidate data from each worksheet into a master worksheet. The worksheets can be in the same workbook or other workbooks. When you consolidate data, you are assembling data so you can more easily update and aggregate it on a regular or ad hoc basis.
For example, if you have a worksheet of expense figures for each of your regional offices, you might use a consolidation to roll up these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.
To consolidate data, you use the Consolidate command from the Data menu to display the Consolidate dialog box. You can use this dialog box in several ways to consolidate your data:
Position Use this approach when the data in all worksheets is arranged in identical order and location.
Category Use this approach when each worksheet organizes the data differently, but has the same row and column labels, which you can use to match the data.
3-D formulas Use this approach when the worksheets do not have a consistent pattern you can rely on. You can create formulas that refer to cells in each range of data that you're combining. Formulas that refer to cells on multiple worksheets are called 3-D formulas.
Copied from help file:
Consolidate data
To summarize and report results from separate worksheets, you can consolidate data from each separate worksheet into a master worksheet. The worksheets can be in the same workbook as the master worksheet or in other workbooks. When you consolidate data, you are assembling data so that you can more easily update and aggregate it on a regular or ad hoc basis.
For example, if you have a worksheet of expense figures for each of your regional offices, you might use a consolidation to roll up these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.
To consolidate data, use the Consolidate command on the Data menu.
About consolidating data
Show AllHide AllTo summarize and report results from multiple worksheets, you can consolidate data from each worksheet into a master worksheet. The worksheets can be in the same workbook or other workbooks. When you consolidate data, you are assembling data so you can more easily update and aggregate it on a regular or ad hoc basis.
For example, if you have a worksheet of expense figures for each of your regional offices, you might use a consolidation to roll up these figures into a corporate expense worksheet. This master worksheet might contain sales totals and averages, current inventory levels, and highest selling products for the entire enterprise.
To consolidate data, you use the Consolidate command from the Data menu to display the Consolidate dialog box. You can use this dialog box in several ways to consolidate your data:
Position Use this approach when the data in all worksheets is arranged in identical order and location.
Category Use this approach when each worksheet organizes the data differently, but has the same row and column labels, which you can use to match the data.
3-D formulas Use this approach when the worksheets do not have a consistent pattern you can rely on. You can create formulas that refer to cells in each range of data that you're combining. Formulas that refer to cells on multiple worksheets are called 3-D formulas.
Hi Luke,
Thank you for the response,
Can you please solve these questions?
1- How can I specify Category use or position us or 3-d formula approach in this dialog box?
2- I entered some values in B4 to B12 in two different worksheets. I added one range from open workbook and when I came to add the second range from second workbook, I clicked on Browse and picked up the workbook that my worksheet is in there. It says invalid reference.
3- In category approach, you would say that I can use to match data? I can't understand this part as well. Are you saying when two worksheets has the same row size & labels and the same column labels & size, I need to match it to be in the same location as my first worksheet? If so, how do you do that? copy and paste?
4-When I consolidate date, do I need to have all my workbooks open?
Thank you so much for your help
Guity
Thank you for the response,
Can you please solve these questions?
1- How can I specify Category use or position us or 3-d formula approach in this dialog box?
2- I entered some values in B4 to B12 in two different worksheets. I added one range from open workbook and when I came to add the second range from second workbook, I clicked on Browse and picked up the workbook that my worksheet is in there. It says invalid reference.
3- In category approach, you would say that I can use to match data? I can't understand this part as well. Are you saying when two worksheets has the same row size & labels and the same column labels & size, I need to match it to be in the same location as my first worksheet? If so, how do you do that? copy and paste?
4-When I consolidate date, do I need to have all my workbooks open?
Thank you so much for your help
Guity
1. If identical in layout, you could select the entire range. If not, you'd need to do one column at a time.
2. This is connected to question 4...I'm not sure, I've not used it myself, but I'm guessing they both have to be open (if you can use 2 workbooks, I'm not sure if they have to be in same book or not)
3. Lets say Sheet1 and Sheet2 each have a column labeled "Sales" with 1 row per month. Each sheet is for 1 employee. You want to know total sales. With Consolidate, you can select each range (row size doesn't matter, just the labels need to be same). We'll use the SUM function, and consolidate. It should add up all the numbers into a new column. If you select "create links" it will use formulas and group items. Otherwise, it just puts static values.
4. See #2
I admit I don't have any experience personally with using Consolidate, this is just from asking around and minor experimenting. Perhaps Chandoo would be kind enough to write-up an article?
2. This is connected to question 4...I'm not sure, I've not used it myself, but I'm guessing they both have to be open (if you can use 2 workbooks, I'm not sure if they have to be in same book or not)
3. Lets say Sheet1 and Sheet2 each have a column labeled "Sales" with 1 row per month. Each sheet is for 1 employee. You want to know total sales. With Consolidate, you can select each range (row size doesn't matter, just the labels need to be same). We'll use the SUM function, and consolidate. It should add up all the numbers into a new column. If you select "create links" it will use formulas and group items. Otherwise, it just puts static values.
4. See #2
I admit I don't have any experience personally with using Consolidate, this is just from asking around and minor experimenting. Perhaps Chandoo would be kind enough to write-up an article?