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.


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.Because I’d been working hard on a project for many months, I once decided to take a break and play around with XY charts…which also are known as scatter charts.

Unlike line charts, XY charts plot points and lines by specifying both the X and Y values.

(You can download the workbook with these and additional example charts here.)

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.

Six XY (Scatter) charts from Excel.

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…

Table of initial values for the crazy Excel XY chartsIntroducing 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.

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.

A chart of the first 10 points of the figurePoints 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 theNew 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 theName 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 OFFSET formulas returns a range with its height determined by the yellow Pointssetting 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:


Replace the first cell address with Xplot and the second with Yplot, , giving you this formula:


Similar XY charts, but with different settingsAfter 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 table that generates the triangle shaped XY chart in Excel.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.

Additionally, you can switch between straight lines and smoothed lines for your plot. You also can adjust both the number of points in the charts and it’s scaling. To do so, adjust the values in yellow, as shown in the figure above. Doing so will change the appearance significantly, as shown in the right column of stars in the examples below.

Finally, in my  download workbook that’s available at this link I’ve included a page of random shapes, as shown in the first two columns in the figure below. Every time you press F9 you’ll get another shape.

Have fun with your charts!

These examples show two random plots and one star, with straight and smoothed lines.