Add & Delete Multiple Chart Data Series Labels
Jan 16, 2008
My friend has set up an excel workbook with a sheet for every month (labelled Jan graphs, Feb graphs, Mar graphs etc) which has 7 pie charts per sheet. The problem is the pie charts are showing the 0% fields and the data labels are overlapping. I have found a macro that gets rid of the 0% labels but I need to repeat the macro for all the 7 charts on each sheet and all 12 worksheets for each month of the year. I am very new to VBA and coding of any type and have looked but can't find the solution.
The macro I am currently using for "Chart1" on "Jan graphs" is below. The charts are labelled Chart1, Chart2, Chart3 etc.
Sub ClearLabels()
Worksheets("Jan graphs").ChartObjects("Chart 1").Chart.ApplyDataLabels _
Type:=xlShowLabelAndPercent
For Each x In Worksheets("Jan graphs").ChartObjects("Chart 1"). _
Chart.SeriesCollection(1).Points
View 8 Replies
ADVERTISEMENT
May 31, 2008
I just recently installed Excel 2007 and I would like to know if it's possible to change all data points of a chart at the same time. In Excel 2003, I would normally hold down shift while clicking on each of the data points to make a global change. However, it appears I cannot do that in 2007.
I would like to display each data point's series name. When I go to Layout on the Excel Ribbon, and click on "Data Labels", and click on "More Data Label Options", the actual Y-axis values are shown for each data point. However, I do not want this - I actually only want the Series Name, but when I uncheck "Value" and check "Series Name" instead (under "Label Contains"), it only changes it for one of the series. Is there a better way, instead of going through each and every single series to make this change?
View 4 Replies
View Related
Jul 1, 2007
I am building stacked XY Scatter charts and having difficulty formatting the y-axis values (which are data series point labels)
Rob Bovey's XY Chart Labeler manages this fine, but I work in a strict corporate environment which does not allow downloads. Also the file will be posted on our web as a tool for all company users to extract and analyze data from corporate databases.
I am stuck on the number formatting of the labels. My file is too big to attach; I have included the code below.
I'm sure there is something very basic that I am missing. Needless to say VBA is a challenge for me.
Option Explicit
Sub Atest()
Dim intR As Integer, intP As Long
Dim sngVal As Single, sngY As Single
Dim dtX As Date
Dim chtTst As ChartObject
Set chtTst = ThisWorkbook.Worksheets("GRAPHS").ChartObjects("Chart 18")
chtTst.Select
ActiveChart.SeriesCollection("Y Axis Values").DataLabels.Delete
View 5 Replies
View Related
Jun 5, 2014
I click on one label, and all labels get selected. This is good. But then when I try to grab them to move all of them, they become deselected except for the one the mouse was over. I want to move them all at once.
View 2 Replies
View Related
Aug 1, 2006
I am using the following code to delete all of the series in the chart. It is give type mismatch error.
Sub allchannelchart()
Dim i As Integer, s As SeriesCollection
ActiveSheet.ChartObjects("allchan").Activate
'ActiveChart.PlotArea.Select
For Each s In ActiveChart.SeriesCollection
s.Delete
Next s
View 2 Replies
View Related
May 27, 2008
I have a spreadsheet (see attached) in which there are many series and i want to be able to delete most of the series except the first 4.
(The 0%, 5% 10% air voids and the 95% comp line)
iv got this code, which is attacted to a command button, which works fine to delete all the series but makes the graph a blank white box. id prefer it so it delete just the series not the graph "picture?" ...
View 7 Replies
View Related
Feb 7, 2014
I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.
For example, variable ASSIGNMENT has the following possibilities:
1
2
3
4
Here's what each of those "mean" (I have this in another table):
1 - Sick
2 - Overtime
3 - Court
4 - Present
How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?
View 8 Replies
View Related
Apr 29, 2014
I have a file with a small example of sales and % of sales for about 7 persons.
I wanted to insert a pie chart but want to show labels inside the pie chart along with % of sales and the sales person name near each part. How to do this ?
I have attached a file : Book1.xlsx
View 4 Replies
View Related
Jun 16, 2007
I am trying to write an If statement that returns true if Valule is checked off in the Data Labels portion of a chart. I tried using a HasDataLabel properties but Excel didn't recognize that.
View 5 Replies
View Related
Feb 2, 2007
In a chart if you display data labels is there a way of avoiding a zero being displayed if the relevant cell is the result of a formula?
It seems that even if the result of that formula is a blank or 'n/a' it is still displayed as 0.
View 2 Replies
View Related
Apr 21, 2008
I have a pie chart that shows numerical values in dollars and I'm looking for a macro that will delete the $0 data labels. I found this code from Jon Peltier (great website by the way) but I can't seem to get it to work on my pie chart. No errors, just doesn't do anything:
Sub CleanUpActiveChartLabels()
Dim iPts As Integer
Dim nPts As Integer
Dim aVals As Variant
Dim srs As Series
ActiveSheet.ChartObjects("MainChart").Activate
For Each srs In ActiveChart.SeriesCollection
With srs
If .HasDataLabels Then
nPts = .Points.Count
aVals = .Values
For iPts = 1 To nPts
If aVals(iPts) = 0 Then
.Points(iPts).HasDataLabel = False
End If
Next
End If
End With
Next
End Sub
View 2 Replies
View Related
May 16, 2008
I have a scatter chart and have applied data labels using the VBA macro supplied with Excel 2007. They pick up the cells in column A. But I now want to use Autofilter to show different ranges in the chart. Everytime I filter the chart data, the data labels change to show a different label (in fact they seem to be starting from the first label again, even if this value is not shown on the filter). I have tried to edit each data label and enter free text, I've also used a formula to link to the cell and made it absolute, but nothing is working.
The chart must be a scatter chart as it is plotting 2 values. I'm trying to create a Project Portfolio Risk Chart showing Benefits against Difficulty.
View 9 Replies
View Related
Jun 30, 2008
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000.
In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle.
My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
View 9 Replies
View Related
Nov 18, 2008
I have a chart with 3 series':
Series1
Series2
Series3
The chart is called chtEngagement. I want to use vb to update these series'.
Series1 should be shown as:
View 5 Replies
View Related
May 15, 2008
I have a scatter graph that has over 100 curves that im need on one graph that at the moment i putting each x and y and name in by hand in series. I have been doing it for hours and have looked round the site to see if anything similar answer on ozgrid would help but im baffled
i was just wondering if there is an easier way of doing this
i havent really used macros but iv been reading up on it can i used it to do this?
I have added the table and graph and this is how i would like all the columns to look like
its the table called graph that makes the curves that i would like to be able to add in quickly
the rest im not too fussed about.
View 9 Replies
View Related
Sep 2, 2007
I have a Column Graph that will be changing automatically as the data is changed. My problem is that for one set of data the chart looks fine....but if a number becomes negative the bar will overlap with the data labels at the bottom and cause it to be unreadable. I am not able to resize the x axis area or move the labels down any. Is there anyway to make the labels adjust automatically?
View 3 Replies
View Related
May 2, 2008
I m working with bar charts and as I've heard these labels can be a bit of a pain. I've got the labels in roughly the right position 90% of the time however that other 10% has me in rolling fits.
My issues
1. A label within the chart area is wrapped on two lines, how could I restrict Excel from wrappign the label
2. A Labels position covers some of the data reported and thus makes the graph messy
how could I find the position of the end of the bar and also the length of the datalabel so I could reposition it. Note I am using 2003 and that I am trying to use
activeChart.seriesCollection(s).item(i).Datalabel.left
for finding the position although this is only really where im getting to. finding the wrapping position and also being able to manipulate the length of the label is proving tricky.
View 6 Replies
View Related
Jan 17, 2014
I have created a dynamic chart for multiple series. I have created this using the offset function and have named 20 ranges. The problem I have is that if only 19 of the 20 named ranges are populated it reads that the worksheet contains one or more invalid references.
Is there a way to stop this from happening?
View 1 Replies
View Related
Aug 18, 2006
how can i plot multiple series of values in one graph after checking some check boxes, which i have previously done, randomly?
View 4 Replies
View Related
Oct 4, 2013
I'm using Excel 2010 and I've got a chart on a worksheet and the worksheet needs to be protected. I'm able to manipulate the chart in any fashion EXCEPT the position of the Data Labels. Is this a bug in Excel 2010?
View 3 Replies
View Related
Aug 4, 2006
I have a chart that combines 2 xy plots and a set of stacked columns ( total of 4 series).
I can adjust the series ordering between the 2 xy plots, and between the stacked columns, but not for all 4 at the same time.
I'd like the legend to show up as:
Series1, Series2, Series3, Series4
where 1 and 2 are the xy plots and 3 & 4 are the components of the stacked columns.
It is currently:
Series3, Series4, Series1, Series2
Any idea how to re-order the legend entries?
View 9 Replies
View Related
Jan 31, 2009
What I want to achieve is a script that would create a new scatter plot of multiple series from a selected block of cell. I found a thread that was similar but what is different from my data is that my x values are different for each series.
The format of the data is in the following format, the first series will the first x,y pair, the 2nd series would be the second x,y pair. I don't know if it'll be an issue but one thing is that the date might be different lengths. For instance, the 3rd series, only has 4 data points, whereas the other series contains 6 data points?
x0.200.400.600.801.001.20
y5.002.501.671.251.000.83
x0.100.200.300.500.701.30
y5.002.501.671.000.710.38
x0.100.300.400.50
y6.672.221.671.33
So essentially, is there a way I can have the script say, select your range, and then graph the selected range as a scatter plot?
View 9 Replies
View Related
Nov 9, 2006
Trouble: I need to replace address of datacells for serias of chart
Result: data for serias will be some lines above or below as i need
simv = Workbooks(1). Names("adr").RefersToRange.Formula
num = Len(simv)
For i = 1 To ActiveChart.SeriesCollection.Count
oldadr = ActiveChart.SeriesCollection.Item(i).Formula
res = Left(oldadr, Len(oldadr) - num - 3) + simv + Right(oldadr, 3)
ActiveChart.SeriesCollection.Item(i).Formula = res
Next i
simv - my new address data for all serias this code is working on Excel 2003, but not working on some other versions. Beause "Formula" may has different structure :
version 2003: =SERIES(,,'C:...[macros.xls]Sheet2'!$B$8:$M$8,1) other version:
=SERIES('C:...[test.xls]#Source'!$C$6:$D$6,'C:...[test.xls]#Source'!$E$2:$AC$2,,1)
View 3 Replies
View Related
Oct 25, 2007
I have a chart with Data Series being dynamic ranges. There is only one series and x & y each have a dynamic range. I need to be able to include Series Name as a dynamic range.
For example. I have 3 rows of values with 3 columns.
Col 1 Col 2 Col 3
Name X-Val Y-Val
A 1 3
B 4 6
C 8 0
I now need a chart that has 1 series with a x range of col2 and a y range of column 3 but a corresponding name equal to Col 1. So when I move my mouse over each point I get the series name. E.G. I hover over poing 1,3 I show the name as A. Or I hover over point 8,0 I get C.
If it isnt possible using the ranges the way I am, then I would be looking for a possible solution. I am sure I could do this by some sort of loop but not quite sure given the number of rows constantly changes.
View 7 Replies
View Related
Aug 4, 2014
I run a large simulation experiment. I have a loop plotting data in excel of a user defined area. Because of the limit of 255 series I have allowed a maximum of 250 simulations (they all need to be plotted). But the length of each simulation is free. I know there is a limit of 32.000 data points in a graph and I have this as a condition too.
If I set the data range to 100 columns and 3000 rows the graph is produced when I plot by columns. (code below)
But if I set the data range to 250 columns and 1000 rows I get the above mentioned error message. Even though I only have 250 series.
After the data is plotted it is the code below that gets the error:
[Code] .....
View 1 Replies
View Related
Dec 22, 2011
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
View 4 Replies
View Related
Mar 30, 2008
I have received following macro from someone to delete series but the problem is that it gives error when some sheets are protected, and I want those sheets to protected. When run it will ignore/leave protected sheets but delete series only from unprotected sheets
Private Sub CommandButton1_Click()
Dim Rng As Range, i As Long, r As Range, lVal, uVal
Dim DeleteCount As Double
Dim lRow As Long
Dim dr As Long
Dim dc As Long
dc = Sheets("Deleted Numbers").UsedRange.Columns.Count - 1
dr = Cells(Rows.Count, Sheets("Deleted Numbers").UsedRange.Columns.Count - 1).End(xlUp).Row + 1
If dr = 60001 Then ................
View 4 Replies
View Related
Jan 17, 2014
I am trying to produce a line chart, which will use imported data on a separate sheet (but within the same workbook)
The imported data can be of different sizes ie column widths and length. Some columns may contain only zero and should not be included in the chart, and will be to right of the data.
I have created a named range for the data series called ChartData using =OFFSET(Data!$C$1,0,0,info!$C$7,info!$A$2) - C7 and A2 are calculations to set column and row sizes. I have tried to use this as the chart's data series, but without success.
View 4 Replies
View Related
Sep 16, 2006
is it possible to reference part of a data series from a chart to a combo box?
View 3 Replies
View Related
Oct 5, 2006
I want the below code to run from R1C10 (i.e. cell J1) to the end of the data in the row, which will vary in position, rather than having to define where the end of the row will be beforehand (i.e. R1C21).
Is this possible?
View 9 Replies
View Related
