1. High Low Chart In Excel Formula
2. How To Create High Low Chart In Excel
3. Create High Low Close Chart In Excel
4. High Low Chart In Excel Function

Rearrange columns like this: high, low and close; Select columns High, Low and close; Go to tab 'Insert' on the ribbon; Click 'Other Charts' and then 'High-Low-Close' A chart is inserted on your active sheet; Right click on chart and click on 'Select Data.' Click Edit button below Horizontal (Category) Axis Labels; Select your date range. High-low lines Available in 2-D line charts and displayed by default in stock charts, high-low lines extend from the highest value to the lowest value in each category. Up-down bars Useful in line charts with multiple data series, up-down bars indicate the difference between data points in the first data series and the last data series. When to use a line chart #1 Use line charts when you want to show/focus on data trends (uptrend, downtrend, short term trend, sideways trend, long term) especially long term trends (i.e. Changes over several months or years) between the values of the data series: #2 Use line charts when you have too many data points to plot and the use of column or bar chart clutters the chart. Open the Excel file with the chart you want to adjust. Right-click the X-axis in the chart you want to change. That will allow you to edit the X-axis specifically. Then click on Select Data.

Last weekend, I helped someone who needed a chart for a fantasy football league, to show the highest and lowest win/loss scores for each week. To help explain what it should look like, they posted a picture of a hand drawn chart, similar to the picture below.

High Low Chart In Excel Formula

By the way, I created the sketch in Excel, by using the Marker option, in the Artistic Effects. Who knew that Excel was so artistically talented?

Set Up the Data

The win/loss data was set up with a separate column for each week, and rows for the win and loss highs and lows.

To create the type of chart that’s in the sketch, we’ll need to build a clustered stacked column chart. Blank rows and columns need to be added, and the numbers have to be staggered, with the Win and Loss numbers in separate columns.

The difference between the high and low scores needs to be calculated too, so the High amount is correctly shown. I also added a “W” or “L” heading to each column of scores, so the chart will be easier to understand.

Create the Chart

The yellow cells, in the screen shot above, are used as the source for the chart.

On the Ribbon’s Insert tab, click Column Chart, then click the Stacked Column option

After the chart is created, change the formatting so the gap is smaller, and the High and Low series have the colours that you want.

In the screen shot below, you can see the chart that I created.

If you want the actual High scores to appear, copy the Win High label and data (row 7), and Lose High label and data (row 11), and paste them into the chart. Then, change the 2 new series to Line Charts.

Then, add data labels to the Line Charts, to show the High scores, and add data labels at the Inside End for the Low scores.

Clustered Stacked Chart Utility

If you don't have the time or patience to arrange your data and create your own Clustered Stacked Column chart, Jon Peltier has an Excel Chart Utility. that will make the job easier for you.

You can see how it works in this video, or watch on YouTube: Create a Cluster Stack Chart With Excel Chart Utility

How To Create High Low Chart In Excel

Download the Sample File

Create High Low Close Chart In Excel

To see how the data is set up, and view the completed chart, you can download the sample file from my Contextures website. On the Sample Excel Files page, go to the Charts section, and look for CH0010 - Show High and Low in Clustered Stacked Chart. The zipped file is in xlsx format, and does not contain macros.

High Low Chart In Excel Function

_________________________