• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Using Excel 2010 to chart data from a text file

I'm trying to track some network performance stats on a couple of workstations. I've written a small vbscript to handle the job, and it works nicely, in the background, generating a tab-delimited text file as it goes. I have it running as a scheduled task, that kicks off every 5 minutes.

The fields are as follows:

So my file might look something like:
www.yahoo.com     11:06:37 AM     672
www.ucla.edu     11:06:37 AM     110
www.whatever.com     11:06:38 AM     169
www.yahoo.com     11:11:37 AM     535
www.ucla.edu     11:11:37 AM     115
www.whatever.com     11:11:38 AM     168
www.yahoo.com     11:16:37 AM     588
www.ucla.edu     11:16:37 AM     111
www.whatever.com     11:16:38 AM     170

What I want is a chart that will show the response times in the vertical axis, and the time on the horizontal axis. I want each site to have its own colored line in the graph.

How do I do that?
1 Solution
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

with data like this a XY Scatter chart may be best, since all items have different (or slightly different) time stamps.

You can create a pivot table to organize the data by web site, putting the site in the columns and the time stamp in the rows, the response time in the values, using sum.

Then create a blank XY Scatter chart with no data (since Pivot charts don't allow XY Scatter). Add the data series individually, using the row labels as the X values and the column data as the Y values.

You can set up dynamic range names both for the pivot table source as well as for the chart series, so that the chart will automatically grow with the data.

After new values are added in columns A to C, click the pivot table and select "Refresh" on the Options ribbon. The chart will then update automatically.

see attached.

cheers, teylyn
d0ughb0yAuthor Commented:
The sample is exactly what I had envisioned. I have to look up how you did the whole dynamic range thing - I'm not really familiar with the INDEX() function, but yeah - this does what I wanted. Thanks!

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now