How to make scatter plots and curve graphs in Excel, and how to set smooth curves

Key points of this article

When the x value is a number (such as time, concentration, temperature), use an XY scatter plot to locate the data points according to their true spacing, and the line chart will treat x as an equally spaced category.
Scatter plots only display points by default. You need to turn on the connection in the data series format and then check the smooth line to make the curve smooth.
Smooth lines only make visual transitions between data points and do not add or modify the original data. This should be explained when reporting.
After finishing, check whether the x-axis scale is numerical, check whether a few points are consistent with the original table, and see whether the smoothing makes the curve deviate from the real data.

When many people make experimental records or sales trend charts in Excel, they choose a line chart. As a result, the horizontal axis becomes an equidistant text label of "1, 2, 3". The real interval between the two data points is erased, and the curve shape is distorted. This is particularly common when dealing with data such as temperature versus time or drug concentration versus dose.

This article is written for people who need to make accurate scatter plots and smooth curves in Excel, including students who organize experimental data, professionals who make reports, and readers who need to put data graphs into papers or reporting materials. After reading, you will know when to use scatter plots, how to generate curves step by step from the original table, how to set smooth lines, and what to check after setting.

How to choose between scatter chart and line chart? One sentence conclusion

The core of this problem is whether the data on the horizontal axis is numerical or categorical. When the x-values are numbers that can be compared in size and have real spacing, a scatter plot should be selected; when the x-values are just names or groups, a line chart or a column chart is more appropriate. The table below can help you make a quick judgment.

your situation	Recommended practices	reason
x is the time point, temperature, concentration and other values, the intervals are not equal	XY scatter plot with connected lines and smooth lines turned on	Scatterplots position each point numerically, with spacing accurately reflected
x is an equally spaced category like "January, February, March"	Line chart	The categories are inherently equidistant, and the line chart is more intuitive.
I just want to show the distribution of each point without connecting it into a line.	Scatter plot with data points only	Do not connect lines to avoid implying a definite relationship between points.
The data has obvious noise and I want to see the overall trend.	Scatter plot plus trend line	The trend line reflects the fitting relationship and has more basis than simple smoothing.
Functional relationships need to be given in a paper or report	Scatter plot with trend line and display formula	The formula and degree of fit can be quoted directly

If x in your table is a series of numbers recorded based on actual measured values, you can basically determine that a scatter plot should be used.

Before you start: Confirm these points first

Whether the data has been sorted into two columns. Scatter plot requires a set of x values and a set of y values. Try to put them in two adjacent columns, and write the header and unit clearly in the first row. If the data is scattered in multiple worksheets, merge it into the same area first, otherwise the selection will be prone to errors.

x column is a real numerical value. If the number is stored in text form (there is often a small green triangle in the upper left corner of the cell, or it is aligned to the left), the chart will treat it as a category. Even if the scatter chart is selected, the horizontal axis may not be evenly scaled. First convert this column to numerical format and then draw the graph.

Whether there are outliers or null values. A significantly deviated point will cause a non-existent bend in a smooth curve. Before drawing, try to read the original table first to confirm which data are measurement errors and which are real fluctuations. The handling of outliers should be decided by you based on the experimental situation, and the charting tool will not judge for you.

Whether the curve should express the fitting relationship. Smooth line just makes the connection smooth, it does not perform mathematical fitting. If you need to show the quantitative relationship between y and x, you should use a trend line and describe the fit in the report.

End use determines drawing dimensions and annotations. If the chart is to be put into a Word document or slide, it is recommended to consider the layout width first and then adjust the chart size to avoid blurred text or axis labels being crowded together after scaling.

Steps to create a scatter plot in Excel

Organize data. Put the independent variable x in the first column, and the dependent variable y in the second column. Each row contains a set of corresponding data. Write the name and unit on the first row, such as "time (h)" and "concentration (mg/L)".
Select the data area. Use the mouse to drag and select two columns of data; if the table header is selected, the chart will usually automatically recognize it as the series name.
Insert scatter plot. Find the scatter chart type in the chart insertion area of Excel, give priority to the style with only data points, and first observe whether the distribution of points is reasonable.
Check the horizontal axis scale. If the horizontal axis displays a uniform numerical scale, it means that the x column is recognized correctly; if it displays a text label, return to the table to check the x column format, modify it and regenerate the chart.
Open the data series format. Right-click any data point on the chart, select Format Data Series, and the settings pane will pop up on the right.
Enable connection and check Smooth Line. In the line-related options, first set the connection line to a solid line, and then check the smooth line. At this time, the data points will be connected with smooth curves, and the original points will still be retained.
Supplementary chart information. Add a chart title, set the title and unit of the horizontal and vertical axes, and adjust the minimum and maximum values of the coordinate axis if necessary to center the curve.
Export or copy the chart. When copying a chart into Word or PowerPoint, choose a paste method that preserves the chart style, and check the font size and line thickness in the document.

Note that smooth lines are visual transitions between data points and do not change the original data. If you have fewer data points, the smooth curve may make people mistakenly think that there are real measured values in the middle. Try to indicate in the report that "the curve is a smooth connection, not an actual measured value."

Check each item after completion

After the chart is generated, do not take a screenshot directly and check the following points one by one.

Check items	How to see	What to do if there is a problem
Whether the horizontal axis is a numerical scale	The scale spacing is proportional to the numerical value, and there are no text labels such as "1, 2, 3"	Check if column x is in text format and regenerate scatterplot after conversion
Are the data point locations correct?	Three points are randomly selected to check the coordinates against the original table.	Check whether the selection is multi-selected, omitted, or wrong.
Is smoothing excessive?	Whether the curve passes through a point that deviates significantly, or whether there is a bulge that is not found in the original data.	Turn off smooth lines, or eliminate outliers with obvious errors first
Axes and units	Whether the title, unit and numerical range are complete, and whether the curve is edge-attached.	Modify the axis range to supplement the title and units
The clarity of the chart in the document	Zoom in to the actual print size to see if the text and lines are clear	Adjust the width of the chart and increase the font size and line width appropriately

If the chart is used for formal reporting, the original data table should also be retained to facilitate others to check. The shape of the curve cannot replace the data itself.

# Core comparison

There are three common practices when making a graph, each of which has its own applicable scenarios:

Practice	Applicable scenario	Matters needing attention
Scatter plot + smooth line	X is a numerical value, you need to show the process of continuous change, and you want the curve to be smooth.	Smoothing is not equal to fitting. It should be stated in the report that the curve is a visual connection.
Scatter plot + trend line	It is necessary to explain the relationship between variables and give the fitting equation or degree of fit, which is often used in papers and scientific research reports.	Specify the fitting type and retain the original data points
Line chart	X is an equal interval category (such as month, quarter), which is easy to make.	Does not apply to the value of different spacing, different spacing will be distorted

# frequently asked questions (FAQ)

1. What is the difference between an Excel chart and a scatter chart?

The line chart treats the horizontal axis as an isometric category, while the scatter chart locates each point numerically, and the results are very different when the x values are not evenly spaced. For a real curve effect, you usually open lines and smooth lines on the basis of a scatter chart.

2. The smooth line is set, but the curve is still broken, what should I do?

First make sure that the chart type is a scatter chart, not a line chart, and then check that the line option in the data series format is solid. If the line is not displayed, it means that only the data points are displayed. You need to open the connection first, and then check the smooth line.

3. Can the smooth curve be used as the fitting result of the experiment?

It can not be regarded as the fitting result directly. Smooth lines are only visual transitions between data points, and no functional relationship is given. When quantitative instructions are needed, trend lines should be used and the type of fit and degree of fit should be stated in the report.

4. There are many data points, and the chart appears to be very messy. How to deal with it?

You can first reduce the marker size of the data points, or leave only the markers without showing connections to make the distribution clearer. If you only need trends, you can replace a large number of connections with trend lines while retaining the original data points as a reference.

5. What is the reason why the chart becomes blurred after being copied to Word?

Usually the chart is scaled too large or too small, or the picture format is used when pasting. It is recommended that you resize according to the final layout in Excel, then copy and paste, and check whether the font size and line thickness are appropriate in Word.

# Summary

Excel chart how to make scatter chart, the key is to judge whether the x value is a numerical value: if it is a numerical value, use a scatter chart, and then open the connection through the data series format and check the smooth line, the curve can truthfully reflect the changing process of the data. After the production is finished, don't forget to check the horizontal axis scale, spot check the location of the data points, and state in the report that the smooth lines are only visual transitions.

Open OmniDoc and process your form file