How do I create a mixed line/bar graph with one datapoint?

Galena1

Active Member
Joined
Jun 10, 2010
Messages
300
Ok, so here's the trick:

I know how to create combo or mixed graphs with line/bars on single axis, and secondary axis. I even know how to do mixed stacked and unstacked columnar charts...

But how do you depict in single view graph

a single bar (1 datapoint) with a single line (upper control limit e.g. target).?

And I don't want to to use shapes to draw the target line.

I want the target line to be automatically plotted by Excel...

Anyone?

Thanks!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

teylyn

Active Member
Joined
Sep 4, 2008
Messages
416
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hello,

you could use error bars.

cheers,
 

Galena1

Active Member
Joined
Jun 10, 2010
Messages
300
Good idea...might work.

but not sure where they are in Excel 2010..hmm
 

Galena1

Active Member
Joined
Jun 10, 2010
Messages
300
Ok, I found them, but error bars are vertical.

I need something horizontal....

strange, I don't think this can be done in excel with a single datapoint..

weird, huh? you would think someone would have asked for over the years.
 

teylyn

Active Member
Joined
Sep 4, 2008
Messages
416
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Horizontal error bars are not available for all chart types. For example a line chart with categories on the X axis cannot show horizontal error bars, since the horizontal units are not numeric, but categories.

XY Scatter charts, though, can show horizontal error bars, since the X axis is in numeric units and error bars can be calculated based on these units.

cheers,
 

Steve=True

Well-known Member
Joined
May 27, 2011
Messages
993
These should do what you are looking at doing:

I think this one is what you are looking for as it shows you how to set an Average line in combination with Column charts:
How-to Make a Conditional Column Chart in Excel
http://www.exceldashboardtemplates.com/?p=891

These are similar:
Group Column Chart with Lines for Excel Dashboard Presentations:
http://www.exceldashboardtemplates.com/?p=604

Step-by-Step Horizontal Bar Chart with Vertical Lines Tutorial
http://www.exceldashboardtemplates.com/?p=630


Good luck!
 

teylyn

Active Member
Joined
Sep 4, 2008
Messages
416
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Steve, if you read the question carefully, you will notice that the OP is already familiar with combination charts. The issue is about creating a line from a SINGLE data point.
 

Steve=True

Well-known Member
Joined
May 27, 2011
Messages
993
Hi Teylen,

I read it that Galena1 is trying to make a line with only one point to make a target. Like a column with a value of 5 and a target of 5.25.

As you can see from this picture, changing the target series of 5.25 to a combo line chart from a column chart doesn't create a line because there is only one point (as highlighted by the dot above the column).

Therefore, i feel that Galena1 can make the target line desired by making a combo chart using an XY Line series as shown in the right picture. I think this was the desired outcome. Hopefully this helps!

Line-for-a-Single-Point.png
 

teylyn

Active Member
Joined
Sep 4, 2008
Messages
416
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Cool. Still, the target line feeds from four cells in the data table.

Here is a screenshot of how to do it with error bars, with just one cell as the data source:
Chart_with_average_line_from_on_data_point.png.scaled1000.png
 

Steve=True

Well-known Member
Joined
May 27, 2011
Messages
993
Hi Gelena1,

I forgot that there is one other way to get the line you desire quicker and easier than my other replys. It will also move when the data changes so no need to draw a new line every time the target moves. It is by making a combo chart with a line and markers and a column chart. then you replace the markers on the line with marker series with a marker that looks like a line (versus a box or diamond or circle, etc.)

How to - If you have data as such:

Column = 5
Target = 5.25

Make them as a column chart.
Then change the 5.25 to a line chart WITH markers.
Then draw a line using the insert ribbon and draw a line shape. Copy the shape
Then select the 5.25 data point marker.
Paste the line shape as a new marker.

Here is a sample where i replaced thed line with marker that is only one point with a red line and then replaced another with a Red and Blue Arrow.

As your data changes your marker will move with the data. Good luck!

Line-for-a-Single-Point-via-marker-replacement.png
 

Forum statistics

Threads
1,141,315
Messages
5,705,693
Members
421,406
Latest member
kluna90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top