SOLVED

Showing which month is the busiest in excel?

Brass 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
best response confirmed by Bailey100 (Brass Contributor)
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?

 

 

 

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

@Wyn Hopkins 

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

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

@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

1 best response

Accepted Solutions
best response confirmed by Bailey100 (Brass Contributor)
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?

 

 

 

View solution in original post