For budgets Microsoft offers free templates from its site that can be used with Excel. The template comes pre-built with most of the formulas and labels that one typically needs in a budget template. You could then use copies of this template for the various months i.e. from January till December. You could also keep a blank copy which we’ll use later for the consolidation for instance for the first quarter, i.e.  for the months January, February and March.

Now let us focus on just a few of the budgets and actual details of a few items in the template and perform consolidations on them. For our example we’ll be looking into the budgets and actual of a few items such as Office, Advertising, Insurance, Employee Benefits and Delivery Costs.  Let us assume these are in the range D4 (the first cell of the range for Office) down to E8. Now to consolidate the data for all these items from the different sheets for January, February and March first click on cell D4 (the first cell of the range) and then click Data from the menu and on the ribbon click on Consolidation. The Consolidate dialog opens. Select Sum from the Function drop-down list. The Sum is the most commonly used consolidation function. Next for Reference click your January worksheet and select the range that we need to consolidate, in our case cell-range D4 to E8. Click on Add and it is added to the box All References. Now to use the data from the datasheet of February just point to the February sheet. Now here is a neat trick. As we use the same budgeting template for January, February and March when we point to the February worksheet the Reference field is already populated with the range of cells from the February Sheet that matches the range already selected previously for January. Click on Add and the range is added to the All References box.  Finally we repeat the process for the March spreadsheet too. Just point to the spreadsheet and the range from March is automatically populated in the Reference field. Finally click OK on the Consolidate dialog.  Our consolidated worksheet is instantly filled with the consolidated details for Budgets, Actuals and the difference. This process of consolidation is called consolidation by position.

Now here is another functionality that you can perform to make your consolidation more dynamic. Remember the Consolidation dialog a few steps earlier.  Revisit this dialog and we’ll explain another important functionality. Located in this dialog is a checkbox for Create Links to Source Data. Click this checkbox.  With this checkbox checked you can make changes to any of the data in the spreadsheets for January, February or March for the items we were trying to consolidate and instantly the changes are reflected in our consolidated sheet for the quarter.  This way we create a dynamic consolidation of our data.  There is an added advantage using the Create Links to Source data functionality. In our consolidated spreadsheet we also get an outline view. Clicking on any of the expand buttons of data in the outline view expands the view to display the details hidden by the outline view.  Also if you point to a cell you can actually notice the reference it makes to the source of the data i.e cells address from the spreadsheets it was obtained from.