Graphing X-axis to right margin & expanding x-axis for recent data

Weekly I use several large (65meg) excel files to calculate market data using a macro that runs for 7 1/2 hours. I then use PPT to publish the data during a Zoom meeting that is then achieved on YouTube. I would like to learn how to automate a couple of issues.

1) I would like to learn how to expand the x-axis (dates) for recent data. For example, I would like data from 1/1/2014 through 12/31/2021 to be normally spaced, and data from 1/1/2022 through 12/31/2023 to have double spacing. I currently do this manually by plotting two extra years and then using the snip tool to paste the image over the graph and then elongate the copied snip.

2) I would also like to move the default y-axis (data) from the left side of the graph to the right sided of the graph. I currently do this by plotting and not displaying the primary data, deleting the primary y-axis, and plotting the real data on the secondary axis.

The result of these two efforts is
Right axis.jpg

The goal is to learn if and how to do this in a more automated manner.

Thanks

Without using any dummy axis, simply combining 2 tables, with first item in the second table linked to the last item in the first table.

Thank you.

2) I found the vertical axis control. The default is "Between dates" which does seem to make sense. The second option was "At date" with a box for the user to enter any date they would like. The third option is "At maximum date" which places the vertical axis at the right. These options are under format axis.

1) I think what you are suggesting is change from line graph and dates to a scatter graph. Then place the data in every row for the condensed plotting and skip a row between data points to get a more spread out section. Although this does work, it has the disadvantages of losing the actual date label along the x-axis and prevents showing a line between data points. I would also have to add the blank lines for the recent data and then remove these blank lines after two years to make the data condensed when no longer recent. Although this would work for my own use, the 30+ other viewers of these weekly graphs would likely be confused by the lack of the date reference.

Still looking for other ideas.

For now, I will make the first graph from 1/1/2014 through 1/15/2023 and the second graph 1/1/2019 through 1/1/2025. I then use the snip tool and paste the first graph onto top of the second graph. I align the top right corner and then adjust the scale of the pasted snip to the second graph. It works out surprisingly well. I can add all 52 weeks of 2024 before I need to get a new image of the graph. By going to 1/15/2023 opposed to 1/1/2023 made it a lot easier to match the data at the seam of the two graphs.

Could your provide additional guidance as I am not understanding your suggestion. I have weekly data points for ten years and 2024 will be the eleventh year, for 572 data points at the end of 2024. I would like the first 9 years or 468 data points to be condensed and data points for 2023 and 2024 469-572 to be stretched out at about double the spacing of data points 1-468. The reason to stretch out the recent data is so you can more accurately determine the week that the data changes direction.

Thanks for the additional clarity.

I was confused by josephteh's solution as I took 2 tables opposed to 2 charts.

I was adding the blank lines as I was using the Excel default of assigning each data point to a numerically sequenced input. By adding the blank row Excel would also increment the blank row effectively increasing the horizontal spread of recent data by two. I now believe you are suggesting a two column input table where the first column controls the horizontal spacing and the second column the data I want to plot. For old/condensed data I could increment the first column by one and for the recent/spread out data I could increment the first column by two. Your method would let me do any ratio as I could increase the old/condensed data by ten and the recent/spread out data by by 11 for a 10% spread or 29 for a 2.9 times spread.

I really want to maintain the date references for the x-axis and the annual and quarterly gridlines.

So I think I will cut and paste two Excel charts together to look like one. This requires an annual redo as I add in a new year of data. For 2024 I will show 2023 and 2024 as spread out and double 2014-2022. But for 2025, I might try displaying 2023, 2024 and 2025 in the spread out format using 6 years of horizontal spacing for double 6/3 horizontal spacing. Then in 2026 condense four years of data 2023-2026 in to the same horizontal spacing resulting in a 6/4 for horizontal spacing.

I have been reporting the same information since 1997 and it has and will continue to evolve.

Thanks again for the input.