SOLVED

Showing which month is the busiest in excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-677071%22%20slang%3D%22en-US%22%3EShowing%20which%20month%20is%20the%20busiest%20in%20excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677071%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20document%20in%20which%20I've%20got%20two%20tables%2C%20looking%20like%20the%20one%20attached.%20I%20want%20to%20combine%20the%20tables%20together%20(perhaps%20i'd%20use%20a%20power%20query%3F)%20and%20then%20i%20want%20to%20show%20which%20month%20has%20more%20'actions'%20in%20it%2C%20to%20see%20when%20i'm%20busiest.%20I'm%20unsure%20how%20I%20would%20group%20the%20actions%20in%20months%20and%20then%20show%20which%20one%20was%20busier%20-%20has%20anyone%20got%20any%20advice%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBailey100%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-677071%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677147%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20which%20month%20is%20the%20busiest%20in%20excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677147%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPower%20Query%20will%20be%20good%20then%20load%20to%20Data%20Model%20and%20insert%20a%20Pivot%20Table%20which%20can%20then%20summarise%20your%20dates.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20file...%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117442iF467209814FF9A53%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EOnce%20you%20put%20a%20date%20in%20a%20row%20or%20column%20in%20a%20Pivot%20Table%20you%20can%20right%20click%20and%20group%20by%20month%20and%20year%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHave%20you%20ever%20used%20Power%20Query%20before%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20version%20of%20Excel%20are%20you%20using%3F%26nbsp%3B%20%26nbsp%3BExcel%202016%20%3F%26nbsp%3B%20%26nbsp%3BExcel%20365%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677167%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20which%20month%20is%20the%20busiest%20in%20excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%2C%20if%20you%20attach%20small%20sample%20file%20it'll%20be%20easier%20to%20demonstrate.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677169%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20which%20month%20is%20the%20busiest%20in%20excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677169%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20Wyn%2C%20I%20answered%20too%20long%20and%20didn't%20see%20your%20post%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677276%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20which%20month%20is%20the%20busiest%20in%20excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677276%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20response%2C%20I%20have%20used%20power%20query%20before%20but%20only%20on%20a%20basic%20level.%20I'm%20unsure%20how%20you've%20created%20the%20pivot%20table%3F%20I'm%20using%20excel%202016.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%2C%3C%2FP%3E%3CP%3EBailey100%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677415%22%20slang%3D%22en-US%22%3ERe%3A%20Showing%20which%20month%20is%20the%20busiest%20in%20excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677415%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20Load%20to...%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20375px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117448iB10488BCB22BFBC8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESelect%20Add%20to%20the%20Data%20Model%20and%20Only%20Create%20a%20Connection%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20311px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117449iD81FC7CCA40B1BC4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInsert%20%26gt%3B%20Pivot%20Table%20%26gt%3B%20OK%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20set%20up%20like%20this%20and%20right%20click%20on%20the%20dates%20and%20choose%20Group%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20953px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117450iB23AE396EE5409DB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGroup%20by%20Day%2C%20Month%2C%20Year%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20365px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117451i1F8A39D53CE522C2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi,

 

I have a document in which I've got two tables, looking like the one attached. I want to combine the tables together (perhaps i'd use a power query?) and then i want to show which month has more 'actions' in it, to see when i'm busiest. I'm unsure how I would group the actions in months and then show which one was busier - has anyone got any advice please?

 

Many thanks

 

Bailey100

5 Replies
Highlighted
Solution

Hi @Bailey100 

 

Power Query will be good then load to Data Model and insert a Pivot Table which can then summarise your dates.

 

See the attached file...

image.png

Once you put a date in a row or column in a Pivot Table you can right click and group by month and year

 

 

 

Have you ever used Power Query before?

 

What version of Excel are you using?   Excel 2016 ?   Excel 365?

 

 

 

Highlighted

@Bailey100 , if you attach small sample file it'll be easier to demonstrate.

Highlighted

@Wyn Hopkins 

Thank you Wyn, I answered too long and didn't see your post

Highlighted

Hi, @Wyn Hopkins 

 

Thanks for your response, I have used power query before but only on a basic level. I'm unsure how you've created the pivot table? I'm using excel 2016.

 

Thanks again,

Bailey100

Highlighted

@Bailey100 

Select Load to...

image.png

Select Add to the Data Model and Only Create a Connection

  

image.png

 

Insert > Pivot Table > OK

 

Then set up like this and right click on the dates and choose Group

image.png

 

 

 

Group by Day, Month, Year

image.png