Highlight Max and Min data point in your charts

Hello People, I am sure most of us have the need to highlight special points in our charts.. lets say the maximum and minimum values..

Here is how you do it

Step 1. Assume the following average stock price data for 12 months

You can generate random stock prices by =RANDBETWEEN(1000,2000) function

Max Min 2

 

Step 2. Calculate max & min points for your data

Max Min 3

Please note that the data is changing because we have used the =RANDBETWEEN function, which changes the random values every time spreadsheet is refreshed or calculated

 

Step 3. Create Dummy Data for the Chart

Create 2 dummy series (using IF) for

  • Plotting only the maximum value
  • Plotting only the minimum value

Max Min 4

Quick Tip : Use Ctrl + D to drag down the copied formula, that’s much quicker than the mouse dragging or the conventional Ctrl + C shortcut for copying

Step 4. Select the data (including dummies) and plot a line chart with markers

Max Min 5

 

 Step 5. Dress up your Chart

Do some formatting to the chart – Remove the outlines, add labels if you want, make the stock price line lighter .. etc. Now every time the sheet refreshes (use the F9 key) --> data changes due to =RANDBETWEEN function --> max and min points are captured by your dummy --> which get updated in your chart 😀

Download the Finished File

 

Additional Stuff

 

 

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI