Microsoft Excel – Embedding Vertical Reference Line in Chart

These can both be (rather) easily accomplished in Excel.

The first example is a basic combo chart (clustered column and line with markers), then formatted to taste.

The second example is two charts (as noted by Emily). Use Page Layout>Align>Snap to Grid to align the Chart and Plot Areas so the Axis are nicely aligned. Change the Border to No Line for a seamless experience (I only have it turned on to show the relative borders).

Based on your desired outcome, there's no need for two separate charts. A single area chart with two series, one positive and the other negative, will accomplish the same thing. A pseudo-panel/lattice chart effect can also be created by using gaps in the data series. Or multiple-aligned charts can be used horizontally to create the panels.

EDIT
To create the above image, using data from a table like this:

USING NEGATIVE VALUES

1. Create an empty Area Chart (Insert>Charts>2-D Area). It's best to do this on an empty cell, at least one row and column away from your data to prevent Excel from making (bad) decisions on it's own.
2. Add your data series. In this case (based on your example) there are two series, A & B. A is the positive series and B is the negative series.
   The two blank cells in the column become the separation between the groups. This is a shortcut-they are not necessary if you don't want gaps along the horizontal axis (like your example). And, even if you do want gaps, there are better ways to do that-but that should probably be a separate question.
   

OR, USING SECONDARY AXIS

1. If your values are all positive (pointing up), but you want to display them negative (pointing down), you can use a secondary axis. In this case you'll need to convert your B-Series values to positive.

2. Create a blank 2-D Area Chart (as above).

3. Add your data series (as above).

4. Format your B-Series to plot on the secondary axis.

5. Format your Secondary Axis to plot Values in Reverse Order.
   As you've discovered, the problem is the scales overlap and consequently they're "facing" one another, instead of having their "backs" together. This is because they're essentially the same "size" (0-1) and they're occupying the same space relative to the horizontal axis. So, we need to create enough "room" for them to both fit. So, we need to create a vertical axis that's roughly twice as tall and then move the two series to opposite sides of the horizontal axis. So...

6. Format both the Primary and Secondary Axis with:

• Minimum Value = -1
• Maximum Value = 1
  

And all those steps together:

7. Format the rest to taste. For the original above, I removed all the axis, adjusted color and transparency, and grid lines to more closely resemble your example.

Note that both processes result in the same visual product, but they differ considerably in complexity. Use the simplest that results in the product that best meets your needs.