Free Microsoft Excel 2013 Quick Reference

Using vba to generate xy scatter plots in excel

Hello. I was trying to use vba to generate some xy scatter plots. It seems
like a simple task, but I haven't been able to find much on it. Anyways, I
have one column of data which I want to be the x-values, and another column
of y-values. I want to set the y-axis to a logarithmic scale, and I want to
be able to specify the maximum and minimum x and y values. Any help would be
greatly appreciated.


I want to use VBA to insert an Organisation Chart in Excel 2007 (ie if I did this manually it would be Insert, SmartArt, Hierarchy), but I cannot find anything on line that shows how to do this in Excel 2007. I know the code for Excel 2003, but that does not work in 2007.

Many thanks in advance for any help anyone can provide.


Hi Excel:

I am using a simple macro in Access to create an XY scatter chart in Excel.
It exports out information in a query, puts it in Excel and makes an XY
scatter chart. The problem is that it only charts one axis. When I go into
'Chart Source' in Excel and look at the 'Series' it is only using the Y axis.
But I want it to utilize both columns D and E to symbolize the relationship
between the acres and the price per acre in my data. Any help you can give me
would be greatly appreciated. My code is enclosed.

Public Function ExportPrice_Acre()

Dim appExcel As Excel.Application
Dim wkbCurr As Excel.Workbook
Dim wksCurr As Excel.Worksheet
Dim chrNew As Excel.Chart

Dim rs As New ADODB.Recordset
Dim lngRows As Long

Set appExcel = New Excel.Application
Set wkbCurr = appExcel.Workbooks.Add
Set wksCurr = wkbCurr.Activesheet
Set chrtnew = appExcel.charts.Add

rs.Open "qryPrice_Acre", CurrentProject.Connection
wksCurr.Name = "Price_Acre"
lngRows = wksCurr.range("a2").copyfromrecordset(rs)
chrtnew.chartwizard wksCurr.range("D2", "E" & lngRows + 1),
gallery:=xlXYScatter, _
HasLegend:=True, Title:="Price per Acre in Cleveland"

appExcel.Visible = True
End Function

How do I make a 3D scatter plot in Excel? Ideally I will be able to define
the viewing angle of the 3D graph. Alternaviely, are there plug-ins that can
fix this that are not too expensive? Alternatively, which other tools are
available that are not expensive and are easy to use?

I need help adding a treadline to an xy scatter plot in excel?

It would be of great help if any one could assist me to know if there is a way to generate a error msg in excel in the presence of a formula like a summation of rows H1:S1, and if one number in the rows b/w H1 through S1 is missing an error message notification is prompted saying that the particualr cell has no value entered, but provides the summation either ways--I tried the following

Sub PJ()
If (H13="","H13 is blank",SUM(H13:S13)
End Sub

I think there is something wrong in the formula or my logic is wrong plz HELP!!


I want to be able to generate a scatter plot directly, by just selecting the data I want to plot and select which chart type I want. No further settings should be neccessary. This is because I am generating an Excel Worksheet from another program and therefore it all has to be done automatic.

The data I want to plot consists of three series with three X-Y pairs. However, the X-values are not the same in the three series why I have to organize the data as can be seen in the attached file (X values to the left, series at the top, Y values the rest).

The problem is when I select the data and plot is as a XY Scatter plot, there are empty cells (since there are no Y-values for some X-value in each series) in each series which results in breaks in the lines in the plot. For instance, in the series called 15 there is not a line between 30 and 72 (since that series does not have an Y-value for X=80). Look in the attached file for clarification.

Is there any way to tell Excel to skip the empty cells and just interpret the data as three X-Y values for each series? Or should I organize the data in another way to get a satisfying result (which is three lines, one for each series)?

Thank you in advance!
Best regards,
Samuel Alfredsson


I am trying to create an XY Scatter Plot within Excel 2003 that has 5 series of data on it. My data exists in two columns (A and B) on the spreadsheet. There is an unspecified number of rows of data within each data set, and each set of data is located 8 rows down from the end of the previous data set (all within columns A and B). Because the number of rows in each data set is variable, I can't simply create a loop that selects a pre-determined number of rows.

To solve this issue, I created a Do While loop that uses the End(xlDown) function. The code I wrote to graph the data can properly plot one range when the code is located outside of the loop, but once placed inside the loop, running the maco simply creates a spreadsheet with no data plotted.

My issue is with the data series creation - the data is not being properly placed on the chart created within the workbook. Does anyone see an error/mistake in this code?

I am new to writing Excel Macros, so please forgive any seemingly simple errors I have made.

Thank you for the help.

My code is below, and I have attached the spreadsheet.

Option Explicit

Sub NewGraph()
    Dim cell1, cell2, cell3, cell4, rng1, rng2 As Range
    Dim i As Integer
    i = 1
    Set cell1 = Cells(1, 1)
    Set cell2 = cell1.End(xlDown)
    Set cell3 = Cells(1, 2)
    Set cell4 = cell3.End(xlDown)
    'Debugging Help
    On Error Resume Next
    If Err.Number <> 0 Then
        MsgBox Err.Description, vbCritical, "Error # " & Err.Number
    End If
    'End Debugging Help

    With Charts.Add ' Make new chart
    ' Set its properties
    .ChartType = xlXYScatter
    .Location Where:=xlLocationAsNewSheet
    .HasLegend = False
    End With

    Do While i <= 5
        Set rng1 = Range(cell1, cell2)
        Set rng2 = Range(cell3, cell4)
        'Insert series naming scheme here as well (always a certain distance from cell1)
        'Insert graphing code here
        ' Add the series
        With ActiveChart.SeriesCollection.NewSeries
            .XValues = Worksheets("Sheet1").rng1
            .Values = Worksheets("Sheet1").rng2
        End With
        Set cell1 = (cell2.End(xlDown).End(xlDown))
        Set cell2 = cell1.End(xlDown)
        Set cell3 = (cell4.End(xlDown).End(xlDown))
        Set cell4 = cell3.End(xlDown)
        i = i + 1
End Sub

I'm trying to use vba to find the last value in a column and then use this value in a simple formula.

All I've been able to figure out is how to "select" this value. I do not know how to "use" this value in my formula.

Sub last_row_col()
End Sub

I've been able to select the value I want. What do I need to do in order to use this value in a formula on my spreadsheet?

James Keeton


I have to create a chart XY Scatter plot in excel. I have to differentiate the data points color based on the another column "category". Now I can change the data points color manually. Since the number of points is huge, is it possible to color the data points based on a column?


How do I add a linear regression line and an equation to an xy-scatter plot?

Thank you.

(Note: I asked this question recently, but when I got a response, it was a
blank page.)


I'm trying to create a scatter plot chart in excel 2007 with a label
on each point. But I can't seem to do this without creating 50
different series. I have three columns (x-axis, y-axis, and labels).
Is there a anyway to plot points and then at a separate label?

i am trying to create a scatter plot in excel with one set of x vaules, and 2 sets of y values ( both sets of y value data uses the same variable). essentially, how can i get two scatter plots on one graph ? is it possible ? do you guys know what i mean ?



Hi there!

Is it possible to use VBA to lock all cell formatting in a range of cells except for background color? I've got a form in which I need to lock down the field formats (date, etc.) but my users want to be able to "highlight" fields by changing the background color.

Thanks very much in advance!

vba--i'd like to use vba to set the default directory when excel opens...

(i have tried the options, default directory, but this always seem to work...)



I am trying to create a template in Excel using VBA to open a Mappoint file in Excel. I have tried to record the macro while I Insert>Object>Create from file, but unfortunately it creates a macro that opens a specific file, while i would like it to open a directory. Any suggestions will be appreciated.

This is the VBA I recorded:

Sub Macro1()

ActiveSheet.OLEObjects.Add(Filename:= _
"Z:Lehman BrothersHealthcareProperty Maps2.ptm", Link:=False, _
End Sub

I have used VBA to alter the user interface in Excel 2003. In the process
Excel crashed and has left remnants of the VBA generated interface. I wish
to get back to Excel default settings without having to reinstall Excel. I
have tried all the normal methods outlined in Excel 2003 help to no avail.



I need to create a scatter plot in Excel 2007 and I am clueless.

I have two columns, one called REASON, the other called LENGTH OF

I have selected both columns and attempted to insert a Scatter chart,
but to no avail. Can you please help?


I got some great help yesterday extremely fast, and I ran in to a another little question today. I have a automation program that I'm using vba to call two different macros in excel. Yesterday I got my excel code perfected, but I have one more little problem with the code that calls the excel macros from my automation program.

Here's the code I currently have...

     'Dimension a string for the Excel file path
    Dim sExcelFilePath As String 
     'Dimension Excel objects
    Dim XL As New Excel.Application 
    Dim wrk As Excel.Workbook 
    On Error Goto ErrHandler 
     'Build the path to the Excel file
    sExcelFilePath = "c:testtest.xls" 
     'Set the workbook object using the file path
    Set wrk = XL.Workbooks.Open(sExcelFilePath) 
     'Run the macro using the passed in name
    wrk.Application.Run (MacroName) 
     'Log the macro execution to the RSView32 activity log
    gActivity.Log "Excel Macro: " & MacroName & " has been executed", roActivityInfo, roActivityApplication 
     'End the Excel application
     'Clean up objects
    Set wrk = Nothing 
    Set XL = Nothing 
    Exit Sub 
     'Log the error message and clean up
    gActivity.Log Err.Number & "  " & Err.Description, roActivityError, roActivityApplication 
    Set wrk = Nothing 
    Set XL = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code works for me, but only when the excel file is not open. The problem with that is I'm communicating through DDE to send and retireve values with one of my excel sheets, so the excel file has to be open.

When the above code runs with the excel file allready open, it open's a read-only of the file, then asks me to save in a different location, when all this I hope can go on in the background.

Maybe instead of this approach, I'm sure someone knows the DDE syntax to run an excel macro?

Thanks in advance,


Hi, guys

I tried to make a scatter plot in Excel. The issue is that there are
repeated data points.
For example,
(x,y) style (1,3.4), (2,5.5), (2,5.5), (3, 9.9), (3,9.9), (4,23.0)...

Can I add a related frequency number around the corresponding data

Thank you for your help!


I have an xy scatter plotted in excel. For a certain y value, I want the corresponding x value based on the plot. How to show that in the chart? Please view the screenshot attached for better undestanding.

I am trying to create a box plot in excel. My book says to go to tools and
Data Analysis Plus. I have already added data analysis, but I have no Data
analysis plus. Can anyone help?

When plotting many data points in an XY scatter plot as one series, all
points show up as one marker. When trying to identify each point linked to a
series name, the chart becomes too crowded if I want to show series name on
the chart next to each marker. So, I want to replace colored symbols with
numbers? Is this possible w/o having to paste custom drawn objects into the
marker positions? I'm sure there is a VBA way to do it but I have never used
VBA and am not familiar with how to use it. Thanks, Andrew

Hi all,

I am new to VBA, and I'm using Excel 2007 to do some automated graphing for work. Anyhow, I am having heck of a time getting a macro I am working on to do what I want. Here's what's happening:

1. I record the macro - I select both columns in the attached sheet - Go to insert chart- select the XY scatter plot - and it gives me a linear graph with the XY axis in the right place (A is X axis, B is Y axis). However, when I run this macro, it totally screws up the axis variables, and my graph comes out incorrect - I can eve tell where it's getting mixed up.

I realize at this point the chart wizard would be appropriate, but I need automation to be incorporated within this sheet. I am writing this macro because I will need to append this graph with new curves as I bring more columns of data in, but I can't even get it to do a simple plot yet - Any ideas?

Attached is the document

Here is the code for reference:

Sub Macro4()

    ActiveChart.SetSourceData Source:=Range("'500mW impedance'!$A:$B")
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

End Sub



Excel 2000's XY Scatter charts do not permit using a 3rd column of data (Z) to be inserted as chart tips or labels. I am interested in inserting chart tips - these are the hidden descriptors that are displayed (names and/or values) when the mouse pointer is on a data point. I don't like labels because they cause too much screen clutter. Anyone know of any such utility ? I've tried the one at appspro but that one only does labels, not the chart tips. I'd like to know if the ozgrid labeler add-in can do this. If you want more explanation, you can see the Excel chart tips selections under Tools/Options, then the Chart tab; the tips selections are at the bottom. The names and values selectors refer to the Name and X-Value fields used on the chart under Source Data/Series tab, but Name does not work using the XY Scatter plot (the X-value is used as the chart tip, not the Name).