Expand Fill of Area Chart to be Same as Bar Chart

bisel

Board Regular
Joined
Jan 4, 2010
Messages
152
Hello All,

I have a combo chart with stacked bars in front of an area chart. The area chart extends from first to the last tick mark. The stacked bars are centered on the tick marks with the result that the area chart's end data points are centered on the stacked bars. Not a big deal, but I would like to extend the first and last area chart data points so that the they extend to the ends of the stacked bars rather than ending abruptly at the center line of the stacked bars.

Here is how my chart appears ...
1601834955598.png

Notice that the area chart, "Total Available Funds", has the first and last data point centered on the stacked columns in the years 2021 and 2051.

Using Photoshop, here is how I would like it to appears ...
1601835246439.png



Any thoughts?

Regards,

Steve
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,922
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
It's going to be very difficult to extend the areas only to the edges of the bars. It would be easier but still tricky to extend the areas to the edges of the plot area. This second option looks better to me, so that's what I'll show below.

Here's a simpler example. The columns line up with the axis, the area lines up behind the columns, but only reach to the center of the first and last columns.

ExtendAreaBehindBars1.png


We'll use the same column data but modify the area data. I've added half year values before, between, and after the original years.

The first chart shows the original column data.

Copy the new area data (both columns), select the chart, and use Paste Special to add it as a new series, name in first row, categories in first column (second chart).

Right click on the added series Change Series Chart Type. Select Area, and check the secondary axis box (third chart).

Using the floating '+' icon next to the chart, under Axes, check the secondary horizontal axis and uncheck the secondary vertical axis (fourth chart).

Select the top horizontal axis, press Ctrl+1 to format it. Under Axis Position, select On Tick Marks (fifth chart).

Finally hide the secondary horizontal axis via formatting (don't delete it). For axis tick label position, select None. For axis line format, select No Line.

ExtendAreaBehindBars2.png
 

bisel

Board Regular
Joined
Jan 4, 2010
Messages
152
Thanks
It's going to be very difficult to extend the areas only to the edges of the bars. It would be easier but still tricky to extend the areas to the edges of the plot area. This second option looks better to me, so that's what I'll show below.

Here's a simpler example. The columns line up with the axis, the area lines up behind the columns, but only reach to the center of the first and last columns.

View attachment 23701

We'll use the same column data but modify the area data. I've added half year values before, between, and after the original years.

The first chart shows the original column data.

Copy the new area data (both columns), select the chart, and use Paste Special to add it as a new series, name in first row, categories in first column (second chart).

Right click on the added series Change Series Chart Type. Select Area, and check the secondary axis box (third chart).

Using the floating '+' icon next to the chart, under Axes, check the secondary horizontal axis and uncheck the secondary vertical axis (fourth chart).

Select the top horizontal axis, press Ctrl+1 to format it. Under Axis Position, select On Tick Marks (fifth chart).

Finally hide the secondary horizontal axis via formatting (don't delete it). For axis tick label position, select None. For axis line format, select No Line.

View attachment 23702

Thanks for reply. I think I will just leave as is.

Steve
 

Watch MrExcel Video

Forum statistics

Threads
1,114,070
Messages
5,545,816
Members
410,708
Latest member
SanTrapGamer
Top