Can I Apply Conditional Formatting to a Chart?

I don't agree with my boss's ideas regarding Excel Charts - I think that they 
are to cluttered and become less effective. So, I would like to apply 
conditional formatting via a checkbox to "turn off"(apply white/transparent 
properties)to one or more series in a chart, effectively eliminating the 
overlapping series from view temporarily. Can this be done? Is the answer 
VB?I think this could be very effective in clarifying data that is 
consistently overlapping each other. (Actually I would just make 3 separate 
charts on the same page, but nobody is asking me!) 
Tell me what you think...
Thanks,
Tim
0
10/19/2005 6:26:05 AM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
271 Views

Similar Articles

[PageSpeed] 15

Hi Tim Richards,

Check out my and Jon Peltier's answers to 'Click Boxes for each series in a 
graph' on Tuesday, 18 October 2005 5:56 AM in this forum.

If you want to explore conditional formatting for charts look at the sample 
at http://edferrero.m6.net/charting.aspx or check out Jon's pages at 
http://peltiertech.com/Excel/Charts/format.html#CondChart

Ed Ferrero
http://edferrero.m6.net/

>I don't agree with my boss's ideas regarding Excel Charts - I think that 
>they
> are to cluttered and become less effective. So, I would like to apply
> conditional formatting via a checkbox to "turn off"(apply 
> white/transparent
> properties)to one or more series in a chart, effectively eliminating the
> overlapping series from view temporarily. Can this be done? Is the answer
> VB?I think this could be very effective in clarifying data that is
> consistently overlapping each other. (Actually I would just make 3 
> separate
> charts on the same page, but nobody is asking me!)
> Tell me what you think...
> Thanks,
> Tim 


0
mail953 (171)
10/19/2005 6:37:43 AM
Ed,
Thank you for your prompt response. I looked at the links you sent to me and 
saw some great charts, but I don't think I was clear. I need to activate 
conditional formats via a checkbox. This would enable me to check a box and 
temporarily remove a series from the current view (with extensive overlapping 
data points) for clarity(perhaps I could change the color to white as an 
example). As I type this reply, I am realizing that a macro that 
deletes/undeletes a series might be more effective. If the two series have 
extensive overlapping points, turning one of them white may just mask the 
other series... unless it's possible to send that (whitened) series to the 
back as you would with groups.

Thanks,
Tim

"Ed Ferrero" wrote:

> Hi Tim Richards,
> 
> Check out my and Jon Peltier's answers to 'Click Boxes for each series in a 
> graph' on Tuesday, 18 October 2005 5:56 AM in this forum.
> 
> If you want to explore conditional formatting for charts look at the sample 
> at http://edferrero.m6.net/charting.aspx or check out Jon's pages at 
> http://peltiertech.com/Excel/Charts/format.html#CondChart
> 
> Ed Ferrero
> http://edferrero.m6.net/
> 
> >I don't agree with my boss's ideas regarding Excel Charts - I think that 
> >they
> > are to cluttered and become less effective. So, I would like to apply
> > conditional formatting via a checkbox to "turn off"(apply 
> > white/transparent
> > properties)to one or more series in a chart, effectively eliminating the
> > overlapping series from view temporarily. Can this be done? Is the answer
> > VB?I think this could be very effective in clarifying data that is
> > consistently overlapping each other. (Actually I would just make 3 
> > separate
> > charts on the same page, but nobody is asking me!)
> > Tell me what you think...
> > Thanks,
> > Tim 
> 
> 
> 
0
10/21/2005 7:56:02 AM
Tim -

Maybe you can expand upon this example:

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

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


Tim Richards wrote:

> Ed,
> Thank you for your prompt response. I looked at the links you sent to me and 
> saw some great charts, but I don't think I was clear. I need to activate 
> conditional formats via a checkbox. This would enable me to check a box and 
> temporarily remove a series from the current view (with extensive overlapping 
> data points) for clarity(perhaps I could change the color to white as an 
> example). As I type this reply, I am realizing that a macro that 
> deletes/undeletes a series might be more effective. If the two series have 
> extensive overlapping points, turning one of them white may just mask the 
> other series... unless it's possible to send that (whitened) series to the 
> back as you would with groups.
> 
> Thanks,
> Tim
> 
> "Ed Ferrero" wrote:
> 
> 
>>Hi Tim Richards,
>>
>>Check out my and Jon Peltier's answers to 'Click Boxes for each series in a 
>>graph' on Tuesday, 18 October 2005 5:56 AM in this forum.
>>
>>If you want to explore conditional formatting for charts look at the sample 
>>at http://edferrero.m6.net/charting.aspx or check out Jon's pages at 
>>http://peltiertech.com/Excel/Charts/format.html#CondChart
>>
>>Ed Ferrero
>>http://edferrero.m6.net/
>>
>>
>>>I don't agree with my boss's ideas regarding Excel Charts - I think that 
>>>they
>>>are to cluttered and become less effective. So, I would like to apply
>>>conditional formatting via a checkbox to "turn off"(apply 
>>>white/transparent
>>>properties)to one or more series in a chart, effectively eliminating the
>>>overlapping series from view temporarily. Can this be done? Is the answer
>>>VB?I think this could be very effective in clarifying data that is
>>>consistently overlapping each other. (Actually I would just make 3 
>>>separate
>>>charts on the same page, but nobody is asking me!)
>>>Tell me what you think...
>>>Thanks,
>>>Tim 
>>
>>
>>
0
10/21/2005 12:56:13 PM
Reply:

Similar Artilces:

Chart Automatically Updating
I have set up a chart that automatically updates as new data is entered into the spreadsheet. The problem is....I would like to copy this worksheet so that there are 9 different worksheets (of this same set-up only with different names) in the same workbook. What is the correct way for me to make copies of worksheet, so that my chart in each worksheet still automatically updates based off of what is entered in that worksheet? The directions I used for setting up my original worksheet is located at http://peltiertech.com/Excel/Charts/DynamicCharts.html Maybe this only works for on...

Ppt animations working perfect; now off & can't figure out why
Ppt 2007 - New Laptop Vista. 4G RAM.. Created Missions slideshow. Includes: custom animations on photos and/or text--all in sync with different music clips,and slides have diff. timing for transition. Even has 2 movie clips included. Have shown it over a dozen times in past 2 months. Has always worked perfect. Today, ALL slide transitions & custom animation timing is off--very slow to change. Even a :01 sec. black slide take 7 sec. before the slide moves. I timed the different sections. The music clips play the speed they are suppose to, but the slides don't match up ...

Bar Chart Column Width?
I am trying to create a Std Bar Chart with 52 Bars (1 bar for each week) ... I am on 11x17 paper ... Issue is ... The bars are very narrow ... I have other charts with 30 Bars on 8.5x11 paper & bar width is fine ... I do not profess to be a Chart Guy ... What am I missing??? Thanks ... Kha If u want to increase the width of the bars then follow this. right click on any bar > format data series > options > gap width Increase the gap width that suits you. Regards Asim ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ Vie...

Help with Bar-Line Chart..not aligned??
Hi All, I am trying to plot a line chart over a column chart (4 categories). The columns on the primary y-axis, while the lines on the secondary y-axis. The problem is that the line charts starting points are always at the middle of the data series but not properly aligned with the columns. I want to shift them and align them with the column charts. Here is the data. 2011 2011 2012 2012 Volume % of sector Volume % Sector Company 1 150 7% 310 11% Company 2 170 5% 320 12% Company 3 160 4% 330 13% If you plot them in columns, you will...

Adding labels to a waterfall BAR chart
Chaps, I've got a waterfall chart which is based on bars, not on columns. I.e. the bars run from left to right. This is fine until I try to add the labels, which using the Peltier method involves adding an invisible line series. And here's where the problem arises. Excel only does line charts horizontally, not vertically, so I can't put it onto my waterfall. Anybody got any ideas? Hi, Instead on Line series use an XY-scatter series. This will allow you to control both the horizontal and vertical position of the labels. Cheers Andy -- Andy Pope, Microsoft MVP - Excel...

Erase cell contents based upon a condition
I have a huge list which counts the number of items in the list (per each record) in the total row. Based upon an entered condition ( like put a 2 in B1 and compare all entries down that column to the 2 - if the entry in each cell equals the 2 - great - if not, erase the 2 and leave the cell blank). I use the CountA at the end of the row, for each record, to see how many items this person has versus another record. A B C D 2 Total Jim 2 4 2 Bob ...

Hyperlink in Excel 2000 can't open bookmarked Word 2000 file
In Excel 2000, I tried to create a hyperlink to a bookmark in a Word 2000 document. When I clicked the "bookmark" button in the "insert hyperlink" dialog box, I got a message saying Excel could not open or parse the file. I can create a hyperlink to the beginning of the file and, in Word, it appears that the bookmarks are there since I can "go to" them. Both of these worked ok for me: (Insert|hyperlink style) C:\MY DOCUMENTS\WORD\MyDoc.DOC#bkmk01 Or =hyperlink() style: =hyperlink("C:\MY DOCUMENTS\WORD\MyDoc.DOC#bkmk01") You may want to post what...

Same format using =
I have a cell (let's say A1), that is formatted with a fill color, a specific font, and a specific font color. I have another cell (A5) that has the formula =A1. How can A5 have the same formatting as A1 and not only its value ? thnks Stabilo, Select A1 Edit-Copy Select A5 Edid-Paste Special-Formats-OK Hope this helps Pete "stabilo" wrote: > I have a cell (let's say A1), that is formatted with a fill color, a specific > font, and a specific font color. I have another cell (A5) that has the > formula =A1. How can A5 have the same formatting as A1 and no...

Default Formatting Settings.
I've recently setup Office 2003 on my new system and am trying to get the formatting that I had on my old system onto my new one. My difficulty lies with setting default settings for formating ($ % ,). How do I set them so that when I select (,) a negative number is displayed as (500.00) and not -500.00? I know how to format it manually each time but I don't know how to set it up as a default. Any suggestions? There's a windows setting you can change: The way I'd get to in Win98 is: Windows Start button|settings|control panel|Regional Settings Currency Tab|Negativ...

Pivot Table Formatting Question
I feel stupid asking this, but couldn't find the answer anywhere. When I initially started working with pivot tables, I was able to select all like items by placing my cursor to the extreme left of the row. This would allow me to change the formatting in all the total fields (for example) at once. I am no longer being able to select all like fields this way. Do any of you geniuses know how I might enable this feature again? Thanks in advance for your help. To enable selection: From the Pivot toolbar, choose PivotTable>Select Click on Enable Selection marmur1 wrote: > ...

why can't i use readingwindow in draft and outbox
I wonder why Microsoft have taken out the function to view the email in a readingwindow under the list of emails, in the draft and outbox folders? In the help section Microsoft writes that it is not possible to use it in draft folder. I use Outlook 2003. My colleague got an elder version and there the readingwindow works in al folders. Is it possible that it works in newer versions? "Rob" <Rob@discussions.microsoft.com> wrote in message news:0CD0849C-DFC2-4715-99F3-DD307FDC2AA0@microsoft.com... >I wonder why Microsoft have taken out the function to view the...

Can not open file
I have a workbook which I built with vlookups and charts. When I went back today to open it it starts to open then asks if I would like to enable/disable macros. I have done both enable and disable after I hit the button it closes excel down completely. It only does this with this file? Hi sounds like a corrupted file. You may try using OpenOffice (seems to be able to open such files): www.openoffice.org -- Regards Frank Kabel Frankfurt, Germany "Pat Quinn" <pquinn@biolabinc.com> schrieb im Newsbeitrag news:a34001c4d6fa$16da9b90$a401280a@phx.gbl... > I have a workbo...

I can't open any "pps" presentation. What is missing in my compu
I can't open any e-mails with "pps". I'm I missing some program in my computer? Ask in a PowerPoint group - not Outlook related which this group supports. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, Mickey asked: | I can't open any e-mails with "pps". I'm I missing some program in my | computer? you need PowerPoint or PowerPoint Viewer to open those, google...

chart preferences 2007
can I set default chart preferences in Excel 2007 such as line color and style in bar chart ? Hi, You can not change the defaults but you can create your own settings via templates. http://office.microsoft.com/en-gb/excel/CH102859891033.aspx Cheers Andy rolando wrote: > can I set default chart preferences in Excel 2007 such as line color and > style in bar chart ? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

can I serialize to xml instead of proprietary format
Hello, For example in the case of CObject::Serialize(archive); is there a way to get that to write out in xml format instead of proprietary? Then I wouldn't have to erase the preferences file each time I add a new element to add, e.g. if (archive.IsStoring()) { archive << GetSize(); archive << m_debug; if above code in use for a while, and now if I want to add archive << m_do_not_use_skin; I need to erase preferences because serialize only knows about the order in which to expect the properties, but if it could understand xml would not need to be in any specifi...

Can't Autofilter converted date??!?
A database query program outputs everything as a text string. One of the fields is a date, formatted as yyyymmdd. I need this as at least a number, and preferably a real date that I can Autofilter. Using a tip from JE McGimpsey, I select the column, choose Data/Text to Columns, and choose the YMD from the Date option drop-down. (JE suggested choosing the yyyymmdd, but that's not available in my Excel 2003, unless I'm doing something wrong.) Then I Format all the dates as Special >> yyyymmdd. But I can't Autofilter >> Custom >> "is greater than or equal ...

Can I write a macro that triggers off an Outlook alert
I would like to write a macro or other program that begins when it senses an alert in outlook. See the Reminder event of the Application object. -- Best regards Michael Bauer - MVP Outlook Category Manager - Manage and share your categories: SAM - The Sending Account Manager: <http://www.vboffice.net/product.html?lang=en> Am Fri, 23 Apr 2010 14:30:01 -0700 schrieb Fred: > I would like to write a macro or other program that begins when it senses an > alert in outlook. ...

Why will my Excel charts not print in color?
Why will my Excel charts not print in color? I've seen this happen for these reasons: 1) Not using a color printer. 2) Printer set up on your computer to be B+W for everything instead of color. Take a look at the printer prefs (I think). "cyncha" <cyncha@discussions.microsoft.com> wrote in message news:1D574F2A-2AE9-4333-9555-F7B71F1D6FBB@microsoft.com... > Why will my Excel charts not print in color? Another scapegoat is the printer driver. Make sure these are up to date. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials an...

How do I create a sales funnel graph/chart with Excel?
CharlesJ - It might help if you describe the kind of chart you want (and the data upon which it is based). A Google search for "sales funnel chart" yields approx. 125,000 results. Some examples seem to be quantitative (with what some chart gurus might regard as unnecessary deception using length and areas), and some seem to be merely line diagrams, not based on numerical data. - Mike www.mikemiddleton.com CharlesJ - It might help if you describe the kind of chart you want (and the data upon which it is based). A Google search for "sales funnel chart" yiel...

buble chart
I want to take data from excell or a Database to create a buble chart. One piece of Data would determin the size of the Circle. another piecie of data determin the location location of the circle on the sheet- Can it be done? we spell it a little different. Try over here http://blogs.msdn.com/visio/archive/2007/02/06/introducing-the-bubble-chart.aspx "Brent" <sagemaster@mvdsl.com> wrote in message news:uLTAeOZkHHA.5084@TK2MSFTNGP03.phx.gbl... >I want to take data from excell or a Database to create a buble chart. One >piece of Data would determin the size of...

Conditionally Format A Line?
Greetings, Access Novice here. I have Line18 formatted as Visible = "NO" in Properties, and I want to make it visible depending on the value of certain fields. Why doesn't this code work when put in the "On Format" or "On Print" events of the Detail section? If [Inventory Posting Group] = "RESALE" Then Line18.Visible = "YES" BTW, I tried "TRUE" in place of "YES", and it still didn't work. TIA. Assuming [Inventory Posting Group] is bound to a control in the report, you should be able to use: ME.L...

merge from excel and formatting
I have fields in my excel document that include double spacing, etc. When I export to Word, I loose that formatting. Can I fix this? (I asked this in the Excel section, but realized it may be a word function) Thanks! Can you spell out exactly what you mean by "double spacing" and how you applied it? Peter Jamieson http://tips.pjmsn.me.uk On 30/03/2010 19:37, rroswell86 wrote: > I have fields in my excel document that include double spacing, etc. When I > export to Word, I loose that formatting. Can I fix this? (I asked this in > the Excel section...

Pie Charts and Grids
I would like to know if there is any way of inserting a grid onto a pie chart. If using excel VBA is the only way, that's fine. I just need some examples. Thanks How did you envision this grid? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ Naresh M wrote: > I would like to know if there is any way of inserting a > grid onto a pie chart. If using excel VBA is the only way, > that's fine. I just need some examples. > > Thanks ...

Excel Organizational Chart
How do I change margins, paper size and print? Hi debrag3d! File > Print Preview > Margins Button File > Page Setup -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "debrag3d@comcast.net" <anonymous@discussions.microsoft.com> wrote in message news:505801c402f1$e5088050$a601280a@phx.gbl... > How do I change margins, paper size and print? ...

Stacked Data Charts from Excel
I have two columns of data - the first is a set of dates, i.e., 2/4/08, 3/4/08; the second column contains 4 different values depicting incidents that occurred on the corresponding dates. I want to display a horizontal axis of months from June 08 through March 2010, and a vertical axis of numbers of incidents. The objective is to show how many incidents if each type occurred each month, stacked up for a total quantity of incidents per month. I have just up an EXCEL 2007 file for you at:- http://www.pierrefondes.com/ Item Number 80 towards the top of my home page. This is...