Perfect square plot area, perfect square gridlines

posted Nov 29, 2013, 4:21 AM by Krisztina Szabó   [ updated Jan 24, 2014, 1:24 AM ]

Freddy: What a filthy mess.
Igor: I don't know, a little paint, a few flowers, couple of throw pillows [...]


by The FrankensTeam

In our latest post about chord diagram we combined xy scatter and pie charts. We observed that Excel adjusts the plot area of the scatter chart to be the same as the plot area of the pie.
The pie plot area is always a perfect square, as you see on the picture below. This means that combining the two charts the xy scatter plot area becomes a perfect square too, and more importantly the shape will not change if you add other chart elements like title or legend.


xy scatter plot area as perfect square:


How to do it?

1. create a xy scatter chart.

2. add a new data series using fix values (for example 1) for the x and y series too:
UPDATE: if you add the series with 0 values, so ={0} for both X and Y, you will have an invisible pie chart, no need to set the colors and borders to invisible.

3. Select this series and change the chart type of it to pie:

in Excel 2013:

in Excel 2010:

4. set the color of the pie to blank and the border to no line.

5. change the axes scaling according to your needs.

Why is it good? By adjusting the scaling settings of the axes you can easily create a grid of perfect squares. It makes easier comparing the values of x and y axis, and also makes easier to use Excel to draw perfect circle using coordinates, and other geometry drawings.