I have several thousand rows of date, time, and values in columns.

Each row is contains a date, time for that date, and a value for that time period (hour)

So each 24 rows has the same date with each having the next hour of the day.

I'd like to collapse or group the 24 rows leaving the last row with a subtotal of the value (column D) to the right in column F.

While this can obviously be done manually, several years of data would take a while and there ought to be a way to do this other wise?


The fastest way to accomplish this is to convert your data into an Excel Table. Then, create a Pivot Table from your Data Table. Choose Dates for your rows and Sum your Values in the totals section.

Two bonuses with this. First, the Pivot subtotals can update as you update your data table. Second, you can group the dates for subtotals by week, month, quarter, year, etc...

EDIT: Here's step-by-step directions (for Excel 2010, but they're very similar for Excel 2007).

  1. Convert your data into an Excel Table. Highlight a cell in your data and then Insert > Table.

  2. Create a Pivot Table from your data table. Highlight a cell in your new Data Table and then Insert > Pivot Table.

  3. Set-up your Pivot Table for your analysis. Highlight a cell in your Pivot Table and then choose the following:

    • Row Labels = Date

    • Values = Values

  4. Your Values are now subtotaled by Date. From here, feel free to play around with it. You can add more criteria in your rows or columns for a more robust report.

  • While I'm not very knowledgable with Pivot tables I'll give it a go.
    – Will
    Oct 7 '12 at 20:33
  • I can't get to it now, but i'll add some detailed directions in the morning.
    – dav
    Oct 8 '12 at 0:27

Try using Data \ Subtotal after sorting your data on the date. This will automatically insert "Total" rows beneath every change of date value and put each set of rows plus their Total in a collapsible group.

Collapsible group brackets show up on the left side of the spreadsheet with a little - symbol you can click to collapse a particular group. You can collapse all the groups to hide everything but the "Total" rows by clicking the number (usually "2") above the set of groups you want to collapse. If you want to copy just the total rows to a new sheet, first collapse all groups so you see only "Total" rows. Select all "Total" rows. Press Alt+; to deselect hidden rows, then copy. With only the total rows selected you can also add any subtotal function you want in the appropriate columns.

For a longer explanation with pictures, see this article.

  • Thank you for the suggestion. I'll do a test with some data in a separate sheet and see what becomes of it.
    – Will
    Oct 7 '12 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.