Peltier Tech Excel Charts and Programming Blog

Microsoft Excel Stock Charts

Tuesday, September 10, 2019 by Jon Peltier 11 Comments

Tuesday, September 10, 2019 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Excel Stock Charts

Microsoft Excel offers a variety of charts you can use to plot stock prices over time. If all you have is a single value per time period, such as closing price, you can just use a simple line chart. But if you have additional values, such as high and low price, opening price, and volume, there are specialized stock charts for that.

Built-In Stock Charts

The Insert tab of Excel’s ribbon has a group called Charts. Mouse over the Waterfall icon, and the tooltip will tell you that stock charts are found here.

Ribbon > Insert tab > Charts > Waterfall icon

Click on the icon, and a gallery of chart types will appear.

Gallery of Chart Types, including Stock Charts

Select your data, then click one of the stock chart icons to create a stock chart. If you have not selected data that is appropriate for the icon you’ve clicked, you’ll get an alert which will help you get it right.

An alert appears if your data doesn't match the stock chart variation you've selected.

The four built-in types of Excel stock chart are shown below, with a sample of the data layout required for each.

High-Low-Close

The High-Low-Close (HLC) stock chart uses four columns of data. The first column is a category, usually a date but stock names can also be used. The next three columns in order are for high, low, and closing prices. The range of price for each category is indicated by a vertical line from low to high, and closing price is shown using a tickmark extending to the right of this line.

I haven’t made this stock chart variant in quite a long time, and when I did, I was surprised how much I had to squint to see the tickmarks for closing price. Back in Classic Excel (2003 and earlier), these ticks were easy to see because they were black, but in the chart above they are gray. They also seem smaller at 3 points, but in Excel 2003 they were also 3 points; I think in more recent versions of Excel, the vertical line partially obscures the tick marks.

I’ve made visibility improvements in the chart below. I’ve made the vertical line 2 pixels (1.5 points) thick, and I’ve made the tickmarks black; the first set of tickmarks are increased to 5 points, and the second set to 8 points. Anything to help my aging eyes.

High Low Close stock chart with increased visibility

Open-High-Low-Close

The Open-High-Low-Close (OHLC) chart uses five columns of data, in order: category, open, high, low, and close. The range of prices in each category is again indicated by a vertical line, while the range between open and close is given by a wider floating bar; if the price increases in the category (close is higher than open), the bar is filled with one color, while if the price decreases, the bar is filled with another. This type of chart is often called a candlestick chart.

Often people will use colors rather than black and white to indicate increasing and decreasing prices. In the first set of candlesticks below, green shows increasing nad red decreasing prices. This color scheme isn’t ideal: about 8% of men and 0.5% of women (of European descent) suffer from the most common color vision deficiency, an inability to distinguish red and green. Using the first two colors of the modern Excel palette, blue and orange, are often used instead, as in the second block below.

Volume-HLC and Volume-OHLC

The other two stock charts include a column for volume immediately after the first (category) column, and the charts include a column chart on the primary axis showing this volume, while the prices are moved to the secondary axis.

Here is the Volume-High-Low-Close (VHLC) stock chart:

Excel's Volume-High-Low-Close Stock Chart

Finally, the Volume-Open-High-Low-Close (VOHLC) chart:

Excel's Volume-Open-High-Low-Close Stock Chart

I don’t particularly care for the stock chart variations that include volume: they look cluttered and are difficult to read. If you make them as large as a full sheet of paper, and adjust the scales so the volume bars fill the bottom of the chart and the price data the top, I suppose they’re okay. But I won’t talk about them further.

Home-Made OHLC Candlestick Chart

You can hack together a candlestick chart using features of Excel’s line charts. Below is a line chart made with our simple data set. Note the order of the data columns. After the category (date), open is first and close is last, while high and low are between open and close.

It’s easy to add high-low lines to a line chart. Select the chart, or select a line chart series if the chart is mixed, and on the Chart Design tab of the ribbon, click the Add Chart Element dropdown and select Lines, then High-Low Lines. A line will connect the lowest value at each category to the highest.

It’s even easier to add up-down bars to a line chart, because you don’t need to go traipsing up to the ribbon. Click the plus icon that floats next to the chart, and check the Up-Down Bars box. The bars in the chart below have already been formatted with the color-vision-friendly blue and orange colors.

It’s an easy matter to add both high-low lines and up-down bars to a chart (below left), then hide the line chart lines (don’t delete them, just format them with no lines) to roll your own candlestick chart (below right).

In classic Excel, at this point, Excel would know that the chart was a stock chart. If you then made sufficient changes (like adding or removing series), Excel would know that it was no longer a stock chart. In modern Excel, stock charts are treated as something inviolable, so it’s not an easy job to combine one with another chart type. So if you need to combine your stock chart with a series of another chart type (say, an XY scatter series to show some kind of index), it’s good to know how to build a more flexible candlestick chart from a line chart.

OHLC Chart with Open/Close Tickmarks

Excel’s High-Low-Close chart uses a tickmark to indicate the closing price. There is a variation of the Open-High-Low-Close chart, which uses tickmarks rather than up-down bars for both open and close prices. While it’s not native to Excel, it’s not too difficult to build.

We’ll use line chart series for High and Low to exploit a line chart’s high-low lines. We’ll use horizontal error bars for the Open and Close tick marks; since line charts only accommodate vertical error bars, we’ll change the chart type of these series to XY scatter.

Start by inserting a line chart using your stock data.

Right click on a series in the chart and choose Change Series Chart Type to get the following dialog:

Change Open and Close from Line to Scatter. Excel wants to check Secondary Axis for both, which would probably be fine, but it will be simpler if you uncheck the box.

Here is the resulting mixed-series chart.

Add error bars to each of the XY series: select the series, click the plus icon beside the chart, and check the Error Bars box.

Hide the scatter chart markers so they don’t obscure the error bars.

Delete both sets of vertical error bars: select and delete. They might be hard to select with the mouse, so select the Y error bars from the chart element dropdown that appears when you right click on the chart.

Format the Open X error bars: Minus direction (so they point to the left), no cap, fixed value of 0.2 (adjust to suit), and a line thickness of 1.5 pt (2 pixels).

Format the Close X error bars: Plus direction (so they point to the right), no cap, fixed value of 0.2 (adjust to suit), and a line thickness of 1.5 pt (2 pixels).

Select one of the line series, go to Add Chart Element in the Chart Design tab of the ribbon, and choose Lines > High-Low Lines. Make the line thickness 1.5 pt (2 px).

Hidh the line chart lines (don’t delete them, just format to have no line) and ditch the legend, and you’ve got it.

Stock Chart with No Gaps for Weekends

A common question about stock charts is, “How can I remove the gap that appears on weekends, when there is no trading and therefore no price data. The dates don’t even appear in the table, why does Excel include them on the axis?”

Personally. I like the gap for weekend dates. It helps me to see the data more clearly, broken up by weeks instead of mashed all together. But my preference for nicely spaced data won’t prevent me from showing you how to mash them all together.

The reason the data-less dates appear is that Excel treats the date axis as a number line. Even though there is no data for some days, each day has a slot along the axis.

In a line chart (or column or area chart), the category axis can be either a text type or a date type. Normally Excel automatically guesses the type based on the data.

OHLC Candlestick

Here is Excel’s candlestick chart with an automatic axis type, and dates in the category column. Note the gap on Saturday and Sunday, August 23 and 24, 2019.

Let’s format the axis and select Date Axis. No change, still a gap over the weekend, so obviously Excel correctly selected this type based on the dates it detected in the first column of data.

Excel OHLC Candlestick with Date Axis: See the Gap For Weekends

Now let’s select Text Axis. Now the weekend dates do not appear on the axis and there is no gap.

Excel OHLC Candlestick with Text Axis: No Gap For Weekends

So there’s your answer, and it’s actually pretty easy: to remove the weekend gaps in the chart, change the axis type to Text Axis.

OHLC with Tickmarks

Since the OHLC chart with tickmarks is a combination of line chart and XY scatter chart, we need to work a little harder to eliminate weekend gaps. But it still can be done.

Here is our custom OHLC chart with Open and Close tickmarks as it appears with either automatic axis or date axis. The axis is defined by the line chart series, and since the XY data uses the same dates, these series are plotted along the same axis, with no issue.

OHLC Tickmark Chart with Automatic or Date Axis

Let’s see what happens when we change the axis type to Text Axis.

OHLC Tickmark Chart Broken by a Text Axis

Oh, that’s pretty ugly. All the data is pushed off to the left, the axis labels are all scrunched up. But there’s no need to panic. Let’s add markers to the Open and Close series to see if we can figure it out.

OHLC Tickmark Chart Still Broken, but Now We Can See Why

While the high-low lines of the line chart are squished against the left edge of the chart, the XY markers are pushed all the way to the right. Why is that? Well, in a line chart’s text axis, the categories are treated as if they are numbered 1, 2, 3, etc. So our line chart data covers categories 1 through 12. Our XY data is still using 18-August-2019 through 29-August-2019, which according to Excel’s date system are the numbers 43,695 through 43,706. So there are really almost 44,000 axis slots between the line chart data and the XY data.

So how do we fix this? We could plot the lines on the primary axis and the XY data on the secondary axis, then take heroic measures to keep them aligned whenever the data is extended.

But there’s an easier way. We’re going to remove the dates, in fact, we’re going to remove the X values altogether, from the XY series. You can edit the series formula, or use the Select Data dialog.

The series formula for the Open series looks something like this, where the arguments in parentheses are for the series name, X values, Y values, and series plot order:

=SERIES(OHLC!$B$1,OHLC!$A$2:$A$11,OHLC!$B$2:$B$11,1)

Select the series, and edit the formula in the Formula Bar just like any other formula, to remove the X value reference:

=SERIES(OHLC!$B$1,,OHLC!$B$2:$B$11,1)

Do this for both Open and Close series.

Or if you find it easier (especially since it’s hard to select series which are not easy to click on), you can right click on the chart and choose Select Data.

Select a series in the list and click Edit. In the small Edit Series dialog that pops up (below left), delete the contents of the X Values box (below right)

Do this for both Open and Close series. Now the XY points line back up with the line chart.

OHLC Tickmark Chart with Fixed Text Axis

So why did this work? When there are no X values specified for an XY scatter series, it defaults to using the counting numbers 1, 2, 3, etc., as its X values. Since the line chart treats the categories along a text axis as the numbers 1, 2, 3, etc., both sets of series are again plotted on the same basis, and they align nicely.

Include an Index in a Stock Chart

While the volume columns can really clutter up a stock chart, sometimes one or more line series can actually enhance your stock chart, depicting an index or some kind of analytical construction, like Bollinger bands.

Here is a dummy index to go with our stock data.

OHLC Candlestick

If you had the forethought to put all the data together nicely, the easiest way to add the data is to select the chart, then resize the highlighted data range to include it. If your data isn’t so nicely arranged, you can copy it and use Paste or Paste Special to add it to the chart.

Stock chart before including Index data:

Stock chart after including Index data:

Candlestick Chart Plus Index with Data Range

But that has messed up the chart, because Index is now the last line chart series, not Close. The up-down bars now span the difference between Open and Index.

When we add line and markers to the added series, we can see this more clearly.

Candlestick with Index Plus Distorded Candlesticks

The easiest fix is simply to format the series and assign it to the secondary axis (below left). Then you can delete the secondary vertical axis (below right).

Candlestick Chart with Index on Secondary Axis

This approach, a line chart series on the secondary axis, turns out to be the best way to add an index to a native Excel candlestick OHLC chart.

Initially, I’d thought it would be easy to change the series to XY, and I could keep it on the primary axis with the OHLC data, but when I right-clicked on the Index series and selected Change Series Chart Type, I go this nasty message:

No Combination Charts Using Stock Charts!

This happens to be one of the restrictions that Modern Excel places on stock charts. If I had instead built my own candlestick chart from a line chart, using up-down bars and high-low lines as I described earlier, I could have done this very easily.

It turns out that VBA doesn’t have all of these same restrictions, so I gave that a try, typing this into the Immediate Window:

ActiveChart.SeriesCollection(5).ChartType = xlXYScatterLines

And I got an XY series plotted on the secondary axis.

Candlestick with XY Series on Secondary Axis, Thanks to VBA

I couldn’t change the axis in the Excel interface (which actually showed it was on the primary axis, despite the presence of the secondary axis in the chart), so I tried VBA again:

ActiveChart.SeriesCollection(5).AxisGroup = xlPrimary

But I got a run-time error, and the chart didn’t change.

OHLC with Tickmarks

Let’s add the Index series to the OHLC Tickmark chart. Before (below left) and after (below right) the series is added. It’s probably more likely that the series will be added as an XY series. There is no change, since an XY series does not interact with up-down bars or high-low lines.

OHLC Tickmark Chart Before and After Addin Index Series (as XY Series)

All we need to do is format the lines and markers of this series, and we’re done.

OHLC Tickmark Chart with Visible Index Series

Sometimes, Excel might trip you up and add the Index series as a line series. When this happened to me, the high-low lines went away (below left). When I restored them (below right), it was obvious that they were affected by the Index line series.

Applying line and markers to the Index series made this clear.

OHLC Tickmarks with visible Index line chart

The best approach would be to change the Index from a line to an XY scatter. But I wanted to make sure.

I reassigned the Index series to the secondary axis (below left), then deleted the horizontal and vertical axes that appeared (below right).

This line on the secondary axis is more complicated than the XY series on the primary axis that I showed first.

Posted: Tuesday, September 10th, 2019 under Chart Types.
Tags: Stock Charts.
Comments: 11

Error Bar Gantt Chart

Thursday, May 30, 2019 by Jon Peltier 3 Comments

Thursday, May 30, 2019 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Floating Bars and Gantt Charts

Gantt charts are a special kind of floating bar chart used in scheduling and program management. A list of tasks or activities is plotted along the left hand axis, and dates along the bottom axis. Floating horizontal bars indicate when tasks begin and end, and which tasks are in progress at any given time.

In Floating Bars in Excel Charts I showed several ways to produce vertical or horizontal floating bars in Excel charts, including stacked bar and column charts, XY Scatter chart line segments formatted with thick lines, and error bars formatted with thick lines.

Floating Columns and Bars in Excel Charts

My article Custom Error Bars in Excel describes how to customize error bar values, and I’ve used the error bar-floating bar technique in many ways, such as the recent article that showcased Low-Medium-High charts.

In Gantt Charts in Microsoft Excel, I shared a couple ways to make Gantt charts, including the use of Conditional Formatting to shade cells in the worksheet (which doesn’t actually produce a chart), and the use of floating bar charts to produce the timeline bars.

This tutorial will show how to use custom error bars to create a Gantt chart in Microsoft Excel. It’s also a case study of features in Excel charts, and how to go about designing your own non-native chart in Excel. You’ll see me encounter a few false starts, not unusual as I zero in on a working protocol.

XY Scatter Error Bar Gantt Chart

My first approach with almost any non-native custom chart starts with an XY Scatter chart, and make adjustments from there. My plan is to use XY points as the endpoints of the timeline bars, applying negative horizontal error bars to the points, and adding data labels with task names to the right of the points.

Below is the minimal data needed for the chart I envisioned, in A1:D9. I need columns for Task (a label), End Date (the X values of my points), Height (the Y values of my points), and Duration (the lengths of the timeline bars). I have eight tasks, which I want vertically distributed evenly in the chart. This formula in cell C2, copied into C3:C9, does the trick:

=1-(ROW()-0.5-ROW(E$1))/COUNTA($A$2:$A$9)

I have included a small satellite data region in F1:G4, with two dates in column F (the desired endpoints of my horizontal axis), and the same dates in column G formatted as General. Cell G4 shows the difference between the axis endpoints divided by 3, which I’ll use as the tick spacing of the horizontal axis. You need to know the numeric values of any dates if you want to manually scale an axis, because XY charts don’t accept dates in the Format Axis task pane.

I selected the End Date and Height data (B1:C9) and inserted an XY Scatter chart (below left). I have already added the error bars to the XY Chart series. You can see that the dates are a mess, with the labels so close that they overlap. The vertical axis scale was selected to show tick marks and labels between the horizontal error bars.

The chart below right shows the following improvements (I’ll share the exact steps later, when I’ve developed a protocol I like):

Deleted vertical error bars
Added custom negative values only to horizontal error bars
Formatted the horizontal error bars as thick semitransparent lines
Added Task labels to XY Scatter points
Formatted the minimum, maximum, and major unit of the horizontal date axis, using the satellite data I calculated in G2:G4, above.

I haven’t finished cleaning up the chart, but you can easily see the problem. Using an XY chart, I can’t get a nice axis scale with a tick mark and label at the first of each month: my axis has 31-May instead of 1-June. In fact, I recently wrote a tutorial, Gantt Chart with Nice Date Axis, which presented an intricate adjustment to my original Gantt chart that avoids this issue.

Let’s try using a Line chart directly, with a nice date scale axis which inherently provides a nice axis scale.

Line Chart Error Bar Gantt Chart

I applied the same data as above to a Line chart. I did not need the extra “satellite” data, because I can enter dates into a Line chart’s Format Axis task pane.

I selected the End Date and Height data (B1:C9) and inserted a Line chart (below left). I have already added the error bars to the Line chart series. The dates only include the End Dates from the table, not the starting date of 1-Apr. As above, the vertical axis scale shows tick marks and labels between the horizontal error bars.

I’ve applied the desired minimum date to the chart below right. I’ve also highlighted why I can’t use this as my error bar Gantt chart: a Line chart series can only have vertical, not horizontal, error bars.

Let’s use a combination chart, with a Line chart for the date axis scale and an XY Scatter chart for the horizontal error bars.

Combination Error Bar Gantt Chart

Here is the data range for a combination chart. The XY data is in A1:D9, and the Line chart data is in F1:G3. I selected this Line chart data and inserted a Line chart, below left. The date axis spans the two dates in the data range.

I copied the End Date and Height data (B1:C9), selected the chart, and used Paste Special to add the data to the chart as a new series by columns, using the first row for series names and the first column for X values (below right).

The XY series only shows two points, but that’s okay, because it’s still a Line chart series, and uses the X values for the existing Line series, which only has two points. I converted the added series to an XY Chart type, then added and formatted the horizontal error bars (below left).

Oh yeah, then I moved the added series, now XY Chart type, back to the primary axis (below right). The XY Chart series is plotted at the intended dates, but again, it is reduced to two points.

So the problem is that even if the second series doesn’t use the same dates as the first Line chart series, it is limited to having no more points than the first series.

Combination Error Bar Gantt Chart ... Almost

So let’s see if we can pad the Line chart series with extra points.

Simple Combination Error Bar Gantt Chart That Works

You can safely use the following protocol to make a simple Gantt chart. I’ve included all the little steps that you need to follow.

Here is the adjusted data range for a combination chart. The XY data
(Task Name, End Date, Height, and Duration) is in A1:D9, and the Line chart data is in F1:G11. Notice the line chart data in column F now has ten dates, more than needed to plot the endpoints, and more than the number of XY points we will have. The dates between Start and End are evenly spaced.

Select the Line chart data in F1:G11 and insert a Line chart, below left. The date axis spans the two dates in the data range, and the points are uniformly spaced.

Copy the End Date and Height data (B1:C9), select the chart, and use Paste Special (go to the Home tab of Excel’s ribbon, click the Paste button dropdown, select the last item on the menu) to add the data to the chart as a new series by columns, using the first row for series names and the first column for X values (below right).

All of the XY points are there, but spaced according to the Line chart dates from the first series, since the new series is not yet converted to an XY Scatter type. Easy enough, right click on the added series, choose Change Series Chart Type from the dropdown, and select an XY Scatter type (below left).

In the same Change Series Chart Type dialog, or in the Format Series task pane, reassign the XY series to the primary axis (below right). Now the points follow the End Date data.

Select the added series, click the plus icon next to the chart, and check Error Bars (below left).

Select the vertical error bars, and press Delete. Then select the horizontal error bars, press Ctrl+1, and in the Format Error Bars task pane, under Error Amount, select Custom and click the Specify Value button. Enter 0 (zero) for Positive, and select the Duration range (D2:D9) for Negative (below right).

Format the error bar lines. Choose No Cap, then under the paint can, choose a line color and thickness (below left). I used the same orange color as the data points use, with 50% transparency, and with a thickness of 9 points.

Add data labels to the right of the XY markers. I used the Value from Cells option, selecting the Task names in A2:A9 (below right).

Format the Line and XY series to show no markers and no lines, select None for the vertical axis labels, and remove the gridlines, and the error bar Gantt chart is finished.

Simple Combination Error Bar Gantt Chart

Label Variation in Above Gantt Chart

You want the Task labels before the bars, not after them? You can use the same protocol, replacing Start dates for End dates, and switching the positions of the error bars and data labels.

The adjusted data range for a combination chart is shown below. The XY data (Task Name, Start Date, Height, and Duration) is in A1:D9, and the Line chart data is in F1:G11. Notice the line chart data now has ten dates, more than needed to plot the endpoints, and more than the number of XY points we will have. The dates between Start and End are evenly spaced.

Select the Line chart data in F1:G11 and insert a Line chart, below left. The date axis spans the two dates in the data range, and the points are uniformly spaced.

Copy the Start Date and Height data (B1:C9), select the chart, and use Paste Special to add the data to the chart as a new series by columns, using the first row for series names and the first column for X values (below right).

Right click on the added series, choose Change Series Chart Type from the dropdown, and select an XY Scatter type (below left). In the same dialog, or from the Format Series task pane, reassign the XY series to the primary axis (below right).

Select the added series, click the plus icon next to the chart, and check Error Bars (below left). Select the vertical error bars, and press Delete. Then select the horizontal error bars, press Ctrl+1, and in the Format Error Bars task pane, under Error Amount, select Custom then click the Specify Value button. Select the Duration range (D2:D9) for Positive, and enter 0 (zero) for Negative.

Choose No Cap for the error bars, then under the paint can, choose a line color and thickness (below left). I used the same orange color as the data points use, with 50% transparency, and with a line thickness of 9 points.

Add data labels to the left of the XY markers. I used the Value from Cells option, selecting the Task names in A2:A9 (below right).

A little clean-up, and your simple error bar Gantt chart is done.

Advanced Combination Error Bar Gantt Chart

Let’s extend out Gantt Chart methodology to show more of our project data. We’ll indicate percent complete for each task with a data label, we’ll split each horizontal bar into a completed portion in blue and an incomplete portion in orange, and we’ll use distinct milestone markers for completed and incomplete tasks.

Our advanced Gantt chart data range is much larger than either simple range above. Task names are in column A. The next three columns are Start Date, Duration, and End Date, related to each other by addition or subtraction. Percent Complete is in column E. The next two columns are the Done and Not Done portions of Duration, defined by these formulas:

=E2*C2 =(1-E2)*C2

Then come the Complete and Incomplete End Dates, defined by

=IF(E2=1,D2,NA()) =IF(E2<1,D2,NA())

Finally we have Height in column J, defined as before:

=1-(ROW()-0.5-ROW(J$1))/COUNTA($A$2:$A$9)

In a separate range we have data for the Line chart in L1:M11.

Select the Date and Axis data in L1:M11, and insert a line chart (below left).

Select the Start Date data (B1:B9), then hold Ctrl while you select the Height data (J1:J9), press Ctrl+C to copy, then select the chart, and use Paste Special to add the data as a new series in columns, series names in first row, categories (X values) in first column (below right). I’ve added a legend temporarily, to help keep track of all the series we’re adding; I’ve also changed the series name from cell J1 to cell B1.

Select the Complete dates (H1:H9), then hold Ctrl while you select the Height data (J1:J9), press Ctrl+C to copy, then select the chart, and use Paste Special to add the data as a new series in columns, series names in first row, categories (X values) in first column (below left). I’ve changed the series name from cell J1 to cell H1.

Select the Incomplete dates (I1:I9), then hold Ctrl while you select the Height data (J1:J9), press Ctrl+C to copy, then select the chart, and use Paste Special to add the data as a new series in columns, series names in first row, categories (X values) in first column (below right). I’ve changed the series name from cell J1 to cell I1.

Eventually the markers for Complete and Incomplete will become our milestone markers. We will label the Complete markers “Complete” (conveniently the series name), and the Incomplete markers with the percent complete. We will extend the blue Done timeline bars from Start Date towards the right (positive error bars), and the orange Not Done timeline bars from Incomplete to the left (negative error bars). Then we’ll label the Tasks in one of two ways.

Select the Start Date series, click the plus icon next to the chart, and check Error Bars. Repeat for the Incomplete series. I have already colored the error bar lines blue and orange (below left).

Select the vertical error bars for Start Date, and press Delete. Repeat for Incomplete (below right).

Select the horizontal error bars for Start Date, press Ctrl+1, and in the Format Error Bars task pane, under Error Amount, select Custom and click the Specify Value button. Select the Done range (F2:F9) for Positive, and enter 0 (zero) for Negative. Repeat with the horizontal error bars for Incomplete, entering 0 for Positive and the Not Done range (G2:G9) for Negative (below left).

Format the lines for both sets of horizontal error bars. I used Blue for the Start Date error bars and orange for Incomplete, with 50% transparency for both colors, and a line thickness of 9 points for both (below right).

Time to perform a little clean-up. Format each series too use no lines. Format Axis and Start Date to use no markers. Format the milestone markers for Complete and Incomplete as desired. Format the vertical axis, and choose None for the labels. Delete the gridlines (below left).

Add data labels to the Complete series, and choose the option Label Contains Series Name. Add data labels to the Incomplete series, and choose the Label Contains Value From Cells, and select the % Complete range (E2:E9) as the source for the labels. I’ve used a font color to match the bar and marker color scheme (below right).

Now decide if you want the Task labels at the start of each bar (below left) or along the vertical axis at the left hand edge of the chart (below right)

Error Bar Gantt Chart Advanced Task Label Options

Task labels at the beginning of the bars require only one additional step. Add data labels to the left of the Start Date points (the markers are no longer visible, but you can point and click at the beginning of the bars to select them). Use Label Contains Value From Cells, selecting the Task names in A2:A9 (below left).

Task labels along the left edge of the chart requires several more steps. First, select the Task Names (and header) in A1:A9, hold Ctrl while selecting most of the last Axis column (M1:M9), press Ctrl+C to copy, select the chart and use Paste Special to add the data as a new series in columns, etc. The points don’t appear, but they do exist (below right)

Right click on any series, select Change Series Chart Type, find the last added series, and change it to a Bar chart. The points still don’t appear, but you should see a secondary horizontal axis along the top of the chart (below left). Click the plus icon next to the chart, click the right-pointing triangle next to Axes, and check Secondary Vertical Axis. The new axis appears along the right edge of the chart, with Task 1 through Task 8 going bottom to top instead of top to bottom. The added bars now appear as thinner blue bars across the chart (below right).

Format the secondary vertical axis, and choose Categories in Reverse Order (below left).

Format the secondary horizontal axis (the numeric axis, not the date axis), and select Vertical Axis Crosses Automatic (below right).

Select the secondary horizontal axis and click Delete (below left). Perform any additional clean-up, such as using no line for the vertical axis (below right).

Articles Related to Gantt Charts on this Blog

Posted: Thursday, May 30th, 2019 under Chart Types.
Tags: Error Bars, Floating Bars, Gantt Charts.
Comments: 3

Low-Medium-High Charts

Tuesday, May 28, 2019 by Jon Peltier 2 Comments

Tuesday, May 28, 2019 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Low-Medium-High Charts

When comparing values, it is often desirable to compare ranges of values. You would use some kind of chart to plot Low-Medium-High values. Low and High typically refer to minimum and maximum values, and Medium might be an average or medium value.

You could use a line to denote the range between Low and High…

Low-Medium-High Chart with range denoted by dotted lines

… or you might prefer a thicker bar…

Low-Medium-High Chart with range denoted by thick bars

This article shows an easy way to set up such a chart, using error bars to construct the lines or bars.

This article uses techniques in two of my previous tutorials, Custom Error Bars in Excel Charts and Floating Bars in Excel Charts. It also shows ideas I shared when answering Making horizontal max-min-average chart in Excel in Stack Overflow.

You can download the companion workbook, Error Bars for Horizontal Lines and Bars.xlsx.

Low-Medium-High Chart with Horizontal Lines

The simple Low-Medium-High chart with lines is an XY Scatter chart with horizontal error bars. The data is simple: two columns with X (Medium) and Y values, two columns with Low and High values, and two columns with error bar lengths (from Low to Medium and from Medium to High). The error bar columns contain subtraction formulas to compute the values, but if you know the error bar lengths, you don’t need Low and High in the data range.

Selecting the X and Y columns of data, and insert an XY Scatter chart.

Click the plus icon next to the chart, and add error bars. Excel adds X and Y error bars with default values.

XY Scatter chart with default error bars

Select the vertical error bars, and click Delete.

XY Scatter chart with horizontal error bars only

Select the horizontal error bars, use the Ctrl+1 shortcut to open the Format Error Bars task pane, and under Error Amount, choose Custom, and click Specify Value. The dialog shown below left appears (yes, the range boxes are really that narrow!), with default ={1} for the custom values. Select the ={1} in the first box, delete it, and select the range with the positive error bar values. If you don’t delete the default value before selecting the range, Excel may try to add the range to the {1} already in the box, and give you an error:

={1}+Sheet1!$F$2:$F$5

Repeat for the second box and the range with the negative error bar values (below right).

Here is the chart with our custom error bar values.

Format the error bars. Here I selected No Cap for the error bar’s end style, gave the line the same color as the XY markers, chose a dash style, and used the diamond-shaped option for begin and end arrow types.

XY Scatter chart with formatted error bars

Finally, click the plus icon next to the chart, and check the box to add data labels. Format the data labels so they are above the points, and so they show the X values, not the default Y values.

XY Scatter chart with formatted error bars and data labels

Low-Medium-High Chart with One-Colored Horizontal Bars

For a simple Low-Medium-High chart with one color bars, follow the same protocol as above, up to where you have applied custom error bar values. Select the markers, press the Ctrl+1 shortcut to open the Format Series task pane. Under the paint can icon, choose Marker > Marker Options > None.

Hidden XY Scatter series with custom horizontal error bars

Format the error bars. Here I selected No Cap for the error bar’s end style, gave the line the first accent color in Excel’s default color scheme, gave it a transparency of 50% so the gridlines show through, and set the line width to 12 points.

Hidden XY scatter series with thick error bars

Finally, click the plus icon next to the chart, and check the box to add data labels. Format the data labels so they are centered on the points, and so they show the X values instead of the default Y values.

Hidden XY scatter series with thick error bars and data labels

Unfortunately, all of the error bars have to be formatted the same, so you can’t do it so simply if you want different color bars. Read on for the procedure to create multi-colored bars.

Low-Medium-High Chart with Multi-Colored Horizontal Bars

In order to get multi-colored bars in the chart, you need multiple sets of error bars, which means multiple series in the chart. The data range is like that used above, but there are separate columns for Y values for each individual color of error bars. The other columns can be shared amonf the different series.

Multi-series data for Low-Medium-High chart with multi-colored bars

Select the columns with X and all Y values and insert an XY Scatter chart. Make sure Excel plots the data in columns. Each point in the sample chart below is a separate series, so each has its own color.

Click the plus icon next to the chart, and add error bars. Excel adds X and Y error bars with default values. The default Y error bar values are very small, so they are hidden by the markers; I’ve temporarily assigned a value of 0.5 you you can see them.

Multiple series XY Scatter chart with error bars

Select one set of vertical error bars, and click Delete; repeat for all other vertical error bars. Remember, each series has its own set.

Multiple series XY Scatter chart with horizontal error bars

Select one set of horizontal error bars, use the Ctrl+1 shortcut to open the Format Error Bars task pane, and under Error Amount, choose Custom, and click Specify Value. Select the ={1} in the first box, delete it, and select the range with the positive error bar values (use the whole range, even though the series has fewer points). Repeat for the second box and the range with the negative error bar values.

Repeat for each additional set of horizontal error bars, using the same positive and negative range for each.

Multiple series XY Scatter chart with custom error bars

Select one of the markers, press the Ctrl+1 shortcut to open the Format Series task pane. Under the paint can icon, choose Marker > Marker Options > None. Repeat for the other series.

Format the error bars. Use No Cap for the error bar’s end style, give the line its own unique color with a transparency of 50% so the gridlines show through, and set the line width to 12 points.

Posted: Tuesday, May 28th, 2019 under Chart Types.
Tags: Error Bars, Floating Bars.
Comments: 2

Gantt Chart with Nice Date Axis

Monday, May 20, 2019 by Jon Peltier 15 Comments

Monday, May 20, 2019 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Gantt Chart with Labels and Gridlines on First of Month

Simple Gantt Charts

A year ago I wrote an article about Gantt Charts in Microsoft Excel. It was pretty comprehensive, showing how to make simple Gantt charts using worksheet conditional formatting and using regular Excel stacked bar charts, and it covered some advanced techniques for adding embellishments, such as a reference line for a given date, multiple bars showing percent complete of a task, and milestone markers at the end of the bars.

Simple Gantt Chart in Excel Worksheet — Simple Excel Gantt Chart using Conditional Formatting in a Worksheet

Simple Microsoft Excel Gantt Chart — Simple Excel Gantt Chart using Stacked Bar Chart

Advanced Microsoft Excel Gantt Chart — Simple Excel Gantt Chart starting with a Stacked Bar Chart

These Gantt charts all looked great, but a clever reader asked about a nicer axis scale across the bottom of the chart. In my example above, I had a tick label and vertical gridline every week, which is great for the time scale of my data. But what if the chart spans a longer time, for example, a year, and I want a label and gridline at the first of every month.

Gantt Chart with Wrong Monthly Date Scale

Below is a similar data set, but spanning a year rather than a few months.

Following the protocol in the earlier tutorial, we can build a Gantt chart like this one. Never mind the steps, because we’ll have to do it differently. Read on.

Simple Gantt Chart with Inelegant Date Axis Scale

The chart spans the year nicely, but the axis scale is not what we’d like. When setting the bar chart’s axis scale, I can’t just say, put a label every month on the first. I have to pick a set number of days. Above I tried 31 days, which worked for January and February, but quickly went awry.

Below you can see the 31-day axis spacing, and a 30 day spacing, which is also wrong. If we recall that 365 days divided by 12 is 30.41666…, we might try 30.4 days, in the bottom scale, and it almost works.

Attempting different major unit settings for a date axis scale.

Excel Line charts can give us a nice axis scale. Let’s have a look.

Line Chart Date Axes

In the first example, we had a bar chart with a horizontal axis date scale that had a label every 7 days. The horizontal axis in a bar chart is a value axis.

Simple Microsoft Excel Gantt Chart with weekly date axis scale

We can use that same data to create a line chart, with the same 7-day axis spacing. The horizontal axis in a line chart is a category axis, in this case, a date-scale axis. The axis can have the same min and max as the bar chart’s value axis above, but there are more formatting options.

Below are the Format Axis task panes for the bar chart’s horizontal value axis (left) and the line chart’s horizontal date axis (right).

Format Axis Task Panes for Bar Chart's Horizontal Value Axis (left) and Line Chart's Horizontal Date Axis (right).

In the bar chart, the minimum and maximum are the numbers 43184 and 43289, which are actually the serial numbers (days since 1 January 1900) of the min and max dates in the line chart, 3/25/2018 and 7/8/2018. The
major unit (axis tick spacing) in the bar chart is 7, and in the line chart is 7 days. So these two axes are the same.

In the current example, we have a bar chart with a horizontal axis date scale that had a label every 31 days. Not exactly every month.

Simple Microsoft Excel Gantt Chart with not-quite-monthly date axis scale

Again, we can use that same data to create a line chart, with monthly axis spacing. But here, the spacing is exactly monthly.

Now we can see the real difference in the axis scales. Here are the Format Axis task panes as before.

In the bar chart, the minimum and maximum are the numbers 43466 and 43831, which are the serial numbers of the min and max dates in the line chart, 1/1/2019 and 1/1/2020. The major unit (axis tick spacing) in the bar chart is 31, which is only appropriate for some months, while in the line chart it is 1 month. Excel uses this setting and adds the appropriate number of days, 31, 30, 29, or 28, so the tick labels are one month apart.

Gantt Chart with Nice Monthly Date Scale

Sometimes different chart types can share incompatible axis types, but here we are not so lucky. We need to create a chart with bar chart series to display the Gantt chart bars, and we need to combine this with a line chart to produce the date axis with the desired tick spacing. It’s a lot of steps, but not too complicated, and for the most part, the order you apply them is immaterial.

The Data

Let’s start with the data, below. On the left is the data needed to create the Gantt chart itself, and on the right is the data for the axis scale. The Gantt data has Task names, Start dates, Duration, and End dates; only the first three columns are needed. The Axis Scale data has the first and last dates in the first column, dummy values of zero in the second column, and serial numbers of the dates in the third column. The formula in the third column links to the first, but the cells are formatted not as a Date but as General.

Data for Gantt Chart with Monthly Date Scale

Create and Format the Chart

Select the first three Gantt columns, and insert a bar chart.

Let’s format the bars and get it over with. Start has no fill, so it’s hidden, and Duration is given the desired color. I have applied a 50% transparency to Duration, so the gridlines show through the bars.

Format the vertical category axis (showing task names), choosing the Categories in reverse order. In the simple Gantt chart from the earlier tutorial, we also moved the time axis to the bottom of the chart, but we’ll keep them at the top for this chart.

Gantt Chart Step 3 - Reverse the Vertical Category Axis

Now add the data for the dummy axis. Copy the first two columns of the right hand data block shown above, with dates and dummy zero values. Select the chart, then go to the Home tab of the ribbon, click the Paste dropdown, and choose Paste Special from the bottom of the menu. Make sure these options are selected: Add Cells as New Series, By Column, Series Names in First Row, Categories in First Column.

The series doesn’t appear as much: it is a new set of stacked bars, but the values of zero means the bars have zero length and don’t appear. There is a new entry in the legend.

Use Copy and Paste Special to add the dummy date axis data to the chart.

Right click on any series in the chart, and choose Change Series Chart Type. All three series are listed as Stacked Bar on the Primary Axis.

Click the Chart Type dropdown for the series named Axis, and select a Line type (I used the line with markers subtype so the series shows more clearly in the following steps).

Excel automatically changes the Axis series from Primary to Secondary Axis.

Here’s our combination chart.

Ganyt Chart Step 5 - Convert Dummy Series to Line Chart Type

We don’t need the legend any more, so delete it.

Format the Date Axes

Now add the secondary horizontal axis, the whole reason we added the dummy Axis series. Easiest way: select the chart, click on the “+” icon that appears next to the chart, click the right-pointing triangle next to Axes, and check the Secondary Horizontal box.

Now move the new axis to the bottom. Format the secondary vertical axis, on the right edge of the chart. Under Horizontal Axis Crosses, choose Automatic.

Now double-click on the bottom horizontal date axis to format it. Make sure the minimum and maximum are correct, change Base Units to Days, and change Major Units to 1 Month. Under Axis Position, choose On Tick Marks. Also, click under the paint can, and choose No Line for the axis.

Change the Number Format of the axis labels. I like to use a two-line format, with the day above the month. You can get a line feed in the middle of a number format in the worksheet, by typing Ctrl+J in the Number Format box, and then use this data for the axis and check Linked to Source in the Format Axis task pane. Unfortunately there is no way to get the line feed in the Format Axis task pane, which is a severely annoying shortcoming.

I was so annoyed that I wrote a little VBA procedure to do this, and I’ve added it to the Advanced Edition of Peltier Tech Charts for Excel 3.0, pardon the shameless plug. I can right click on the axis, and select Axis Number Format from the context menu.

Chart Axis Context Menu, with a few of my custom menu items: Apply a Nice Axis Scale, and Axis Number Format.

A little dialog pops up, showing the current number format for the axis.

Axis Number Format Dialog with current number format

Then I enter the number format I want, inserting ‘<newline>’ where I want a line feed.

Axis Number Format Dialog with desired number format

I click OK and I get the desired number format.

The axis may have been fine with just the short month name, that is, a number format of “mmm”, which you can apply using the Format Axis task pane.

Now set the axis scale limits on the primary axis, showing dates at the top of the chart. The axis had used 43200 and 43900, the serial numbers of 10-April-2018 and 10-March-2020, which were autoscaled by Excel. We need to use the serial numbers of 1-January-2019 and 1-January-2020, which are 43466 and 43831. If you use a major unit of 365, you will get tick labels at only the min and max of the axis, as shown below.

Finish with a Little Clean-Up

Turn off the primary major vertical gridlines and turn on the secondary major vertical gridlines. Easy: click the “+” icon next to the chart, click the right-pointing triangle next to Gridlines, and use the checkboxes.

There are two axes we don’t want to show. Format the top horizontal axis so it uses no line (in this case it already had no line) and no tick labels. You could format the right-hand vertical axis the same way; since we really don’t need it any more you could also delete it, but don’t delete the horizontal axes!

Finally, hide the dummy axis series. Double click on the series, and under the paint can, choose No Line and No Markers for its formatting.

There’s our Gantt chart with gridlines and axis labels at the first of each month. You don’t need to squint too hard to see that the month between 1 February and 1 March is shorter than the months between 1 January and 1 February and between 1 March and 1 April.

Articles Related to Gantt Charts on this Blog

Surplus-Deficit Area Chart

Monday, March 18, 2019 by Jon Peltier 6 Comments

This Surplus-Deficit chart shows the history of surpluses and deficits (positive and negative values) over the past few years. The two colors help show when the deviation was positive and when it was negative. It takes a little bit of Excel magic to get that effect to work properly in an Excel area chart.

I showed how to make this kind of chart in an old tutorial, Area Chart – Invert if Negative. That approach required repeating the data in a new range, inserting a row after every row of data, and doing some interpolation to get the chart data right.

But I was looking over a “visual vocabulary” by my colleague Jon Schwabish of Policy Viz, a rough draft summary of many chart and visualization types and guidelines for building them in Excel. When it’s ready for public consumption, I’ll be sure to post a link.

Jon showed a surplus-deficit area chart, and on inspection, it wasn’t exactly right. He went to my earlier post and fixed it, but in the meantime, I thought of a way to do the calculations in a more compact way, supplementing rather than supplanting the original data.

Surplus-Deficit Column Charts

For this tutorial I will use a simple data set, with monthly surplus-deficit data. I’ve shaded this original data blue, so you can follow it in the long protocol. Note that the data is in an Excel Table, which has lots of magic of its own.

You can make a surplus-deficit chart with any number of chart types. Below is a simple column chart of this data showing surpluses and deficits in a simple data set.

Monochromatic Surplus-Deficit Column Chart

Easy to make, easy to read: surpluses grow upwards from the horizontal axis, deficits downwards. Excel makes it easy to show positive and negative values in different colors in bar and column charts. Format the bars with the main (positive) color, then in the Format Fill part of the Format Series task pane, check the Invert If Negative box, and select the color for the negative values.

Two-Color Surplus-Deficit Column Chart, Using Invert If Negative.

Surplus-Deficit Area Charts

The Two-Color Area Chart

In general, I prefer columns for this kind of data rather than areas: it always seems to me that an area chart minimizes the points on either side of an axis crossing, and overemphasizes points in the middle of a positive or negative run.

But area charts have their place, so let’s make an area chart of the data:

Monochromatic Surplus-Deficit Area Chart

Let’s format the colors like we did for the column chart. Main color, blue, that’s fine. But there’s no Invert If Negative setting. Let’s stop and scratch out chins for a while. What if we have separate Pos and Neg series in the chart? We can always format them with the desired different colors.

Here’s the data with Pos and Neg data columns added. I’ve used these formulas for Pos and Neg respectively:

Pos: =MAX(0,B2)
Neg: =MIN(0,B2)

Here’s the (unstacked) area chart showing Pos and Neg:

It’s not exactly right. The sloped edges of the different colored should meet on the horizontal axis, along the lines shown in the following chart:

Lines Showing Desired Area Shapes in Surplus-Deficit Chart.

Our data isn’t complete enough. We only have points at the vertices of the surplus-deficit data, indicated by the circular markers in this line chart:

Surplus-Deficit Line Chart Showing Markers on Data Points

That’s fine for a one-series area chart. But in our Pos and Neg data, here are the points we have:

Surplus-Deficit Line Chart with Incomplete Data

Adjusting the Data

For the areas to line up correctly, we would need data points at the axis crossings between vertices, like this for the one-series line chart:

One-Color Surplus-Deficit Line Chart with Points at Axis Crossings

…or like this for the Pos Neg chart:

Two-Color Surplus-Deficit Line Chart with Points at Axis Crossings

This is where we need to interpolate our area chart data. In my old tutorial, I inserted rows to make room for interpolation, like this. The blue is our original data, and the gold shaded rows are the interpolated data. The green shaded row at the top is a placeholder to make sure I have as many interpolated rows as original rows, for when I adjust the data in a minute.

In the Value column, the formula in the gold cell B4 compares the values in the blue cells above and below it (B3 and B5). If their product (B3*B5) is positive, it means that both have the same sign, so there is no axis crossing, and the cell will show #N/A, to suppress plotting of that point. If their product is negative, it means the blue cells have different signs; the line must cross in between, so the cell contains zero for the Y value at the crossing. Don’t worry about the formulas yet, they need to be redone when the table is adjusted.

In the Date column, if there is no axis crossing (product of blue Value cells B3*B5 is positive or gold Value cell contains #N/A), then the gold Date cell contains #N/A, to suppress plotting. If there is a crossing (product of blue Value cells is negative or gold Value cell contains zero), the gold Date cell A4 contains an interpolated value between the two blue Dates A3 and A5.

Anyway, I realized that I can add columns to the data table for these calculations. To illustrate, I’ll adjust my data in two steps. First I’ll move the gold and green cells to the right by four columns:

Interpolated Surplus-Deficit Data, Partly Adjusted

Now I’ll remove the blank cells between rows.

Interpolated Surplus-Deficit Data, Fully Adjusted

And we no longer need the gold and green to distinguish interpolated rows.

The table contains the original data in the blue columns, and these formulas in the other columns

Pos:     =MAX(0,B2)
Neg:     =MIN(0,B2)
Date 2:  =IF(ROW()=ROW(INDEX([Date],1)),NA(),IF(B1*B2>0,NA(),
             B1/(B1-B2)*(A2-A1)+A1))
Value 2: =IF(ROW()=ROW(INDEX([Date],1)),NA(),IF(B1*B2>0,NA(),0))
Pos 2:   =IF(ROW()=ROW(INDEX([Date],1)),NA(),IF(B1*B2>0,NA(),0))
Neg 2:   =IF(ROW()=ROW(INDEX([Date],1)),NA(),IF(B1*B2>0,NA(),0))

There are a few tricks. The first IF looks at the condition

ROW()=ROW(INDEX([Date],1))

which is true if the row is the first data row of the table. And if it’s true, the formula uses NA() to put #N/A into the cell.

The second IF looks at B1*B2. If values in both B1 and B2 are positive or both are negative, the result of multiplication is positive, so the formula returns #N/A. If they are mixed, we get a negative result, so the formula puts zero into the cell.

We’re never actually multiplying B1 and B2, which would produce a #VALUE! error, because cell B1 contains a text header label. In that row of the table, the first IF takes precedence, because it already has determined that the cell is in the first data row and the value should be #N/A.

Adjusting the Chart

Now we just need to get this additional data into the chart. The original Pos-Neg area chart looks like this.

The two series formulas look like this:

Pos:  =SERIES(Sheet1!$C$1,Sheet1!$A$2:$A$13,Sheet1!$C$2:$C$13,1)
Neg:  =SERIES(Sheet1!$D$1,Sheet1!$A$2:$A$13,Sheet1!$D$2:$D$13,2)

We need to change the X values from
Sheet1!$A$2:$A$13
to
(Sheet1!$A$2:$A$13,Sheet1!$E$2:$E$13)

And the Y values from
Sheet1!$C$2:$C$13
to
(Sheet1!$C$2:$C$13,Sheet1!$G$2:$G$13)
and
Sheet1!$D$2:$D$13
to
(Sheet1!$D$2:$D$13,Sheet1!$H$2:$H$13)

You can make the changes by typing in the series formula directly, or by using the Select Data dialog. After changing the series formulas, the areas in the resulting chart align across the axis as desired.

It’s a nice little trick, and it works for area charts and other charts that have category axes that can display a Date scale. These charts internally sort by date before plotting the data. So we can put the data anywhere in the list, even in a separate column that makes up a different part of the list.

Posted: Monday, March 18th, 2019 under Chart Types.
Tags: Area Charts, invert if negative.
Comments: 6

Peltier Tech Excel Charts and Programming Blog

Excel Stock Charts

Built-In Stock Charts

High-Low-Close

Open-High-Low-Close

Volume-HLC and Volume-OHLC

Home-Made OHLC Candlestick Chart

OHLC Chart with Open/Close Tickmarks

Stock Chart with No Gaps for Weekends

OHLC Candlestick

OHLC with Tickmarks

Include an Index in a Stock Chart

OHLC Candlestick

OHLC with Tickmarks

Floating Bars and Gantt Charts

XY Scatter Error Bar Gantt Chart

Line Chart Error Bar Gantt Chart

Combination Error Bar Gantt Chart

Simple Combination Error Bar Gantt Chart That Works

Label Variation in Above Gantt Chart

Advanced Combination Error Bar Gantt Chart

Articles Related to Gantt Charts on this Blog

Low-Medium-High Charts

Low-Medium-High Chart with Horizontal Lines

Low-Medium-High Chart with One-Colored Horizontal Bars

Low-Medium-High Chart with Multi-Colored Horizontal Bars

Simple Gantt Charts

Gantt Chart with Wrong Monthly Date Scale

Line Chart Date Axes

Gantt Chart with Nice Monthly Date Scale

The Data

Create and Format the Chart

Format the Date Axes

Finish with a Little Clean-Up

Articles Related to Gantt Charts on this Blog

More Axis Scale Articles

Surplus-Deficit Column Charts

Surplus-Deficit Area Charts

The Two-Color Area Chart

Adjusting the Data

Adjusting the Chart