I have created a chart representing a geological profile/cross-section. On the vertical axis you can see the height in metres above sea level and on the horizontal axis you can see the distance in metres (about 400).

Current chart

The chart is currently based on this data:

Current data

Now I want to add this data, which gives the positon of objects along the profile:

New data

How can I add this data to the chart and use the same horizontal scale, so that the objects are plotted in the middle of the diagram?

I have tried adding the data by right-clicking the chart --> Select data --> Add new data. However, it doesn't recognize the distance values belong to the existing horizontal axis. Instead, they end up on the vertical axis:

Resulting diagram with secondary vertical axis

Simply put, how do I plot the objects along the existing horizontal axis based on the values in the Distance column?

I use Excel 2016.

  • What exactly is the data for the objects you want to add, the height of the objects above sea level? And are you saying that all the objects are located at 400 on the x-axis? – Bandersnatch Apr 2 '18 at 13:39
  • The objects have no values except their names (not important) and locations along the line 400 m line. In the example above, all objects are located around the 200 m mark and should be plotted somewhere in the middle of the existing diagram (horizontally). I could give them height above sea level values but the important information is the locations along the profile. All I want to know is how to add the objects and have them show up at the correct positions along the 400 m horizontal axis. I need the program to recognize they belong on the existing horizontal scale. – Linebeck Apr 2 '18 at 13:53
  • Graphs are 2-dimensional. You're asking how to add something to a graph by specifying only one dimension. You can add points to the graph having the correct horizontal positions, but you have to decide where (vertically) you want the points to be. Are the objects on the ground? If so, then I'd use the corresponding value for the ground level at that horizontal distance. Add the points as a new series, specifying BOTH x- and y-values. After the new data is plotted, click the series and format it as "marked scatter" to convert it into points. – Bandersnatch Apr 2 '18 at 14:12
  • Thank you for your answer. I can give the objects a temporary value of 1 for the vertical axis (actually, I've already done this). However, the question remains how I get them to plot correctly along the horizontal axis. How do I get Excel to understand I want to use the same scale? – Linebeck Apr 2 '18 at 14:19
  • By the way, sometimes when I try to add new data I can only select one series of values (not both x and y). I'm not sure why that is. – Linebeck Apr 2 '18 at 14:22

I did this in Excel 2011 for Mac, so the details will be different for you, but the process went like this:

I started with an area graph similar to yours.

enter image description here

Then I added two columns of data for the objects. Here the first column is the x-axis values and the second is the y-values. The y-values will determine where the points are located vertically on the graph.

enter image description here

Next I selected the chart and used the Chart > Add Data... menu and, in the resulting dialog box, I selected just the three 10's. Closing the dialog gave me this graph:

enter image description here

I clicked to select the blue area and converted it to a Marked Scatter graph using the Scatter tab under Charts. While the data points were selected, I dragged and shrunk the pink selection area (that specifies the x-axis data) to cover only the 203 to 216 values of the added data. This could also be done under Graph > Source Data... That gave me this graph, with two new added axes:

enter image description here

Then, I selected each of the new axes in turn, and formatted the Scale to have the same Minimum, Maximum and Major Unit as the left and bottom axes. During this process, the lower axis developed gaps on the left and right sides, and to correct that I had to format that axis and uncheck the box for "Vertical axis crosses between categories". I also formatted the data markers for the objects (scatter graph series) to be round and smaller.

enter image description here

You can select and delete the graph objects you don't want. I couldn't delete the upper axis, so I formatted the font to be white. In the end, you'll get something like this:

enter image description here

I hope this helped and good luck.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.