Improve company productivity with a Business Account.Sign Up


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

Posted on 2010-08-16
Medium Priority
Last Modified: 2012-05-10

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
Question by:Guglani
  • 4
  • 3
  • 3

Expert Comment

ID: 33444347
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.
LVL 50
ID: 33444424
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

Accepted Solution

sijpie earned 1000 total points
ID: 33444561
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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


Expert Comment

ID: 33444598
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.

Author Comment

ID: 33444629
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.


Author Comment

ID: 33444662
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
LVL 50
ID: 33444788
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


Expert Comment

ID: 33445042
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.
LVL 50
ID: 33445070
@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

Author Comment

ID: 33446484
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

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering.  This article seeks by illustration …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question