I have an excel spreadsheet for charting share prices of the FTSE 100. The first worksheet stores closing prices of all the FTSE 100 shares
from May last year. Each day I paste & copy the closing prices into
this sheet. Sheet 2 consists of a pivot table that resorts the data from sheet 1
with Company names listed along the top of the worksheet as column
headings and dates listed down the left hand side of the sheet. So,
with every new days closing prices a new row gets added to the bottom
of the pivot table. Next, I have a sheet for each Company that lists the latest 70 dates
and their associated closing prices. The date information is imported
into each sheet by way of the following formula: =VLOOKUP(MAX(OFFSET(HistoricData!$A$1,1,0,COUNTA(HistoricData!$A:$A)-1,1)),HistoricDate,1,FALSE) Where 'HistoricData' is a name space that represents the pivot table
data containing the closing prices summary. The data in each worksheet for each Company updates perfectly. I have then created a line graph in each worksheet to show the
movement of the closing prices over time. The line graph updates
correctly upon each day's addition of closing prices, apart from the
dates on the X axis which seem to stubbornly stay at their original
values. This is also the case when you hover the mouse over a point on
the line graph - it displays the correct closing price as per the data
in the sheet but the incorrect date. I have tried setting the X axis property set to Automatic and Category
in the Chart options but all this does is change the scale of the X
axis not the date values displayed.
Can anyone help me with this problem?
Are you also updating the x values data the way you are with the y values? What do your series formulas point to for the range containing the X values? (Or do Chart menu, Source Data, Series tab, and check out the X Values or Category Labels).
- Jon
What a dipstick I am!! Thanks for pointing me to the obvious! My X value range was incorrectly set in relation to my Y data range. I.e. I had more X values than Y values, so the dates on the X axis were being 'offset' against the Y values when graphed.
Martin.
Martin - Here's a trick. Since X and Y ranges are usually the same size, I define the X range, then my defined name for my Y is: =OFFSET(myXvalues,0,1) Since I don't specify a size, it picks a range the same size as the reference range, myXvalues. And since there are often several Y ranges, they get defined like this: =OFFSET(myXvalues,0,1) =OFFSET(myXvalues,0,2) =OFFSET(myXvalues,0,3) =OFFSET(myXvalues,0,4)
- Jon
Thanks for the tip! Martin.
Martin - Here's a trick. Since X and Y ranges are usually the same size, I define the X range, then my defined name for my Y is: =OFFSET(myXvalues,0,1) Since I don't specify a size, it picks a range the same size as the reference range, myXvalues. And since there are often several Y ranges, they get defined like this: =OFFSET(myXvalues,0,1) =OFFSET(myXvalues,0,2) =OFFSET(myXvalues,0,3) =OFFSET(myXvalues,0,4) - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services
//PeltierTech.com/Excel/Charts/
_______
Anyone ever seen this before? I've got a dynamic name defining the axis for a chart - and it's working. You can see in the screenshot that the correct range is selected. However in the data selection area of the chart it just has the first 2 entries. It's as if it is not... refreshing?
//imgur.com/a/Lv1GPVn
Thanks in advance!
Often there is a need to change the data labels in your Excel 2016 graph. The procedure is a little different from the previous versions of Excel 2016. You will add corresponding data in the same table to create the label. You can also create a new set of data to populate the labels. Be more efficent and accomplish more with Excel Beginner to Advance Course up to 90% discount from this link.
1. Select the Chart that you have created and navigate to the Axis you want to change.
2. Right-click the axis you want to change and navigate to Select Data and the Select Data Source window will pop up, click Edit
3. The Edit Series window will open up, then you can select a series of data that you would like to change.
4. Click Ok
For a time series like months, when you click select data you will not have the option to directly edit the x-axis. However, you still can by simply clicking Edit Legend Series and choosing X values. However, if you graph is chronological or time series based you need to pick a complementary chronological data. For categorical data, you don’t need to worry about this. Also, you can directly change x values from Select Data Source window. But essentially the steps are the same.
Follow the visuals instructions below or watch the video:
Create a graph. From the image below, you can see that this graph is based on the index column and the Selected Period column. Our goal is to replace the X axis with data from Date Column.
Right-click the graph to options to format the graph. In the options window, navigate to Select Data to change the label axis data.
Once you choose Select Data, an Edit Series window will open with information on the axis.
Our goal is to change the x-axis so that you can delete the x values and replace them with the new values. If you have chronological data, you can directly access the labels.
Once, you add the new data and click OK, you will be able to see the update labels reflecting the new data.