Dynamic data label placement for graphs

I've read the article KB179199 on how to create custom 
data labels for charts and it's good, however doesn't meet 
all my requirements.
Is there a way to have the data labels automatically move 
to a desired location relative to the graph?
For instance, I'm using 50 bar graphs per work book and 
would like each data label to be just above their 
respective bar.

TIA
Hafeez
0
no (445)
7/7/2004 5:10:08 PM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
760 Views

Similar Articles

[PageSpeed] 19

I looked up article 179199 and got this:

   BUG: Grid Based on .QPR File Appears Blank in Visual FoxPro 5.0

which probably isn't the one you meant. By above the bar, are they 
horizontal bars and you want them to be between their bar and the next 
higher one? Or do you mean they're columns (vertical bars) and you want 
them above the top of the column?

If it's a column chart, one of the positions you can specify for data 
labels is Above. Double click on the labels, click on the Alignment tab, 
and choose Above from the dropdown.

If it's a bar chart, and you want to move the labels off the axis of the 
bars, you'll need to use VBA. Start with the labels in the Center 
position, then determine how far you want to move the bars; a good guess 
is PlotArea.InsideHeight/SeriesCollection(1).Points.Count/2. Record a 
macro while you drag one label around manually to get the syntax right.

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

Hafeez wrote:

> I've read the article KB179199 on how to create custom 
> data labels for charts and it's good, however doesn't meet 
> all my requirements.
> Is there a way to have the data labels automatically move 
> to a desired location relative to the graph?
> For instance, I'm using 50 bar graphs per work book and 
> would like each data label to be just above their 
> respective bar.
> 
> TIA
> Hafeez

0
DOjonNOT (619)
7/7/2004 5:52:13 PM
Hi Jon
My appologies, its 279199.
The graphs are vertical bar graphs.
What I would like to do is to change the value of each 
label in each graph.
What I found out just now was that the number I wish to 
display as the label appears 1 cell to the right of the 
cell that contains the value (soure data) for that 
particular bar.
Is there a way - through code I suppose - I can 
automatically cycle through all of the graphs in a 
workbook and make that change?

Any direction would be muchly appreciated
TIA
Hafeez

>-----Original Message-----
>I looked up article 179199 and got this:
>
>   BUG: Grid Based on .QPR File Appears Blank in Visual 
FoxPro 5.0
>
>which probably isn't the one you meant. By above the bar, 
are they 
>horizontal bars and you want them to be between their bar 
and the next 
>higher one? Or do you mean they're columns (vertical 
bars) and you want 
>them above the top of the column?
>
>If it's a column chart, one of the positions you can 
specify for data 
>labels is Above. Double click on the labels, click on the 
Alignment tab, 
>and choose Above from the dropdown.
>
>If it's a bar chart, and you want to move the labels off 
the axis of the 
>bars, you'll need to use VBA. Start with the labels in 
the Center 
>position, then determine how far you want to move the 
bars; a good guess 
>is PlotArea.InsideHeight/SeriesCollection
(1).Points.Count/2. Record a 
>macro while you drag one label around manually to get the 
syntax right.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>Tutorials and Custom Solutions
>http://PeltierTech.com/
>_______
>
>Hafeez wrote:
>
>> I've read the article KB179199 on how to create custom 
>> data labels for charts and it's good, however doesn't 
meet 
>> all my requirements.
>> Is there a way to have the data labels automatically 
move 
>> to a desired location relative to the graph?
>> For instance, I'm using 50 bar graphs per work book and 
>> would like each data label to be just above their 
>> respective bar.
>> 
>> TIA
>> Hafeez
>
>.
>
0
anonymous (74722)
7/7/2004 8:07:12 PM
Hafeez -

Don't kill yourself. There are two free third party add-ins that do this 
for you:

   Rob Bovey's Chart Labeler, http://appspro.com
   John Walkenbach's Chart Tools, http://j-walk.com

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

Hafeez wrote:

> Hi Jon
> My appologies, its 279199.
> The graphs are vertical bar graphs.
> What I would like to do is to change the value of each 
> label in each graph.
> What I found out just now was that the number I wish to 
> display as the label appears 1 cell to the right of the 
> cell that contains the value (soure data) for that 
> particular bar.
> Is there a way - through code I suppose - I can 
> automatically cycle through all of the graphs in a 
> workbook and make that change?
> 
> Any direction would be muchly appreciated
> TIA
> Hafeez
> 
> 
>>-----Original Message-----
>>I looked up article 179199 and got this:
>>
>>  BUG: Grid Based on .QPR File Appears Blank in Visual 
> 
> FoxPro 5.0
> 
>>which probably isn't the one you meant. By above the bar, 
> 
> are they 
> 
>>horizontal bars and you want them to be between their bar 
> 
> and the next 
> 
>>higher one? Or do you mean they're columns (vertical 
> 
> bars) and you want 
> 
>>them above the top of the column?
>>
>>If it's a column chart, one of the positions you can 
> 
> specify for data 
> 
>>labels is Above. Double click on the labels, click on the 
> 
> Alignment tab, 
> 
>>and choose Above from the dropdown.
>>
>>If it's a bar chart, and you want to move the labels off 
> 
> the axis of the 
> 
>>bars, you'll need to use VBA. Start with the labels in 
> 
> the Center 
> 
>>position, then determine how far you want to move the 
> 
> bars; a good guess 
> 
>>is PlotArea.InsideHeight/SeriesCollection
> 
> (1).Points.Count/2. Record a 
> 
>>macro while you drag one label around manually to get the 
> 
> syntax right.
> 
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Hafeez wrote:
>>
>>
>>>I've read the article KB179199 on how to create custom 
>>>data labels for charts and it's good, however doesn't 
> 
> meet 
> 
>>>all my requirements.
>>>Is there a way to have the data labels automatically 
> 
> move 
> 
>>>to a desired location relative to the graph?
>>>For instance, I'm using 50 bar graphs per work book and 
>>>would like each data label to be just above their 
>>>respective bar.
>>>
>>>TIA
>>>Hafeez
>>
>>.
>>

0
DOjonNOT (619)
7/8/2004 2:57:08 AM
Reply:

Similar Artilces:

Graph #3
Hi guys I have a graph see attachment Is it poss to have a formula that would look at the graph and come up with the answer as if i was following the graph i.e look at the weight on the vertical scale then go along (horizontally) to the over all width (of the shutter) and see where the lines meet 150kg by 6000mm width would fall in the 5" 10swg (127x3.25) range and this would be the answer (can excel do the same thing???) Many thanks +-------------------------------------------------------------------+ |Filename: graph.jpg | |Downlo...

Deleting 1 out of 3 label sheets
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I have an &quot;Avery Wizard&quot; label program copied from PC to AppleMac.It consist of 4 sheets with 33 labels <br> on each sheet. Sheet 2 is blank and I like to delete that sheet, but don't know how. <br> Can anyone please help. <br> John If I have understood correctly... Make a backup of your document, and ensure you know where the Undo button is. The chances are that this is a fairly ordinary 4-page document with a table (probably 11x3) on each sheet, in which case a simple appro...

listbox data from a closed file
i have a listbox in an excel file on my desktop and want to open a excel file C:\A1.xls having found the info on this site i still find difficulties in openin said file can any one point me in the correct direction to go forward ********************************************************** From a closed workbook that is opened and closed again without the use noticing it With the macro below you can fill a ListBox control with input fro another closed workbook. The macro opens the closed workbook without the user noticing it, get the values for the ListBox and closes the source workbook without...

Data from another window
Ok, here is a question for you super smart people. I have one program that has an alpha-numeric string of data in once place. My users copy this data and past it into a cell, then go back to the first program, run another query, copy and paste that alpha-numeric number and past it into the next cell etc, etc, etc all day long. Is there a way I can write a macro (or you guys write it for me) to where after I run a query on my first program, I go to excel, run teh macro and have it read teh data off of my first program and paste it in the currently selected cell in Excel?? That would be awe...

Can you create 1 chart that will update based on filtered data?
I have budget data for 120 or so medical centers in rows. I filter this data by center. Is there a way to create one chart that will present data for only the center currently selected in the filter or would I have to create 120 diff charts? Thank you, Hi, Create you chart on all the data. Assuming your total data does not exceed the charts limits. Make sure the 'plot visible cells only' option is on. Tools > Options > Chart. If the chart is located near the data to be filtered you will need to change the Move and size properties of the chart object. Double click th...

Data won't display on tabbed subform
I have a Main page with several tabs for various aspects of our shipping jobs. I'm trying to create a further one for invoicing. I have a Debtors table and I created a form that displays the debtor name and address and account no etc. The debtor can be selected by means of a combo, and when the required record is found, all the fields are populated, fine. Next I created the new tab control and added the above-mentioned form onto it as a subform. This will form the top section of the invoice. However, when I use the combo on the subform, all it does is to display the debtor names, and...

Data across different worksheets
How would I get data from the same cell across several different worksheets to be pulled it one summary worksheet. One way is shown in the "Summarizing Data from Different Sheets" section of this article: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ nelsquid wrote: > How would I get data from the same cell across several different worksheets > to be pulled it one summary worksheet. > > ...

The account's primary contact should by dynamic
Depending on if you are in Sales, AR, Service, etc... the primary contact on the account should be able to dynamic. Alternatively, there should be primary contacts on the Account for each of the departments in the organization. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the messa...

transfer of data from Excel to MS Word
I am attempting to print mailing labels in MSWord.The database I have in Excel only transmits the first page even though all data is shown when I brouse selected file. What am I doing wrong? This may be an over simplification but, COPY|PASTE always works for me... HTH doco "amateur" <amateur@discussions.microsoft.com> wrote in message news:76CBFF04-2030-493C-B761-BD811A99444E@microsoft.com... >I am attempting to print mailing labels in MSWord.The database I have in > Excel only transmits the first page even though all data is shown when I > brouse selected fil...

Batch importing data sets into EXCEL and then plotting
Hi, I have a series of TXT files. All of these TXT files are in same format. I want to import all the TXT files in a batch into EXCEL. After bringing in the TXT file in each worksheet, I then want to make a scatter plot of each TXT file. The plot would be Date format in x-axis and in y -axis Data values. Once these plots are made I then want to put total of six plots in each page. I am including a TXT file as an example. I would appreciate if anyone can help as how to automate the process. Thanks. Attachment filename: ex1.txt Download attachment: htt...

Data Validation and Error Report Production
Hi, Can anyone help me please with a data validation issue. I have a set of Oracle Tables which are being used in a PeopleSoft Financials system. There is a requirement to validate all data being input into an Excel Spreadshet against these Oracle Tables. What would be the best way of doing this? I have looked at the Data Validation option within Excel but the volumes of data we are talking about here makes it not possible. It would also be nice to create a report (maybe txt file) showing where, if any, there were errors Any help would be gratefuly apprecitated. Richard ...

Dynamics GP Deployment
I am new to GP and working on an version 9 install. If we set this up as client/server, when service packs come out, do we apply these only to the server? Or do they need to be applied to all clients too? Also, I have developed an application using the GP Developer's Toolkit and eConnect. I compile my app as a DLL and drop it in the GP AddIns folder. Do I do this only on the server? Or do I need to put my DLLs on each client? Do I need to install the Developers Toolkit / eConnect on each client? Or only on the server? A lot of questions I know! Thanks, Chris Not being a ...

Protecting data validation Drop DownColumn
I made a column which displays only dropdown lists made from datavalidation. When i copy and paste cells over it the dropdowns are no longer available. Is there a way to protect the column ? thx See if this works for you.... With cell A1 having the Data Validation Set these protection options: A1 is UN-locked B1 is LOCKED Merge A1:B1 Protect the sheet (with or without a password) End result: The merged cells (effectively A1) can be edited but...can't be cleared with the [delete key] and...you can't paste (or paste special) over it. The only way to erase the contents is to: E...

Data Import/Export
I have a worksheet that contains data exported from my Sybase database using an ODBC database query in Excel. One of the fields that is exported is an address field which can contain anywhere between 1 and 7 lines (separated by character returns). What I need is to extract the postcode from this field. In an ideal world it would be fixed to line 7 but its not an ideal world when it comes to our software. If the address only has 4 fields then the postcode will appear in line 4, if it has 6 fields then it will appear in line 6. I need to be able to extract this postcode, delete the rest of ...

Pivot table export data to table format
I have a pivot table with the row heading listed once for multiple crosstab line items. I need to use this data for a report that will list the row heading on each row for each cross tab line item. Is this possible? I have tried everything... nothing seems to work. You can't get the heading labels to repeat in the pivot table. You could copy the pivot table, and paste as values on a different sheet in the workbook. Then, fill the blank cells, and format the copy of the pivot table. There are instructions here for filling in the blank cells: http://www.contextures.com/xlDataEntr...

inserting pics as part of data
How do I insert a pic as prt of data for that row and columb. Pic woul have to be a part of data for that row so that i can sort and etc. just got be able to put in picture like its a data. thanks so much fo your help -- Message posted from http://www.ExcelForum.com Or are there any other programs?(that can treat pic as data) -- Message posted from http://www.ExcelForum.com Maybe insert the pic into a comment over an underlying cell ? Experiment with this: Right click on a cell > Insert a Comment Right-click on the Comment box border > Format Comment In the Format Comment d...

exporting data
What is the best way to export specific data to excel or .csv format? JayK, Create a report that has the info you wish to export, right click on the body of the report, Export | File | Comma Separated CSV and give it a name of the file. -- * "JayK" <JayK@discussions.microsoft.com> wrote in message news:A7B82E63-4B8C-4B68-8A8B-EB36D0F1DCC4@microsoft.com... What is the best way to export specific data to excel or .csv format? From an existing Active Report is very simple. Just run the report and at the top of the report you should see an icon to the left of the one th...

Can data files for mailmerge be updated?
I have a 4 year old 3.5 disc with addresses that I update each year. Last year I updated using Windows 98, with Office maybe Publisher 2000. Now I am unable to access the info with my Windows XP and Publisher 2003. Is there a way to get to this data, edit it, and make labels in 2005? Thanks Robin from Alaska Copy the list to "My Data" folder in "My Documents." Is it a Publisher list? If it is a .mdb file, Publisher should read it. I think your problem is mainly the floppy disk. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews...

Summing only cells that have data and ignoring cells that have #DIV/0! in them
I have a spreadsheet that acts as a template. Because of that, there may be data in rows 2-10, but nothing below row 10. Data in columns A through Z feed formulas in columns AC through AL. Prior to putting any data in columns A through Z, the formulas in columns AC through AL are all showing #DIV/0! because of course there's no data in the cells those formulas are looking in. I want to sum the totals of some of the columns that in AC through AL, but of course if I only have data in rows 2-10, then the #DIV/0! message in the rows below 10 will not allow me to sum the entire column. I c...

Dynamic Chart Scaling Error
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have finally figured out how to dynamically scale a chart that links to a table that can change by have the trailing cells empty. <br><br>The Table has two rows and 16 columns C6:R6 are dates and C7:R7 are numbers. When any of the cells at the end of the table are blank such as Q6 and R6 being blank I want the chart to only plot 14 data points. I should add C6:R7 are all formulas that reference another place in the work book. <br><br>That I did is define names Where the name &quot;NBCL&a...

Why do my Office 2007 programs automatically copy my data?
A few days ago, Outlook, Word, Excel and OneNote started automatically and erroneously copying data from within the programs. For example, in Excel different cells will appear as "copied" even though I only selected them. Also when I exit Outlook, I am asked whether I want to keep the many data on the clipboard available to other programs. I have been running Office 2007 SP2 on Vista Home Basic SP2 without problems for several months. However I started actually using OneNote, which was already installed, at the same time as the onset of this problem. Copying items ca...

Data not showing in combo box
I have a database which uses a combo box with 2 columns, in Access 2007 I can see the information from both, in Access 2003 I can only see the data from column 2, if I go to the SQL query and run that I can see both so I know the data is there. Strangely if I try in Access 2000 I can see the data no problem, This database was designed originally in 2000 and has been running fine on all versions untill now so I am baffled - any suggestions so my colleague can use in 2003 would be much appreciated. I have tried on a 2nd machine with 2003 and still does not work. Sheila There was a bug...

Data Migration Pack extremely slow
We are trying to import aproximately one million records from external custom applications. We have copy all the data into de appropiate CDF tables but the problem is that when we run migration, it is extremely slow. We get a performance of 4 records/second so the complete migration runs for about 70 hours ! We are using SQL Server 2005 64 bitsas the database server. The machine has an AMD Opteron dual CPU with 8GB RAM. Hard drive is SCSI 15.000 rpm. Has anyone experienced similiar problem? Any idea about improving performance. Thanks Data migration can be slow. The speed varies across ...

Excel should allow me to screen scrape the data only
When dealing with business partners I have a need to send them price lists in excel format so they can 'use' the data meaningfully, however I don't want them to see all the underlying formulae and hidden rows/columns. Right now I have no option but to convert the spreadsheet into an Acrobat document making it less useful to them What Excel needs is a way of deleting all the underlying formulae and functions in a spreadsheet while keeping the visible data in the spreadsheet, a 'snapshot' if you will. I could then send the visble data to my business partners in '...

How do I copy a graph that I want to modify in powerpoint?
With this version of excel, unlike the old one, I'm not able to modify in powerpoint a graph created in excel. Is there any solution, or I have to go back to the old version? thanks! Anna By modify, do you mean turn into separate drawing shapes, and modify those? In Excel, select the chart, on the Home tab, click on Paste > As Picture > Copy As Picture. Choose the Picture option, not Bitmap. They've hidden this command even better in Excel 2007. I know all about it, and I still took three weeks to find it. But now I know. next time I want Copy, I'll look under Paste....