Excel三维数据折线图制作：无需转置，保留重复行组实现目标图表

Got it, I totally get wanting to keep your original row-based data structure (duplicate X-axis values and legend groups included). Here’s a step-by-step, no-transpose method to build that right-side target chart directly from your left-side raw data:

Step 1: Confirm Your Raw Data Structure #

First, make sure your data is organized in columns where each row represents a single data point:

• Column 1: X-axis values (can have duplicates)
• Column 2: Legend group labels (can repeat across rows)
• Column 3: Y-axis values (the corresponding value for each X-group pair)

Leave the rows exactly as they are—no sorting or rearranging needed.

Step 2: Insert an XY Scatter Chart (Not a Regular Line Chart) #

Regular Line Charts rely on unique column-based X labels, which is why they don’t work with your row structure. Instead:

• Select all your raw data (including headers)
• Go to the Insert tab > Under the Charts section, click Scatter > Choose Scatter with Straight Lines and Markers (this is the critical pick—it lets you map arbitrary X/Y pairs per group)

Step 3: Manually Define Your Data Series #

Excel will likely auto-generate messy series based on columns, so we’ll fix this:

• Right-click the empty chart > Select Select Data
• In the Select Data Source window, click Add to create a separate series for each unique legend group:
  1. Series Name: Click the cell with your group’s label (e.g., "Group A")
  2. X Values: Use the data picker to select only the X-axis values that belong to this group (hold Ctrl to select non-contiguous rows if your group is spread out)
  3. Y Values: Repeat the same selection for the corresponding Y-axis values for this group
• Keep adding series for every unique group, then delete any auto-generated series that don’t match your legend groups

Step 4: Format to Match Your Target Chart #

Tweak the chart to look like your desired output:

• X-Axis: Right-click the X-axis > Format Axis > Set the Axis Type to match your data (use Value axis for numbers, Text axis for categories)
• Series Styles: For each group’s series, right-click > Format Data Series to adjust line color, marker shape/size, and transparency to match your target
• Legend: Drag it to the right side, or use Format Legend to adjust font and spacing

Step 5: Double-Check the Plot #

Once all series are set up, your chart should correctly plot every data point—even with duplicate X values and repeated legend groups in your raw data—without ever needing to transpose rows to columns.

Pro tip: If you have tons of legend groups, create a quick pivot table to list all unique groups first. This makes selecting X/Y values for each series way faster, but you still won’t touch the original row structure.

内容的提问来源于stack exchange，提问作者Ivo