Hi, in the attached spreadsheet, I want to have the x-axis show specific interval values only, e.g. all 50 years (or more or less) but always the most right hand value, i.e. 2019 here.
Anyone can help how to do this?
I had to construct the x-axis as category rather than date because of presumably issues with years prior to 1900.
Thanks
I am unaware of any way to do this by formatting the axis. The usual approaches I see to this involve data labels. See if this works for you:
1) Select the final data point of the series.
1a) My version of Excel would not allow me to select the final data point of the series as an area chart, so I changed the chart type to line.
1b) Select the final data point of the series (click on a point in the series to select the data series, then click on the final point in the data series to select just that data point). It might also be helpful to format the chart so it only shows that last several years to make it easier to select just one data point (I formatted the axis as date and chose a minimum date much closer to the end date).
1c) With the last data point selected, add data labels to the last point https://support.microsoft.com/en-us/...rs=en-us&ad=us
1d) Format the data label to show just the category axis. Drag the data label to the bottom of the chart near the axis.
1e) Undo the changes to chart type and axis type/range (change chart type back to area, change axis to text).
2) Evaluate result.
Did that do what you want?
Originally Posted by shg
like this ?
Last edited by bsalv; 10-25-2021 at 01:28 PM.
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
@bsalv: For me this is showing values from 1700 to 2050 in steps of 50.
I'd like to have after 2000 e.g. 2019 (if increments >20), or if increments = 10, then 2000, 2010, 2019; but the last value on the x-axis should always be displayed, here 2019.
I was thinking someting along those lines: https://www.myonlinetraininghub.com/...art-axis-dates
I got it working for a Scatter chart.
But once I change it to a stacked area chart, it also starts to stack the 'Positions' column even if I bump it onto the secondary axis - which doesn't make much sense to me. Presumably there's no second axis possible in a stacked area chart?
But I will need a stacked area chart ultimately as there's multiple columns like the one titled 'R'.
@MrShorty: thanks for the suggestions but that sounds like a rather manual approach, i.e. once I change something, I will have to realign that one data label every time. Is that right?
I hope I understood your description.
I guess by designating the dummy as the first data to be stacked, it's almost doing what it should.
I mean to attached this before already which is my current working status.
From here I suppose it's still manual moving of data labels. Brings back memories or chart labelling add-ins from some 10 yrs. ago xD
@bsalv: I care more about the x-axis: In your example it goes until 2020.
While it is clear from the data that the last value is "probably" 2019, it's not so easier for larger intervals.
My original data starts in 1750, and when I then make 50 yrs. steps, the last ones will be 2000 and then 2019.
Your approach might then extend the x-axis to 2050, making it near impossible to say if the last data point is 2018, 2019, 2020 or 2021 etc.
Apologies if I am still not clear.
But maybe my "close to what I want workbook" is more illustrative.
If you don't like labeling the last data point, I would probably do something based on a stacked area + XY scatter combination chart. Basically, you build the stacked area chart as normal. Then add a "dummy" series consisting of a single X,Y pair and change the chart type of this series to XY scatter (my copy of Excel automatically adds secondary X and Y axes). Then add a data label to this series/point that will show the "last" value in the horizontal category range. Starting with your original file in post #1:
1) I copied column C into column D, added it as a second data series to the chart, then changed the chart type to stacked area -- just so I was working with a stacked area chart.
2) I enter 1 in N4, 0 in O4, and =MAX(B1:B1000) in P4.
3) Select N4:O4 -> Select Chart -> Paste special -> Add new series with the first column containing the X values.
4) Select the newly added data series -> Change Chart type -> XY scatter. Excel adds secondary axes and I have a marked point at 1,0 on the secondary axis.
5) Select the secondary horizontal axis and format it to have a min of 0 and a max of 1. Same for the secondary vertical axis. Now, the X,Y point 1,0 will always be in the lower right corner of the chart.
6) Select the dummy series and add a data label to it, use the "values from cells" option for this data label and choose P4 for the data label, and make sure "below" is checked for the position of the data label.
7) Test it carefully to make sure the chart will correctly position and label this point through any edits you expect to make.
8) Format the secondary axes and the dummy series so that they are invisible (no marker, no tick marks, no lines).
It takes a little more to set up, but it should be more reliable through edits. Will something like that work for you?
MrShorty, thanks for the step-by-step description.
I managed to replicate this. Attached for reference.
The rest will be some manual moving I guess.
Thanks for your efforts, much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks