<-- Return to Excel Statistics Page

How to Draw and Ellipse in Excel

Keith Greiner

August 9, 2020

This essay is about how to plot an ellipse in Microsoft Excel. Although Excel is ubiquitous, it's typical use has to do with collecting and analyzing accounting operational and economic data. Those are data that are represented in rows and columns of numbers. Its powerful tools allow users to perform statistical analysis and to create a multitude of charts.

While the widely used forms of data are important, and there is a great deal of public available information on how to use Excel for operational and economic data, very little information is published to show how to graph geometric shapes.

Below, I will describe how to plot an ellipse and how to move that ellipse around on an Excel chart. Once you get your spreadsheet set up, the application of this kind of spreadsheet is limited only by your own creativity.

An ellipse is a circle that has been modified to contain one major axis (denoted as the a axis) and one short axis (denoted as the b axis). If a = b, the same rules apply but the drawing is a circle.

There are several equations for an ellipse. First is the conventional equation. Below is a typical presentation of the equation, where we show the left side set equal to 1 on the right.

The above equation has its center at the point (0,0) on the chart. If we want to shift the ellipse away from (0,0), the formula becomes the following.

Solving for y, we have the equation shown below that can be used to graph values of y, given x and several other needed parameters.

When applying this equation, you supply the values of x, a, b, h, and k where:

x is the x value of the graph,

a is the major axis length of the ellipse,

b is the minor axis length of the ellipse,

h is the offset for the x axis, and

k is the offset for the y axis.

Another two equations make up the parametric formula for an ellipse. Here, the x and y values are determined by their sine or cosines relationships with an angle that has its center at (0,0). This is the equation used in Excel Image 1 and Excel Image 2 below.

X = a * cos(t)

Y = b * sin(t)

If we want to shift the ellipse away from (0,0), the formulae become the following.

X = h + (a * cos(t)

Y = k + (b * sin(t))

Each ellipse has two focal points along the longer, a, axis. The focal points may be found using the following equation.

...where c is the distance from the central point (0,0) to the left-hand focal point.

The eccentricity of the ellipse is the ratio of c to a.

If the values of a, and b are equal, the c value will be zero and the eccentricity value will also be 0.0, and the ellipse is a circle.

Below are two images of ellipses that were drawn in Excel using the data in Excel Image 1 and Excel Image 2.

First is the Excel graph of an ellipse.

... and next is the ellipse after rotation of a few degrees to the right.

When an ellipse is drawn in Excel the automatic formatting can affect the appearance of the graph. Some graphs may appear slightly different from what you expect. Adjustments to the size of a graph that is displayed on a worksheet can change the appearance of the ellipse. You can even make an ellipse look like a circle or vis versa.

Below are two images from Excel. Excel Image 1 shows the calculation of an ellipse in columns D and E, and a rotated ellipse in columns F and G. The rotation is accomplished by using the =MMULT(...) function to multiply the range, D18:E58 by the rotation matrix in C9:D10. In Excel Image 1, you see numbers in C9:D10. Those are the values returned by sin(...) and cos(...) functions that are, in turn based on the number of degrees entered into cell D. See Excel Image 2 for the Excel coding that accomplishes that in range C9:D10.

Excel Image 2 shows the Excel code for Excel Image 1. Here, we see that D5 includes the input number of degrees that the ellipse will be rotated. D6 includes the code to covert degrees to radians. Cells C9:D10 include the rotation matrix of sin(…) and cos(…) functions. Range C18:C58 sets up degrees of a circle as coordinates in increments of 9 degrees. Range D18:E58 draws an ellipse using the parametric equation for an ellipse. You can graph this range using a scattergram and connected dots to see an ellipse. Range F18:G58 includes the Excel code of =MMULT(D18:E58,C9:D10) to multiply range D18:E58 by the rotation matrix of C9:D10. Draw this as a scattergram to see the rotated ellipse.

Following are instructions on how to rotate the ellipse described in Excel Image 1.

1. Cell Range D18:E58 contains the x and y values to be multiplied by the range C9:D10.
2. Cell D5 and D6 define the angle of rotation. D5 has the angle in degrees, and D6 uses the =RADIANS(…) function to convert the degrees to radians.
3. Cells C9:D10 contain sin(…) and cos(…) functions to form the matrix to be multiplied by range D10:D225 in the steps that follow.
4. Now, set up the range to receive the results. Put the headers in rows F17 and G17, and enter the “x” and “y” into those cells.
5. Now, enter the following code into cell F18. =MMULT(D18:E58,C9:C10)
6. If everything else has been set up according to the example and instructions, then you will be setting up to multiply array D18:E58 by the rotation matrix C9:C10.
7. With the Excel code entered into F18, now highlight the entire results range of F18:G58.
8. Keeping that range highlighted, go to the formula edit bar above the sheet, and click somewhere in the code.
9. Now do this. Click on Shift+Control+Enter simultaneously. On a Mac computer you can also, click on Shift+Command+Enter simultaneously.
10. You should see the entire results range fill with numbers and the Excel code you entered into cell F18.
11. Now, create two scatter graphs of ranges D18:E58 and F18:G58 (separately), and watch for the interesting results. When you set up the graphs use the sequence of selections (at least those were the selections on my vision of Excel -- watch for possible differences in yours)...
    1. Chart
    2. Scatter chart
    3. Smooth Lined Scatter
12. Enter new values into the degrees cell, D5 and watch the graphs redraw. When you enter 90 degrees, you may notice that the trend lines are not fully 90 degrees apart. This is because of the auto-formatting used by Excel

For information on array functions and how to do the matrix multiplication, see either of these two links on this site:

• Data Rotation via Matrix Multiplication
• How to use Excel Array Functions

<-- Return to Excel Statistics Page

-30-