Say you have total monthly spend for 36 months and you want to display it in a bar graph to show all the spend for the 3 Januarys, then all the spend for the 3 Februarys, etc. When I sort my data that way, and then put it in a chart, it automatically displays it in chronological order, no matter how I have it sorted. I want a view of month-over-month to identify seasonal trends, so seeing the spend with the same month of each year grouped together would be ideal. I can't get that to display.
4
-
Welcome to Super User. We need a little more information about how your data is laid out and how are your dates formatted. What version of Excel are you using?– CharlieRBCommented Oct 7, 2015 at 19:47
-
I am using Excel 10, version 14.0.6129.5000 (32-bit). My data is very basic. I have the year in one column as 10-2012 and a dollar amount in the next column as $5,000, for example. I did try using the Month function, @Riet. It did returnt he correct numerical month but I need a bar chart, not a scatter graph.– Caryl BeatyCommented Oct 7, 2015 at 20:34
-
I think if figured it out. I put the months of the year across Row 1 from B1 to M1 with no year reference, and the Years in Column A, then populate the dollar amounts for each month/year in B2 to M4, it works. Separating the month and year into different cells seems to be the key.– Caryl BeatyCommented Oct 7, 2015 at 20:45
-
Glad to hear. You can answer your own question. Just use the area below to describe how you resolved it.– CharlieRBCommented Oct 8, 2015 at 3:46
Add a comment
|
1 Answer
You might try excel's month() formula. Putting this in a column as the x axis and then scatter plotting it should work.
For example, if the dates are a1:a12, then I would put =month(a1) in column b, with the data in column c.
-
1