Prevent chart from plotting above 100%...

W

Waxaholic

Board Regular
Joined
Apr 2, 2002
Messages
72
I have a chart where i plot the "top 20" pieces of data. The y-axis is set to automatically adjust it's values to the maximum data value. What i want to do is prevent it from going above 100. Occasionally i will have some data that comes in at 125% and so on. This is a bogus percentage and makes the chart look odd. I would like to retain the auto adjusting of the y-axis but prevent it from adjusting to more then 100%. Is this possible?

Thanks,

Waxaholic
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Waxaholic,

Just thought - if values over 100% are always bogus, why not fix them in the data before you chart. If you're worried about messing with the original data, add an extra column:

=IF(A1/B1>1,1,A1/B1)

& format as a percentage...

Hope this helps,

Paddy
 
Upvote 0
I had worked out a similair solution in the worksheet containing data but wanted to try and stay away from adding formulas and instead was hoping there was a way to do it within the chart. I will go the formula route. Thanks for the insight and help.

Waxaholic
 
Upvote 0
Waxaholic,

if you double click a %age on the y-axis, it'll bring up the "format axis" box

click "scale" and then change "auto maximum" to 1 (ie 100%), it'll chop any values >100% by only showing the max y value as 100%
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Creating a Graph/Chart needing TWO secondary axis options for a combination of Clustered and Stacked Graph Columns
Replies
0
Views
387
OrangeJuice
O
P
  • Question
Turning OFF the visibility of a horizontal (category) axis labels if certain conditions apply
Replies
1
Views
423
NdNoviceHlp
NdNoviceHlp
S
  • Question
Trendline values using Dynamic Range in Excel - comparing with LINEST function
Replies
1
Views
244
Domenic
D
P
  • Question
How do I make these two pivot charts from this specific table? I can't figure it out, and this is a very common table format.
Replies
1
Views
235
awoohaw
awoohaw
B
  • Question
Prevent Line from Continuing Point-to-Point (Non-continuous) On Scatter Plot in VBA
Replies
1
Views
472
Jon Peltier
J

Forum statistics

Threads
1,215,346
Messages
6,124,417
Members
449,157
Latest member
mytux

Share this page

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
Back
Top