How do you guys Chart random Large Values?

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,302
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Guys,

I have a some data that is tracked on a weekly basis, I have created a bar chart to show what the figure is on each day.

Now there could be some random days where the figure is really high so that shows all the other values as really small and bar charts shrink and therefore cant see trends..

My manager wants me to either show the abnormal values as 0 and have a comment somewhere or do some other technique that ensures all data can be visually viewable and be anle to spot trends...

So what is the best way to do it and how shall i do
it.

If someone can come up with something that would be awesome..
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Thank you

For me - im trying to see a trend without the odd occasions of these high values. Which way would becthe best way to do it?

It mentions about fading and amending axis but it doesn't show how to get this fade..

If its a case off amending the axis manually, id like to amend it via code so its more robust
 
Upvote 0
If you want to totally ignore the abnormally high values, you can use a helper column to do so. Suppose my X values are in A2:Ax (x=last row), and my Y values in B2:Bx. In C2 I would put a formula like
=IF(B2>$D$1,na(),B2)
where $D$1 contains an arbitrary cut-off value that defines "abnormally high", and I would fill it down to cell Cx.

Now plot column C instead of column B. The axis will scale automatically based on the numbers in column C, which are not abnormally high.
 
Upvote 0
Hi Jon,

I have left a comment on the site (please see my comment below)
Hi Jon, thank you for your input on the charts. In my case, i may have some days that could be abnormally high or just higher than normal. With this in mind, the smaller more common values are shown very small. I have used the LOG base 10 to show all the values but like you have advised, the smaller values look like its half of the big values. I guess the best way to do it in my case is to show the larger values off the scale shaded like you have done and show the smaller values (SHOW PART OF THE DATA EXAMPLE fits best i think to show a trend of what really is happening on a day on day basis).
How can i add this shaded part for larger values and ensure the axis is updating correctly just encase i get a stupidly high value 1 day?

It would be nice to graph the the small or common values and show the axis based on that and then show the larger values off screen with it shaded just like you have done in the SHOW PART OF THE DATA example.

I dont know how to do it though :(
 
Upvote 0
Did you read my latest comment? I describe one approach that you could use.
 
Upvote 0
Hi The last bit is referring to [h=2]Think Further Out of the Box - is this the bit your referring to?[/h]
 
Upvote 0
Click on the link (repeated here). This is a comment I added to my post, after your comment. There is a quick step-by-step protocol you can follow.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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