Line chart, value labeling

I like to change line graph settings so that only one value will appea
on the line (not all the values).
I right clicked on the point on the line, selected format data series
selected Data Labels tab, checked box Show Values. and then click ok.
All the data values show up on the line. But, I like to see only on
specific value. How do we do that

--
Message posted from http://www.ExcelForum.com

0
4/15/2004 8:19:27 PM
excel.charting 18370 articles. 0 followers. Follow

19 Replies
480 Views

Similar Articles

[PageSpeed] 33

Hi,

You need to select the line series and then select a single point before 
displaying the format dialog and enabling data labels.

Cheers
Andy

NYBoy < wrote:

> I like to change line graph settings so that only one value will appear
> on the line (not all the values).
> I right clicked on the point on the line, selected format data series,
> selected Data Labels tab, checked box Show Values. and then click ok.
> All the data values show up on the line. But, I like to see only one
> specific value. How do we do that?
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
4/15/2004 8:32:53 PM
Thanks Andy,  It works!!

One more question. 
It would be the last point. Every month I update the data and the lin
extends and new data is entered. I always want to see the value of las
point. Would it work

--
Message posted from http://www.ExcelForum.com

0
4/15/2004 8:49:18 PM
Jon Peltier's has a page on the very subject!

http://peltiertech.com/Excel/Charts/LabelLastPoint.html

NYBoy < wrote:

> Thanks Andy,  It works!!
> 
> One more question. 
> It would be the last point. Every month I update the data and the line
> extends and new data is entered. I always want to see the value of last
> point. Would it work?
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
4/15/2004 9:02:19 PM
Thanks Andy, 
I am not familiar with Excel VBA project stuff. I don't know where t
paste once I copied the commands.
I think I am gonna have ask my boss to do it

--
Message posted from http://www.ExcelForum.com

0
4/15/2004 10:03:44 PM
How do we close the thread

--
Message posted from http://www.ExcelForum.com

0
4/15/2004 10:43:42 PM
How do we close the thread?  :confused

--
Message posted from http://www.ExcelForum.com

0
4/15/2004 10:44:45 PM
If you have the workbook with the chart open use ALT+F11 to get to the 
VBE (Visual Basic Editor/Environment).
Use the Insert menu to add a Module.
Paste the code from Jon's page.

ALT+F11 to return to your workbook.
select the chart and the press ALT+F8 to display the Macro dialog.
 From here run the LastPointLabel Macro.

To 'close' the thread just post a reply to say the solution worked.

Cheers
Andy

NYBoy < wrote:

> Thanks Andy, 
> I am not familiar with Excel VBA project stuff. I don't know where to
> paste once I copied the commands.
> I think I am gonna have ask my boss to do it.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
4/15/2004 11:07:50 PM
Thanks Andy, It somewhat worked. 
But it gave me the Title (of the line from that row) instead of th
mathematical value of the last point

--
Message posted from http://www.ExcelForum.com

0
4/15/2004 11:26:49 PM
This is the first time I am visiting the Exceltip.com....it's prett
cool..

Andy, I went to your website and downloaded your clocks. When I clic
Start, a msg pop up saying "macros are disabled....". Is it just me o
my computer?

--
Message posted from http://www.ExcelForum.com

0
4/15/2004 11:42:58 PM
NY -

By default, the security settings in Excel are high. If you set them to 
medium, you at least have an option to enable macros in each workbook 
you open. On the Tools menu, select Macros, then Security, and on the 
Security Level tab, select Medium.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/
_______

NYBoy < wrote:
> This is the first time I am visiting the Exceltip.com....it's pretty
> cool..
> 
> Andy, I went to your website and downloaded your clocks. When I click
> Start, a msg pop up saying "macros are disabled....". Is it just me or
> my computer??
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

0
jonNO1 (306)
4/16/2004 4:02:14 AM
Gentlemen, It doesn't give the mathematical value. Instead, it gives m
the legand of the line (e.g. Monthly Performace). 
I am looking for the value of the last point.
By the ways, you guys are great help...I learn a lot from here...


*********************************************
Re: Line chart, value labeling 
If you have the workbook with the chart open use ALT+F11 to get to the
VBE (Visual Basic Editor/Environment).
Use the Insert menu to add a Module.
Paste the code from Jon's page.

ALT+F11 to return to your workbook.
select the chart and the press ALT+F8 to display the Macro dialog.
From here run the LastPointLabel Macro.

To 'close' the thread just post a reply to say the solution worked.

Cheers
Andy

NYBoy < wrote

--
Message posted from http://www.ExcelForum.com

0
4/16/2004 2:05:27 PM
Just a little tweak to the code is needed.

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long
For Each mySrs In ActiveChart.SeriesCollection
     With mySrs
         nPts = .Points.Count
         mySrs.Points(nPts).ApplyDataLabels _
             Type:=xlDataLabelsShowValue, _
             AutoText:=True, LegendKey:=False
         mySrs.Points(nPts).DataLabel.Text = mySrs.Values ' <**
     End With
Next
End Sub

Cheers
Andy

NYBoy < wrote:

> Gentlemen, It doesn't give the mathematical value. Instead, it gives me
> the legand of the line (e.g. Monthly Performace). 
> I am looking for the value of the last point.
> By the ways, you guys are great help...I learn a lot from here...
> 
> 
> *********************************************
> Re: Line chart, value labeling 
> If you have the workbook with the chart open use ALT+F11 to get to the
> VBE (Visual Basic Editor/Environment).
> Use the Insert menu to add a Module.
> Paste the code from Jon's page.
> 
> ALT+F11 to return to your workbook.
> select the chart and the press ALT+F8 to display the Macro dialog.
> From here run the LastPointLabel Macro.
> 
> To 'close' the thread just post a reply to say the solution worked.
> 
> Cheers
> Andy
> 
> NYBoy < wrote:
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
4/16/2004 2:28:09 PM
Andy, It worked. It gave me the value of the last point (for month
April) but.....

Just to test...As I entered the new data in the spreadsheet for next
month, those values didn't role to the next point. 
I was expecting that it would give me new values for the new last
point... 
Sorry to bother you again..


---
Message posted from http://www.ExcelForum.com/

0
4/16/2004 3:12:05 PM
You did run the macro again once the data changed.
Also note the additional code line to clear any previous labels.

Sub LastPointLabel()
Dim mySrs As Series
Dim nPts As Long
For Each mySrs In ActiveChart.SeriesCollection
     With mySrs
         .HasDataLabels = False  ' Remove previous labels
         nPts = .Points.Count
         mySrs.Points(nPts).ApplyDataLabels _
             Type:=xlDataLabelsShowValue, _
             AutoText:=True, LegendKey:=False
         mySrs.Points(nPts).DataLabel.Text = mySrs.Values ' <**
     End With
Next
End Sub


NYBoy < wrote:

> Andy, It worked. It gave me the value of the last point (for month
> April) but.....
> 
> Just to test...As I entered the new data in the spreadsheet for next
> month, those values didn't role to the next point. 
> I was expecting that it would give me new values for the new last
> point... 
> Sorry to bother you again..
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
4/16/2004 3:47:29 PM
Andy, It worked great. I copied your new code and pasted and run the
macro again....

But, still my goal is not to touch the chart at all. Can we also set so
that the marco would run automatically.....
If there is no way, then I can live with whatever I have so far...

but another thing is, everytime I run the marcos, text size, color,
etc... changes. Is there a permanent fix?


---
Message posted from http://www.ExcelForum.com/

0
4/16/2004 5:19:05 PM
Andy, It worked great. I copied your new code and pasted and run th
macro again....

But, still my goal is not to touch the chart at all. Can we also set s
that the marco would run automatically.....
If there is no other way, then I can live with whatever I have s
far...

but another thing is, everytime I run the marcos, text size, color
etc... changes. Is there a permanent fix

--
Message posted from http://www.ExcelForum.com

0
4/16/2004 6:22:04 PM
You will need to add the formatting to the macro. Use the macro recorder 
whilst resetting the information to get the code required.

I have added a page to my site that shows how to label the last point 
using dummy data series. This maybe more appropriate if you have dynamic 
charts.
<http://www.andypope.info/charts/Labellast.htm>

Cheers
Andy

NYBoy < wrote:

> Andy, It worked great. I copied your new code and pasted and run the
> macro again....
> 
> But, still my goal is not to touch the chart at all. Can we also set so
> that the marco would run automatically.....
> If there is no other way, then I can live with whatever I have so
> far...
> 
> but another thing is, everytime I run the marcos, text size, color,
> etc... changes. Is there a permanent fix?
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
4/17/2004 3:48:30 PM
Andy, Can I send you the file? May be you can do it the quick way

--
Message posted from http://www.ExcelForum.com

0
4/19/2004 4:03:55 PM
Yes, email it to me off list.

NYBoy < wrote:

> Andy, Can I send you the file? May be you can do it the quick way.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
4/19/2004 4:25:16 PM
Reply:

Similar Artilces:

Deleting "hidden" charts on a worksheet
Hi everyone, I've been playing around w/ a worksheet that contains data and charts, inserting and removing multiple rows at a time. Unfortunately, I did not change the properties of the charts and they were all set to "Move and Size w/ Cells". When I deleted a series of rows, these charts disappeared and left a line across the spreadsheet where they used to be. I've tried everything to get rid of them (because they are now calculating incorrectly and giving me error messages), including deleting the rows where they appear, trying to select them on the sheet, but nothing w...

Re : Excel Range of Values Amidst Characteristic Transitions
Re : Excel Range of Values Amidst Characteristic Transitions Enter an Excel worksheet ; now that the tabulation is prepared for (preferential) presentation, Column A is shown as follows :- 01 8.0 02 8.0 03 <A Blank Row> 04 8.0 05 <A Blank Row> 06 <A Blank Row> 07 9.0 08 <A Blank Row> 09 9.0 10 9.0 11 <A Blank Row> 12 6.5 13 6.5 14 <A Blank Row> 15 <A Blank Row> 16 <A Blank Row> 17 8.0 18 <A Blank Row> 19 8.0 20 <A Blank Row> 21 8.0 22 <A Blank Row> 23 6.5 24 <A Blank Row> 25 ...

Pivot chart and Total row
Hi All I have a Pivot table and attached Pivot chart (all good so far). However, there are three columns (drop column details here) which are the two columns of data and a grand total column (showing the average of my two data columns) When I turn it into a pivot chart it does not display the grand total column as well (is there a way to do this). I have tried to go into the chart options but these are locked. I can copy and paste the results but this is a bit clumsy. Any suggestions - any help greatly appreciated Kind regards Rexmann PS using excel 2003 ...

How do I add column A data labels to a scatter of columns B and C?
I have made a scatter plot using columns B and C as my x and y coordinates. I would like to use the data in column A to label my points. I already have the XY chart labeler 97 installed and selected, but I don't know how to get the data labels to show up on my graph. Thank you! Hi Christina - There are a couple of well written Excel add-ins you can download to do this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Both are free downloads, they install easily, and they integrate into Excel's interface very nicely. They l...

Changing cell values through form
Hi... I'm not that good with access so I decided to look for help here. I have a table filled with drivers. Starting number, Driver name, and Number of laps are the columns. My Idea is to make a form that will list all the drivers, and add a value of +1 into the "Number of laps" cell to that driver when double clicked. That would be a sort of Lap counter form... Every suggestion is welcome. -- ::: www.ktvj-online.com - Motocros Portal ::: ::: www.forum.ktvj-online.com - Forum ::: ...

VBA to insert .xlborder if cell value not equal to previous cell
I've got a worksheet and I'm wondering whether it is possible to insert a line when a value in Column A, B, C & D does not equal the values in the row above or below it. I've currently got a formula in Column A that reads.... =IF(AND(B3=B2,C3=C2,D3=D2,E3=E2),"","IL") and a conditional format that if the cell value is equal to "IL" then put a border. Wondering if there is a better way to do this via VBA or is that the better way? In your conditional formatting formula, instead of =A1="IL" (which I assume is what you've...

2nd try, parameter values
I'm having problems posting... sorry if this comes up twice. I have parameter values set to make things easier for the users... The prompt pulls Last Name or Emp #. If the Emp# is not available, is there a way to search by last name, including suffixes, but only entering the last name? or even just the first letter of the last name? On Thu, 19 Jul 2007 09:31:24 -0700, melinda.pluma@agedwards.com wrote: >I'm having problems posting... sorry if this comes up twice. > >I have parameter values set to make things easier for the users... > >The prompt pulls Last Name or...

How do I delete chart superimposed on Excel document?
Created Excel document. Print preview did not show gridlines. In attempting to get gridlines, somehow got into charts. A chart was created and is superimposed on original document. How do I get rid of the chart? How do I get gridlines on to original document? Oz Right-click on the chart and Edit>Clear>All To print gridlines.................File>Page Setup>Sheet Check "gridlines" and uncheck "draft quality". Gord Dibben MS Excel MVP On Fri, 4 Aug 2006 14:27:01 -0700, Oz Osborn <Oz Osborn@discussions.microsoft.com> wrote: >Created Excel docume...

Using commas in "Value List" list boxes
I am trying to add a column that contains a comma to a 'value list' list box. I have a patient name (Last, first MI) column and a patient ID column (among others). I have my Row Source Type set to Value List and the number of columns correct, column widths etc. all correct, but when I try and do this: lstbox.AddItem "TreatmentId;LastName, FirstName MI;PatientId" The help documentation shows that the delimeter should be the ; not comma's, however instead of getting the name all in one column, it is being placed in two separate columns (because of the comma... it is...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

Chart: range info from seriescollection?
I would like to fill a combobox (Rowsource) with data that are used in for instance the XValues or Values of a Seriescollection of a graph. "ActiveChart.SeriesCollection(1).Values" does not give the right type (I was hoping for a string type of info like "Sheet1.Range(a1:a10)"). How to get a Range to be used as Rowsource for my combobox??? Thanks for your suggestions, JB The Values and XValues will return an array of values. To get the range address like this, you will need to parse the series formula. Or you could use John Walkenbach's class module for extractin...

Is it possible to save an excel chart as .emf?
I would like to save an excel chart as .emf instead of .jpeg or .gif using the script below. Is this possible? Sub SaveChartAsGIF () Fname = ThisWorkbook.Path & "\" & ActiveChart.Name & ".gif" ActiveChart.Export FileName:=Fname, FilterName:="GIF" End Sub ...

Step change charts
Help, I am trying to create a bar charts by year but only want to show increases and decreases through the middle years as step changes i.e. 1999 100 2000 +10 2001 -30 2002 +40 2003 120 How can I do this? What I should see is the first vertical bar being 100 then from the 100 level an increase of 10 to get to 110 then a decrease bar of -30 (from the 110 level) to get to 80 etc etc. For a tutorial, see the Excel | Charts | Waterfall Chart page of my web site. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Cus...

Excel Bar Chart with many elements Example
I am working with a survey database where one field represents one question in the Survey. Some questions allow the user to enter multiple answers. This data isn't normalized but I have t work with what I've got. Anyway I tried to create a chart in Acces with the wizard but got the error: "Cannot have more than elements in the chart". Anyway I attempted to recreate the char in Excel but my chart looks crammed and canno print entirely within one page. Does anyone know how or have a example of how to create a bar chart with many elements in a clea and consice manner T...

Can no longer cut/paste ranges in chart "edit data" fields?
In Excel 2007 I can no longer cut/paste cell ranges into the x and y datasets using the edit data tool. Is there a way to turn this back on? Also the chart range resets itself to the current length of the data, however, in many instances I want to set an exact future range. Is there a way to have excel not reset the range to what it "thinks" I want. > In Excel 2007 I can no longer cut/paste cell ranges into the x and y > datasets > using the edit data tool. Is there a way to turn this back on? I have no problem doing what you can't. Could you provide more det...

ReInstallation of Outlook Client off line version on Vista Office 2007 Version 3.0vc
Has anybody successfully managed to achieve this as i need to add a new user to a vista machine which already has a CRM offline user attached. http://www.microsoft.com/dynamics/crm/using/deploy/removeclient.mspx Following the above link i know works fine for XP and 2003, but as things have changed in 2007 the above i am sure would not work with the new SQL Database that is installed with Vista and Outlook 2007. Any ideas. ...

Changing bar graph colours according to value
Is there any way to have the colours of the bars in a bar graph change according to value? Hi, Have a look at Jon's example. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy nikki8327 wrote: > Is there any way to have the colours of the bars in a bar graph change > according to value? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Need to format text in header, but value is generated using VBA
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6360C.2372C770 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I wanted to reference a cell value in my spreadsheet header and found = out how to do that using this function: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim WS As Worksheet For Each WS In Worksheets ActiveSheet.PageSetup.RightHeader =3D _ Format(Worksheets("Time Period Info").Range("B3").Value) =20 Next WS End Sub However, this leaves my text for ...

Moving average trend line
When graphing, does a moving average trend line give equal weight to all data within the trend period (30 days in my case) or does the more recent data have greater weight (significance)? If the latter, do you know the weighting function? Thanks, Don Culp no weighting is done. Don Culp wrote: > When graphing, does a moving average trend line give equal weight to all > data within the trend period (30 days in my case) or does the more recent > data have greater weight (significance)? If the latter, do you know the > weighting function? > > Thanks, > Don Culp ...

can not see chart
I created a simple chart. For some odd reason the chart is all gray. I can only see the chart if i select the chart object. Please help! Thank you! Hi, Via the menus Tools > Options. On the View tab select Obejcts Show All. Cheers Andy Nick wrote: > I created a simple chart. For some odd reason the chart is all gray. I can > only see the chart if i select the chart object. Please help! Thank you! -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

"Disappearing" lines of text in Word docs
I've noticed a really unusual intermittent bug in my version of MS Word. Suppose there are two consecutive lines of text in my document, Line A and Line B. I want to insert a new line between Lines A & B, so I position the cursor in between them and hit return (not highlighting Line B beforehand, because I'm not trying to get rid of it). When I do so, Line B seems to disappear, with the cursor (and empty line in front of it) taking the place of Line B. However, when I hit "page up" or "page down", Line B reappears. Anyone know why this might ...

Finding X- & Y-intercepts of lines crossing curves
I created some charts with multiple data series by specifying certain points on the curves. THe series are displayed as lines. Now I want to draw (or specify the Y coordinates of) a horizontal line on the chart, & have Excel tell me the X intercepts where the horizontal line crosses each curve. Ditto for drawing a vertical line & reading Y intercepts Any ideas Thanks!! Hi Dale, Maybe you adapt my example of intersections. http://www.andypope.info/charts/intersection.htm Cheers Andy Dale wrote: > I created some charts with multiple data series by specifying certain points on...

How to avoid when linked source data is changed, chart turns blan.
When I created new charts in Excel 2003, each previous chart became blank. Therefor I saved the file unter a new name and copied the previous chart to the new file. This created ofcourse links to the other files. Now I want to change the source file, but when I do that the chart becomes blank as well. Anybody who had this problem before and found out how to solve it ? Rgds, Chris ...

chart type keeps changing
My chart is "embedded" in my worksheet. The worksheet has months across the top in one row and net income per share in a row below it for each corresponding month. The x-axis is pulling data for the month and year eg. Jan-04...Feb-06. The y-axis is pulling data from a different row for the net income for each month. The chart has been maintained since Jan-04. It is a standard clustered column w/ a 3-D visual effect. I am trying to enter data for March '06 in data source, series one. I am updating the series 1 with the new column address that represents the new month eg...

Highlight a line in a CListCtrl
I want to highlight a line in a CListCtrl via the code and cant find a member function to do this. Anyone know? >I want to highlight a line in a CListCtrl via the code and cant find a member >function to do this. Chris, By highlight, do you just mean the normal selection state? If so, just use SetItemState with LVIS_SELECTED. Dave and you may require to set this list view extended style to highlight items and subitems (full line) through SetExtendedStyle LVS_EX_FULLROWSELECT "David Lowndes" <DavidL@example.invalid> wrote in message news:9gp913dspm6mprunai829je...