subtotal results in another sheet

New Contributor


I want to use the window subtotal (data/subtotal), but obtain the results in another sheet. For example, I have the columns: year, month, day, hour and temperature. I would like to obtain the average temperature of the day (and then of the month and then of the year), not in the original sheet between the rows of my data, but each time in another new sheet.

Any suggestions?

Many thanks


3 Replies


With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture.

Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel,

on the contrary in some cases.

This would also be a blessing for all of us, as we can understand the problem much better,

a win-win situation for everyone.

It is also helpful to know the operating system and Office version,

as different approaches may be required depending on the Office version and OS.


Thank you for your understanding and patience


Geia mas



I know I don't know anything (Socrates)


Here is a sample. The first sheet contains YEAR, MONTH, DAY, HOUR and TEMPERATURE. The second one contains the caluclations after Data/Subtotal where the average value of TEMPERATURE is calculated every time the DAYy changes.

I would like to have the lines of the average values in a new sheet. Otherwise, I have to copy alll o ta new sheet as values, slecte all data except the average values and delete them. The target is to avoid this extra calculation and have these results directly in a new sheet.

@Efthimios_Zervas Perhaps a pivot table will do what you need. See example attached.