OmniDocHow to set the coordinate axis of Excel chart: scale, value and date
Explain the common settings of the coordinate axes of Excel charts, including the customization of abscissa classification and date, the small and large values of the ordinate, scale interval, number format and data source modification, and provide operating steps and verification methods.
Key points of this article
- How to customize the abscissa, first check whether the data is text classification or real date. The setting items of the two are different.
- The ordinate range and scale interval determine the reading effect of the chart. Starting from zero for column charts is less likely to mislead.
- The date axis can be evenly arranged according to day, month, and year, and the start and end time of the display can be limited.
- Axis settings only change the display, not the original data; modifying the data source area will change the chart content.
After making an Excel chart, you often encounter several problems: the vertical axis starts from 0, and the values are crowded at the top and cannot be seen clearly; the date on the horizontal axis has one label per day, which is too dense to read; or the order of the months is wrong. These are all issues with axis settings and have nothing to do with chart titles and legends.
This article is written for readers who are illustrating weekly reports, monthly reports, course assignments, or papers. After reading, you can judge how the abscissa should be customized, adjust the range and scale interval of the ordinate, and use the date axis to limit the time period.
How to customize the abscissa of excel chart? One sentence conclusion
The setting of the abscissa depends on the data type. Text classification, ordinary numbers, and real dates each have their own settings. Determine the type first, and then adjust the range, interval, and order, which can save you a lot of detours.
| your situation | Recommended practices | reason |
|---|---|---|
| The horizontal axis is the department, product name or text month | Adjust the order of source data and set reverse categories if necessary | The text category axis is arranged in the order in which the data appears, and the time period cannot be set. |
| The horizontal axis is the real date, want to display it by month or year | Set as date axis, and then select the basic unit | The date axis is evenly arranged according to time units, and the intervals are more reasonable. |
| The date on the horizontal axis is text (such as "2024.3") | Convert the data to real dates first and then draw the graph | The text is not recognized as time, so the date axis cannot be used. |
| The vertical axis values are crowded together | Manually set minimum and maximum values | The automatic range may not include the range you want to highlight |
| The vertical axis scale is too dense or too thin | Set major scale units | The scale unit directly determines the number of labels |
Before you start: Confirm these points first
Before you start, take a look at the following items to avoid having to rework after making changes.
- Confirm whether the data is a real date. In Excel, real dates are usually recognized as date formats, and text is mostly displayed to the left. If the cell format is incorrect, the date axis will not take effect.
- Confirm the chart type. The date axis is mainly used for line charts, column charts, etc. The two axes of scatter charts are both numerical axes and are set in different ways.
- Consider whether the starting point of the vertical axis should be zero. Column charts start from non-zero, which will amplify the differences and easily lead to misjudgment by readers. Line charts usually don't start from scratch, but are illustrated on the chart.
- Pay attention to the number format of the tick labels. Label shows the format of the source data. If you want to display "ten thousand yuan" or a percentage, you need to adjust the numerical settings of the axis instead of just changing the title.
- Clear whether to change the display or data. Axis settings only affect the appearance of the chart and will not change the original values in the table. Changing the data range will change the contents of the chart.
Steps to set axis scale and date in Excel
The following steps apply to most Excel versions. The specific button locations may be slightly different depending on the version. You can find "Axis Format" or "Axis Options" along the same lines.
- Select the chart, right-click or double-click on the axis that needs to be modified, and open the axis formatting panel.
- In the coordinate axis options, change the "small value" and "large value" from automatic to fixed values. For example, if sales fluctuate between 800 and 1200 in a certain month, you can set the vertical axis to 800–1200 to make the changes clearer, but make sure that this does not exaggerate the difference.
- Set the "Major Scale Unit". When the value range is 0–1000, set the scale unit to 200, and the label will appear every 200, making the reading more intuitive.
- When the horizontal axis is date, select the axis type as date axis, and set the basic unit to "month". A piece of data spanning two years, with one record per day, will be displayed evenly by month. If you need to view only a certain period of time, you can also set the small date and large date in the same panel.
- When the horizontal axis is text classification and the chart is a bar chart, you can check the "Reverse Category" on the vertical classification axis so that the first item is displayed at the top, which is in line with reading habits.
- Select the appropriate format in the numerical area of the axis, such as thousands separator, percentage or one decimal place, and the labels will change synchronously.
- If you need to change the data, open the data source settings of the chart, re-specify the area of the series and category labels, and the coordinate axis will be updated accordingly.
- After the settings are completed, check the starting point, interval and label of each coordinate axis one by one to confirm that there are no omissions.
Check each item after completion
After the chart adjustment is completed, check it according to the table to find most display problems.
| Check items | How to see | What to do if there is a problem |
|---|---|---|
| vertical axis starting point | Whether the column chart starts from 0, or whether there is a description after truncation | If it is truncated, add annotation on the graph, or use a line graph to represent the trend. |
| tick interval | Are the differences between adjacent labels consistent and the number is appropriate? | Adjust major scale units; increase units when there are too many |
| Is the date axis valid? | Whether the horizontal axis labels are evenly arranged by time | If it is still a text axis, check whether the source data is a real date and reset it after conversion. |
| missing time points | Times without data in the date axis will be left blank and the polyline may be broken. | First check whether the source data is missing rows; if there is indeed no data, the breakpoints will be displayed normally. |
| Number formats and units | Whether the label unit is consistent with the title | Unify units and decimal places in titles, labels, and data |
| data range | Whether the chart contains new rows after adding data | Check whether the region covers all data in the data source settings |
📊 Core comparison
There are two common types of abscissas. The difference is mainly in scale and time processing:
| Compare dimensions | Text classification axis | date axis |
|---|---|---|
| Scale mode | One tick per category | By basic units such as days, months, years, etc. |
| missing time | Will not fill vacancies | Automatically leave space |
| Time period setting | Unable to set start and end time | Small and large dates can be set |
| Applicable data | Department, product name, text month | real date value |
If your horizontal axis is just ordered categories, use a text category axis. Whenever intervals and time periods are involved, a date axis should be used.
❓ Frequently Asked Questions (FAQ)
1. What should I do if the date on the abscissa is displayed as a string of numbers?
This is usually a cell format issue. Set the cell where the source data is located to date format, then refresh the chart, and the numbers will return to date display.
2. After changing the minimum value, the chart does not change. Why?
First make sure you are modifying the vertical coordinate and not other coordinate axes. Also check if the boundary is still "Auto", the setting will not take effect until it is changed to a fixed value.
3. How to make the horizontal axis only display a certain period of time?
Use the date axis and set the small date and large date in the axis options. Out-of-range data will not be displayed in the chart, and the source data itself will not be deleted.
4. The chart is a screenshot from a PDF. Can the coordinate axis be changed directly?
cannot. Charts in PDF are usually already images or fixed shapes, and the axes cannot be modified individually. You should go back to the original Excel file for modifications, then regenerate and replace the chart.
📝 Summary
How to customize the abscissa of an excel chart? The key is to first determine whether the data is a text category or a real date, and then select the corresponding settings. Once the vertical axis range, scale interval, and number format are adjusted, the chart will read much clearer. Check the starting point and data range before and after adjustments to ensure that the chart accurately reflects the original data.
