7

I got an excel table with two columns: date (dd/mm/yyy hh:mm) and value (integers).

The table has about 8000 values in a time range of 4 days.

When I try to create a line or bar chart on these two columns, excel somehow accumulates all values for one day (ignoring the time). i.e. I get a bar chart with 4 bars.

What I actually want is having a bar/line chart where every single row resembles a bar/point in line chart. Where can I find the friggin option to tell Excel what I want?

INFO: I use MS Excel for Mac: 2011, but I am pretty sure this is not a version related issue, but rather a 8th layer problem ;)

Alex
  • 1,141
  • 1
  • 13
  • 24
  • use the column that has the time as your value, not the int column. And make sure all the times are different. – user2140261 Oct 28 '13 at 12:44
  • I can't make sure all times are different - I am not supposed to edit the data nor do I want to, as its 8000 rows and growing rapidly... – Alex Oct 29 '13 at 09:34

2 Answers2

6

When you create an axis that has time and date values, Excel tries to be helpful and guess what you are trying to do. I'm not sure about the mac, but in Excel 2010, when you right click the axis, and format it, this is the first window that pops up:
enter image description here

For the Axis Type (seen around the middle here) it has Automatically select based on data selected. This is what is grouping your data together as dates. Change it to a Text axis, and all the points on your graph should now appear.

SeanC
  • 15,695
  • 5
  • 45
  • 66
  • Ditto. I still want the x-axis to be continuous across time, but I just want a point at each hour for which there is data. If I make the type 'text', places where there are gaps between data entries aren't taken into account – Serenthia Dec 11 '14 at 16:17
  • 1
    The way to select this in Office 365 in 2019 looks rather different to the screenshot from 2013, but the principle remains the same. Changing the axis type from "automatic" to "text" worked for me. (Can't believe that this isn't made more obvious!) – Amos M. Carpenter Mar 21 '19 at 00:38
0

You need to separate date from time.

  1. Create a new column and copy your date/time field (via equation or copy/paste)
  2. Format one column as a date and the other as time.

Now your chart shouldn't auto-group.

(I realize this question is old but maybe this will help someone else.)

Jordan Smith
  • 91
  • 1
  • 8