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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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

C
  • Question Question
VBA - Creating a Dashboard with Charts including data changes
Replies
1
Views
266
Jon Peltier
J
T
  • Solved
Setting up Two Pie charts in VBA
Replies
4
Views
207
TheTiredEngineer
T
M
  • Question Question
Plot a chart based on common date from multiple date rows using VBA.
Replies
1
Views
233
myall_blues
myall_blues
Z
  • Question Question
Dynamic chart only filling half the plot area
Replies
0
Views
360
zephyrunimpressively
Z
T
  • Solved
Pull Data from Multiple Excel Files into a Summary Excel File to Create Graphs
2
Replies
11
Views
330
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,224,903
Messages
6,181,652
Members
453,059
Latest member
jkevin

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