Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-08-16
10
Medium Priority
?
4,831 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
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.
0
 
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
Book1.xlsx
0
 
LVL 6

Accepted Solution

by:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Expert Comment

by:sijpie
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.
0
 

Author Comment

by:Guglani
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.

Thanks
Guglani
0
 

Author Comment

by:Guglani
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
0
 
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

0
 
LVL 6

Expert Comment

by:sijpie
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.
0
 
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
0
 

Author Comment

by:Guglani
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

916 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