Excel values not updating
The clustered column chart shows the values we want to highlight: we want a floating column to connect each low value to its corresponding high value.We achieve this by inserting a column in the worksheet which has a simple formula to calculate the difference between high and low (“Delta” in the table below).Adjusting the data range and changing from clustered to stacked columns shows all we need.The floating column is resting on top of the Low value column. The Low series is formatted to be invisible: no border and no fill.Our simple formulas are not adequate, and we need a different approach.We’ll add three columns to the data sheet: one for the blank columns on which the floating columns will rest, one for whatever part of the floating column is positive (above the X axis), and one for whatever part of the floating column is negative.
Even though the Delta begins below the X axis, the Delta column has a positive value, and is plotted starting at zero or at the top of the minimum, if that value is positive.There are numerous ways to create floating bars in an Excel chart.There are so many ways that I should write more than one post, but I’m going to cram them all into this one.I like to use gap widths of 50% to 100%, and I used 75% in most of the charts here.With this technique, each column can be selected (it may take two single clicks) and formatted independently of the rest, for purposes of highlighting one or more specific values.
The gold and purple colors above may show extreme highlighting, and were selected to clearly show the different colors.