MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Here's an easy MS Excel question for you lot.
I've got a spreadsheet that I want to add a line chart to based on 2 columns within the sheet.
The sheet contains data on 4 tasks
Column A contains the date the activity occurred
Column B contains the name of the task
I want to produce a chart showing the number of each task per month/year i.e. I want to group by task and month/year.
I'm sure this must be really easy to do, but I can't see how to do it. If it helps I've stuck another column in with the date formatted to month and year just can't get a count of each activity per month.
Pivot chart?
Long hand do a month(cell) collective then count the instances of each one.
Or install the analysis pack and do a frequency histo on date
It's not that simple. You can use COUNTIF( ) ... its useful as you know the 4 activities so that can be one criteria
or download Power BI free in some ways but not sharable and does all the things excel thought were hard
I quite often just chuck another column in and do =text(A2,"mm/yyyy") next to each date to give each row a month & year
Then just do a countifs on another table to tot everything up
The other ways of doing it are entirely valid and can look neater
Hmm I've already got another column with mm/yyyy in it. Will look to use countif. Surprised there's no 'group by' function OOTB.
That's pivot tables swizzle, also try thinking how you would write a group by stuff command (like stuff man)
Easy - pivot chart.
[img] 
?0116124819[/img]
Use the "group" function in the pivot table to group by month & year. From there the chart is a doddle.
or download Power BI
And spend the next 6 months teaching yourself DAX so you can do really useful stuff with it
It would do the stuff the op wanted in a few clicks probably unless Ms change the interface/stuff it is however what we all wanted excel to do (using it on commercial projects in place of significant cost bi products)
SundayJumper - that looks exactly like what I want, is that using standard excel? Care to explain a bit futher, have you got any pseudo columns or anything else or is it built just using a pivot table and those 2 columns?
Pivot charts have saved hours of very clever formula writing
It's a standard Excel function now, save your data before playing with it, then have a go. You can't really get it too wrong, it's fairly intuitive
Steve - it's all done with a pivottable / pivotchart. I'll email it to you, probably easier than trying to describe it !
Pivotchart, it's aboput 10 clicks to create what you want including the grouping of dates.
Actually just checked it's 11 🙂
Many thanks to Sundayjumper for going above and beyond and also to everyone else who replied. It's now sorted and was pretty straightforward once you know how!