Chart & Visualization Issues

How to Add 9-Period and 20-Period Moving Averages to an Excel Chart

Rana Garcia Oct 9, 2026 869 views

Question details

The user wants to create an equity chart to display cumulative profit and loss by trade number, and overlay 9-period and 20-period simple moving averages on the same chart.

How to Add 9-Period and 20-Period Moving Averages to an Excel Chart

Product: Excel
Device & OS: not provided
Scenario: Visualizing financial trading data to track the equity curve alongside short-term and long-term moving average trendlines.
Observed behavior: Needs to correctly plot trade numbers on the horizontal axis, cumulative PnL on the vertical axis, and calculate the two moving average data series.

Before you start

Ensure your trading data is sorted chronologically by trade number, and verify that you have empty columns adjacent to your cumulative profit and loss data to store the moving average calculations.

Solution 1Recommended

Calculate and Add Moving Average Data Series Manually

Use the AVERAGE function to manually calculate the moving averages in helper columns, then add them as new line series to your equity chart for maximum control over the data.

This method involves calculating the averages directly in the spreadsheet using formulas. It is highly recommended because it gives you precise control over the data ranges and allows you to easily extract moving average values for other financial formulas.

Create calculated columns

Next to your cumulative Profit and Loss column (e.g., Column J), create two new column headers: '9-Period MA' and '20-Period MA'.

Enter the 9-period average formula

In the '9-Period MA' column, go to the 9th row of your data. Enter the formula =AVERAGE(J2:J10) (assuming data starts at row 2) and press Enter. Drag the fill handle down to apply this formula to the rest of the column.

Enter the 20-period average formula

In the '20-Period MA' column, go to the 20th row of your data. Enter the formula =AVERAGE(J2:J21) and press Enter. Drag the fill handle down to fill the column.

Insert the base equity chart

Select your Trade Number column and your Cumulative PnL column. Go to the 'Insert' tab on the ribbon and click on the 'Line Chart' icon to insert a basic 2D Line chart.

Add the moving averages to the chart

Right-click the newly created chart and choose 'Select Data'. Click 'Add' under Legend Entries (Series). For the Series Name, select the '9-Period MA' header, and for Series Values, select the calculated column data. Repeat this process to add the '20-Period MA' data.

Calculate and Add Moving Average Data Series Manually

Chart Updated: Your equity chart will now visually track your cumulative profit and loss alongside both short-term and long-term trend indicators.

Solution 2

Use the Built-in Chart Trendline Feature

Quickly overlay moving averages on an existing line chart using the built-in Trendline element without writing formulas or adding new columns.

Visualize Financial Data with WPS

Easily Add Moving Averages to Charts in WPS Spreadsheet

WPS Spreadsheet provides intuitive chart creation tools and robust financial formulas, making it simple to track equity curves and calculate moving averages for your trading data without lag.

1. Open Data: Open your trading dataset in WPS Spreadsheet.
2. Calculate Averages: Use the =AVERAGE() formula in adjacent columns to calculate the 9-period and 20-period data.
3. Insert Chart: Highlight your data, navigate to the 'Insert' tab, and select 'Line Chart'.
4. Refine Chart: Right-click the chart area to 'Select Data' and adjust your moving average series and horizontal axis labels as needed.

Fully compatible with Microsoft Excel formats (.xlsx and .xls)Built-in advanced charting elements for quick financial visualizationsLightweight architecture handles large trading datasets seamlesslyFree to use with a familiar, easy-to-navigate tabbed interface

Frequently Asked Questions

Why does my moving average line start later than my equity curve?

A simple moving average requires a specific number of previous data points to calculate properly. For a 20-period moving average, the first mathematical value can only be plotted on the 20th data point, leaving the beginning of the chart line blank by design.

Can I use exponential moving averages (EMA) instead of simple moving averages (SMA)?

Yes, but standard built-in trendlines in spreadsheet software generally only calculate simple moving averages. To plot an EMA, you will need to calculate the EMA mathematically in a new column using the EMA formula, and then add that column to your chart as a new line series.

How do I change the color of the moving average lines on my chart?

Right-click the moving average line on your chart and select 'Format Data Series' or 'Format Trendline'. From the side panel, navigate to the 'Fill & Line' (paint bucket icon) section to change the color, line thickness, and dash type of your moving average.

Excel & Data Problems