How to Create Separate Excel Line Series from a Location Column
Question details
The user needs to plot separate line series for different locations from a single dataset containing location, date, time, and measurement values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a multi-line chart where each line represents a unique location, based on a single flat data table.
- Observed behavior
- Currently, the charting tool combines all measurements into a single continuous line instead of breaking them down by location.
Ensure your data is organized in a flat table format with distinct columns for Location, Date/Time, and the measurement value (e.g., Conductivity), without blank rows interrupting the dataset.
Use a PivotChart to Auto-Separate Series by Location
The most efficient way to dynamically split categories into separate line series without manually rearranging your data.
A PivotChart automatically groups and aggregates data. By placing your location field into the series/legend area, the chart will immediately generate distinct lines for each location.
Select any cell inside your data table, navigate to the Insert tab on the ribbon, and click PivotChart. Choose to place it on a New Worksheet or Existing Worksheet.
In the PivotChart Fields pane on the right side of the screen, drag your 'Date' and 'Time' columns into the Axis (Categories) area.
Drag the column containing your numerical data (e.g., 'Conductivity') into the Values area.
Drag the 'Location' field into the Legend (Series) area. The chart will instantly update to show a separate colored line for each unique location.
Reshape Data with Helper Columns for Standard Charts
Ideal if you prefer not to use PivotTables and want a standard 2D Line Chart with full manual control over every series.
Create Multi-Series Line Charts in WPS Office
WPS Spreadsheet provides intuitive charting tools and robust PivotTable features that easily separate single-column categories into distinct visual series, ensuring your data is presented clearly.
- 1. Open Your Data File: Launch WPS Spreadsheet and open the file containing your flat data table.
- 2. Insert a PivotTable: Select your data range, navigate to the Insert tab, and click on PivotTable to create a summary table.
- 3. Arrange Fields: In the PivotTable fields, drag 'Location' to the Columns area, your Date/Time fields to the Rows area, and your measurement data to Values.
- 4. Insert a Chart: Highlight the resulting pivot data, select the Insert tab, and click the Line Chart icon to generate distinct series lines instantly.
Frequently Asked Questions
Why does my line chart connect points from different locations?
Standard line charts read down a single value column chronologically. Unless you separate the locations into different columns or use a PivotChart's 'Legend (Series)' field, the chart groups all data points together as one continuous line, drawing zigzag lines between different location measurements.
Can I use formulas to split the location data dynamically?
Yes. If your spreadsheet version supports dynamic arrays, you can use the FILTER or UNIQUE functions to dynamically pull data for specific locations into new helper columns. Once structured, you can plot these new columns as individual chart series.
How do I format each location's line differently?
Once the series are separated, click on a specific line directly within the chart to select it. Right-click and choose 'Format Data Series'. This opens a side panel where you can independently change that line's color, width, dash type, and marker styles.
