How to Find Peak Value in Excel Graph

Ever stare at an Excel chart, tracing the line with your finger to find the single highest point? Pinpointing the peak value in a graph is a common task, whether you're identifying your best sales month, the day website traffic spiked, or the most effective marketing campaign. This guide will show you several practical ways to find and highlight the peak value in an Excel graph, ranging from a quick visual trick to a fully automated formula and a dynamic PivotChart.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

The Easiest Method: Using Data Labels

Sometimes, the simplest solution is the best one, especially if you just need a quick answer for a one-time analysis. This method involves simply adding labels to your data points and visually identifying the highest number. It's fast, requires no formulas, and is perfect for simple charts.

Here’s how to do it:

1. Create your line or bar chart as you normally would. Select your data, go to the Insert tab, and choose your preferred chart type.
2. Add Data Labels. Click on your chart to select it. Then, click the green plus sign (Chart Elements) that appears on the top-right corner and check the box for Data Labels.
3. Find the highest number. Excel will place a label over every data point on your chart. You can now quickly scan the labels to find the highest value.

When to use this method: This is ideal for straightforward charts with a manageable number of data points. If your chart is dense with hundreds of points or if you need to find the peak value frequently in a recurring report, you'll want a more automated approach.

The Formula Method: Dynamically Highlighting the Peak with MAX() and IF()

For a more robust and professional-looking solution, you can use a combination of Excel formulas to automatically find the peak and highlight it on your chart. This method creates a separate data series that only contains the single peak value, which we can then plot and format differently on the graph.

Imagine you have monthly sales data like this:

Here are the steps to automatically identify and plot that $75,000 peak value.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Set Up a Helper Column for the Peak Value

First, we need to create a new column in our spreadsheet that will isolate the peak value. Let's call this column "Peak." We'll use a combination of the IF() and MAX() functions.

Assume your months are in column A (from A2 to A13) and your sales are in column B (from B2 to B13). In cell C2, enter the following formula:

=IF(B2=MAX($B$2:$B$13), B2, NA())

Let’s break that down:

• MAX($B$2:$B$13): This finds the absolute highest value in your sales range. We use dollar signs ($) to lock the range, so it doesn't change when we drag the formula down.
• IF(B2=...): This checks if the value in the current row (B2) is equal to the maximum value in the entire range.
• , B2: If the condition is true (meaning this row contains the peak value), the formula returns the value from B2.
• , NA()): If the condition is false, the formula returns #N/A. This is the key trick. Using NA() tells Excel to not plot anything for this point on the chart. If we used 0 or an empty string (""), Excel would plot a zero value, messing up our chart’s scale and appearance.

Drag this formula down from C2 to C13. You’ll see a column filled mostly with #N/A errors, with the single peak value appearing in the correct row.

Step 2: Add the Peak Data Series to Your Chart

Now, we'll add this new "Peak" column as a second data series to our graph.

1. Right-click on your existing chart and choose Select Data.
2. In the "Legend Entries (Series)" box on the left, click the Add button.
3. For the "Series name," you can type "Peak" or click on the header cell for your new column (C1).
4. For the "Series values," select the range containing your IF() formula results (C2:C13 in our example).
5. Click OK twice to close the windows.

You’ll now see a single dot on your chart, right on top of the peak of your line or bar graph. Because all the other values were #N/A, Excel only plots this one point.

Step 3: Format the Peak Marker for Emphasis

That single point is great, but we can make it stand out more.

• Right-click on the new peak data point on your chart and select Format Data Series.
• In the formatting pane that appears, you can change its color, increase the marker size, add a different shape (like a star or triangle), or even add a colored border to make it pop.
• You can also right-click that same point and select Add Data Label to display the peak value directly above the marker.

The best part? This chart is now dynamic. If you change your sales figures and a new peak emerges, the chart will automatically update to highlight the new highest point without any extra work.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Finding Context: What Is the Peak Using INDEX and MATCH

Okay, so we've found the peak value, but what about the corresponding label? Which month had the highest sales? You can pull this information automatically into a cell using the powerful combination of INDEX() and MATCH().

The logic is simple: First, we find the maximum value. Then, we find which row that value is in. Finally, we pull the corresponding month from that same row.

• Find the highest value with MAX() like before. Let’s put this in cell E2:

=MAX(B2:B13)

• Find the label associated with that value. In cell F2, enter this formula:

=INDEX(A2:A13, MATCH(E2, B2:B13, 0))

Here is what the formula is doing:

• MATCH(E2, B2:B13, 0): This looks for the value in cell E2 (our max sales number) within the sales range (B2:B13) and returns its relative position. The 0 ensures an exact match. If the peak is in the 5th row of the range, MATCH will return 5.
• INDEX(A2:A13, ...): This function then takes the result from MATCH (which was 5) and returns the 5th item from our Month column (A2:A13).

Now you have both the peak value and its corresponding month isolated in cells you can display prominently next to your chart for a mini-summary.

The Interactive Approach: Finding Peaks with a PivotChart

If you're dealing with a large dataset that you need to filter—for example, finding the peak sales day for a specific product or region—a PivotChart is your best friend. A PivotChart allows you to quickly summarize, filter, and rearrange your data to find peaks within different segments.

Step 1: Create a PivotChart

1. Select your entire dataset, including headers.
2. Go to the Insert tab and click PivotChart.
3. Excel will ask where to place the PivotTable and Chart, a new worksheet is usually best. Click OK.

Step 2: Arrange Your Fields

The PivotTable Fields pane will appear. Drag and drop your fields into the boxes:

• Drag your date field (e.g., "Month") into the Axis (Categories) box.
• Drag the value you want to measure (e.g., "Sales") into the Values box. It will likely default to Sum of Sales, which is what we want.

You now have a basic chart summarizing your sales by month.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Filter for the Top Value

Here’s where it gets interesting. Instead of showing all months, we can tell the PivotChart to only show the one with the highest value.

1. Click on the filter dropdown arrow next to "Row Labels" (or whatever your axis is called) in the PivotTable.
2. Hover over Value Filters and then select Top 10....
3. In the dialog box that appears, change "Top 10" to "Top 1".
4. Make sure it is based on "Sum of Sales" (or your appropriate value).
5. Click OK.

Your PivotChart and PivotTable will now instantly update to show only the single month with the highest sales. To see all the data again, just go back and clear the filter.

For even more power, you can add a Slicer from the Analyze tab to filter your entire PivotChart by another category, like "Product Category" or "Region," letting you find the peak performance for any slice of your business with a single click.

Final Thoughts

Finding the peak value in an Excel graph can be as simple as adding data labels for a quick look or as sophisticated as building a dynamic, formula-driven chart that highlights top performers automatically. Using MAX, IF, helper columns, or diving into PivotCharts gives you a range of tools to surface your most important data points with clarity.

While these Excel techniques are incredibly useful, they often rely on manual steps, helper columns, and a bit of formula wrestling to get right. We built Graphed to streamline this entire process. Instead of writing formulas, you can simply connect your data and ask in plain English, "Show me my sales last year and highlight the best month." A chart is instantly created for you, highlighting the peak and ready to share—all without touching a single spreadsheet cell. It turns hours of data wrangling into a 30-second task.