This shows which tasks are fully completed and which are in progress.
In Cell I4, write % Completion.
Select cells G5-G13, copy the formatting and paste it into cell I5. Select cells I5-I13, and in Home, change the formatting of these cells to %.
Type in some values for % completion:
The aim is for these values to be reflected in the chart. To do this, use error bars.
As previously mentioned, if you want to show horizontal error bars, you need to use a scatter plot, as they have values on the X as well as the Y-Axis.
The bar chart also has horizontal error bars because it has categories on the Y-Axis and values on the X-Axis. This means it’s not necessary to introduce a new scatter plot series here. The existing series can be used, all we have to do is activate the error bars for that.
However, there are two series. The bottom and top stack. If the error bars for the bottom stacks are activated, a point is added at the end of the first stack and at the beginning of the second stack. That would be perfect in this case.
Highlight the stack, and activate Error bars.
In the Format Error Bars sidebar, under Horizontal Error Bar, Direction, choose Plus.
The length of the lines will depend on the % Completion.
The % Completion needs to be translated to days. So for example, if it’s 100% (in cell I5), the length should be identical to the value for #days (in cell M5) which is 8.
ADD # days completed to the Date Prep. Table.
(Here, B16 is the cell with the drop-down for viewing Plan or Actual
I5 is % Completion
M5 is # days)
Copy down the formula.
In the Format Error Bars sidebar, under Error Amount, select Custom and Specify Value.
For the positive error bars, select the cells N5-N13 (the # days completed section). Press OK.
In the Format Error Bars sidebar, under End Style, choose No Cap.
Under the Paint Bucket symbol in this sidebar, change the color to green, and the Width to a thicker value of 8 pt.
To check, it’s clear that for Actual, the % Completion is visible.
When the scenario is changed to Plan, nothing is visible as completion values don’t apply here.
Again, to check, change cells I7, I8 and I9 to 50%, 20% and 10% respectively. These changes are all reflected in the chart.