Free Microsoft Excel 2013 Quick Reference

Moving data points in chart Results

how do you click and drag a data point in an excel chart?


I have ran into a problem with some code that I have been using. Currently, the code is in a class module of an embedded chart.

The code was intended to find a data point that was clicked on. Once found, it will move the data point to where the user moved it, in the vertical plane only, using a mousemove subroutine. It seems to work fine on the machine I am using, screen resolution of 1280x1024 pixels at a zoom of 100%. At this zoom level and screen resolution, the embedded chart will fit entirely in the window when the tab of the chart worksheet is selected. However, at various other screen resolutions/zoom levels, the chart doesn't fit entirely in the window. When this happens, the movement control used in the mousemove subroutine doesn't work right. The data point will move, but will move so fast that the autoscaled chart will change it's scale, which in turn will cause that datapoint to move faster through ever increasing chart scales. I have turned the auto chart scaling off but the data point will just move out of veiw of the scale that was there. I am including some of the code that is used. Any suggestions on how to correct this would be greatly appreciated.


Private Sub myChartClass_MouseMove(ByVal Button As Long, _
ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

Dim IDNum As Long
Dim a As Long
Dim b As Long
Dim PlotArea_InsideLeft As Double
Dim PlotArea_InsideTop As Double
Dim PlotArea_InsideWidth As Double
Dim PlotArea_InsideHeight As Double
Dim AxisCategory_MinimumScale As Double
Dim AxisCategory_MaximumScale As Double
Dim AxisCategory_Reverse As Boolean
Dim AxisValue_MinimumScale As Double
Dim AxisValue_MaximumScale As Double
Dim AxisValue_Reverse As Boolean
Dim datatempx As Double
Dim datatempy As Double
Dim Xcoordinate As Double
Dim Ycoordinate As Double
Dim X1 As Double
Dim Y1 As Double
Dim height As Double

ActiveChart.GetChartElement x, y, IDNum, a, b
If IDNum = xlPlotArea Then
If drag1 = True Then

If Button = xlPrimaryButton Then

On Error Resume Next

X1 = x * 75 / ActiveWindow.Zoom
Y1 = y * 75 / ActiveWindow.Zoom

height = ActiveSheet.ChartObjects("Chart 1").height
With ActiveChart

PlotArea_InsideLeft = .PlotArea.InsideLeft + .ChartArea.Left
PlotArea_InsideTop = .PlotArea.InsideTop + .ChartArea.Top
PlotArea_InsideWidth = .PlotArea.InsideWidth
PlotArea_InsideHeight = .PlotArea.InsideHeight

With .Axes(xlCategory)
AxisCategory_MinimumScale = .MinimumScale
AxisCategory_MaximumScale = .MaximumScale
AxisCategory_Reverse = .ReversePlotOrder
End With

With .Axes(xlValue)
AxisValue_MinimumScale = .MinimumScale
AxisValue_MaximumScale = .MaximumScale
AxisValue_Reverse = .ReversePlotOrder
End With

End With

datatempx = (X1 - PlotArea_InsideLeft) / PlotArea_InsideWidth * _
(AxisCategory_MaximumScale - AxisCategory_MinimumScale)

Xcoordinate = IIf(AxisCategory_Reverse, _
AxisCategory_MaximumScale - datatempx, _
datatempx + AxisCategory_MinimumScale)

datatempy = (Y1 - PlotArea_InsideTop) / PlotArea_InsideHeight * _
(AxisValue_MaximumScale - AxisValue_MinimumScale)

Ycoordinate = IIf(AxisValue_Reverse, _
datatempy + AxisValue_MinimumScale, _
AxisValue_MaximumScale - datatempy)

Worksheets("Calc % Spread").Cells(cutcrange, 17).Value = Ycoordinate

End If
End If
End If

End Sub

I have a spreadsheet that I continually add data to. I have a chart
based on the data. My problem is I want the chart to only show the last
5 data points entered in the spreadsheet and continue to do so ass I
add more data.
Example Data:

1 13
2 12
3 14
4 12
5 13
6 15

As I add data I want the chart to show only the last 5 entries.
Is there a simple way to do this?

I have used Excel to construct various ship curves in all versions until
2007. I would create a table of offsets (x,y points) and build a simple
integration scheme around the table. I would then graph the offsets. I
could go into the graph, grab a particular data point and move it either
horizontally or vertically. This would update the table and update the
integration. I would have the area (or volume) display on the graph. So I
could fair a smooth curve by adjusting offsets (with the smooth lines turned
on) until I got the area and centroid I needed. Until Excel 2007! I cannot
find a way to grab and move a single data point in this version. Is there a
way to do it? Help?

I hope someone can help me with an Excel 2003 problem.

I have a chart that plots multiple weeks of data to allow me to spot weekly trends, I'm then using the autofilter to reduce the data to a subset which I can review on a chart.

As an example, I have data for w/c 02/04, 09/04, 16/04, and 23/04 plotted as 4 series on an XY Scatter chart of time (00:00-23:59) vs. price, and I've used automfilter, to display one product and data only for Fridays.

I've noticed a problem with a point in the latest set of data. When plotted as the only series it plots correctly and when I hover over the point it correctly reports "14:08 0.14", however, if I add another series the point moves on the graph and then reports as "16:46 0.14", add a third series and it moves again to "14:39 0.14", and so on.

So to summarise, it plots in the correct place on the chart for the data value as reported, but the data value is being reporte incorrectly, unless it is in the only series being plotted.

Hope the above makes sense and thanks in advance for any help.

I have a spreadsheet that I continually add data to. I have a chart
based on the data. My problem is I want the chart to only show the last

5 data points entered in the spreadsheet and continue to do so *** I
add more data.
Example Data:

1 13
2 12
3 14
4 12
5 13
6 15

As I add data I want the chart to show only the last 5 entries (in this
case lots 2-6).
Is there a simple way to do this?

I have a spreadsheet that I continually add data to. I have a chart
based on the data. My problem is I want the chart to only show the last
5 data points entered in the spreadsheet and continue to do so *** I
add more data.
Example Data:

1 13
2 12
3 14
4 12
5 13
6 15

As I add data I want the chart to show only the last 5 entries.
Is there a simple way to do this?

Dear all,

Currently I have a chart showing mean sales, upper confidence limit and lower confidence limit across states. Right now I'm using a stock chart for this, but I can also use y-error bar if need be. I have another variable, let's call it stat_diff, that counts the number of states that have means that are statistically different from the current state.

The objective:

I would like to put stat_diff as data labels above the upper confidence limit data points in the chart.

I first tried inserting stat_diff as a new series, whiting out the point and line colors, adding data label and then moving the labels up above the coordinate of the upper confidence limit's coordinates. The problem is that to do this in VBA I'll need to access the coordinates of the corresponding upper limit data point for each state. Once I have that, I can just run a loop from 1 to 50 (one for each state) and move the data label of the stat_diff...say 5 pixels above the corresponding data point in the upper confidence limit series.

Another strategy might be to show the data label of the upper confidence limit data series. However, the only options I know of data labels for data series are value, category or series name. There's none for "external source."

Any ideas?


How to move a data point on XY-chart in XL 2007 ?
In XL 2003, one selects the data series, selects the data point, and drag it in any of the 4 directions.
In XL 2007, the data series is selected OK, but can't select the data point !
Could it be because the chart was created in XL 2003 environment ??

Thank you.

I have a two-column stacked chart -- the right-hand column represents a
blowup detail of one of the stacks on the left hand column. I can make a
PivotChart (or a regular chart) that displays all the data on a primary Y
axis, but whenever I choose one data point in the right-hand column to define
on a secondary axis, the other fields disappear. I want to select all four
stacked data points on the right-hand column and change them to a secondary
axis, and I cannot select multiple data points at once. Help!

In Excel 2003 you could click on the line that was plotted in your XY chart
and move the data point. This would then also change the data in your table.

This does not appear to work in Excel 2007.

Any ideas?

I'm new to Excel (using Excel 2002). I have a chart that shows a running
list of dates and the account balance on that date. I want to add some text
to a couple different data points within the chart to simply show when
specific changes took place.

For example, I want to add "moved investment from ABC to XYZ" on the data
point for Dec. 1, 2005 in the graph so I can clearly see on the graph when
specific changes took place and how those changes effected things.

How can I add text to a couple different data points along my chart?


this is driving me crazy

in all excell version previous to 2007 I could create and XY chart and then manually move the point in the chart (not in the data table) so i could reshape the line to my will. In excel 2007 i can do it or i dont know how

can someone help me pleasse

it important for me to learn this in excel 2007


Hi everyone,

It's been a while since I last visited this forum. Got a question regarding how to keep the autoshape tags in sync with the data point.

I often draw autoshape tags on top of a line chart & insert brief text to provide further explanations for the up and down movement. As the source data series expand over time, the data points representing the position for each month move accordingly on the chart graph. The tags however do not move.

Would it be possible to make these autoshape arrows move in auto sync with the data points they relate to. For example, if I have a tag on top of the data point for June. No matter how the line graph changes, I want that tag to be always on top of the June data point.

Would appreciate your input & thanks in advance.



I have an xy scatter plot and I'd like a way to put some verticle lines / cursors on the plot. These lines could be moved along the x axis (right and left). Where these lines cross the curve, i'd like the x and y value of the data point on the curve to be given in cells within the spreadsheet.

If this is not possible, is there a way to get the values of the data points in the curve by selecting them. I know you can hover over the data point and it comes up in box, but i'd like those values to be actualy transfered into the spreadsheet.

The location of the data point will vary, so the system needs to be dynamic, i.e, I need to be able to select the datapoint then extract the x and y value.


We have drawn rectangular autoshapes on an Excel chart (sample chart attached), but we would like to move the rectangular autoshapes behind the chart.

The rectangular shapes represent a time period of an event in recent history.

Selecting the rectangular autoshapes and then right clicking on the mouse, then selecting “Order”, and then “Move to Back” does not work. That only works to control the order of the autoshapes.

In the attached sample, the yellow areas are the “background” of the chart. If we hover over the plotted data points in the yellow areas, we are able to see the Series name, the date and the value. But when the points are behind a rectangular autoshape then the data values are not displayed. The rectangular autoshapes seem to “block” the ability to view the data.

Any help would be appreciated.

Thank you.

In Excel 2003, I was able to drag individual xy scatter point data to a new
position on the actual graph. This was a great feature when one wanted to
data to get them to coalesce with a scanned curve that had been included in
the graph background. I am not able to do this in Excel 2007. Does anybody
know if this feature is available in Excel 2007?

Hi All,

I am generating a chart (Stacked Bar) where number of series is fixed
(6) but number of data points vary and as a result the scale of the Y
axis keeps on changing every time it is refreshed.

I also have three lines on top of the chart which I need to show to
define boundaries within which the data points should lie. One at the
origin (coinciding with X Axis), other at 50% and the last one at

My Y axis will never go beyond 100% and -100% but because the X aixs
labels keep on expanding as I select more data to plot, the graph
moves up and down and the lines changes their position.

Is there anyway, I can keep the line static?

Please help

Thanks in advance

I'm using Excel 2002 (and a newbie) and want to add comments to different
points in a chart so I can clearly see when specific things changed and how
they effected my chart.

(I was referred by one to look at an add in program that can add data
labels, but couldn't get it to work the way I needed it to)

Is there a way in Excel 2002 for me to add a comment and place in the chart
where I want. I was able to figure out how to "insert comments" to a normal
cell and then move it to the chart where I want that comment to be. The only
problem with doing it this way is I can't figure out how to get rid of the
annoying arrow from where I put that comment on the chart and the cell where
I added that comment. Is there a way to delete this arrow?

If not, how would you add a simple comment to different points in a chart?

Anyone know how I can accomplish what I want?

Thanks in advance...

In Excel 2003 I can move data values (scattered points) in XY Scatter chart
however this functionality is not working in Excel 2007.