Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
global formatting of legend keys
Hi
In a chart i have multiple legend keys, as a result all colors are not visible because the columns are thin and border line black, thus i can see only black columns. i can format each key manually but if i have 200 keys it would never end, how can change the border option from automatic to none? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
global formatting of legend keys
Hi,
Quickest way would be via code. Sub x() Dim objSeries As Series With ActiveChart For Each objSeries In .SeriesCollection objSeries.Border.LineStyle = xlNone Next End With End Sub That said a chart with 200 legend entries is not really going to be readable. Excel colours are limited to 56 and even that is too much in 1 chart. Maybe you need to reconsider your chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Yossi evenzur" <Yossi wrote in message ... Hi In a chart i have multiple legend keys, as a result all colors are not visible because the columns are thin and border line black, thus i can see only black columns. i can format each key manually but if i have 200 keys it would never end, how can change the border option from automatic to none? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
global formatting of legend keys
Try this macro -
Sub NoSeriesBorders() Dim cht As Chart Dim sr As Series On Error Resume Next Set cht = ActiveChart On Error GoTo 0 If cht Is Nothing Then MsgBox "No Chart is selected" Exit Sub End If For Each sr In cht.SeriesCollection sr.Border.ColorIndex = xlNone ' or ' sr.Border.LineStyle = xlNone Next End Sub There's no undo but you could change xlNone to xlAutomatic to reset borders Regards, Peter T "Yossi evenzur" <Yossi wrote in message ... Hi In a chart i have multiple legend keys, as a result all colors are not visible because the columns are thin and border line black, thus i can see only black columns. i can format each key manually but if i have 200 keys it would never end, how can change the border option from automatic to none? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
global formatting of legend keys
"Peter T" wrote: Try this macro - Sub NoSeriesBorders() Dim cht As Chart Dim sr As Series On Error Resume Next Set cht = ActiveChart On Error GoTo 0 If cht Is Nothing Then MsgBox "No Chart is selected" Exit Sub End If For Each sr In cht.SeriesCollection sr.Border.ColorIndex = xlNone ' or ' sr.Border.LineStyle = xlNone Next End Sub There's no undo but you could change xlNone to xlAutomatic to reset borders Regards, Peter T "Yossi evenzur" <Yossi wrote in message ... Hi In a chart i have multiple legend keys, as a result all colors are not visible because the columns are thin and border line black, thus i can see only black columns. i can format each key manually but if i have 200 keys it would never end, how can change the border option from automatic to none? every time i wan't to do this i have to create a macro? |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
global formatting of legend keys
Hi Andy,
Excel colours are limited to 56 and even that is too much in 1 chart. FWIW starting in the 57th series a two colour 50% pattern is 'automatically' applied for the next 56, then a different %grey in the following 56 an so on. In theory a different 'apparent' colour can be applied in each fill up to max 255 series. Might want to customize the 10 duplicate colours in a default palette. I agree with your point though. Regards, Peter T "Andy Pope" wrote in message ... Hi, Quickest way would be via code. Sub x() Dim objSeries As Series With ActiveChart For Each objSeries In .SeriesCollection objSeries.Border.LineStyle = xlNone Next End With End Sub That said a chart with 200 legend entries is not really going to be readable. Excel colours are limited to 56 and even that is too much in 1 chart. Maybe you need to reconsider your chart. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Yossi evenzur" <Yossi wrote in message ... Hi In a chart i have multiple legend keys, as a result all colors are not visible because the columns are thin and border line black, thus i can see only black columns. i can format each key manually but if i have 200 keys it would never end, how can change the border option from automatic to none? |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
global formatting of legend keys
Hi
First thanks for the help, now, usually i try to avoid this but in this case i had too, but maybe you can offer a better solution, as an analogy, say you have 200 students and test scores in math and their age and you need to find the worst performing student with cross correlation to his age, i thought that by putting this on a 3D chart will enable me to pinpoint the most likely candidates, what you think? |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
global formatting of legend keys
3D charts sound like a great solution, but 3D chart rendering on a 2D medium
(paper or monitor) will always be limited. A 3D column chart with 200 points would be horrific. I would carry out my analysis with a few charts. I'd use an XY chart with age on the X axis and test score on the Y. I would use a labeling routine to put the student names or other ID on the points as data labels, but for 200 students, it would probably be too cluttered. I'd also make a couple dot plots, both with student name down the left axis, one with test score and the other with age plotted horizontally, both plotted in descending order of score or age. Probability plots of the test scores and ages would also be helpful to show whether any apparently outlying points were truly out of distribution. Chart data labeling routines: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Dot Plots: http://peltiertech.com/Excel/Charts/DotPlot.html Probability Plots: http://peltiertech.com/Excel/Charts/...lityChart.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Yossi evenzur" wrote in message ... Hi First thanks for the help, now, usually i try to avoid this but in this case i had too, but maybe you can offer a better solution, as an analogy, say you have 200 students and test scores in math and their age and you need to find the worst performing student with cross correlation to his age, i thought that by putting this on a 3D chart will enable me to pinpoint the most likely candidates, what you think? |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
global formatting of legend keys
Post on top, as is customary in these groups, to make it easier to follow
the thread chronologically. every time i wan't to do this i have to create a macro? Put this macro into your Personal.xls workbook, add a button to a toolbar somewhere, and attach the macro to the button. It will always be available. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Yossi evenzur" wrote in message ... "Peter T" wrote: Try this macro - Sub NoSeriesBorders() Dim cht As Chart Dim sr As Series On Error Resume Next Set cht = ActiveChart On Error GoTo 0 If cht Is Nothing Then MsgBox "No Chart is selected" Exit Sub End If For Each sr In cht.SeriesCollection sr.Border.ColorIndex = xlNone ' or ' sr.Border.LineStyle = xlNone Next End Sub There's no undo but you could change xlNone to xlAutomatic to reset borders Regards, Peter T "Yossi evenzur" <Yossi wrote in message ... Hi In a chart i have multiple legend keys, as a result all colors are not visible because the columns are thin and border line black, thus i can see only black columns. i can format each key manually but if i have 200 keys it would never end, how can change the border option from automatic to none? every time i wan't to do this i have to create a macro? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting a Legend in a chart (superscripting) | Excel Discussion (Misc queries) | |||
"Global" Cell Formatting | Excel Discussion (Misc queries) | |||
Formatting Legend Key Markers | Charts and Charting in Excel | |||
Formatting Legend | Charts and Charting in Excel | |||
Can I change legend keys to rectangular instead of square in exce. | Charts and Charting in Excel |