OmniDocHow to set the abscissa coordinates of Excel line chart to be equidistant
Explain the reasons and correction methods for unequal spacing between abscissas in Excel line charts: determine the type of x value, distinguish between text, date and numerical coordinates, and provide the scatter plot method and item-by-item checklist for the standard curve.
Key points of this article
- If the abscissa of the line chart is text or simply arranged by category, set it to the text axis to get equidistant scales.
- When the date abscissas are unevenly spaced, Excel may arrange them according to date proportions by default, and you need to change them back to the text axis to achieve equal spacing.
- If the concentration intervals of the standard curve are uneven, a scatter plot with a straight line should be used so that the abscissa is displayed in proportion to the true value.
- After completing the chart, check whether the data series is correct, whether the coordinate range includes all data, and add axis titles with units.
After many people use Excel to make a line chart, they will find that the distance between the scales on the abscissa is suddenly large and small, making it look uneven. Especially when placing experimental concentrations, time points or irregular numbers on the horizontal axis, the shape of the curve will be distorted, making it difficult for readers to see the changing trend at a glance. This article is written for people who need to make experimental data graphs, report graphs or teaching graphs. It specifically answers how to make the abscissas equidistant when generating a line chart from an excel spreadsheet, and how to set the abscissas when generating a standard curve graph from excel data.
The problem usually lies in two places: first, what kind of coordinate axis does Excel regard as the abscissa, and second, whether the x values in the data themselves are equally spaced. By clearly distinguishing these two points, most situations can be corrected directly. The following will first give a conclusion, then explain what needs to be confirmed first, then follow the steps, and finally check it again.
The horizontal coordinates are unevenly spaced, how to change them to equal intervals? One sentence conclusion
If you only need the abscissas to be evenly arranged and don't care about the real gap between values, set the axis type of the line chart to text axis; if the abscissas are numeric values and need to be displayed in true proportions, you should use a scatter chart with straight lines instead.
| your situation | Recommended practices | reason |
|---|---|---|
| The abscissa is text, category name or equally spaced number | Line chart, axis type is set to text axis | Each category occupies the same width, and the scales are naturally equidistant |
| The abscissa is the date, but the dates are irregularly spaced | Line chart, changed to text axis | The date axis will be arranged in proportion to the number of days. If the intervals are uneven, it will easily appear unequal. |
| The abscissa is a numerical value, unevenly spaced (such as 0, 0.5, 1, 2, 4, 8) | Scatter plot with lines and data markers | Scatter plots are positioned according to numerical values and can correctly reflect proportions. |
| To make a standard curve, you need to fit and label formulas | Scatter plot with trend line showing formula and R² | Only when the abscissa scale is accurate can the fitting results be meaningful. |
Before you start: Confirm these points first
Please read the following items clearly before taking action, otherwise it is easy to choose the wrong chart type, and it will still be wrong after changing it for a long time.
- Confirm the data type of column x. Open the data table and see if column x is text, date, or number. Excel handles these three categories differently, which determines which axis should be selected later.
- Confirm if you want "Isometric" or "Proportional". If you just want the position of each point to look neat, the text coordinate axis is enough; if it is experimental data, the gap in the abscissa itself is meaningful and must be displayed in proportion, otherwise the curve will be distorted.
- Confirm whether the intervals of x values are inherently consistent. If the x value is 1, 2, 3, 4, then the line chart will have a uniform scale after setting it as a text axis; if the x value is 1, 2, 5, 10, both line charts will change the relationship between the values, and a scatter chart should be used.
- Confirm that the data area does not contain blank rows or merged cells. Blank rows will cause Excel to misjudge the data range, resulting in more blank series or fewer data points.
- Confirm that the header has been written. The table header will be used as the source of the series name and axis title. Writing it clearly in advance can save a lot of modification later.
Operation steps: Make the unequal spaced numerical abscissas into a standard curve
The following takes the standard curve of "concentration and absorbance" as an example. The concentrations are 0, 0.5, 1, 2, 4, and 8 respectively. Such unevenly spaced values are suitable for use in scatter plots.
- Organize data. Put the concentration (x) in the first column, and the absorbance (y) in the second column. Write the names in the headers, and leave no blank lines in the middle.
- Select the data. Use the mouse to select two columns of data, including the header.
- Insert scatter plot. In the chart menu, select a scatter plot with straight lines and data markers. Do not select a normal line chart, otherwise the abscissa will be treated as a category.
- Check the data series. If there are redundant series in the graph, the concentration column is usually regarded as data. Open the settings for selecting data, delete redundant series, keep only the absorbance series, and set the concentration as the abscissa value.
- Set the abscissa range. Right-click on the horizontal axis, open the axis format settings, set the minimum value to 0, set the maximum value to a value slightly larger than the maximum concentration, and select an easy-to-read primary unit.
- Add axis title. Check the axis title in the chart element, write "Concentration (Unit)" on the horizontal axis, and "Absorbance" on the vertical axis. The title must have the unit.
- Add trend lines if needed. Right-click on the data series, add a linear trendline, and check Show formula and R². The fitting method of the trend line should be selected based on experimental principles, not just based on the appearance of the graph.
- Simplified charts. Remove unnecessary grid lines and legends, make the curves and data points the visual center, and check again to see if there are any omissions.
If you just want the horizontal coordinates of the ordinary line chart to be equidistant, you can change the selection of the line chart in step 3, then right-click the horizontal axis and set the axis type to text axis. The rest of the steps are the same.
Check each item after completion
After drawing the chart, do not use it directly. Checking it item by item can avoid most errors.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| abscissa spacing | Whether the distance between adjacent ticks is proportional to the numerical difference or uniform under the text axis | When the spacing is not correct, confirm the chart type first, and change the numerical Abscissa to scatter plot. |
| Data point location | Does the position of each point correspond to the values in the data table? | If there is any confusion, check to see if the x column is treated as a data series |
| Coordinate range | Do the small and large values of the axis contain all data points | Manually modify the axis range so that the data points are not trimmed or trimmed |
| Axis title and unit | Whether the horizontal and vertical coordinates have a title and whether the unit is clearly stated. | Add the title to the chart element and put the units in parentheses |
| Fitting result | Is the trend line formula and R ²consistent with the data? | R ²close to 1 only means that the straight line fitting is better, but also combined with the experimental principle to determine whether it is applicable, we can not only look at this number. |
| Copy to another document | Whether the axes and labels are deformed or missing after the chart is pasted into the Word or presentation | Choose to keep the source format when re-pasting, or export to a picture before inserting |
# Core comparison
Also do the line chart, Abscissa processing methods are different, the results are very different. The following three methods are suitable for different data.
| Mode | Abscissa spacing | Suitable for the scene | Matters needing attention |
|---|---|---|---|
| Line chart + text axis | Each category is equidistant and does not reflect the numerical difference. | Time point equal interval, classification label, number | The numerical gap will not be reflected in the diagram and is not suitable for the standard curve. |
| Line chart + date axis | Arranged in proportion to the date difference | Date sequence, which needs to reflect the time span | When the date interval is uneven, the graph will appear unevenly spaced |
| Scatter plot with straight line | Arrange in proportion to numerical value | Experimental data of standard curve and uneven x spacing | You need to set the coordinate range and title manually. There are more steps than a line chart. |
# frequently asked questions (FAQ)
1. The Abscissa is displayed as 1, 2, 3, but my value is not displayed. What should I do?
This is usually because your x column is not correctly set to Abscissa. Open the settings for selecting data, check the series list, delete the item that treats column x as data, and specify the Abscissa label as your numeric column, and the chart will display the actual value.
2. The distance between the date Abscissa is not the same, is it the wrong data?
Not necessarily. Excel in the date Abscissa will be arranged in proportion to the number of days, if your date is not recorded every day, uneven spacing is normal. If you want each date point to be arranged equidistant, change the axis type to the text axis.
3. Where are the axis titles and units set?
After selecting the chart, check the axis title in the chart element and enter text for the horizontal and vertical axes, respectively. The unit is suggested to be written in parentheses, such as "mg/L", so that the reader does not need to check the data sheet.
4. Why is the curve only dotted and not connected?
Scatter plots may display only data markers by default. Select the data series and select the style with straight lines in the settings to connect the points. If it is a standard curve, you usually need to confirm that the trend line has been added.
# Summary
Whether the line chart Abscissa is equidistant or not depends on whether the x value is a category or a numerical value: a text axis is used for the category, and a scatter plot with straight lines is used for numerical values and uneven spacing. According to the steps of this article, we sort out the data, check the series, set the coordinate range and title, and then check item by item. how to make the Abscissa equidistant when the excel table generates the line chart and how to set the excel data to generate the standard curve can basically be solved.
