Chart Techniques
Fun with XY (Scatter) Charts in Excel
Excel's XY
(or "scatter") charts provide amazing power. This introduction to XY charts offers a fun way to learn more about this
powerful chart type.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

Because I’ve been working hard on a project for many months,
I decided to take a break recently, and play around with XY
charts…which also are known as scatter charts.
These charts are the result of my play time.
Honestly, I can’t think of a practical use for any of them,
but they definitely are fun to play with.
You can
download a workbook with these charts
here.
The first two charts above are from the same chart object,
but with two different settings. The last chart relies on random
numbers for its initial data. And the remaining charts show the
same charting techniques applied to different shapes.
My inspiration for these designs came from an article that
Daniel Gasteiger wrote in LOTUS magazine about 30 years ago,
when I also wrote for the magazine.
Normally, if I were going to display these figures as an
example of computer art, I’d hide the X and Y axes of the
charts. But I included the axes so you could see that they
really are Excel charts.
So let’s create one of these charts…
Introducing the Square XY Chart
Unlike typical line charts, each point of an XY chart
requires two values: an X position and a Y position. And if the
chart shows lines, like the charts above do, the chart requires two pairs
of values for each line: a starting point and an ending point
for each line segment.
To illustrate, the first five points in this table define the blue
square that outlines the topleft chart above, and for the
simple chart below the table.
Point 1 in the table is at the bottomleft corner of the blue
square,
where both the X and Y values equal zero. Point 2 is at the
topleft corner, where the X value equals 0 and the Y value
equals 1. These two sets of points define the blue vertical line
at the left side of the chart’s Plot Area.
The remaining three points draw the rest of the square, completing
it at point 5 in the table...a point that has the same values as
point 1.
Points
6 through 10 define the position of the next box, which is
slightly rotated, as shown in this figure.
The Scale setting above the table specifies about how large each new
rotated shape should be compared with the shape that precedes it.
Here, for example, the inner box is 93% of the size of the outer
box.
How to Create the Square Chart
To begin, launch a new workbook with one worksheet, and name
that sheet Square. Then enter the numbers, text, and formatting shown in
rows 1 through 9 of the table above.
Assign range names to the two settings marked by the yellow cells.
To do so, first select cell C1. Choose Formulas, Defined Names,
Define Name (or press Ctrl+Alt+F3) to launch the
New Name
dialog. Change the Scope setting to Square; make sure that the
Name text is Points; then choose
OK. Now select cell C2 and
repeat the process to define the name Scale.
Now enter these formulas for the cells shown:
A10: =A9+1
B10: =B5(B5B6)*Scale
C10: =C5(C5C6)*Scale
Copy the range A10:C10 down the column for
at least 200
rows. (The exact number of rows isn’t critical. Add even more
rows if you want.)
To create the chart, first select the range from cell B5
through the bottom of your formulas in column C. (So, for
example, you might select the range B5:C200.)
Now, in your Ribbon, choose Insert, Charts, Scatter, Scatter
with Straight Lines. And after Excel creates your chart, cut and
paste it to the top area of your worksheet, if necessary.
Select and delete the chart’s legend, title, and gridlines,
as needed. Then click on one of the chart axes and press Ctrl+1
to launch the Format Axis dialog or side panel. Set the minimum
axis option to 0 (zero) and the maximum value to 1. Then do the
same for the other axis.
Now resize the chart object so that it’s approximately
square.
Set Up the Dynamic Range Names
After I created the first chart, I set up a way to control
how many points the chart would display. Doing so gave me two
benefits. First, I could experiment with both settings in yellow
to change the appearance of my chart significantly. Second, I
could step through each point in the chart, comparing the values
in the table with how Excel displays each new line in the chart.
To control the number of points that the chart displays, I
set up two dynamic range names. To set up these names, press
Ctrl+Alt+F3 again to launch the New Name dialog. Enter the
Name and Refers
to formula shown in the first line below. After you choose
OK,
repeat the process with the second Name and its
Refers to formula.
Xplot
=OFFSET(Square!$B$4,1,0,Square!Points,1)
Yplot
=OFFSET(Square!$C$4,1,0,Square!Points,1)
(Click
here to find help with Excel's OFFSET function.)
Each of these formulas returns a range with its height
determined by the yellow Points setting in the worksheet named
Square.
To test that you’ve set up these names correctly, press
F5 to
launch the Go to dialog and enter Xplot as the
Reference. Excel
should select the range specified by the formula above. Then
repeat this test with the Yplot dynamic range
name.
Once you confirm that the two dynamic range names are working
correctly, you need to set them up in the SERIES formula in your
chart.
To do so, first click on a line in your chart. When you do
so, your formula bar should display a formula that looks somewhat like
this:
=SERIES(,Square!$B$5:$B$204,Square!$C$5:$C$204,1)
Replace the first cell address with Xplot and the second with
Yplot, , giving you this formula:
=SERIES(,Square!Xplot,Square!Yplot,1)
After you press
Enter, you can adjust your chart settings
easily.
For example, the first chart in this figure relies on a
setting of 100 Points and a Scale of 85%.
And the second chart relies on a setting of 200 points
and a Scale of 98%.
How to Create Other Shapes
The
octagon, star, and triangle, at the top of this page use a
different number of values to begin the data table that controls
their chart.
Here, for example, the table for the triangle has four points
rather than five points used by the square. And the table for
the octagon has nine points. In all cases, the value for the
last point is the same as the value for the first point.
You can experiment with other shapes in your charts. You'll
just need to enter X and Y values for each point need to draw
the shape.
Again, you can
download the workbook with
the charts
here.
