Skip to main content

Create a step chart in Excel

Author: Siluvia Last Modified: 2024-11-05

A step chart is used to show data which changes at irregular intervals, and keep constant for a period until the next change comes. In fact, it is a Line chart but uses vertical and horizontal lines to connect the data points (The below screenshot makes a comparison between a normal Line chart and a Step chart). In Excel, there is no build-in function to help users directly create a Step chart. In this tutorial, we are providing a step-by-step guide to help you easily create a Step chart in Excel.

step chart

Create a step chart by creating helper data range
Create a step chart by changing the chart formula
Easily create a step chart with an amazing tool
Download the sample file
Video: Create step chart in Excel

Create a step chart by creating helper data range

Please do as follows step by step to create a step chart in Excel.

Firstly, prepare your data

Supposing you have a sales table as the below screenshot shown, you need to create a helper data range based on the original table as follows.

steps of creating a step chart by creating helper data range

1. Copy the whole table range (A1:B12) and then paste it to a new range.

steps of creating a step chart by creating helper data range

2. Delete the first date cell and the last sales cell from the new table range.

Tips: Right click the first date cell and the last sales cell, select Delete from the right-clicking menu. In the popping up Delete dialog box, click the OK button. Then the remaining cells are shifted up or left automatically.

steps of creating a step chart by creating helper data range

3. Copy the original table range (exclude headers), and then paste it below the new table range you created in step 2. See screenshot:

steps of creating a step chart by creating helper data range

Create a Step Chart based on the helper data range

1. Select the new table range (D1:E22), click Insert > Insert Line or Arear Chart > Line (Unser the 2-D Line section).

steps of creating a step chart by creating helper data range

Then the step chart is created immediately as the below screenshot shown.

steps of creating a step chart by creating helper data range

Notes:

  • Before applying this method, make sure the dates in the original Date column are in ascending order. If not, the step chart may not create correctly.
  • If there is a Year formatting column instead of Date in the original table, after following the above steps, you will get a chart as the below screenshot shown. You need to go ahead to configure until it turns up as a Step chart.
  • steps of creating a step chart by creating helper data range
1) Select the chart, click Design > Select Data.

steps of creating a step chart by creating helper data range

2) In the Select Data Source dialog box, select the Year series, click the Remove button. Go ahead to click the Edit button in the Horizontal (Category) Axis Labels section.

steps of creating a step chart by creating helper data range

3) In the Axis Labels dialog box, select the Year column without header in the helper data range, and then click the OK button.

steps of creating a step chart by creating helper data range

4) When it returns to the Select Data Source dialog box, click the OK button to save the changes.
5) Right click the horizontal axis and select Format Axis from the menu.

steps of creating a step chart by creating helper data range

6) In the Format Axis pane, check the Date axis under the Axis Options section. Then the Step chart is created.

steps of creating a step chart by creating helper data range

Create a step chart by changing the chart formula

If you don’t want to create a helper data, or the dates in the Date column is not in ascending order, you can still create a Step chart in Excel with the below formula.

1. Select the original table data you will create a step chart based on. Click Insert > Insert Line or Arear Chart > Line (Unser the 2-D Line section).

steps of creating a step chart by changing the chart formula

2. Select the series in the chart, you will see a formula is displayed in the Formula Bar. Pease replace it with the below formula.

=SERIES('Step chart (formula)'!$B$1,('Step chart (formula)'!$A$3:$A$12,'Step chart (formula)'!$A$2:$A$12),('Step chart (formula)'!$B$2:$B$11,'Step chart (formula)'!$B$2:$B$12),1)

The original chart and formula:

steps of creating a step chart by changing the chart formula

The Step chart and the final formula:

steps of creating a step chart by changing the chart formula

Notes:

1. In the formula, Step chart (formula) is the current worksheet name.
2. $B$1 is the header of the sales column;
3. $A$3:$A$12 is the date column range without the header and the first date cell;
4. $A$2:$A$12 is the date column range without the header;
5. $B$2:$B$11 is the sales column range without the header and the last data cell;
6. $B$2:$B$12 is the sales column range without the header.

steps of creating a step chart by changing the chart formula

You can change them based on your needs.

Easily create a step chart in Excel with several clicks only

The Step Chart utility of Kutools for Excel can help you easily create a step chart in Excel with several clicks only as the below demo shown.
Download and try it now! 30-day free trail

Download the sample file

sample of creating a step chart

Video: Create step chart in Excel

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.