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.

I have an Excel table which is simply one column of dates. Each date has a distinct value, and they are already sorted. Something like:

5/29/2011
6/21/2011
6/26/2011
7/1/2011
7/3/2011
...

I would like to produce a chart from this where the X axis is time and the Y axis is the row count for that time, like this:

Chart example

I can do this by introducing a new column with a value of =Row() and charting both columns, but this seems silly and inelegant. Can Excel plot this chart without me going through this additional hoop?

share|improve this question
    
Yes, but Excel IS silly and inelegant. That's why so many of us enjoy bending it to our needs, while so many others bail for a non-silly, elegant solution like R. –  dav Apr 15 at 11:50

2 Answers 2

up vote 1 down vote accepted

There is a built-in way do accomplish this task in Excel without using an additional column, using a feature called Named Ranges.

This example assumes that the dates are entered into cells A1:A5.

On the Formulas tab, choose Name Manager. Choose "New" to create a new name. Choose a name for the name, such as rows. Under "Refers to", enter the formula =ROW(Sheet1!$A$1:$A$5) (this assumes that your sheet is called Sheet1). This formula should be extended past row 5 depending on how many dates you have.

Edit Name

Then, right-click on your chart, and choose Select Data. Edit the data series. Under "Series values", enter =Sheet1!rows, replacing rows with whatever you named the name before.

Edit series

Click OK twice, and the Y axis of your chart should now show the row numbers.

Chart

share|improve this answer

Named Ranges are the way to go for your solution. However, since you prefer something more elegant, I would use ranges for both your data and your axis labels, and make sure that they expand as you add data.

So, create a Named Range called Data, using the formula

=OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A),)

Then, create a Named Range called Labels, using the formula

=ROW(OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A),))

These formulas will count your cells and add the data to both your chart and it's axis labels. ASSUMING that you start in Row 1 and don't have any gaps (blanks) in your data.

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.