Warning: Keywords and tricky phrases may not be correctly being used here. I'm not extremely knowlegable about the terms used with Microsoft Excel so I'm using my best guess.

I'm failing to find a simple means of creating a Scatter or Line chart in Excel given 3 columns: NumberMatches, DateOfMatch, and IP_AddressMatchedTo. The goal is a Y axis of NumberOfMatches and X-Axis of Time mapped to DateOfMatch and each IP address has its own line to represent event changes over time. The problem experienced is that I'm failing to find means of rapidly grouping each IP address to its own set of data.

Is there any way to logically group sets of data to a particular line in the chart so that the cumbersome task of grouping them is avoided?

Here is a sample Excel file I'm working with.


Generally, the grouping your describing is assigning the data to a "series" which shares attributes on an Excel Chart. A quick way to do this is to use Excel's built-in Table functionality.

  1. Convert your data to a Table Insert > Table
  2. Use your Table to feed a Pivot Table Insert > Pivot Table
  3. Set up your Pivot Table with:
    • Row Labels = Date
    • Column Labels = IP Address
    • Values = sum of Matches
  4. Create a blank Scatter/XY Chart.
  5. Add data, using the manual dialog box (it keeps Excel from getting too smart and messing things up). Each series will be named as the IP Address, X values will be your dates, and Y values will be the sum of Matches.
  6. Format to taste.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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