Solved

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

Posted on 2010-08-16
10
3,773 Views
Last Modified: 2012-05-10
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
Comment
Question by:Guglani
  • 4
  • 3
  • 3
10 Comments
 
LVL 6

Expert Comment

by:sijpie
Comment Utility
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
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
 
LVL 6

Accepted Solution

by:
sijpie earned 250 total points
Comment Utility
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
 
LVL 6

Expert Comment

by:sijpie
Comment Utility
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
 

Author Comment

by:Guglani
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:Guglani
Comment Utility
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
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
 
LVL 6

Expert Comment

by:sijpie
Comment Utility
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
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
@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
 

Author Comment

by:Guglani
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now