How to Show Yearly Labels on an Excel Chart Axis While Keeping Data Dips in WPS Office
Question details
The user wants to format a date-based Excel chart so the horizontal axis displays yearly labels while retaining all individual data points, rises, and dips across multiple years.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- A chart with date-based data spanning several years needs readable yearly axis labels without aggregating or hiding daily or monthly point fluctuations.
- Observed behavior
- The axis currently shows non-yearly or dense date labels, making the yearly trend harder to read while the underlying data points must remain visible.
Open the workbook in WPS Office Spreadsheet, select the chart you want to format, and make sure the horizontal axis is a date-based axis rather than a text axis.
Format the Horizontal Axis with Yearly Major Units and a Custom yyyy Number Format
Use this method when you want readable yearly labels on a date-based Excel chart while keeping all individual data points and dips visible.
The Major unit controls the interval for primary axis labels, and the Minor unit controls smaller tick spacing. Choosing 2 Years for Major and 1 Year for Minor gives a clean yearly structure, while the custom yyyy number format shows only the year instead of full dates. This formatting changes only the axis display—not the chart data.
Double-click any horizontal axis label on the chart to open the Format Axis options.
Under Units, set Major to 2 Years and Minor to 1 Year; leave Base set to Days.
Expand Number, set Category to Custom, enter yyyy in the Format Code box, and click Add.
Show Readable Yearly Axis Labels Without Losing Data Dips in WPS Office
Open your Excel workbook in WPS Office Spreadsheet and use the Format Axis pane to set a 2-year Major unit, a 1-year Minor unit, and a custom yyyy number format. This keeps daily or monthly data points and dips visible while making the chart axis read cleanly by year.
- 1. Open the workbook in WPS Office Spreadsheet: Launch WPS Office Spreadsheet and open the Excel file that contains the chart you want to format.
- 2. Open the Format Axis pane: Click the chart, then double-click any horizontal axis label to display the axis formatting options.
- 3. Configure the yearly axis units: Under Units, set Major to 2 Years, Minor to 1 Year, and Base to Days.
- 4. Apply the yyyy custom number format: Expand Number, select Custom, type yyyy in the Format Code field, and click Add.
- 5. Review the chart: Confirm that the horizontal axis shows yearly labels and that the data points, rises, and dips remain visible.
Frequently Asked Questions
How do I make an Excel chart show only years on the horizontal axis?
Double-click any horizontal axis label to open Format Axis, set Major unit to 2 Years and Minor unit to 1 Year, then apply a custom number format of yyyy.
Why do my axis labels still show full dates instead of years?
The number format may still be set to a date category. Expand Number in Format Axis, choose Custom, enter yyyy, and click Add so labels display only the year.
Will changing the axis units to years combine or hide my data points?
No. Setting Major and Minor units only changes how axis ticks and labels are spaced. All underlying data points, rises, and dips remain unchanged.
Can I set the year interval to something other than every 2 years?
Yes. In the same Format Axis pane, set Major unit to 1 Year, 2 Years, 5 Years, or another date interval that fits your time span, and keep the custom yyyy format.
Does this work when I open the chart in WPS Office Spreadsheet?
Yes. WPS Office Spreadsheet supports Excel chart axis formatting, including major/minor units and custom number formats, so you can apply the same steps.
