Chart & Visualization Issues

How to Identify and Label Peaks and Valleys in Excel

Kushani Nimanthika Sep 28, 2026 871 views

Question details

The user needs to identify and label local maximums (peaks) and minimums (valleys) in an Excel dataset, especially when charting data or handling repeated values that form plateaus.

How to Identify and Label Peaks and Valleys in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Visualizing and analyzing data trends in Excel where highlighting the highs and lows is required for technical analysis or reporting.
Observed behavior: Excel does not automatically label all peaks and valleys on a chart, and standard filtering methods remove valid repeating points during oscillation.

Before you start

Ensure your dataset is sorted chronologically or sequentially, and consider backing up your data before applying complex array formulas or macros.

Solution 1Recommended

Use Helper Columns and IF Formulas

The most straightforward method to identify peaks and valleys is by creating helper columns alongside your data to evaluate each point against its immediate neighbors.

By comparing a data point to the value above and below it, you can accurately pinpoint where a trend reverses. This method works perfectly for datasets with continuous fluctuation.

Set up the Peak Formula

In a new column next to your data (assuming your data is in column B, starting at B2), enter the formula: =IF(AND(B2>B1,B2>B3),"Peak","")

Set up the Valley Formula

In an adjacent column, enter the formula to identify valleys: =IF(AND(B2<B1,B2<B3),"Valley","")

Apply to the Entire Column

Select both formula cells and drag the fill handle down to apply the logic to your entire dataset.

Add Labels to the Chart

Select your line chart, right-click the data series, and click 'Add Data Labels'. Then, format the data labels to pull values from your new helper columns using the 'Value From Cells' option.

Handling Empty Chart Points: If you plan to plot the helper columns as a new chart series rather than using them for labels, replace the empty string "" in your formula with NA() to prevent Excel from plotting empty cells as zeros.

Solution 2

Handle Plateaus with Advanced Logic

When your dataset contains flat spots where values repeat before changing direction, standard IF formulas need adjustment to look back at previous non-equal values.

Efficient Data Analysis

Identify Chart Peaks and Valleys in WPS Spreadsheet

WPS Spreadsheet provides robust formula capabilities and flexible chart settings, allowing you to easily set up helper columns and dynamic data labels for tracking trends.

1. Open Your Data: Launch WPS Spreadsheet and open your dataset containing the values you want to analyze.
2. Apply Formulas: Create helper columns using standard IF and AND logic to flag the local maximums and minimums.
3. Create a Chart: Highlight your original data and insert a Line Chart from the 'Insert' tab on the ribbon.
4. Add Dynamic Labels: Right-click the chart line, choose 'Add Data Labels', and configure them to display values directly from your helper columns.

100% compatible with Microsoft Excel's IF and AND formulasSupports dynamic chart data labels directly from cell rangesProcesses large datasets quickly and efficiently without laggingClean, intuitive interface for advanced data visualization

microsoft office alternative - wps office

Frequently Asked Questions

How do I ignore flat spots or plateaus when labeling peaks?

Simple mathematical formulas struggle with repeating values (e.g., 3, 3, 3). You will need to use a more advanced array formula that compares the current value to the last non-matching value, or write a custom VBA macro to track the trend direction across duplicate points.

Why does my line chart drop to zero when using the helper formula?

If you plot the helper column as a separate line series on the chart, empty strings ("") are often treated as zeros. To prevent the line from dipping, change the empty string in your formula to NA(), which forces the chart to safely ignore the blank points.

Can I use Power Query to find peaks and valleys in massive datasets?

Yes. For very large datasets with tens of thousands of rows, loading your data into Power Query is much more efficient. You can add an Index column and merge the query with itself offset by one row to compare previous and next values without slowing down your workbook.

Excel & Data Problems