Excel - shift secondary chart axis to get round values on it

Maybe you could add another Helper series, that the X-values include the number that you need on X-axis, the Y-values are all 0.

Then add the series into the chart.

Set no laber for the lower X-axis, but add the lables for Helper series.

Then double click each labe, enter "=" in formula bar, then click the number that you want to show.

Big thanks to Emily's answer for great suggestion. I have created more complex solution with 2nd axis lines.

This is final result (I kept it orange, it's more distinguishable.).

How it works

Another data series is used for a secondary axis (orange). This helping series goes out of the visible part of the chart. So only vertical lines are visible.

Numbers on horizontal axis are data labels (data from a range) actually. This is how whole helping data series looks like

Notice that each point of this series has its own label. But labels are shown only for points presented in the visible part of the chart. (Labels visible in the final result are placed on X-axis (coordinate on Y-axis is zero).

Peaks above and under are needed for cosmetic reasons. Otherwise horizontal connections (lines) between visible points are visible too.

The helping data series is driven by this table named HelpingTable

Notice column named Label which is source of the data labels.

This HelpingTable could be stretch to desired length. Stretching leads to dynamic change of the helping data series in the chart. Data in the table are calculated from these parameters

• Min Y bottom end of vertical lines
• Max Y top end of vertical lines
• Min X position of the first vertical line
• Max X position of the last vertical line
• Step X distance between vertical lines
• RowsNeeded how much you have to stretch the HelpingTable to cover whole wanted range
• t/Δt diff (static) difference between values on the chart axis and a new "axis" represented by helping data series

There is also 2nd helping table named YValuesTable which holds Y values of the helping data series points.

Only column Y is calculated here. Using this simple formulas:

• AboveTopAxis = Max_Y+(Max_Y-Min_Y)
• TopAxis = Max_Y
• BottomAxis = Min_Y
• BelowBottomAxis = Min_Y-(Max_Y-Min_Y)

Finally the HelpingTable columns are calculated using these formulas

• Row =ROW()-ROW(HelpingTable[[#Headers],[Row]])
• Step =ROUNDUP((ROW()-ROW(HelpingTable[#Headers]))/3,0)
• Label =Min_X+([@Step]-1)*Step_X
• X =[@Label]+t_Δt_diff
• Y =INDEX(YValuesTable[Y],MATCH(IF(MOD([@Row],6)>3,6-MOD([@Row],6),MOD([@Row],6)),YValuesTable[ID],0))

Calculation of the Y column is maybe a little overkill, but I had limited time.