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 am trying to produce a Line graph that shows the number of access requests over a given time period to my server.

The log data is like so:

  • 10/05/2013
  • 10/05/2013
  • 10/05/2013
  • 11/05/2013
  • 11/05/2013
  • 14/05/2013
  • 14/05/2013

Sp the graph would show on the 14/05/13 there was 2 rows in the spreadsheet.

How do I represent this in a line graph.

The column is "D" where all my dates are held.

Thanks

share|improve this question

2 Answers 2

up vote 3 down vote accepted

Hmm, you could do a pivot with this data, or insert a column which will have the number of occurrences of each date.

Pivot table

  1. Be sure to have a column name and then do a PivotTable on the field D (Go to "Insert" > "PivotTable" > Select your data and then "OK").
  2. Drag the field into "Row Labels" and "Values" both. enter image description hereenter image description here

  3. You can then insert a line graph from the pivot table itself.

Adding a new column

Add a column to the right of the dates containing the formula (I assume that D1 contains the column name and that the data starts in cell D2):

=COUNTIF(D:D,D2)

Name the column (e.g. Number of access requests) and insert a line graph based on the two columns.

share|improve this answer

If this data will need to be updated on a regular basis, a Pivot Table fed by a Table is the way to go.

  1. Convert your data into a Table (Insert>Table). This will update whenever you add data. Also, if your data is coming from an ODBC compliant source (e.g. SQL Server), you can have it automatically update with current data.
  2. Add a column to your Table, called Count. Then enter the formula =1. This will automatically fill a 1 in for every value in your Table.
  3. Create a Pivot Table (Insert>Pivot Table) using your Table as a data source.
  4. Format your Pivot Table like this (it will be similar to Jerry's Pivot Table):
    • Row Labels = Date
    • Values = Count
  5. With your Pivot Table highlighted, create a Line Chart (Insert>Line). This will create a Pivot Chart which will automatically update with your Pivot Table.

When you're done with this setup, everytime you add a value to your primary data Table, you can refresh your Pivot Table and the associated Pivot Chart will also update.

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.