How to make a line chart in Excel, add punctuation points and trend lines

Key points of this article

The data in the line chart should be arranged in columns. The first column contains the abscissa (such as date, month), and each subsequent column is a polyline. Do not leave empty rows or columns in the middle.
If you want each data point to be marked, select "Line Chart with Data Markers" first; you only want to change the markers on the existing line, and then set it in the data series format.
Marker shapes can be round, square, diamond, triangle, etc. The size and fill color should be distinguished from the polyline color to avoid covering the data trend.
Excel does not have a button to add trend lines to all series with one click. Usually, you need to add them one by one. When there are many series, you can use macro loop processing.
The trend line type should be selected according to the data pattern. R² close to 1 only indicates a high degree of fitting, but does not mean that this model is suitable for your business data.

When making reports, many people will encounter the same problem: there is sales per month in the table, or visits per week, and they want to use a line chart to see the trend, but they don’t know where to start. After the chart was made, the data points and lines were too thin to clearly see the specific positions of each month; some people wanted to add a trend line to the polyline to predict the direction of the next quarter, but could not find a way to batch process it.

This article is written for those who need to make a line chart in Excel, especially those who want to add data punctuation to the line, change the shape of the markers, and add trend lines. After reading, you can first organize the data into a structure suitable for graphing, generate a line chart, then set punctuation points and trend lines, and finally check whether the chart is accurate item by item. The steps in this article only cover general operations and do not depend on the menu location of a specific version.

How to make an Excel line chart and add punctuation points and trend lines? One sentence conclusion

First organize the data into columns, generate a chart using line chart type, and then set separate markers and trend lines for each data series. The table below provides recommended practices for your situation.

your situation	Recommended practices	reason
Data is a set of values arranged by time. You only need to look at the trend.	Select a normal line chart without markers	The chart is concise and the line trend is clear
Each data point needs to be clearly understood.	Select the line chart with data markers, and then set the marker shape	Markers correspond directly to each data point, making reading easier
There is already a polyline in the chart, but I just want to change the label	Modify the marker style in the format settings of the data series	No need to redo charts, keep original colors and data
Want to predict subsequent trends	Add linear or other types of trend lines on a series-by-series basis and set forecast periods	Trend lines can give a direction reference and are suitable for regular data.
There are many series, I want to deal with them all at once	Add trendline with macro loop through all series	It is easy to miss if you add them one by one manually, but macros can be executed uniformly.

Before you start: Confirm these points first

The data arrangement must be correct. Line charts usually use the first column as the abscissa, such as month or date, and each subsequent column is a line. If the data is arranged in rows, you need to transpose or adjust the selection first, otherwise the chart will treat the data as another structure.

Dates and numbers must use real date format and numerical format. If the date is saved as text, the chart will be arranged in text order rather than in chronological order, which may easily lead to confusion in the abscissa. After inputting, you can click on the cell to see whether the date displayed in the edit column is a date.

First make sure there are no null values in the data. When a polyline encounters a blank cell, the default processing method may be to disconnect, process by zero, or connect adjacent data points. Different settings will make the graph look completely different. Before drawing, check to see if there are any gaps and decide how to deal with them.

If the data comes from a PDF table, export it first and then draw the graph. The PDF editor of the OmniDoc desktop client supports exporting PDF to Excel. After exporting, check whether the numbers are misplaced and the headers are merged, and then start drawing after confirming that they are correct. If the table in the PDF is a scan, you can use OCR to identify it before exporting, and the identification results also need to be checked item by item.

Trend lines are for reference only. The trend line is a fitting line calculated based on existing data. When there is little data, large fluctuations, or obvious seasonal effects, the fitting results may mislead judgment. Before making decisions, it is recommended to understand the chart in the context of the business.

Steps to create a line chart in Excel and add data punctuation

Organize the data area. Enter the abscissa (such as month) and the value of each polyline in the worksheet. Write the series name clearly in the column title, such as "East China" and "South China". Make sure there are no extra blank rows or merged cells in the range.
Select the data area. Use the mouse to drag the entire data including the table header, and Excel will automatically recognize the table header as the series name.
Insert a line chart. Select Line Chart in Chart Type. If you want each data point to have a marker, directly choose the line chart type with data markers; if you just want a normal line chart, choose the type without markers.
Check whether the abscissa and series are correct. After generating the chart, check whether the abscissas are arranged in chronological order and whether the name of each polyline is consistent with the table. If the abscissa changes to a numerical sequence, it means that the first column is not recognized as a category and the data area needs to be readjusted.
Set the marker shape of the data series. Click a polyline to select the entire line and find the marker option in the data series format. Choose a built-in marker type, such as circle, square, diamond, or triangle, and set the marker size.
Set the mark color. It is recommended that the mark fill color and border color be consistent with the polyline color, or use a contrasting color. When the color is too light or close to the background, the mark will be unclear.
Repeat settings for each polyline. Multiple polylines need to be selected and set separately. It is recommended to use different shapes to distinguish the series so that they can be easily identified when printing in black and white.
Adjust the chart title and axis title. The title should clearly state the indicator and time range, such as "Monthly sales in each region in 2024", and the axis title should indicate the unit to avoid readers guessing the meaning of the value.

Steps to add trend lines in batches to line charts

Select the data series to which you want to add trend lines. Click a broken line in the chart to confirm that this series is selected, not the entire chart.
Open the trendline option. find the entry to add trend lines through the chart element button or right-click menu, and select linear, exponential, logarithmic, polynomial or moving average types. Linearity is suitable for data with roughly uniform change of value, and index is suitable for data with faster and faster growth rate.
sets the prediction cycle of the trend line. if you need to forecast future data, fill in the number of cycles predicted forward in the trendline option, such as 3 months or 2 quarters, and note that the units of the Abscissa correspond to the data.
Select whether to display the formula and R ². when the display formula is checked, the fitting formula appears on the chart; when R ²is checked, a fitting degree value between 0 and 1 appears. The closer the value is to 1, the better the fitting, but it still needs to be judged by the data itself.
repeat operations for other series. * Excel usually needs to add trend lines one series at a time. If there are only two or three series, you can do it manually; if there are many series, you can use macros to traverse all the series in the chart, adding the same type of trend line. It is recommended that you test on the copy before using the macro to avoid overwriting the original settings.
adjust the trend line style. * trend lines are generally represented by dotted lines, the color is distinguished from the corresponding broken lines, and the line width should not be too thick to avoid overpressing the original data.

# Core comparison

There are two common ways to punctuate broken lines, which are suitable for different situations.

Contrast item	Line chart with data tag	Manually set the mark after a normal line chart
Operation steps	Select directly when generating the chart with few steps	Sir, make a broken line, and then modify the marks one by one, with many steps.
Flexibility	Tag type and size can be adjusted later	Can be set separately for a particular line, making it more flexible
Suitable for the scene	Create a new chart and display a marker for each data point	I already have charts, and I only want to adjust the style for some series.
Matters needing attention	The tag default style may be small. It is recommended to enlarge it.	It is easy to miss some series and check them one by one after setting.

# check item by item after completion

After the chart is completed, it is recommended to check it according to the list below and deal with it when you find a problem.

Check item	What do you think?	What if there's a problem?
Abscissa order	Is the time arranged from morning till night?	Check that the date is in the true date format and re-enter it if necessary
Series quantity	Is the number of broken lines consistent with the number of columns in the table?	Re-select the data area to remove the extra empty columns
Mark position	Whether the tag falls on the corresponding data point	Check whether the data area is misaligned, or if the tag is mistakenly set to another series
Null value display	Is the blank disconnected, zero, or connected?	Adjust to fit the business meaning in the empty cell display settings of the chart
Trend line direction	The trend of the trend line is roughly consistent with the data.	Compare with another trend line type, or reduce the data range to re-fit.
Fitting reference value	Is the formula and R ²in line with expectations?	R ²height does not mean that the prediction is reliable, and it should be judged according to the amount of data and the actual situation.
Numerical reading	Random sampling of several points, compared with the table value	If inconsistent, go back to the data area to check if there are hidden rows or filters

# frequently asked questions (FAQ)

1. How to choose Excel line chart and bar chart?

See if the data have a chronological relationship. It is easier to see the trend with a line chart for the data that changes over time, and the bar chart is more intuitive when the size of each category is larger. Both can be marked or trend lines, but the broken line graph is more suitable for continuously changing data.

2. The punctuation on the line chart is missing. What is the reason?

There are three common reasons: the tag is set to none, the tag fill color is the same as the background, or the line chart type chooses a normal line instead of a marked type. First check the marking options of the data series, and then confirm that the color and size are appropriate.

3. Is the R ²of the trend line as close to 1 as possible?

R ²only indicates the degree to which the trend line fits the existing data, but does not mean that the prediction results are necessarily accurate. When the amount of data is small or is heavily affected by seasons or emergencies, even if R ²is high, the prediction may deviate from reality.

4. What should you pay attention to when making a line chart from a table copied in PDF?

The copied numbers may have thousand quantile symbols, units, or hidden spaces, which Excel recognizes as text, causing the chart to not be drawn properly. Convert the numeric column to numerical format before drawing, and check whether several key numbers are consistent with the original text.

# Summary

The key to making an Excel line chart is to arrange the data first, select the right chart type, and then set punctuation and trend lines according to the series. The shape and size of punctuation will affect the reading, and the type of trend line should be selected according to the rules of data. After completion, check the Abscissa, null values and fitting results item by item, the chart can really be used for analysis and reporting.

Open OmniDoc and process your form file