MAKING EXCEL WORK FOR YOU

Summarise data from several Excel Worksheets…

If you have data on several sheets and need to create a summary then this example demonstrates a simple way to  create a summary sheet of the data from those sheets.

Note: The layout of the data must be exactly the same on each sheet, i. e corresponding amounts should always be in the same cell on each sheet.

For example a chain of shops has a sheet for each shop that records sales.. These sheets should be collected into one workbook, that in addition has a sheet with the same layout to hold the summary. You could use a formula like:

 =Sheet1!B2 +Sheet2!B2 + Sheet3!B8

However, this formula will need updating whenever a sheet is added or removed. My example demonstrates a simple way to summarise the data that will not involve having to change formulas if you change the number of data sheets. To make this trick work you need to add two sheets, one between the summary sheet and the the first data sheet and the other sheet should be to the right of the last data sheet. In the example I have named them Top and Bottom. In the summary sheet you add formulas to sum the corresponding data like:

=SUM(Top:Bottom!B2)

In the formula Top & Bottom are the outside sheet names. So this formula will add up the amounts on each sheet between these sheets that is in B2. If you use this method to record monthly data then you can add sheets as required. Providing that you use the same layout and place the new sheet between “Top” & “Bottom” then the data will update without any changes to the formula. By moving a sheet outside of the Top & Bottom tabs, that sheet's data is no longer included in the Summary sheet.

In Excel you can move the position of a sheet by simply clicking on the relevant Worksheet tab and dragging it to a different position. This makes this trick even more useful because you can see what would happen to the summary if you move a data sheet from between between the Top & Bottom tabs. This data is then removed from the totals, so in the simple example you could see the effect of closing one of the group’s branches.

I have added a macro that adds a sheet in the specified position between Top  & Bottom. This sheet is based on a hidden Template sheet.in the workbook. The user is prompted by a Excel VBA MessageBox to provide a new name for the record sheet that is added.

royUK Summarise Data Tip.zip

Get the free example workbook

Watch the demonstration video

 © Roy Cox 2005 to 2013

Our Privacy & Cookie Policy