Skip to Content

I am pretty sure that most of us have encountered with the below problem while working with line charts/barcharts in   Dashboards ie, the y-value are repeating for the graph, or the value plotted is not correct.

Suppose the values to be plotted in the graph is as shown in below.

Excel values.JPG

The value plotted in graph will be as shown below

First image.JPG

ie, the value 2 is plotted  in between 2 and 3, instead of plotting at the line of 2 itself.

It is because the height of the graph is less. If we increase the height of the graph, the graph looks like as shown below.

Second image.JPG

Here the Y-axis values are repeating.

Giving decimal values to the scale will not solve the problem, and it is not acceptable at certain scenarios (Eg: plotting age, count etc..)

In order to avoid these problems, we have to customize the scale.

Below steps explains how we can customize the scale.

1.    Right click the line chart and go to the behavior tab.

       By default, ‘Auto(Y) axis’ is selected. We have to change it to ‘Manual(Y) Axis’ for customizing the scale

2.    ‘Size of Division’ and ‘Maximum Limit’ and ‘Minimum limit’ (If ‘_’ ve values needs to be plotted) are the parameters to be set.

       While selecting the ‘Size of division’ and ‘Maximum limit’ and ‘Minimum limit’ we should be careful.

3.    Below steps can be used to find the ‘Size of division’ and ‘Maximum limit’

       a)    Find the ‘Maximum’ and ‘Minimum ‘values of the numbers to be plotted in the graph.

              Let Maximum be ‘Max’ and Minimum be ‘Min’

       b)   If (Max<10 or ((Max-Min)/2)<10), then S=Max/2

              Else S= (Max-Min)/2,   Where S is ‘Size of Division’

              Round down the value to the nearest integer.

      c)    Find the remainder after the ‘Maximum’ value divided by ‘size of division’

             We can use the ‘mod’ function available in excel for this.

             Remainder (R )=mod(‘Max’,’S’)

     d)    The Maximum Limit ‘MaxLim’ can be found by using the formula

             MaxLim=Max-R+S

     e)    If ‘-‘ve values needs to be plotted, we have to find the Minimum Limit value .(We can use the steps ‘c’ and ‘d’ with some changes to find the ‘Minimum limit’)

             If ‘-‘ ve value are not there, we can set the ‘Minimum Limit’ to 0.

4.    As the last step we have to map the ‘Maximum Limit ‘ to ‘MaxLim’ and ‘Size of Division ‘ to ‘S’

       Manual-Y-aix.jpg

      Now the values in the graph are plotted correctly.By using the ‘Mouse –Over values’ property we can see the exact value of each point as shown below

     Mose over image.jpg

The above implementation worked fine, for all scenarios in our recent project.

Hope this will be useful…

      

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Kailas Kurup

    This is a great piece of information. We usually stick to auto-scale most of the times and for any of the scenarios mentioned in the article, we would say it’s the scale that best fits the data values plotted on the chart and xcelsius best decides these scale – tool limitation.

    Great job Anu for thinking out of the box and coming up with this super-useful workaround.

    (0) 

Leave a Reply