How to Create and Use the Scatter Chart in Your Excel Worksheet

Except for the lines or columns in a chart, you can also use the markers in a scatter chart to show data and information. In this article, we will show you how to create and work with the scatter chart.

The scatter chart is a very special chart in Excel. Some special markers in the chart will represent the data in your worksheet. With those obvious markers, you can easily analyze the data and information. Below allow us to show you how to create such a chart.

Create a Scatter Chart

  1. Click a cell within the range in your worksheet. Besides, you can also click the whole range.
  2. And then click the tab “Insert” in the ribbon.
  3. After that, click the button “Scatter” in the toolbar.
  4. In the drop-down list, choose the scatter chart.Insert Chart

And then the chart will appear in the worksheet. It is very easy to create such a chart in your worksheet.

Scatter Chart

Modify the Scatter Chart

In this part, we will show you how to modify this scatter chart.

  1. Delete the gridlines in the chart.
  2. And then right click one data series in the chart.
  3. In the pop-up menu, choose the “Format Data Series”.Format Data Series
  4. In the new window, choose the option “Marker Options”.
  5. And then choose the “Built-in” option.
  6. Next choose a type for the marker.
  7. Change the size if you need.Marker Options
  8. And then change the fill color for the markers of the series according to your need.
  9. Now repeat the steps and modify the markers for another series.
  10. In addition, you can also make other modifications according to your need. You can also refer to the example in the image below.

New Style

Locate a Marker

When there are many markers in the chart, it will be hard for you to locate a certain data point. In order to solve this problem, you can use the method in this part.

  1. Press the shortcut keys “Alt +F11” on the keyboard.
  2. And then insert a new module into the Visual Basic editor.Insert Module
  3. Now input the following codes into the module:
Sub LocateMarker()
  Dim nTargetSeries As Integer, nPoints As Integer

  'If the cell is not in the range, exit the current macro
  If Intersect(Range("B2:C13"), Selection) Is Nothing Then Exit Sub

  nTargetSeries = Selection.Column - 1
  nPoints = Selection.Row - 1

  ActiveSheet.ChartObjects("Chart 1").Activate
End Sub

In this module, you need to change certain elements, which include the target range, the chart name and other elements.

  1. Click the tab “Developer” in the ribbon.
  2. And then click the button “Insert”.
  3. In the menu, choose the option “Button”.Insert Button
  4. Next click in the worksheet.
  5. In the “Assign Macro” window, choose the module.Assign Macro
  6. And then click the button “OK”.

Now you have finished the setting. When you click a cell in the worksheet and then click the button, the corresponding marker in the chart will also be selected. For example, we click the cell B7 and the corresponding marker in the chart will be selected.Result

Therefore, you can use this macro to solve the problem.

Repair Excel Problems on Your Own Computer

Whenever you experience Excel corruption, you need to repair your files as soon as you can. There are many methods that you can take for your files. But in order to protect the security, you’d better repair Excel problems on your own computer. Our Excel repair tool is able to repair corrupt Excel xlsx data on your own computer. You will never need to concern yourself with the Excel security.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair corrupt docx document and outlook repair software products. For more information visit

Comments are closed.