  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, MBAMicrosoft Excel MVP, 2005-2014 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. 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 top-left chart above, and for the simple chart below the table.

Point 1 in the table is at the bottom-left corner of the blue square, where both the X and Y values equal zero. Point 2 is at the top-left 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-(B5-B6)*Scale
C10:   =C5-(C5-C6)*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)

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. Click to see testimonials from readers Click to see who uses Excel dashboards. 