You can use Excel's Consolidate feature to consolidate your worksheets (located in one workbook or multiple workbooks) into one worksheet. Below you can find the workbooks of three districts. Show
Before you start: if your worksheets are identical, it's probably easier to create 3D-references (if you have one workbook) or External References (if you have multiple workbooks) to consolidate your data.  As you can see, the worksheets are not identical. However, the beauty of the Consolidate feature is that it can easily sum, count, average, etc this data by looking at the labels. This is a lot easier than creating formulas. 1. Open all three workbooks. 2. Open a blank workbook. On the Data tab, in the Data Tools group, click Consolidate. 3. Choose the Sum function to sum the data. 4. Click in the Reference box, select the range A1:E4 in the district1 workbook, and click Add. 5. Repeat step 4 for the district2 and district3 workbook. 6. Check Top row, Left column and Create links to source data. Note: if you don't check Top row and Left column, Excel sums all cells that have the same position. For example, cell B2 (in district1.xlsx) + cell B2 (in district2.xlsx) + cell B2 (in district3.xlsx). Because our worksheets are not identical, we want Excel to sum cells that have the same labels. If you check Create links to source data, Excel creates a link to your source data (your consolidated data will be updated if your source data changes) and creates an outline. 7. Click OK. Result. In: BlogDate: Dec 05, 2017By: Ed Lepre Do you need to view and compare lots of data in a single spreadsheet? There is a quick and easy way to do this – using the Data Consolidation tool. In this blog, Edmund, one of our Excel experts, shares his top tips. Hi, we are HappyWe are leading a movement to create happy, empowered and productive workplaces. How can we help you and your people to find joy in at least 80% of your work? More about HappyData Consolidation allows you to gather together your 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 you can update easily. The trouble is, working with Data Consolidation can be tricky! Don’t be put off. Data Consolidation is an incredibly useful tool that help you understand and present your data quickly and easily. The screen may look daunting but like all great things, good planning is the key. This example contains some tips from Edmund, one of our Excel experts, and will help you decide how to organise your data before you start summarising it to get easily understandable results. Here we look at:
NOTE: The following processes will work in Excel versions: 2007, 2010, 2013 and 2016. If you are using an older version (Excel 2003 or older), this tip may not work for you. Consolidate data in multiple worksheets within the same workbookIn our example, we have data for 3 years expenditure on tea, coffee and milk. The data is broken down into quarters and stored in one year per worksheet in one workbook. We can create a ‘Consolidated Summary’ sheet which will show expenditure by year and quarter. It does not matter if the data has the same arrangement of columns and rows or not. Excel will sort that out for you. Amazing! Year 1 worksheetYear 2 worksheetYear 3 worksheet
Consolidation steps:To start using the Data Consolidation tool, you need to select an empty sheet in the workbook as your master worksheet or add a new one if necessary. The worksheet is renamed ‘Consolidated Summary’. Select the upper-left cell of the area where you want the consolidated data to appear. On the Ribbon, Choose Data > Consolidate to view the Consolidate dialog:
Click in the Reference area and select the first data range to consolidate – to do this you will need to click the Sheet tab i.e. “Year 1” and then drag over the data (including row and column headings) and then click the Add button to add this first set of data to the consolidation dialog. Continue in the same way by clicking on the next sheet, highlighting the data, and clicking on the Add button until all your data and worksheets appear in the References section of the dialog e.g. “Year 2” and “Year 3”.
Automatic vs. Manual updates: If you want Excel to update your consolidation table automatically when the source data changes, select the Create links to source data check box. If unchecked, you can still update the consolidation manually. When you click OK, Excel summarises all the data into your new sheet as your master worksheet (Consolidated Summary). You’ll immediately notice a change to the Excel worksheet that you may never have seen before. You will see grouping tools down the left of the screen which you can use to display and hide the data. Next to rows 7, 10 and 14, there are plus signs. This signifies that cells are part of a group that is currently collapsed. Clicking on the plus sign will expand the group and there is a line connecting these rows to the left:
Copying your worksheets to a new workbookHere’s another time when pre-planning will be your friend. When you look at your Consolidated Summary, you will see that there are some entries which are not helpful. For instance, range C4:C6 shows only the name of the workbook, so we cannot immediately see where the figures in the range D4:G6 come from. If you wish to show the location of the source data on the summary sheet, I strongly advise you to separate all worksheets to individual workbooks before you apply the data consolidation. Let’s look at how to do this. We’re going to copy the selected worksheet into a new workbook, so select (new book) from the To book drop-down list. Select the Create a copy check box. Because we’re creating a new workbook, there are no worksheets in the Before sheet list before which we can insert the copied worksheet. It will be the only worksheet in the new workbook.
Continue creating new workbooks for each year e.g. “Year 2” and “Year 3” to “Year 2.xls” and “Year 3.xls” respectively. Yes, this is tedious, especially if you have a lot of worksheets, but you’ll thank me in the end! Consolidate data from multiple workbooks to one new workbookMake sure all the individual workbooks you wish to consolidate are currently open. Open a new, blank workbook as your master worksheet or add a new one if necessary. The worksheet is renamed as “Consolidate Summary” and save this workbook with a name e.g. Summary.xls. Select the upper-left cell of the area where you want the consolidated data to appear. On the Ribbon, Choose Data > Consolidate to view the Consolidate dialog We now simply proceed as we did in the first example, the only difference being we are selecting data ranges from different workbooks instead of different worksheets. When you click OK, Excel summarises all the data into your new master worksheet (Consolidated Summary).
Related Blogs
Sign up to our monthly newsletter, full of tips, tricks and news to help you to be happier and more productive at work.
Improve your productivity in Excel with happyHappy has high-quality, learner-focused Excel training courses for all skill levels, designed to improve your confidence and productivity. And our new online learning programme is just as interactive as our classroom sessions. Here are our most popular options:
Our learners tell us that they save an average of 32 minutes a day with our Excel training courses. How much time could you save with us? Why learn online with Happy?
Ed LepreEd is a Microsoft Certified Trainer (MCT) and has worked at Happy for over 12 years. Although he trains the full suite of MS Office and non-Office packages at all levels, his favourite package is Excel. A previous winner of the prestigious Learning and Performance Institute's 'Learning Professional of the Year' award, as well as other training accolades, he is also the IT Helpline Manager. More by Ed
Testimonials from happy CustomersIndependent Service Rating
What are the steps for consolidating data in Excel?Consolidate Data. Select the sheet where the consolidated data will be placed.. Click the cell in the worksheet where you want to place the consolidated data.. Click the Data tab on the ribbon.. Click the Consolidate Data button in the Data Tools group. ... . Select a function from the list. ... . Click the Collpase Dialog Box button.. What is the Consolidate tool in Excel?Data Consolidation allows you to gather together your 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 you can update easily.
|
