How to Show the Max Value in an Excel Graph

PDF download Download Article
PDF download Download Article

This wikiHow will teach you how to show the max value in an Excel graph with a formula. First, you'll need to create a line chart with markers with your data, then you can add a formula to find the maximum of your data set, then you can apply that max to your chart in a new color.

Steps

PDF download Download Article
  1. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/8\/83\/Show-the-Max-Value-in-an-Excel-Graph-Step-1.jpg\/v4-460px-Show-the-Max-Value-in-an-Excel-Graph-Step-1.jpg","bigUrl":"\/images\/thumb\/8\/83\/Show-the-Max-Value-in-an-Excel-Graph-Step-1.jpg\/aid12528733-v4-728px-Show-the-Max-Value-in-an-Excel-Graph-Step-1.jpg","smallWidth":460,"smallHeight":344,"bigWidth":728,"bigHeight":545,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    If you're in Excel, you can go to File > Open or you can right-click the file in your file browser.
  2. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/2\/28\/Show-the-Max-Value-in-an-Excel-Graph-Step-2.jpg\/v4-460px-Show-the-Max-Value-in-an-Excel-Graph-Step-2.jpg","bigUrl":"\/images\/thumb\/2\/28\/Show-the-Max-Value-in-an-Excel-Graph-Step-2.jpg\/aid12528733-v4-728px-Show-the-Max-Value-in-an-Excel-Graph-Step-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    Create a line graph with your data . You'll need to create a line graph for this method to work. To do this, select your data, then go to Insert > Line Graph icon > Line with Markers . It's the line graph with dots.
    Advertisement
  3. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/5\/5c\/Show-the-Max-Value-in-an-Excel-Graph-Step-3.jpg\/v4-460px-Show-the-Max-Value-in-an-Excel-Graph-Step-3.jpg","bigUrl":"\/images\/thumb\/5\/5c\/Show-the-Max-Value-in-an-Excel-Graph-Step-3.jpg\/aid12528733-v4-728px-Show-the-Max-Value-in-an-Excel-Graph-Step-3.jpg","smallWidth":460,"smallHeight":344,"bigWidth":728,"bigHeight":545,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    " Since this information will be included in the line graph, keep it close to your original data set so you can easily add it to the data range represented by the graph.
  4. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/3\/3f\/Show-the-Max-Value-in-an-Excel-Graph-Step-4.jpg\/v4-460px-Show-the-Max-Value-in-an-Excel-Graph-Step-4.jpg","bigUrl":"\/images\/thumb\/3\/3f\/Show-the-Max-Value-in-an-Excel-Graph-Step-4.jpg\/aid12528733-v4-728px-Show-the-Max-Value-in-an-Excel-Graph-Step-4.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    =IF(B5=MAX($B$5:$B$16),B5,””) . In this example, B5 represents the first cell in your range, while B16 represents the last. Replace these cell addresses with the actual cells in your data. This formula will make the highest value in your dataset repeat in this new column, but none of the other values will appear.
    • Fill the rest of the column with that formula and you'll see the highest value in your data set repeats in that column.
  5. {"smallUrl":"https:\/\/www.wikihow.com\/images\/thumb\/7\/7e\/Show-the-Max-Value-in-an-Excel-Graph-Step-5-Version-2.jpg\/v4-460px-Show-the-Max-Value-in-an-Excel-Graph-Step-5-Version-2.jpg","bigUrl":"\/images\/thumb\/7\/7e\/Show-the-Max-Value-in-an-Excel-Graph-Step-5-Version-2.jpg\/aid12528733-v4-728px-Show-the-Max-Value-in-an-Excel-Graph-Step-5-Version-2.jpg","smallWidth":460,"smallHeight":344,"bigWidth":728,"bigHeight":545,"licensing":"<div class=\"mw-parser-output\"><p>License: <a target=\"_blank\" rel=\"nofollow noreferrer noopener\" class=\"external text\" href=\"https:\/\/en.wikipedia.org\/wiki\/Fair_use\">Fair Use<\/a> (screenshot)<br>\n<\/p><\/div>"}
    Click your chart to select it, then drag the box highlighting the data it's representing to include the extra column. [1]
    6. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      4 Easy Ways to Add the Time and Date Automatically in Sheets
      How to
      Make a Google Spreadsheet: Beginners Guide
      How to
      Create an IF-THEN Formula in Excel: Basic Syntax, Multiple Conditions, & More
      How to
      Copy the Same Text to Multiple Cells in Excel: Desktop & Mobile
      The 3 Easiest Ways to Sort by Number in Google Sheets
      How to
      Add Custom Calculated Fields to Pivot Tables in Excel
      How to
      Use Google Sheets: The Ultimate Guide for Getting Started
      How to
      Add Rows to a Pivot Table
      How to
      Graph Multiple Lines in Excel
      3 Easy Ways to Get Rid of Dotted Lines in Microsoft Excel
      3 Simple Ways to Create a Shared Spreadsheet
      How to
      Add a Row to a Table in Excel
      How to
      Change the X Axis Scale in an Excel Chart
      How to
      Add Two Cells Containing the Sum of Other Formulas in Excel
      Advertisement

      References

      1. ↑ https://www.xelplus.com/highlight-max-min-values-in-an-excel-line-chart/

      About This Article

      Article Summary X

      1. Open your project in Excel.
      2. Create a line graph with your data.
      3. Create a new column next to your data set labeled "Max."
      4. Enter the following formula: =IF(B5=MAX($B$5:$B$16),B5,””)) .
      5. Add the "Max" column to your chart.

      Did this summary help you?
      Thanks to all authors for creating a page that has been read 57,908 times.

      Is this article up to date?

      Advertisement