Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Looks like I need 365 rows in the pivot table? –  Stan Jul 5 '10 at 4:13
    
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 Jul 5 '10 at 4:59

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..

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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