Create a scatter chart from a pivot table

Excel doesn’t allow you to create scatter chart from a pivot table:

To overcome this, you can use the following approach:

1.  Create a scatter chart using two columns of the pivot table as x, y series
2.  Create two named ranges referring to the two columns
3.  Replace the x, y series of the scatter chart with the named range

Create a scatter chart using two columns of the pivot table as x, y series

1. Menu tab: Insert, choose “scatter” chart
2.  Right click on the chart, select menu “select data”
3.  Edit series

You do not need to be precise about X, Y series, just choose a few values from the two columns of the pivot table. later, you will replace these two series with named ranges.

Create two named ranges referring to the two columns

1.       Menu tab: formula, choose “named range”

2.       Create a new named range referring to the first column of the pivot table

Name: javaPivotName

Formula: =OFFSET(javaChart!$A$4,0,0,COUNTA(javaChart!$A$4:$A$10000)-1,1)

3.       Create a second named range use the first range as the reference

Replace the x, y series of the scatter chart with the named range

Click on a dot in the scatter chart, and replace the x, y series with the two named ranges:

1. Replace “$A$4:$A$12” with the first named range
2. Replace “$B$4:$B$16” with the second named range

Automate the process in a macro

If you try to automate this in a macro, you will encounter something tricky. To create such a macro, you use record to capture creating a named range, which will generate code such as this:

  ActiveWorkbook.Names.Add Name:="javaPivotName", RefersToR1C1:= _

        "=OFFSET(javaChart!R4C1,0,0,COUNTA(javaChart!R4C1:R10000C1)-1,1)"

Notice, it uses RC style to reference cells.

Being a good developer, you of course do not want to use the absolute row, column number, you change the code to:

ActiveWorkbook.Names.Add Name:=xRangeName, RefersToR1C1:= _

        "=OFFSET($" + xColumn + "$" + xRow + ",0,0,COUNTA($" + xColumn + "$" + xRow + ":$" + xColumn + "$10000)-1,1)"

Then you try to replace x, y series in the scatter chart with named range, here is when you encounter the tricky part: Excel thinks your name range is invalid, if you check the named range, its definition is:

=OFFSET(javaChart!'A4',0,0,COUNTA(javaChart!'A4':'A10000')-1,1)

Notice somehow Excel adds single quotes around cell addresses.

Apparently it is because two ways of referencing cell addresses are mixed together, you should stick to one way. So the solution is to change

ActiveWorkbook.Names.Add Name:=xRangeName, RefersToR1C1:= _

        "=OFFSET($" + xColumn + "$" + xRow + ",0,0,COUNTA($" + xColumn + "$" + xRow + ":$" + xColumn + "$10000)-1,1)"

To:

ActiveWorkbook.Names.Add Name:=xRangeName, RefersTo:= _

        "=OFFSET($" + xColumn + "$" + xRow + ",0,0,COUNTA($" + xColumn + "$" + xRow + ":$" + xColumn + "$10000)-1,1)"