How to Filter Excel Data into Separate Chart Series
Question details
The user needs to create a line chart comparing two distinct categories from a single dataset where dates, roles, and values are stored in a mixed format.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a comparative multi-series line chart from a single source table containing multiple distinct groups.
- Observed behavior
- The raw data needs to be restructured and filtered into separate chart-ready series (columns) for each category while preserving a shared date axis.
Ensure your source data is organized into clear, unmerged columns (e.g., Date, Role, Distance) and that there are no blank rows interrupting your dataset.
Combine HSTACK and FILTER Formulas
Quickly generate separate columns for each category using a combined dynamic array formula in Microsoft 365 or supported spreadsheet software.
A chart works best when it reads a shared date column alongside separate columns for each data series. By combining the HSTACK, CHOOSECOLS, and FILTER functions, you can extract and align the data into chart-ready columns in one step.
Locate the exact range of your raw data. For example, assume Dates are in A2:A15, Roles are in B2:B15, and Distances are in C2:C15.
In a new blank cell, type the following formula: =HSTACK(CHOOSECOLS(FILTER(A2:C15,B2:B15="Teacher"),1,3),FILTER(C2:C15,B2:B15="Student")) and press Enter. This will spill the data into a Date column, a Teacher distance column, and a Student distance column.
Highlight the newly generated dynamic array data, navigate to the Insert tab on the ribbon, click on the Line Chart icon, and select a 2D Line Chart to visualize the comparison.
Use Separate FILTER Formulas with a Shared Date Axis
A cleaner, step-by-step approach that creates a common date column and filters the values for each category separately.
Create Multi-Series Line Charts Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas like FILTER and HSTACK, allowing you to seamlessly restructure raw data and build complex comparison line charts in seconds.
- 1. Open Dataset: Launch WPS Spreadsheet and open the document containing your raw source data.
- 2. Apply Formulas: Select a blank area and apply the FILTER formula to extract the teacher and student data into distinct columns.
- 3. Insert Chart: Highlight the newly structured data range, go to the Insert tab, select Chart, and choose a Line Chart.
Frequently Asked Questions
Can I use Pivot Charts instead of formulas to split categories?
Yes, inserting a PivotChart is an excellent alternative. Select your source data, go to Insert > PivotChart, place 'Date' in the Axis (Categories) area, 'Role' in the Legend (Series) area, and 'Distance' in the Values area.
Why is my FILTER formula returning a #CALC! error?
The #CALC! error usually occurs if the FILTER function finds no matching records (for example, if there is no 'Teacher' role found in the array). You can add an 'if_empty' argument like =FILTER(C2:C15,B2:B15="Teacher", "No Data") to handle it gracefully.
Will the chart update automatically if new data is added?
If you convert your raw source data into an Official Excel Table (by pressing Ctrl+T) and use dynamic array formulas referencing the table columns, the extracted series and the resulting chart will update automatically as new rows are added.
