@Pivot Table and analysis of weather data

%3CLINGO-SUB%20id%3D%22lingo-sub-1594709%22%20slang%3D%22en-US%22%3E%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1594709%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20personal%20weather%20station%20with%20almost%2014%20years%20of%20data%20recorded%20at%201%2F2%20hour%20data%20points.%26nbsp%3B%20I%20have%20turned%20this%20txt%20data%20into%20an%20excel%20database%20which%20has%20225%2C000%20records.%26nbsp%3B%20In%20the%20data%20is%20low%20temperature%20for%20each%201%2F2%20hour%20data%20point.%26nbsp%3B%20How%20would%20I%20use%20pivot%20tables%20to%20extract%20out%20the%20highest%20low%20temperature%20for%20each%20day%3F%26nbsp%3B%20I%20am%20not%20expert%20enough%20at%20pivot%20tables%20to%20know%20how%20to%20filter%20data%20so%20that%20I%20only%20extract%20out%20the%20highest%20low%20temperature%20for%20each%20day%20rather%20than%20the%20low%20temp%20for%20each%201%2F2%20hour%20datapoint.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1594709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1595378%22%20slang%3D%22en-US%22%3ERe%3A%20%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1595378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F761252%22%20target%3D%22_blank%22%3E%40monicard01%3C%2FA%3E%26nbsp%3BPerhaps%20like%20in%20the%20attached%20(simplified)%20example%3F%3C%2FP%3E%3CP%3ENot%20sure%20what%20you%20mean%20by%20%22highest%20low%20temperature%22%2C%20though.%20I%20assumed%20you%20wanted%20the%20lowest%20temperature%20on%20any%20given%20day%20and%20used%20%22Min.%22%20in%20the%20value%20field.%20Use%20%22Max.%22%20if%20you%20really%20want%20the%20highest%20(i.e.%20warmest)%20of%20all%20the%20low%20temperatures.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1596507%22%20slang%3D%22en-US%22%3ERe%3A%20%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596507%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20seems%20to%20work.%26nbsp%3B%20I%20wanted%20to%20find%20out%20which%20days%20had%20the%20highest%20minimum%20temperature.%26nbsp%3B%20How%20do%20you%20concatenate%20the%20date%20and%20time%20fields%20together%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1596576%22%20slang%3D%22en-US%22%3ERe%3A%20%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596576%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F761252%22%20target%3D%22_blank%22%3E%40monicard01%3C%2FA%3E%26nbsp%3BIn%20my%20example%20I%20just%20entered%20date%20and%20time%20into%20one%20cell.%20But%20if%20you%20have%20date%20and%20time%20in%20two%20different%20cells%2C%20make%20sure%20that%20they%20are%20formatted%20as%20date%20and%20time%20respectively.%20Then%20you%20can%20create%20a%20time%20stamp%20by%20adding%20the%20two%20together%20and%20apply%20a%20custom%20format%20%22%26nbsp%3Bdd%2Fmm%2Fyyyy%20hh%3Amm%20%22.%20See%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1596589%22%20slang%3D%22en-US%22%3ERe%3A%20%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596589%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOh%20my...That%20simple.%26nbsp%3B%20I%20was%20trying%20concatenate%20among%20other%20things.%26nbsp%3B%20Thanks%20for%20clarifying%20and%20I%20appreciate%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1596595%22%20slang%3D%22en-US%22%3ERe%3A%20%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596595%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F761252%22%20target%3D%22_blank%22%3E%40monicard01%3C%2FA%3E%26nbsp%3BGlad%20I%20could%20help.%20%3CSTRONG%3ESimplicity%3C%2FSTRONG%3E%20is%20my%20middle%20name%20%3A))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1596600%22%20slang%3D%22en-US%22%3ERe%3A%20%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596600%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHmm...I%20just%20did%20the%20same%20thing%2C%20but%20all%20I%20get%20is%20the%20low%20temperature%20for%20the%20month.%26nbsp%3B%20How%20can%20I%20select%20each%20date%3F%26nbsp%3B%20My%20row%26nbsp%3B%20labels%20only%20show%20month.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1596623%22%20slang%3D%22en-US%22%3ERe%3A%20%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596623%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F761252%22%20target%3D%22_blank%22%3E%40monicard01%3C%2FA%3E%26nbsp%3BDid%20you%20group%20by%20day%3F%20(Screenshot%20taken%20on%20a%20Mac%2C%20but%20similar%20on%20a%20PC)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-08-18%20at%2018.57.19.png%22%20style%3D%22width%3A%20199px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F213113i6BE2824B968C4279%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-08-18%20at%2018.57.19.png%22%20alt%3D%22Screenshot%202020-08-18%20at%2018.57.19.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOtherwise%2C%20upload%20part%20of%20your%20file%2C%20say%20the%20first%20200%20rows%20only%2C%20with%20your%20pivot%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1596643%22%20slang%3D%22en-US%22%3ERe%3A%20%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596643%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20where%20grouping%20shows%20up.%26nbsp%3B%20I%20do%20not%20see%20that%20as%20an%20option%20when%20I%20click%20on%20the%20row%20labels%20arrow.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1596660%22%20slang%3D%22en-US%22%3ERe%3A%20%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596660%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F761252%22%20target%3D%22_blank%22%3E%40monicard01%3C%2FA%3E%26nbsp%3BRight-click%20on%20a%20date%20within%20the%20pivot%20table.%20Then%20the%20menu%20should%20show.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1596717%22%20slang%3D%22en-US%22%3ERe%3A%20%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1596717%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20works.%26nbsp%3B%20Thanks.%26nbsp%3B%20Any%20way%20of%20ranking%20the%20values%20within%20the%20pivot%20table%2C%20for%20instance%20highest%20to%20lowest.%26nbsp%3B%20Pivot%20tables%20are%20very%20powerful%20as%20long%20as%20you%20know%20how%20to%20manipulate%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1597016%22%20slang%3D%22en-US%22%3ERe%3A%20%40Pivot%20Table%20and%20analysis%20of%20weather%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1597016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F761252%22%20target%3D%22_blank%22%3E%40monicard01%3C%2FA%3E%26nbsp%3BNot%20sure%20if%20this%20is%20official%20functionality%2C%20but%20when%20you%20select%20the%20first%20cell%20to%20the%20right%20of%20the%20column%20headers%20of%20the%20pivot%20table%20(in%20my%20example%20file%20that%20would%20be%20F1)%20and%20then%20select%20%22Filter%22%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-08-18%20at%2021.11.08.png%22%20style%3D%22width%3A%20111px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F213140i465DD64494F3F7D8%2Fimage-dimensions%2F111x144%3Fv%3D1.0%22%20width%3D%22111%22%20height%3D%22144%22%20title%3D%22Screenshot%202020-08-18%20at%2021.11.08.png%22%20alt%3D%22Screenshot%202020-08-18%20at%2021.11.08.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E...%20it%20will%20put%20a%20filter%20button%20on%20the%20header%20row(s)%20in%20the%20pivot%20table.%20Then%20you%20can%20use%20it%20as%20on%20any%20other%20table%20with%20filter%20buttons.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-08-18%20at%2021.15.02.png%22%20style%3D%22width%3A%20249px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F213142i5E698594DA07F478%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-08-18%20at%2021.15.02.png%22%20alt%3D%22Screenshot%202020-08-18%20at%2021.15.02.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have a personal weather station with almost 14 years of data recorded at 1/2 hour data points.  I have turned this txt data into an excel database which has 225,000 records.  In the data is low temperature for each 1/2 hour data point.  How would I use pivot tables to extract out the highest low temperature for each day?  I am not expert enough at pivot tables to know how to filter data so that I only extract out the highest low temperature for each day rather than the low temp for each 1/2 hour datapoint.

11 Replies
Highlighted

@monicard01 Perhaps like in the attached (simplified) example?

Not sure what you mean by "highest low temperature", though. I assumed you wanted the lowest temperature on any given day and used "Min." in the value field. Use "Max." if you really want the highest (i.e. warmest) of all the low temperatures.

Highlighted

@Riny_van_Eekelen 

 

That seems to work.  I wanted to find out which days had the highest minimum temperature.  How do you concatenate the date and time fields together?

Highlighted

@monicard01 In my example I just entered date and time into one cell. But if you have date and time in two different cells, make sure that they are formatted as date and time respectively. Then you can create a time stamp by adding the two together and apply a custom format " dd/mm/yyyy hh:mm ". See attached.

 

Highlighted

@Riny_van_Eekelen 

Oh my...That simple.  I was trying concatenate among other things.  Thanks for clarifying and I appreciate the help.

Highlighted

@monicard01 Glad I could help. Simplicity is my middle name :)) Keep in mind that dates and times ar just numbers in Excel, but formatted in a particular way.

Highlighted

@Riny_van_Eekelen 

Hmm...I just did the same thing, but all I get is the low temperature for the month.  How can I select each date?  My row  labels only show month.  

 

Highlighted

@monicard01 Did you group by day? (Screenshot taken on a Mac, but similar on a PC)

Screenshot 2020-08-18 at 18.57.19.png

Otherwise, upload part of your file, say the first 200 rows only, with your pivot table.

Highlighted

@Riny_van_Eekelen 

 

Not sure where grouping shows up.  I do not see that as an option when I click on the row labels arrow.

Highlighted

@monicard01 Right-click on a date within the pivot table. Then the menu should show.

 

Highlighted

@Riny_van_Eekelen 

That works.  Thanks.  Any way of ranking the values within the pivot table, for instance highest to lowest.  Pivot tables are very powerful as long as you know how to manipulate them.

 

Highlighted

@monicard01 Not sure if this is official functionality, but when you select the first cell to the right of the column headers of the pivot table (in my example file that would be F1) and then select "Filter"

Screenshot 2020-08-18 at 21.11.08.png

... it will put a filter button on the header row(s) in the pivot table. Then you can use it as on any other table with filter buttons.

Screenshot 2020-08-18 at 21.15.02.png