• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5083
  • Last Modified:

Adding a threshold horizontal line on a graph in excel and powerpoint

Hi

I am creating a powerpoint pres and in which I am copy pasting graphs from an excel sheet. I create the graphs in excel and paste in the powerpoint.

Q1 : Is this a good or bad practice or bad? Will this cause issues, if this ppt if sent to someone else?

Q2 : I want to add a threshold horizontal line at 80% in the graph. Is this possible?

I used Office 2007
0
Guglani
Asked:
Guglani
  • 4
  • 3
  • 3
1 Solution
 
sijpieCommented:
What I always do is in Powerpoint use Edit/paste special/ As Pictue (Enhanced Metafile).

That way
1) other people do not have access to the underlying data,
2) your file size stays minimal
3) you can resize the graph best

I'll get back with your second question. Just need to look up how I did it recently.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello Guglani,

When you paste a chart from Excel into Powerpoint, siipie's method works very well. Make sure to size your chart in Excel to the required size, so that you don't have to scale it in Powerpoint. Scaling will decrease the image quality. If you increase the picture size, it will get blurry, if you decrease the picture size, it will get less readable. So, try to create an Excel chart that has a size that you can copy 1:1 into Powerpoint.

For your second question: Create a new column with at least the start and the end value of the line, then add this data to the chart as a new series, and format it to display on the secondary axis with the Series Format dialog.

See attached for an example

cheers, teylyn
Book1.xlsx
0
 
sijpieCommented:
If your main chart is a bar chart, you need to combine the ar chart with a line. Do this as follows.

Add a little range in your data say
D4: 0
D5: 1
E4: =0.8*MAX(B:B)   (assuming that yor bar chart data is in column B)
E5: =0.8*MAX(B:B)

Now Chart, Add Data and select this range (D4:E5)
Right click on the new bars in the chart, select Chart Type and select 'XY Scatter'
Now a small lie should show up. Right click on this and select 'Format Data Series'
On the Axis tab, set the series to secondary axis.
Now double click the secondary X-axis, select the 'Scale' tabe and set min 0, max 1, minor major intervals to 1. Set the Font colour (font tab) to white
Right click on the chart and select @Chart Options', set the seconday Y-axis to not show (clear tick box). This will set the scale to be the same as the primary y-axis.

Now you should have your line
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
sijpieCommented:
With regards to Teylyn's comment,

if you use paste as picture (enhanced metafile) you can resize without quality penalty as it is imported as a vector map, not a bit map. But if this a task youneed to do on a monthly or weekly basis, then sizing it correctly in Excel makes your life easier.
0
 
GuglaniAuthor Commented:
Thank you Sijpie and Teylyn.

I guess, what sijpie has mentioned is what I want to do. I will try that out and come back if I face a problem. I am trying to insert a threshold line at say 80% for a bar chart which is a % type chart.

Thanks
Guglani
0
 
GuglaniAuthor Commented:
Hi Teylyn

Your excel is also giving me what I want to do.
Now I tried this and I am getting the line, but the actual data is also getting converted to line type. How do I avoid that?

Also let me also state that I am using a template to display the graph
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello Guglani,

add the data for the line into the chart but keep the original chart type. Then select the new data series, open the format dialog for the data series and click "Secondary Axis". Now select the new series again and change the chart type to a line chart and your original chart type will stay as it was.

You can now format the secondary Y axis to show its own min and max values, thus manipulating where the line displays on the chart.

cheers, teylyn

0
 
sijpieCommented:
The two are more or less the same, my method is slightly more automated, in that the formula and leaving away the left hand Y-axis ensures that the line is always at the correct 80% of the highest value.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@sijpie, regardless of the chart type, i.e. XY scatter or line, you can always manipulate the secondary axis.

You recommend a XY scatter chart as the chart type for the line, I suggested a line chart. There's really not much difference.

Guglani, both suggestions work. You'll have to figure out what suggestion works best for you. Either Line or XY chart can be formatted to look exactly the same, and the secondary Y axis can be scaled to show what you want with either chart type.

cheers, teylyn
0
 
GuglaniAuthor Commented:
Thanks for all the help. I think i can take it from here.
I should be able to put the threshold line line now.

Many thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now