Scatter Charts in Excel – Straight and Smooth Lines with Markers

The earlier lesson taught us the usage of Scatter Charts in excel. Scatter charts help in plotting the coordinates on the chart between the axis. In this blog, we will learn the entire thing about Scatter charts with Smooth and Straight Lines in Excel.

Here we go 😎

What is a Scatter Chart with Lines

Scatter Charts in Excel is a unique chart type. The Horizontal and Vertical Axis represents the numerical values for the x and y coordinates of the data points. The data points are visually represented between the horizontal and vertical axis. Consecutive data points, when joined to either form straight lines or smooth lines.

scatter chart with smooth lines and straight lines

Inserting a Scatter Chart with Straight Lines

Let us say we the product details of a company. The details include the price and the number of products sold. The data is as follows:-

inserting scatter chart with straight lines in excel raw data

To insert a scatter chart with smooth lines, simply:-

  • Select the data set A2:B5
  • Go to the Insert tab on the ribbon
  • In the Charts group, click on the Scatter Chart button.
inserting a scatter chart with smooth lines in excel step 2

Consequently, this will insert the required chart type chart on the current worksheet.

inserting scatter chart with straight lines in excel result

As a result, excel inserts default scatter charts with smooth lines.

infographics Scatter chart with smooth and straight lines in excel

Scatter Chart with Straight Lines and Markers

Markers are used to highlight the data points on the chart. They help in the accurate reading of the chart and can be inserted via a new chart type called Scatter chart with Straight Lines and Markers.

We can change the chart type of the above chart to add the markers. Follow the below-mentioned steps:-

  • Select the chart
  • Go to Chart Design Tab on the ribbon
  • In the Type Group , click on the Change Chart Type button.
adding markers to a chart in excel

Consequently. this opens the Change Chart Type Dialog box. Go to the All Charts tab. Select the required chart type for conversion.

changing chart type of a char tin excel

These are the results:-

scatter chart

You can switch the chart type of the above chart to Scatter Chart with Smooth Lines. As a result, the smooth lines will replace the straight lines in the above Chart.

scatter chart with smooth lines and markers

Following is a link to learn the Formatting of a chart in excel.

Dealing with Hidden/Empty Cells in Source Data

Let us say we have the empty cells in the source data. By default, the scatter chart would not contain any data point for the empty/hidden cell and the line joining the data points will become noncontiguous. For instance, look at the below chart:-

hidden empty cells in teh source data for a chart

One entry in the above chart’s source data is kept empty which made the corresponding data point to be missing.

empty cell in the source data for a chart

We can change the way, the empty cell plots on the chart. To do that:-

  • Right Click on the Chart.
  • The Shortcut menu opens, click on Select Data.
  • This opens the Select Data Dialog box.
  • Hit the button at the bottom left corner named Hidden and Empty cells.
hidden and empty cell settings in a chart in excel

As a result, the settings for Hidden and Empty Cell open. There are three valid options from which leaving gaps is the default setting.

  • zero – This will plot zero for each empty cell value in the source data.

Here are two missing values in the source data, for each of the x-axis and y-axis on the chart. (0,0) would be plotted on the chart instead.

settings for hidden and empty cells of a chart in excel step 1
  • Connect data Points with Line – This will directly connect the previous and next data point when there is a missing value in source data.
settings for hidden and empty cells of a chart in excel step 2

With this, we have come to the end of blog.

Thank you for reading.

Leave a Comment