Ron de Bruin, Microsoft Office Excel MVPFrank Rice, Microsoft Corporation August 2008Applies to: Microsoft Office Excel 2007, Microsoft Office Excel 2003, Microsoft Excel 2002, Microsoft Excel 2000Contents or merge the data in each workbook into a master workbook.
The examples described in this article add the data from multiple workbooks to a summary workbook.
In this post, I'll show you how to use the Consolidate data feature in Excel to take data from a series of worksheets - or workbooks - and summarize them into a single worksheet.
Consider the situation where you have data for twelve months of your business stored one month per sheet in an Excel workbook.
For a number of reasons, however, pie charts are not as effective as bar or column charts.
Their sole benefit is that they show each category's proportion of the total of all categories. While proportions are shown graphically in a pie chart, except for proportions of 25% or 50%, it is not easy to visually determine what these proportions are, unless data labels are used to show the percentages.
You can also use it to quickly summarize data in a single sheet - without sorting the data.
To summarize and report results from separate worksheets, you can consolidate data from each into a master worksheet.
You can download workbooks containing the code in this article at Ron de Bruin's Web site.
Of the three types, a bar chart may be the best option, because the category labels can be fairly long without wrapping and without the need to incline them from horizontal.
This is not a problem in the column chart below, because there are only three labels, and none contain much text.
To use formulas to consolidate the list of responses, first place the unique responses into a range, as shown in C2: C4 in the table at left. In cell D2, enter this formula: Copy cell D2, select D3: D4, and paste to complete the consolidated table.
To create a bar, column, or pie chart, select this range or any single cell in the range, and run the chart wizard.