Chart/Graph

[tvisgod]

I have a column of dates. These are the first ten cells in the column:
<table border="0" cellpadding="0" cellspacing="0" width="103"><colgroup><col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt; width:77pt" align="right" height="20" width="103">8/5/1993</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">8/6/1993</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">8/6/1993</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">8/6/1993</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">8/6/1993</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">8/6/1993</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">8/10/1993</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">8/10/1993</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">8/10/1993</td> </tr> </tbody></table>
The column is actually 170,000 cells long. I would like a chart that demonstrates how often certain dates come up. Specifically, spikes (where a date comes up fifteen or more times in a row) or troughs (where eight or nine dates are skipped) should be represented.

Is this possible? Do I just have too many cells? If so, is there a way to group them by month or some other time period (one entry for August, 1993, for instance). How might I go about doing any of this? I am a real chart/graph novice.

 

[CWatts]

Your best bet is to use a pivot table with acompanying pivot chart and you'll be done quickly.

After you have a pivot table set up, you can use value filters to only display those over a certain threshold, and they can also be grouped by year/month/day

 

[tvisgod]

Thank you. Is there any chance you or someone else would want to walk me through a basic pivot table for this?

 

[CWatts]

Sure. What version of Excel are you using?

 

[tvisgod]

2007

 

[CWatts]

Perfect. Assuming your data looks like this.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Dates</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">8/1/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">8/2/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">8/4/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">8/4/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">8/5/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">8/5/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">8/7/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center">8/8/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center">8/8/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center">8/11/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center">8/13/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: center">8/18/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: center">8/24/11</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: center">8/25/11</TD></TR></TBODY></TABLE>

Make sure it has a heading row at the top. Select all of your data. In my example, I'll highlight A1:A15.

Go to the insert tab and click pivot table on the left. Click OK on the box that pops up. You'll now have "To build a report choose fields..." and another box opened up on your screen with Choose fields to add to report.

Take Dates and drag it to the Row Labels section. Now drag dates to the Values sections, and it should change to "Count of Dates"

Now that you have these results, you can filter them if you like.

Right click on a date on your pivot table (any will do) and choose Filter > Value Filters. You can then choose "count of dates" and then "is greater than or equal to" and type 10 (or whatever you want) in the box.

If a date shows up less than the number you entered, it won't show. If the date shows up more times, then it will be displayed.

Afterward, you can chart the results by going to the red Pivot Table tools tabs and choosing Pivot Chart.

Start with that and play around a bit, and if you need help tweaking something from there, just let me know.

 

[tvisgod]

Thank you. The big issue I'm having right now is that when I move "Dates" from "Choose Fields to Add..." to "Row Labels" and then again to "Values," it counts all of my dates together. In your example, it just gives me "14" as a total date count, when what I really want is 1 (for 8/1), 1 (for 8/2), 2 for 8/4, 2 for 8/5, 1 for 8/7, 2 for 8/8, 1 for 8/11....

I've been trying different options, but I can't find one that will stop grouping all the dates together before giving me a Date Count.

 

[CWatts]

I think you either misinterpreted, or I didn't articulate what I meant well enough.

When you have your Pivot Table Field List open, you dont put the Dates in either Row Lables or Values, but you put it in both.

For example.

In the "Choose Fields to add to report" section, you'll see Dates with an empty check box. Drag Dates into the Row Labels box.

In the "Choose Fields to add to report" section, you'll now see Dates with a marked check box. Drag it into the Values box.

 

[tvisgod]

Thank you! I had misinterpreted as you said. This is working great for now.

 

[tvisgod]

I see that I can group items by "month," but it puts January 2007, January 2008, January 2009, etc.... all together in one item called "January." Is there any way to group items by month and not have this happen?