|
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.
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.
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.)
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 |
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.
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