Lines on graph update but x axis doesn't

[ryankerslake]

I have an excel file containing share prices, and graphs to show how the shar price moves each month.

When I drag down the data for the date and share prices for the last 30 days, it updates the line on the graph to show the movement, but the date on the x axis doesn't change.

So last month, I was tracking the month of May, and the graph showed 30th May on the x axis which is correct.
This month, I'm tracking the month of June, but aftee dragging down all the data up intil 30th June, the x axis is still showing 30th May, when it should be showing 30th June


In the settingns, i've tried editing the bounds, and manually typing in the number format for 30th June, which is something like 44377.
When I entered this mannually in the 'bounds' section on graph settings, it still didn't update the date.

I also chdcked that the axis is selected as automatic in the settings

I can't share an image as it's for work and therefore the data is confidential

thank you

Ryan

[Andy Pope]

Any reason you can not move the sheet containing the chart and data to a new workbook, then replace the values with random ones and upload that workbook? A picture is not helpful.

If you are updating the series to use correct values and dates then the chart should reflect those new dates.

[ryankerslake]

Screenshot 2021-07-17 at 09.25.51.png


Please see attached

[AliGW]

That's a picture. It's not of much help, but it does beg a question: are you using Excel for this project (there is no Excel 2020 - please change your profile to MS365), or something else such as Google Sheets???

[ryankerslake]

How do I attach a file?

Even when on the main thread, it doesn't let me attach anything. I see the attachment function but nothing happens when I click on it.

But when I reply to a thread I don't see the attachment function?

Attachment 740734

[josephteh]

It's the Units (Major/Minor) on the Horizontal axis options that you have set. Reset them to Auto.

[ryankerslake]

This is how they looked orignally (155.3) Major and (31.06) Minor

I then selected Auto, and they changed to (200) Major and (40) Minor, the date on the x axis has now shifted to 3rd June 2021, but I need it to be 30th June 2021

Attachment 740736

Attachment 740737

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

Please update your profile, as requested earlier.

[josephteh]

Set the Maximum Bounds to a day after 30th June 2021.

[josephteh]

Set a date after 30th June, not a day after 30 June. Example, maybe 31st July or even 31st December.

[josephteh]

If 30th June is still not showing, drag the chart area wide until it shows on the chart.

[ryankerslake]

profile updated

[ryankerslake]

I've tried that but it doesn't help, any other ideas?

[ryankerslake]

I tried that but it didn't work, any other ideas?

[ryankerslake]

Does anyone know how the bounds work?

It says the bounds for 30th June is 44408 (when I convert from date format to general), but when I change the maximum bounds to that, it doesn't change anything

[MrShorty]

It is difficult to debug from a picture. So far, here's what I see:

0) Chart type is XY scatter
1) Axis min is set to 3 aug 2017 (42950)
2) Axis max wants to be set to 30 jun 2021 (44377, but you say this should be 44408, which my copy of Excel says is 31 July 2021)
3) Difference between max and min is 1427 days (30 Jun 2021) or 1458 days (31 July 2021).
4) Axis major unit is set to 155.3 (why the fraction of a day? is there time information that needs to be accounted for?).

Because the difference between the max and min of the axis is not an exact multiple of the major unit, there will be a vertical gridline/axis tickmark "missing" from the end of the horizontal axis. The chart will update, but a new gridline/tick mark/axis label will not appear until the axis maximum goes beyond the next exact multiple of the major unit value.

I don't know exactly what solution to suggest, because I am not sure exactly what you need this to do. My guess is you want to figure out a combination of axis minimum, maximum, and major unit so that the difference between max and min is an exact multiple of major unit and so that axis max is larger than the last date in your data set. If Excel's "auto" settings do not provide suitable values, you will need to come up with suitable values and enter those in the format axis dialog.

[josephteh]

Please attach a sample workbook.