Plot X and Y Coordinates in Excel

Of course, since it’s a chart series it automatically updates. So if the desired x-value is updated, the horizontal and vertical markers update as well.

Excel koordinat sistemini necə bilirəm

The file PlotXYcontains routines to plot scale drawings in Excel from a list of XY coordinates of node points, node points for each shape, and formatting details for each shape type. The plot is scaled to fit within the selected plot range and, unlike Excel charts, is scaled equally in the X and Y directions, so circles will stay circular. Shapes may either be defined as polylines, with a series of points, or by using any of the built in Excel shapes.

Examples of the output are shown below, and data for the first three is included in the download. The final example was plotted from about 250,000 node points, using over 12,000 polyline shapes (using Excel 2007), illustrating that this tool is capable of producing drawings from very large data sets.

Super T bridge beam cross section

Finite Element Mesh for a Buried Precast Arch Structure

Concentric squares, or a square spiral?

Detail of a very large finite element model of a bat skull

Any comments or requests for additional features? Please let me know.

Share this:

Like this:

Like Loading.

Related

This entry was posted in Drawing, Excel, VBA and tagged Drawing, Excel, shapes, VBA. Bookmark the permalink.

25 Responses to Drawing in Excel 7 – Creating drawings from coordinates

rbruins says:

First of all, thank you for these routines. I’ve been dealing with drawing in excel for quite some time. Mostly in structural engineering. And now I will never have to start and make boundary’s (x- x+ y- y+) scaling factors and drawing ranges, keeping the correct coordinates. Now is it just: start and select. After trying these routines. I have found the following tell me your opinion on this: Basicly I’ve found that us engineers see the world in nodes en lines. The idea of lines are actually easy, two points and you’re done. The node is actualy different. It is infinitaly small, so we can’t see it. So we make a circle fill it up, (thus defining 2dimensions). However there are different kinds of nodes. The mass for instance is big. A keypoint is smaller. and a node is smaller. Here the problem in the routine starts.
Lets say I want a mass node on coordinate x,y, with height (h) and width of 10. And defining it a circle (shape 9 if i remenbered it correctly). The routine starts at coordinate x,y and makes the circle. Thus placing the center at the coordinate at x+h/2 y+h/2. With a small value of h this isn’t a problem. You’ll won’t see that. but with large values you will. And one closely spaced on the outer border will run out of the drawing area, and find problems with deletion! To make a workaround. You’ll have to make new node coordinates based on the type of shape you want. and taking into account the size of the shape. And thus increasing the number of totale node coordinates. And increasing error posibilities. This plea is to create an new shape definition. Called a node with a single size. Incoorporate the workaround. I would also recommend creating a buffer area around the innerborder to prevend deletion difficulties. Kind regards, Rick Bruins Like Like

dougaj4 says:

Rick – thanks for the comments. Good point about the offset you get with shapes. Rather than create a new node shape type it seems to me that it would be better to modify the code so that the centre coordinates can be entered for circles, and the code calculates the top left coordinate. Arcs are also a bit of a problem because they are handled differently in XL 2007 to previous versions. (more details here .. https://newtonexcelbach.wordpress.com/2008/07/26/drawing-in-excel-5-shape-list/), so some code to detect the Excel version and adjust the arc shape accordinly would be useful. Time permitting, I’ll try and get a new version posted with these changes in a week or two. Finally on the buffer, the routine for checking the scale at the moment just checks the limits of the nodes, without making any allowance for the size of the shapes. The easiest workaround is to specify an invisible line from one corner to the opposite one, ensuring that the limits of this line are outside the limits of all the shapes. Alternatively you could go into the VBA code and either increase the value of the constant cMargin, or reduce the value of UScale. Doug Like Like

Alvin Piano says:

Can anyone show me a quick tuturiol of even how to “draw” on excel? That would be great! Currently, my task in school is requiring me to draw a shape of an I-Beam. Like Like

dougaj4 says:

Hi Alvin – In 2007/2010 go to the Insert Tab, click on the Shapes Icon, and have a play with the shapes there. You could just draw three rectangles but also try the “Freeform” shape and then try editing the corners with the “edit shape” icon. With the freeform shape you click in each corner of your shape, then double click to indicate the last point. If you double click close enough to the first point it will draw a closed shape. One trick useful for your exercise is to hold down the shift key as you draw each line segment. This will force the line to be either horizontal, vertical or at 45 degrees. Let us know if you have any problems. Like Like

Plot X and Y Coordinates in Excel

If you have a scatter plot and you want to highlight the position of a particular data point on the x- and y-axes, you can accomplish this in two different ways. As an example, I’ll use the air temperature and density data that I used to demonstrate linear interpolation. In the scatter chart below, the blue line represents the available data points. The intermediate green point on the line was interpolated from the available data.

It would be nice to know where that data point falls on the x- and y-axes, so let’s look at one of the ways to do that:

Table of Contents

• Plot XY Coordinates in Excel by Creating a New Series
• Plot Coordinates in Excel Using Error Bars

Plot XY Coordinates in Excel by Creating a New Series

The lines extending from the x- and y-axes to the interpolated point (x-value, y-value) can be created with a new data series containing three pairs of xy data.

Those pairs are as follows:

The first and second pair of data points comprise the horizontal line from the y-axis to (x-value, y-value) and the second and third points make up the vertical line extending upward from the x-axis.

So if we start with the data from our table of air density and temperature, then add a second series with those pairs of data (using a scatter plot with straight lines and markers), we get the following:

Then we can add a data label and change the horizontal and vertical lines to dashed lines for better readability:

Of course, since it’s a chart series it automatically updates. So if the desired x-value is updated, the horizontal and vertical markers update as well.

We could also use this method for multiple x/y pairs also:

Engineering with Excel

Are you struggling to the find the right solutions to your engineering problems in Excel?

In Engineering with Excel, you’ll learn Excel for advanced engineering calculations through a step-by-step system that helps engineers solve difficult problems quickly and accurately.

Plot Coordinates in Excel Using Error Bars

I’ve used the above method for a long time, but the next method was recently introduced to me by Jon Peltier of PeltierTech.com. Instead of using a series to create the horizontal and vertical lines on the scatter chart, his method is to use error bars.

It still starts by adding a second series to the chart. But this time only select the individual x- and y-values to create a series with a single data point.

Next, we can add percent error bars to this single-point data series. To add error bars in Excel 2013/2016, with the data series selected, click the green plus sign to the right of the chart, select the box next to “Error Bars” and choose the percentage type.

Next, right-click on either the x- or y- error bar and choose “Format Error Bar”.

Change the direction to “Minus” and the percentage to 100.

Repeat the same for the error bar in the other direction to get the following chart:

Of course, you can also add a data label to this data point and change the color and/or style of the error bars.

Convert Latitude and Longitude from Decimal to Degree, Minute, Seconde

Nowadays, GPS localization is common. Some apps use decimal format (48.85833) while others return the coordinates in degrees, minutes and seconds (48°51’29.99”).

In this article I will show you how to convert from one format to the other and vice-versa.

Add GPS coordinates to address

If you are looking to add GPS coordinates to your address, please go to this page.

How to write a GPS coordinates in Excel

GPS coordinates are generally written using the symbols ° (degrees), ‘ (minutes), and ” (seconds).

But if you keep your coordinate as a string, no calculation is possible. So it will be impossible to convert the GPS coordinates ⛔⛔⛔

What is the best way to write a GPS coordinate?

Use the following format for your GPS coordinates – hours:minutes:seconds. Each part of the coordinate should be delimited with a colon “:“

The default cell display could be different depending on the version of Excel that you are using. It can display the GPS coordinates as either a date or a time.

Don’t worry, we will correct this formatting soon ������

Insert a negative time

The standard GPS coordinate is:

Unfortunately, Excel doesn’t accept a negative time, so you need to wrap it in double quotes as though it were text. Then put a minus sign before the opening quote.

But the cell content is displayed as a string of #

Excel doesn’t like negative time. But you can correct this by checking one option in Excel.

• Go to the menu File>Options
• Select Advanced
• At the bottom of the list of options, check the option “Use 1904 date system“

Customize the time format

• Open the Format cell dialog box (Ctrl + 1)
• Select the option Custom
• In the Type box, enter this format code exactly as it is displayed below

The hour between square brackets is compulsory to display a value over 23 hours.

Convert coordinates from Degree, Minute, Second (DMS) to decimal

Because we have written the coordinate as a number, we just have to multiply the cell by 24 (24 hours in a day). The number format is General.

And the the conversion is finished ������

Then just change the format of the cell range D3:E7 to General.

Convert coordinates from decimal to Degree, Minute, Second

And the other way round, to convert a decimal value to degrees, minutes and seconds, simply divide the value by 24.

12 Comments

Can’t figure out how to input conversion formula from this.

kindly provide me any kind of excel format or program for convert arbitrary coordinates to GPS coordinates.

Hi, This seems like a great solution but I’m obviously doing something wrong. I’m converting lat and long from decimal and it works fine until the minutes are over 24 and then they reset to zero. The minutes and seconds remain correct though. Please help. Thanks

Frederick, thank you so much! It works! You saved hours of my working time!

Hi! Thank you very much for the article. It was very useful for me.
How can I write the latitude with degrees, minutes and decimals of minutes?
Just like u wrote here [hh]°mm’ss.00\”, but I want the decimals after the minutes like 10°50′.5
The minutes need to be divided by 6 to obtain decimals.
Second question: how do I format the cells so when I calculate latitude 1 + latitude 2, when I have 10 decimals at the end to go automatically in minutes.
Example : 10°50′.5
+ 20° 5′.6
=30° 56′.1
Thank you very much!

Thank you, I tried many tutorials using VBA but failed. you are such a Genius

I can not make Excel recognize the custom format ..[hh]°mm’ss.00\ it only lets me pick from a select list of formats, none of which is right. Is it my version of Excel?

Frédéric LE GUEN
26/11/2019 @ 15:05

Hi, that’s strange. Do you select custom in the list of format ?

Been reading so many VBA scripts on how to do this DMS to DD conversion within excel
And even Microsoft’s publication of their own VBA script on how to do this
https://support.microsoft.com/en-ca/help/213449/how-to-convert-degrees-minutes-seconds-angles-to-or-from-decimal-angle
Even does the calculation part wrong Wasted so much time trying to remedy their script, and read other scripts on the internet.
Your solution was implemented in 1 minute, so so simple, and brilliant.
Thank you again so much for posting this.

Frédéric LE GUEN
23/07/2019 @ 07:19

GENIUS!! Thanks for sharing
Btw: it misses a double quote at the end of the formula written in black background ([hh]°mm’ss.00\” )

kindly provide me any kind of excel format or program for convert arbitrary coordinates to GPS coordinates.