Chart & Visualization Issues

How to Create Perpendicular Step Lines in an Excel Chart

Natalie Taylor Sep 28, 2026 868 views

Question details

The user wants to convert standard diagonal chart lines into perpendicular, step-shaped lines (a step chart) in Excel.

How to Create Perpendicular Step Lines in an Excel Chart

Product: Microsoft Excel
Device & OS: not provided
Scenario: Visualizing data changes that happen instantaneously rather than gradually, requiring square, step-shaped connections instead of standard diagonal lines.
Observed behavior: Standard Excel line charts automatically connect data points with diagonal lines, requiring data restructuring to force the chart to draw perpendicular steps.

Before you start

Ensure your source data is organized in a clear, tabular format without empty rows before importing it into Power Query for transformation.

Solution 1Recommended

Use Power Query to Restructure Data for Step Lines

Transform your data points into additional horizontal and vertical coordinates using Power Query to create a step chart without manual formulas.

By using Power Query (which utilizes the M language), you can automatically generate the duplicate data points required to form perpendicular angles between your values.

This method is highly repeatable and does not require complex worksheet formulas or VBA macros. It works exceptionally well in Excel 365.

Load Data to Power Query

Select your original data table, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Add Index Columns

In the Power Query Editor, go to the 'Add Column' tab. Add one Index Column starting from 0, and a second Index Column starting from 1. These will be used to offset and shift the data.

Merge the Query with Itself

Navigate to the 'Home' tab and select 'Merge Queries'. Merge the table with itself by matching the 'Index 1' column of the primary table to the 'Index 0' column of the secondary table.

Expand and Clean the Data

Click the expand icon on the newly merged column to extract the shifted Y values. You now have the intermediate coordinates required for the horizontal and vertical steps. Remove the index columns.

Load to Chart

Click 'Close & Load To...' and output the transformed data as a new table in your worksheet. Highlight this new table, go to the 'Insert' tab, and select an 'XY Scatter' chart formatted with 'Straight Lines'.

Use Power Query to Restructure Data for Step Lines

Power Query vs. Power Pivot: While Power Pivot uses DAX and is great for data modeling, this specific transformation relies purely on Power Query and its M language to reshape the data layout.

Free Spreadsheet Tool

Create Professional Step Charts with WPS Spreadsheet

You can easily create perpendicular step lines in WPS Spreadsheet by manually organizing your data points or using simple formulas. It provides a seamless, free environment for complex data visualizations.

1. Prepare Your Data: Open a new worksheet in WPS Spreadsheet and enter your original X and Y values in two adjacent columns.
2. Duplicate Data Points: Create a new section where you interleave your data: duplicate every X value twice, and duplicate every Y value twice, but offset the Y values down by one row.
3. Insert the Chart: Highlight the newly structured data block, navigate to the 'Insert' tab on the top ribbon, and click 'Scatter'. Select 'Scatter with Straight Lines'.
4. Format Your Step Chart: Double-click the chart lines to open the formatting pane on the right. Adjust the line color, thickness, and marker options to finalize your perpendicular step visualization.

100% free and lightweight alternative to Microsoft Excel.Fully compatible with Microsoft Excel (.xlsx) formats and advanced charts.Intuitive interface for creating, editing, and formatting step charts without complicated coding.

microsoft office alternative - wps office

Frequently Asked Questions

Why do standard Excel line charts use diagonal lines?

By default, Excel connects data points directly from one coordinate to the next using the shortest path, resulting in diagonal lines. Creating perpendicular steps requires inserting intermediate data points to force right angles.

Can I create a step chart without using Power Query?

Yes. You can manually copy and paste your data to create duplicates with a one-row offset, or use standard worksheet formulas (like INDEX and MATCH) to interleave the required horizontal and vertical coordinates.

What chart type is best for step lines?

An XY Scatter chart with Straight Lines is typically the best choice for step charts. Unlike standard Line charts, XY Scatter charts handle unevenly spaced X-axis data accurately.

Excel & Data Problems