In my last tip I wrote about how to combine, or consolidate, two or more spreadsheets that had the same layout and headings. These needed to be combined in an all-inclusive file. I used the consolidation tool. Here I am going to investigate if you have two or more files but they don’t have all of the same headings, some have more and some have less. How do you overcome the challenge?
Begin this process with having open only the files you need to consolidate.
Here are two files with almost the same headings on the left of the data in column A.
Notice that they both start in the same place and happen to start with “Wages” but they do have different headings, not so many and in different places if they are the same.
Use the Consolidation Tool just as I showed you in the last tip.
- Create a new file and copy only the cells A1:F4. As these are the same in exactly the same places.
- Overtype the cell A2 with “Consolidated Data”.
- Save the file as Consolidated Data.
Your new file should look like this.
- Select cell A5, as this is where the data will start being collected from both files.
- Select the data Tab.
- Select the Consolidation Tool. This dialog box appears.
- You require the Function of Sum for this exercise.
- Click in the reference line.
- Click on the View Tab.
- Select Switch Windows Tool.
All the files you have open are listed here. Which is why I close all other files and only have the ones I am consolidating open.
- Select the first file ‘Birmingham’.
- Select the area A5:F11
- When you let go of the mouse the reference is in the reference line of this dialog box.
- Select the Add button in the dialog box.
- Select the Switch Window Tool.
- Select the second file ‘Luton’.
- Select the same area again A5:F11.
- Select the Add button.
Sometimes Excel does not leave you looking at the consolidation dialog box and the View tab to be able to select Switch Windows tool. therefore you just need to negotiate to that point.
This is all we are consolidating today. Your dialog box should look like this. The next step is to add the labels of the rows that you have collected.
On the left of this dialog box you have a choice of labels to use.
- In our case today we need the left column labels.
- Select the Create links to source data box.
This will allow any changes to the source data to be reflected in this file too.
- Select OK.
You should be looking at the result in the Consolidated Data file, but sometimes Excel sends you to one of the other files instead so just go to the Consolidated Data file to see your result that should look like this. Note that in the Birmingham file we had 6 headings:
Wages, Rent, Insurance, Council Tax, Entertainment, Advertising.
In the Luton file we had 5 headings:
Wages, Insurance, Rent, Advertising, Petty Cash.
Here in the Consolidated file we have 7 headings:
Wages, Rent, Insurance, Council Tax, Entertainment, Advertising, Petty Cash
Notice too that even though rent is in different places in the two files they come together in this one. similarly, those that are not repeated in the other file are still in this consolidated one.
This is an outlined file with more information hiding behind the expansion marks on the left of your sheet.
- Expand any of the headings by clicking on the plus mark to see the result.
I have expanded all of mine.
I have selected the cell B7 to show you the formula that makes up this result.
Even though we selected the totals line in both files, one being in row 11 the other in row 10 they are not in the consolidated file. You need to create the totals for yourselves. You also may need to reformat the first couple of lines as they hold the heading colour found in rows 3 and 4.
Now you can see that consolidation is a very powerful and easy to use tool to help collect data from many different files, where the data is in the same place but not necessarily having all the same headings.