2

My data has column 1 = date (date format), column 2 = quantity (number format). The data looks like:

date       qty
1/3/2010   5
2/3/2010   2
2/4/2010   3
2/9/2010   1
4/13/2010  5
5/31/2010  5
6/1/2010   1

Then I select these 2 columns and make a bar chart, column1 = x axis, column2 = y axis. I got below chart:

alt text

How can I make the date axis have a real time scale, meaning every month should have the same distance?

Improve this question

3 Answers 3

Reset to default
4

The most simple way to do it is with an Scatter (X-Y) chart but this is a line chart and won't display the bars you want.

To do it with bars you need to create a pivot table with date in the row labels and sum of qty as the values. Note this will only work if you only have one sample per date.

Set grouping on the row labels to 'Days' and 'Years' and then go in to field settings and turn on 'Show items with no data'.

Then create a pivot chart using the bar chart style. You'll get a bar chart with 365 days and the samples spaced correctly.

Improve this answer
2
  • 1
    Looks like I need 365 rows in the pivot table?
    – Stan
    Commented Jul 5, 2010 at 4:13
  • 1
    I think so. Once the grouping is set to 'Days' Excel (2007) wants to list all unique days. I can't find a way to reduce it to a smaller range on the pivot table or on the pivot chart. However, you could base a <i>normal</i> bar chart on a section of your pivot table.
    – Mike Fitzpatrick
    Commented Jul 5, 2010 at 4:59
1

I know you've accepted an answer, but...

You could try some in cell charts. Count the amounts your dates appear in your range and then use the REPT() function to add | within a cell. Here's a link to a website that shows good ways of using in-cell charts.www.chandoo.org/in-cell-charts

Also if you make your range that it counts dynamic, using OFFSET(), then the chart will update as you receive more dates.

...possible issue in that you'll need to find all the unique dates - but this can be done quite easily with a bit of snooping around sites like these..

Improve this answer
1

I know this is an old question, but it's still worth tossing in my two cents.

I'm not really sure you really want a date axis for your bar chart, or even a bar chart, given the vastly irregular spacing of your dates and the way Excel spaces bars along a date axis.

Below I show your bar chart on the left. The vertical axis has a so-called Text Scale, meaning each date has a single slot along the axis, and the dates are treated as non-numeric categories.

To the right, I've changed the axis to a Date Scale, where each day has its own slot, one day wide, along the axis, and the bars are positioned according to the numeric values of the dates.Since each bar must fit within its slot, each bar is less than the width of a day, so they are more like hairlines than bars. If you had two bars for the same day, they would plot one in front of the other.

Below the bar charts is a line chart. The date scale is horizontal, which is more natural for humans to understand. The thin bars are replaced by markers, which are easier to see. Multiple points on the same day are represented by multiple markers, so they don't obscure each other as in the bar chart.

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .