Scatter plots in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the chart type for a scatter plot in Excel.

Excel

ActiveSheet.Shapes.AddChart2(251, [1]).Select

Drag options to blanks, or click blank then click option'

AxlColumnClustered

BxlXYScatter

CxlPie

DxlLine

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column or pie chart type instead of scatter.

Using a line chart type which connects points with lines.

The code uses xlXYScatter to create a scatter plot chart type in Excel VBA.

2fill in blank

medium

Complete the formula to calculate the slope of a scatter plot trendline in Excel.

Excel

=SLOPE(B2:B10, [1])

Drag options to blanks, or click blank then click option'

AB2:B10

BC2:C10

CA2:A10

DD2:D10

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping x and y ranges in the formula.

Using the same range for both arguments.

The SLOPE function takes the known y-values first, then known x-values. Here, y-values are in B2:B10, so x-values must be A2:A10.

3fill in blank

hard

Fix the error in the VBA code to add a scatter plot with markers only (no lines).

Excel

With ActiveSheet.ChartObjects.Add(Left:=100, Top:=50, Width:=400, Height:=300).Chart
  .ChartType = [1]
  .SeriesCollection(1).Format.Line.Visible = msoFalse
End With

Drag options to blanks, or click blank then click option'

AxlXYScatter

BxlXYScatterLinesNoMarkers

CxlXYScatterLines

DxlXYScatterSmooth

Attempts:

3 left

💡 Hint

Common Mistakes

Using a chart type that includes lines with markers.

Setting line visibility to True.

To show markers only with no lines, use xlXYScatter as the chart type. Setting line visibility to False hides lines.

4fill in blank

hard

Fill both blanks to create a scatter plot with a linear trendline in VBA.

Excel

With ActiveSheet.ChartObjects.Add(Left:=50, Top:=50, Width:=300, Height:=200).Chart
  .ChartType = [1]
  .SeriesCollection(1).Trendlines.Add(Type:=[2])
End With

Drag options to blanks, or click blank then click option'

AxlXYScatter

BxlLinear

CxlColumnClustered

DxlExponential

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column chart type instead of scatter.

Choosing an exponential trendline instead of linear.

Use xlXYScatter for scatter plot and xlLinear for a linear trendline type.

5fill in blank

hard

Fill all three blanks to add a scatter plot, set marker style, and add a polynomial trendline of order 2.

Excel

With ActiveSheet.ChartObjects.Add(Left:=20, Top:=20, Width:=350, Height:=250).Chart
  .ChartType = [1]
  .SeriesCollection(1).MarkerStyle = [2]
  .SeriesCollection(1).Trendlines.Add(Type:=[3], Order:=2)
End With

Drag options to blanks, or click blank then click option'

AxlXYScatter

BxlMarkerStyleCircle

CxlPolynomial

DxlLine

Attempts:

3 left

💡 Hint

Common Mistakes

Using line chart type instead of scatter.

Choosing wrong marker style constants.

Forgetting to specify the order for polynomial trendline.

Use xlXYScatter for scatter plot, xlMarkerStyleCircle for circle markers, and xlPolynomial for polynomial trendline.