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.

  • 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? – CharlieRB Oct 7 '15 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 Beaty Oct 7 '15 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 Beaty Oct 7 '15 at 20:45
  • Glad to hear. You can answer your own question. Just use the area below to describe how you resolved it. – CharlieRB Oct 8 '15 at 3:46

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.

| improve this answer | |
  • 1
    ="m" & text(DateColumn,"MMYY") – Hannu Oct 7 '15 at 18:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.