Consolidate Excel data by position

Most companies use Excel to produce budget reports. In this video lesson you will learn how to consolidate data from January, February and March to produce a 1st Quarter Consolidated Budget report – based off of an Excel Template.

Here are the steps to follow in this video lesson:

Start with a Template – either on your computer or download one from the Microsoft website. Modify as necessary and then make multiple copies of it. e.g. 1 ea. for Jan, Feb, Mar and 1st Qtr, etc.
Select the 1st cell to receive consolidated data in your 1st Quarter Consolidated report. Then, choose Data, Consolidate.
Point to the January worksheet and select the range of cells that you want in the 1st Qtr consolidated report. Then click ADD.
Repeat this process pointing to the February and March worksheets. Because we based our monthly reports on a Template, the range of cells in each worksheet is exactly the same – i.e. “By Position.”
If you do not check the “Create links to source data” box, you will simply add (SUM) the data from the selected ranges into the Consolidation worksheet. SUM is the most common function but you are free to choose others.
If you did check the “Create links to source data” box your consolidation worksheet will be outlined and each cell will be linked back to its source cell.