pivot table

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Pivot Table

Pivot Table

resolvedResolved · Medium Priority · Version 365

Linh has attended:
Excel For Scientists course
Excel Intermediate course
Excel Advanced course

Pivot Table

I have a dataset consisting of Date and Time data (in separate columns - one has date i.e. 13/06/2024, and the other has time 11:30:00), along with measurement data. The data frequency is every 15 seconds (11:30:00, 11:30:15, 11:30:30, 11:30:45, etc). I wanted to turn the dataset into 1-minute average instead - so averaging 4 15-second rows into 1 minute (11:30:00, 11:31:00, 11:32:00, etc). Previously I had used the offset function (=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*4,,4,))), but it is manual, takes a lot of time, and doesn't work properly if there are irregular data (i.e. if there are only 3 15-second rows for the minute instead of 4 due to measurement error). So I turned to Pivot Table to try to make it work better. However, when I used Pivot Table, the summarise function works but the format is quite weird: instead of having the proper minutely data (11:30:00, 11:31:00, etc), it listed the data in the hours, followed buy the minutes (e.g. the rows are sorted by hours (11, 12, 13, etc.), and then the minutes are shown separately (30, 31, 32, 33, etc.). Is there a way for it to display the data as 11:30:00, 11:31:00, 11:32:00,... in continuous sequence instead of grouping the rows?

RE: Pivot Table

Hi Linh,

Thank you for the forum question.

When you group by Hours and minutes in a PivotTable you will get the "strange" looking format, but look at attached Excel file.

I have on the Design tab in the Layout group clicked Report Layout.

Here I selected Show in Tabular Form and clicked Repeat All Item Labels.

It is not perfect but better.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

npl.xlsx

Edited on Mon 17 Jun 2024, 09:44

RE: Pivot Table

Hi Jens,

Thanks for your answer. Your solution does help a bit, but what I need is a little bit more specific:

Currently, with PivotTable (tabular form and repeat all labels), the output display is:
Date|Hours|Minutes|Averaged_data
16/06/2024|13 |:50 |0.02
16/06/2024|13 |:51 |4.7
16/06/2024|13 |:52 |6.7

What I need it to display is:
Averaged_time |Averaged_data
16/06/2024 13:50:00 |0.02
16/06/2024 13:51:00 |4.7
16/06/2024 13:52:00 |6.7

Is there a way for PivotTable to display the combined date and time in a single cell? This is because I will need to export the dataset to another excel sheet and that sheet only recognises that date/time format to use in further calculations. Or better yet, is there a way to export the data from the PivotTable that would be displayed as the format I need?

Also, with PivotTable, at the end of every hour, there is a row saying:
16/06/2024 | 13 Total | 4.9

I think it's the average for the hour (it says total but the number is obviously not a total of the values in the hour)? I don't need this row as well, how do I remove it?

Thanks,
Linh

RE: Pivot Table

Hi Linh,

Sorry cannot be done by PivotTables but by Power Query.

See attached file.

I have added the source data to Power Query and merged the columns dates and time. I have grouped it by time and calculated the average.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector


 

Excel tip:

Make a quick copy of a worksheet

Hold down the Ctrl key, then click and drag on a sheet tab to make a copy of that sheet. Though this process usefully copies the formats of the original sheet, note that any Range Names you have on the original sheet will be duplicated too.

To make a copy of a worksheet's contents and formats without duplicating range names: (1) Ensure that you have a blank worksheet to paste to. (2) On the sheet to copy, click on the sheet selection square to the left of Column A's heading to select the whole sheet. (2) Copy the whole sheet. (3) Paste to the blank worksheet.



View all Excel hints and tips


Server loaded in 0.06 secs.

✓ w3speedster