If you have data in different places that you would like to bring together as one, Consolidate is a great option.
You can choose data from different ranges and combine it together either by the headings in the top row, the left column, or both.
Once you select Consolidate from the data menu (shortcut keys: Alt, A, N) it brings up the Consolidate control box.
Enter Ranges
Here you are able to enter the ranges of the information you are wanting to join together. You can type in the ranges, or use your mouse or arrow keys to highlight the areas your data is in. You then have to click Add to add this to the All references section. Selecting Browse… will enable you to choose data from a different spreadsheet.
Note that the ranges you select, do not need to be the same size, or in the same place on each sheet. You can choose to sum the figures, as I have done, or use various other functions, count, average, max, etc.
Use labels in
Lastly, you need to tick one or both boxes in the “Use labels in” section. This will determine how your data is organised and joined together in the output. The data needs to have category labels in either the top row or left column (or both) of the areas you have selected.
Create links to source data?
If you tick the “Create links to source data” your consolidated data will update automatically if the data in the ranges changes because it creates formulas and the layout of the data allows you to drill down into the details of each total, sort of like a pivot table. I usually prefer not to do this as I am doing a one off of collecting all the data together, but have a play around and see what works for your situation.