You may well be tasked with creating one spreadsheet that has data coming from two or more spreadsheets. This could be a daunting prospect if you do not know about “consolidation”. This is a tool in Excel that brings together spreadsheets that have similar data. These files may well have been created by two different people in your organisation. And now you have the task to bring the information together.
The first thing you need to do is familiarise yourself with each file. The layout and data that is held within the files. Life becomes easier if you have the same information using the same words so you may need to change a few titles in the files so that they correspond to each other. Unfortunately, sometimes people use different words for the same thing.
Begin this exercise with only the files you require to consolidate opened.
Here are three files that have similar data and now have the same titles so that putting them together will be much more meaningful and easier. 
- Create a new file and save it as Consolidated We sell Used Cars.
- Copy across the headings in cells A5:A10 and B3:L4
- Start in cell B5. As this is where the data is in all of the other three files that you need to be collected and added together.
- Select from the Data Tab the Consolidate Tool.
This dialog box appears. 
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.
- Select the area B5: L11
- 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.
- Select the same area again B5:L11
- Select the Add button.
- Select the Switch Windows Tool.
- Select the last file.
- Select the same area one last time B5:L11
- Select the Add Button.
Your dialog box should look like this. 
It would be wise to check at this point that all three references are similar. i.e. the same area.
You could at this point select the OK button. But I like my files to have links in them especially if the files I’m taking information from are not mine and would be updated by the author in the future. I would not have to go through this process again if I choose the Create links to source data option at the bottom of the dialog box. 
Once you have checked the Create links box Select the OK button.
You are returned to the Consolidated File with a result looking like this. 
Look closely at the left of the row names. There is a line of plus marks that indicate there is more information held in the hidden rows. This is the data from all of the other files that make up the total in this sheet as seen at the moment. This is called outlining.
- Select one of the Plus marks and see the data beneath.
Here I have selected the Plus mark by the Insurance field.
You have now a wonderful consolidated file, that can be updated simply by saying yes to the opening message when you next open this file or selecting the Refresh all Tool from the Data Tab. 
Occasionally Excel will have a blip.
- It will not stay in the same view so that you can select the Switch Window Tool and the file from the list so that you need to select the View tab first and then the tool to find the list.
- When you finish and select the OK button you may find yourself in one of the files not the consolidated file. Just locate it on your task bar.
In my next tip I will investigate how you can still use the consolidation function when you do not have data that is the same all the way through.
