Advertisment

Resolving Graphing Problems in Excel

author-image
PCQ Bureau
New Update


Advertisment
Direct Hit!
Applies to:

Business Excel users
USP:

Solutions to unique graphing problems
Primary Link:



www.ncsu.edu/labwrite/res/ gt/gt-menu.html

Google keywords:

excel graph help

While Excel is an excellent tool to make graphs, sometimes you need to know the work arounds to depict only the relevant data. We tell you how to do that for some typical problems

With options from standard bar graphs to complex bubbles

and multi-axes charts, data can be presented in various formats.

However, many a times we need solutions to work around unique problems.

We discuss some such work arounds for three graphing problems in this

article.  One of them is avoiding

blank values from being plotted in line graphs.

It may so happen that for a long time there are no data points available.

Line graphs take these blank cells as 0 (zero) and, therefore, drop the

line to intersect with the X-axis when a blank cell is encountered.

Another problem is changing the format of Category Y-axis values

depending upon the variable being plotted, ie, percentage or values.

The third problem deals with avoiding showing the legend when all the

data for a variable is deleted.

Advertisment

Keep blank values away



The original data is in the form shown in the following graph. As can be

observed, the graph for 2004 starts from the X-axis signifying that the sales

for that year is zero.  However,

it's not true.  This may be

incorrect especially when comparing data.  For

instance, if sales data is being compared across various companies and one of

them (Company A) came into existence in 1998 (first year of sales for A) then

plotting zero sales for Company A will be incorrect.

The solution to this problem is to insert an error value if

there is no data to plot. Therefore, if cell B12 has 0 or is blank, cell B4
should show an error value.  The

formula in cell B4 is then IF(B12= 1,100,NA()).

Format of Y-axis values



The Y-axis format should be flexible enough to accommodate different types

of data. That is, if the user desires to show percentages along the Y-axis, the

graph should depict percentages and if he desires to show numbers, it should

show numbers.

Advertisment

The problem here is that the graph shows numbers even if we

select to show percentage.  One may

change the Y-axis to numbers if the range B3:B8 is toggled between number and

percentage format.  One can shift to

percentage format by using the Ctrl+Shift+5 shortcut key and convert to numbers

by using Ctrl+Shift+1.  However, one

has to keep doing this when the range B3:B8 changes.  The task is, therefore, to de-link the number format depicted

in range B3:B8 from the range of numbers in the Y-axis. That is, the graph

should be able to recognize that the range B3:B8 is in percentage format even

though the range is depicted in number format.

To solve this problem, change the number format on the

Y-axis to percentage when all the values in the range B3:B8 are less than 1 and

change it to number format when all the values in the range B3:B8 are greater

than unity.  Values that are zero

are depicted in the number format. 

The assumption inherent herein is that the percentage never

exceeds 100% (which will be true here because we are talking of percentage of

sales.)  

Advertisment

To achieve this, right click on the Y-axis bar on the graph

and select 'Format Axis'. Then select Custom in the 'Category Selection'

box. In the Type box, enter the following formula:

 “<=0>0;<<1>0%;General”.

B11 is not equated to 0 or left blank for no data and accordingly B4 changes. Formula in B4 is IF(B11=1,100,0) An error value in cell B4 causes the graphing range to start from B5


The data in range B3:B8 is currently linked to the range B11:B16. Hence, the range in Y-axis is numbers The format of the Y-axis is changed to account for the change in formats from numbers to percentage and vice versa
Advertisment

Conditional formatting



Assume that you want to plot a line graph with two variables and you would

want to show legends for each variable.  But, if you decide not to show the Region 1 sales and,
therefore, delete the entire column, the corresponding legend will still show at

the bottom of the graph as shown in graph 6 below.

You can avoid this by using conditional formatting and

doing away with the standard legend that appears with the graph.To delete the

standard legend, right click around the legend area and select Clear. 

Now to employ conditional formatting and assign the legend

to range C4:C9, while in cell M9, go to Conditional Formatting>Format.

Advertisment

In the left box, select 'Formula Is' and in the right

box, type in “=COUNT ($C$4:$C$9) >0”. Click on Format and select the

color of the line graph from Patterns tab and finally click on OK.

Follow the same procedure (in cell M8) for showing the

legend for data in range B4:B9. Now if all values are NA() in the range C4:C9,

the legend will not appear and for even one value in the range, the legend will

appear.

Region 1 depicts the sales of that region. The legend against the two variables appears with the graph While the range 1 numbers are deleted, the legend still appears at the bottom of the graph


This formula ensures that even if there is one value in the range, the legend would appear The legend shows in M8. In N8, one may type in 'Region 1' and apply conditional formatting with =COUNT($C$4:$C$9)=0





Ashish Mathur

Advertisment