Keywords:
chart show some few selected select limit limited
marker data label datalabel
| When plotting a lot of data,
one might want to show a data marker only every so many points
as shown in Figure 1, which contains a plot of 100 data points
but only every 5th one has a marker. Similarly, it is
possible that one wants to show a data label only every so many points.
|
Figure 1
|
| The reason for doing so
might range from aesthetics to necessity. If plotting a
large number of points, it might be necessary to do something
other than use the Excel
default of showing a marker for every point since this might make the
chart indecipherable. As shown in Figure 2, all the data
markers run into each other and make the chart hard to read. |
Figure 2
|
| Below are three ways to
create the chart in Figure 1.
Use an extra column
Use a named
formula
Use a named
formula that that doesn't include any #N/As
The data for this tutorial includes the
numbers 1 through 100 in cells A1:A100 and the same numbers
adjusted by a random factor in
cells B1:B100. Column A contains the x--values in the XY
Scatter chart and column B the y-values. A sample of the
data set is shown in Figure 4.
The aim of the tutorial is to show a
marker for every
fifth data point, starting with the first point.
Effectively, the chart will have markers for the first data
point, the sixth point, the 11th, 16th, etc., all the way up
to the 96th.
|
Figure 4 |
|
Use another column
Since Excel doesn't interpolates over any cell that
contains a #N/A, create a second data set from
the first set of y-values such that a non-NA is found after
only every-so-many cells. In C1, enter the formula
=IF(MOD(ROW(),5)=1,B1,NA()) Copy C1 down to all cells up to C100.
The
5 in the formula means that every fifth cell in column C will
contain a real value.
Now, plot A1:C100. Make sure it is
a XY Scatter chart and that column A represents the x values.
Format the first series (corresponding to column B) to show a
line but no markers, and the second series (corresponding to
column C) to have no line but an appropriately sized marker.
[To format a series, double-click it and then in the resulting
dialog box select the Patterns tab.]
|
Figure 5 |
|
Use a named formula
To avoid showing a column largely filled
with #N/As, use a named formula. From Insert | Name >
Define... create a new name, say
SomeCells and set the 'Refers To' field to
=IF(MOD(ROW(Sheet1!$B$1:$B$100),5)=1,Sheet1!$B$1:$B$100,NA())
Next, create a XY Scatter chart with the
data in columns A and B. Now, plot a new series so that
the y-values are this name. For more on how to use a
name in a chart see
this tutorial.
The result should look like on the right.
|
Figure 6 |
|
Use a named formula that that
doesn't include any #N/As
Using a separate column (or a named formula
that provides the same functionality) is one way to go.
However, it is possible to create a data set with only
the non-#N/A values. This named formula is much
more complex, and it requires the use of a complementary named
formula for the x-values.
Create two named formulas (Insert | Name
> Define...). The first, called
NoGapCells is
=N(OFFSET(Sheet1!$B$1,(ROW(OFFSET(Sheet1!$B$1,0,0,ROWS(Sheet1!$B$1:$B$100)/5,1))-1)*5,0,1,1)),
and the second, named NoGapXVals,
is
=N(OFFSET(Sheet1!$A$1,(ROW(OFFSET(Sheet1!$B$1,0,0,ROWS(Sheet1!$B$1:$B$100)/5,1))-1)*5,0,1,1))
Next, create a XY Scatter chart with the data in columns A and
B. Now, add a new series where the x-values and y-values
are specified by the names NoGapXVals and NoGapCells,
respectively. The resulting chart is shown in Figure 7.
Figure 7
|
| How does this work?
x=ROWS(Sheet1!$B$1:$B$100)/5
is just 100/5 or 20
y=ROW(OFFSET(Sheet1!$B$1,0,0,x,1))
creates the array (1,2,...,20}
z=(y)-1)*5
creates the array {0,5,10,..., 95}
w=OFFSET(Sheet1!$B$1,z,0,1,1)
creates a special type of array of the
contents of cells 1, 6,..., 96.
and, finally
=N(w)
converts the array into a form that can be
used in a chart.
|
|
|
|
