Consolidate with formulas if the row & column headers of all worksheets are identical in Microsoft Excel

If you have several worksheets and every worksheet has an identical structure - that is, the headings in row 1 and column A are identical from sheet to sheet, you can create a total worksheet using simple formulas.

Example:
In the workbook 3 sheets - January, February and March contain tables with salary data.
On every sheet, the items in cells A2:A8 are identical.
On every sheet, the employees listed in B1:H1 are identical and never change.
Add a new worksheet, change the worksheet name to Total. Use a formula like the one shown in cell B2 ( see screen shot ) to add the other sheets.
The formula in Cell B2 is =SUM (January:March!B2).

The technique for entering a formula to sum a single cell on a number of sheets.

  1. Select Cell B2 in the sheet name Total.
  2. In the cell, type =SUM, and press Ctrl+A (the shortcut for displaying the formula argument box).
  3. In the first argument box, select the name of the first sheet in the range January.
  4. Press Shift, and select the last sheet in the range March.
  5. Click OK. Screenshot // Consolidate with formulas if the row & column headers of all worksheets are identical in Microsoft Excel
    Consolidate with formulas if the row & column headers of all worksheets are identical in Microsoft Excel

Users are saying about us...

  1. I believe one more step is needed... this was the only way I could get this to work. On either the first or last sheet, you must also select the cell (B2 in this example). You can also select a range of cells (=SUM('Sheet1:Sheet2 (2)'!A2:C2)).

  2. "If I understand correctly, that is exactly what the tip above explains.

    Are you having problems? If so, perhaps you could give some details about the structure of your workbook and we can help. "

  3. I believe one more step is needed... this was the only way I could get this to work. On either the first or last sheet, you must also select the cell (B2 in this example). You can also select a range of cells (=SUM('Sheet1:Sheet2 (2)'!A2:C2)).

  4. "Hi Erica,

    If I understand correctly, that is exactly what the tip above explains.

    Are you having problems? If so, perhaps you could give some details about the structure of your workbook and we can help.

    Alan."

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube