Pie Chart - How to tell the chart not to pick up zero values

Good day,

I have data organised as follows.

Col A Col B
(Header) ( % )
red 39%
blue 0%
white 24 %
Green 10%
Brown 0%

My problem is that when i create a pie chart, it picks up the 0 valuses as
well, and so the chart looks clustured, as i need to have the lables.

Please advise how to tell the chart to omit 0 values.

One option which i cannot use due to layout of the report is Hiding rows and
then plotting only visible cells.

Thanks

Best regards


0
7/28/2003 9:19:32 AM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
787 Views

Similar Articles

[PageSpeed] 52

Good Day, Jon,

Thanks for the answer, but what i need is for the chart not to plot 0
values.
SO that there are not too many slices of the pie.
Any way of doing this without hiding rows with 0 values.

Best regards
Khawar
"Jon Peltier" <jonpeltier@yahoo.com> wrote in message
news:3F250B8C.60800@yahoo.com...
> Khawar -
>
> An easy way is to construct custom data labels.  You can put them into a
> range off the visible part of your printed report, like column M.
> Assuming your data in A:B starts in row 2 (headers in row 1), in cell M2
> put this formula:
>
> =IF(B2=0,"",A2&" "&TEXT(B2,"0%")
>
> Now use Rob Bovey's Chart Labeler (http://appspro.com) or John
> Walkenbach's Chart Tools (http://j-walk.com), both free Excel add-ins,
> to use this range for the data labels for the charted series.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> http://www.geocities.com/jonpeltier/Excel/index.html
> _______
>
> Khawar Ahmad Khan wrote:
> > Good day,
> >
> > I have data organised as follows.
> >
> > Col A Col B
> > (Header) ( % )
> > red 39%
> > blue 0%
> > white 24 %
> > Green 10%
> > Brown 0%
> >
> > My problem is that when i create a pie chart, it picks up the 0 valuses
as
> > well, and so the chart looks clustured, as i need to have the lables.
> >
> > Please advise how to tell the chart to omit 0 values.
> >
> > One option which i cannot use due to layout of the report is Hiding rows
and
> > then plotting only visible cells.
> >
> > Thanks
> >
> > Best regards
> >
> >
>


0
7/29/2003 3:01:08 AM
No, I don't know of any way to avoid plotting zero values in a pie 
chart without removing the data from the plotted range.  You can do so 
with a Advanced Filter, or do it manually, or build some other 
formula/VBA based method, but you have to remove the zeros from the 
plotted data range.

-- 
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <#dbKy2XVDHA.2156@TK2MSFTNGP11.phx.gbl>, 
khawar_ahmad@hotmail.com says...
> Good Day, Jon,
> 
> Thanks for the answer, but what i need is for the chart not to plot 0
> values.
> SO that there are not too many slices of the pie.
> Any way of doing this without hiding rows with 0 values.
> 
> Best regards
> Khawar
> "Jon Peltier" <jonpeltier@yahoo.com> wrote in message
> news:3F250B8C.60800@yahoo.com...
> > Khawar -
> >
> > An easy way is to construct custom data labels.  You can put them into a
> > range off the visible part of your printed report, like column M.
> > Assuming your data in A:B starts in row 2 (headers in row 1), in cell M2
> > put this formula:
> >
> > =IF(B2=0,"",A2&" "&TEXT(B2,"0%")
> >
> > Now use Rob Bovey's Chart Labeler (http://appspro.com) or John
> > Walkenbach's Chart Tools (http://j-walk.com), both free Excel add-ins,
> > to use this range for the data labels for the charted series.
> >
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > http://www.geocities.com/jonpeltier/Excel/index.html
> > _______
> >
> > Khawar Ahmad Khan wrote:
> > > Good day,
> > >
> > > I have data organised as follows.
> > >
> > > Col A Col B
> > > (Header) ( % )
> > > red 39%
> > > blue 0%
> > > white 24 %
> > > Green 10%
> > > Brown 0%
> > >
> > > My problem is that when i create a pie chart, it picks up the 0 valuses
> as
> > > well, and so the chart looks clustured, as i need to have the lables.
> > >
> > > Please advise how to tell the chart to omit 0 values.
> > >
> > > One option which i cannot use due to layout of the report is Hiding rows
> and
> > > then plotting only visible cells.
> > >
> > > Thanks
> > >
> > > Best regards
> > >
> > >
> >
> 
> 
> 
0
ng_poster (159)
7/29/2003 11:53:36 AM
Khawar -

Does the 0-width pie slice show up at all?  If you explode the chart (a 
good thing to do with pie charts, but I mean explode in a different 
way), you see a line where the piece fits in, but you can change the 
border for that pie wedge to None, and it disappears.  You can also hide 
the legend entry for the zero-width piece: click to select the legend, 
click again to select the legend entry, then press Delete.

Here's another idea.  Keep your original data unchanged, copy it, then 
in another worksheet use paste link to set up another table, linked to 
the first.  Now apply your filter to this data, and create your chart 
based on it.  Now move the chart back to the original sheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Khawar wrote:
> Good Day, Jon,
> 
> Thanks for the answer, but what i need is for the chart not to plot 0
> values.
> SO that there are not too many slices of the pie.
> Any way of doing this without hiding rows with 0 values.
> 
> Best regards
> Khawar
> "Jon Peltier" <jonpeltier@yahoo.com> wrote in message
> news:3F250B8C.60800@yahoo.com...
> 
>>Khawar -
>>
>>An easy way is to construct custom data labels.  You can put them into a
>>range off the visible part of your printed report, like column M.
>>Assuming your data in A:B starts in row 2 (headers in row 1), in cell M2
>>put this formula:
>>
>>=IF(B2=0,"",A2&" "&TEXT(B2,"0%")
>>
>>Now use Rob Bovey's Chart Labeler (http://appspro.com) or John
>>Walkenbach's Chart Tools (http://j-walk.com), both free Excel add-ins,
>>to use this range for the data labels for the charted series.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>http://www.geocities.com/jonpeltier/Excel/index.html
>>_______
>>
>>Khawar Ahmad Khan wrote:
>>
>>>Good day,
>>>
>>>I have data organised as follows.
>>>
>>>Col A Col B
>>>(Header) ( % )
>>>red 39%
>>>blue 0%
>>>white 24 %
>>>Green 10%
>>>Brown 0%
>>>
>>>My problem is that when i create a pie chart, it picks up the 0 valuses
>>
> as
> 
>>>well, and so the chart looks clustured, as i need to have the lables.
>>>
>>>Please advise how to tell the chart to omit 0 values.
>>>
>>>One option which i cannot use due to layout of the report is Hiding rows
>>
> and
> 
>>>then plotting only visible cells.
>>>
>>>Thanks
>>>
>>>Best regards
>>>
>>>
>>
> 
> 

0
jonpeltier (303)
7/29/2003 5:03:15 PM
Hi Khawar,

I have an example that uses a few columns of formula and named ranges.
(http://www.andypope.info/charts/piezeros.htm)

you may find it useful.

Khawar wrote:
> Good Day, Jon,
> 
> Thanks for the answer, but what i need is for the chart not to plot 0
> values.
> SO that there are not too many slices of the pie.
> Any way of doing this without hiding rows with 0 values.
> 
> Best regards
> Khawar
> "Jon Peltier" <jonpeltier@yahoo.com> wrote in message
> news:3F250B8C.60800@yahoo.com...
> 
>>Khawar -
>>
>>An easy way is to construct custom data labels.  You can put them into a
>>range off the visible part of your printed report, like column M.
>>Assuming your data in A:B starts in row 2 (headers in row 1), in cell M2
>>put this formula:
>>
>>=IF(B2=0,"",A2&" "&TEXT(B2,"0%")
>>
>>Now use Rob Bovey's Chart Labeler (http://appspro.com) or John
>>Walkenbach's Chart Tools (http://j-walk.com), both free Excel add-ins,
>>to use this range for the data labels for the charted series.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>http://www.geocities.com/jonpeltier/Excel/index.html
>>_______
>>
>>Khawar Ahmad Khan wrote:
>>
>>>Good day,
>>>
>>>I have data organised as follows.
>>>
>>>Col A Col B
>>>(Header) ( % )
>>>red 39%
>>>blue 0%
>>>white 24 %
>>>Green 10%
>>>Brown 0%
>>>
>>>My problem is that when i create a pie chart, it picks up the 0 valuses
> 
> as
> 
>>>well, and so the chart looks clustured, as i need to have the lables.
>>>
>>>Please advise how to tell the chart to omit 0 values.
>>>
>>>One option which i cannot use due to layout of the report is Hiding rows
> 
> and
> 
>>>then plotting only visible cells.
>>>
>>>Thanks
>>>
>>>Best regards
>>>
>>>
>>
> 
> 

-- 

Cheers
Andy

http://www.andypope.info

0
andy9699 (3616)
7/29/2003 9:05:59 PM
Thanks very Much for the help
"Andy Pope" <andy@andypope.info> wrote in message
news:#HZZtThVDHA.652@tk2msftngp13.phx.gbl...
> Hi Khawar,
>
> I have an example that uses a few columns of formula and named ranges.
> (http://www.andypope.info/charts/piezeros.htm)
>
> you may find it useful.
>
> Khawar wrote:
> > Good Day, Jon,
> >
> > Thanks for the answer, but what i need is for the chart not to plot 0
> > values.
> > SO that there are not too many slices of the pie.
> > Any way of doing this without hiding rows with 0 values.
> >
> > Best regards
> > Khawar
> > "Jon Peltier" <jonpeltier@yahoo.com> wrote in message
> > news:3F250B8C.60800@yahoo.com...
> >
> >>Khawar -
> >>
> >>An easy way is to construct custom data labels.  You can put them into a
> >>range off the visible part of your printed report, like column M.
> >>Assuming your data in A:B starts in row 2 (headers in row 1), in cell M2
> >>put this formula:
> >>
> >>=IF(B2=0,"",A2&" "&TEXT(B2,"0%")
> >>
> >>Now use Rob Bovey's Chart Labeler (http://appspro.com) or John
> >>Walkenbach's Chart Tools (http://j-walk.com), both free Excel add-ins,
> >>to use this range for the data labels for the charted series.
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>http://www.geocities.com/jonpeltier/Excel/index.html
> >>_______
> >>
> >>Khawar Ahmad Khan wrote:
> >>
> >>>Good day,
> >>>
> >>>I have data organised as follows.
> >>>
> >>>Col A Col B
> >>>(Header) ( % )
> >>>red 39%
> >>>blue 0%
> >>>white 24 %
> >>>Green 10%
> >>>Brown 0%
> >>>
> >>>My problem is that when i create a pie chart, it picks up the 0 valuses
> >
> > as
> >
> >>>well, and so the chart looks clustured, as i need to have the lables.
> >>>
> >>>Please advise how to tell the chart to omit 0 values.
> >>>
> >>>One option which i cannot use due to layout of the report is Hiding
rows
> >
> > and
> >
> >>>then plotting only visible cells.
> >>>
> >>>Thanks
> >>>
> >>>Best regards
> >>>
> >>>
> >>
> >
> >
>
> --
>
> Cheers
> Andy
>
> http://www.andypope.info
>


0
8/1/2003 2:59:00 AM
Reply:

Similar Artilces:

Charting using Stacked Column Graph
Excel 2002... I think I posted this in the wrong place before. I'm trying to chart a series that is comprised of 5 - 3 number groups. I can chart the first group, but when I try to add the next series, it stacks them onto the existing columns rather than make 3 new ones. The data looks like this (simplified): A1 A2 A3 B1 B2 B3 1-10 .01 .03 .58 .023 .025 .023 11-15 .012 .025 .025 .024 .588 .280 16-20 .012 .255 .158 .024 .254 .241 I get a nice chart with A1-A3...but can't add B1-B3 next to it. Help me, please. Thanks, Gre...

divide by zero error on purchase order report
On a clean install of headquarters manager I get a divide by zero error on the standard purchase order report. Regardless of what date range I put in it generates the same error. Any one else had this problem? Thanks You have a purchase order in the system with an exchange rate of zero. Run the following SQL Statement from HQ Administrator: Select PONumber, POTitle, StoreID, ID, ExchangeRate from PurchaseOrder where ExchangeRate = 0 This will tell you which PO is causing the error, but fixing it will be a problem as there is really no PO Editing function in HQ. You could use a SQL Upda...

Formatting zeros as dashes
I have a worksheet with the data formatted as currency with no decimal places. I would like all my zeros to show up as dashes. For example, no $ 0 or $ (0). I understand that if the number is not exactly zero, it won't use the dash, ie. a .25 will show up as a 0... but this is what I would like to override. I also don't want to actually change the value in the cell using the rounding functions. Simply put, how do I get it to choose the format based upon the display accuracy of the value versus the true value? Ann: If you think about it there is no way for Excel t...

How to produce a chart based on number of cells not values in the cells
Hi I'm using Excel 2007. I have a requirement to base a chart on the number cells rather than the values in the cell. For example, here are some values I might enter against Monday to Friday: 1,3,1,1,2 In this example, I want my (pie) chart to show that 3/5 are value 1, 1/5 is value 2 and 1/5 is value 3 (i.e. the pie chart should have 3 sections). What excel does, is split the chart into 8 sections, and shows each individual value. Any suggestions on how I might achieve this, Thanks for any help Colin You need to do some calculations in the sheet first, then plot the results: http...

detect cell value
Wat do I use to detect cell contain the word "Actual" and assign the row to a variable which I can use latter on? Hi you may explain with some more detail what you mean with 'variable assignment' and what you want to do later on -- Regards Frank Kabel Frankfurt, Germany "crapit" <littlecramP@yahoo.com.sg> schrieb im Newsbeitrag news:ea7B#8u1EHA.2540@TK2MSFTNGP09.phx.gbl... > Wat do I use to detect cell contain the word "Actual" and assign the row to > a variable which I can use latter on? > > I want to use it to hide the entire ro...

Automate a graph/chart?
I am putting together a daily list of the change in prices of stocks, and what I want to do is make a graph on another worksheet that will update whenever enter a new date and amount is entered. Is this possible? If so...how? Thanks in advance Hi, You need to use named ranges in order for the chart to update automatically. See Jon Peltier's article on dynamic charting. http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=246 Chees Andy pugsly8422 wrote: > I am putting together a daily list of the change in prices of stocks, and > what I want to do is make a graph o...

If statement...copy values of cells on another worksheet.
I need a formula that looks at a drop down list on a different worksheet and if it is "Sold", then the data values from T2:AC2 would show up on the other worksheet. Is this possible? I figured it out. "heater" wrote: > I need a formula that looks at a drop down list on a different worksheet and > if it is "Sold", then the data values from T2:AC2 would show up on the other > worksheet. Is this possible? I still need help. I need it to look at S2:S86 (drop down list), if it is "Sold", then T2. I beleive I have to write a formula for e...

Headers and Footer on the Chart do not work??????
I have this program that take values from the cell B3 an B4 on th Sheet1 and put them automatically in all headers and footers in all the worksheets (see below). However, when I run the program all the sheets got the header and footer except the sheets that have chart. Could you please help me to fix this problem?. Thanks in advance. Maperalia Option Explicit Sub HeadersAndFooters() Dim WS As Worksheet For Each WS In Worksheets '***********Header*********** WS.PageSetup.RightHeader = "&14&B&I" & Format(Worksheets("Sheet1").Range(&quo...

Printing chart
I have been successfully printing out charts for quite some time. Now anything below the x-axis is not being printed. Print preview shows the whole charte area and everything looks good, but when printed there is nothing there. It is printing the footer fine. I have not upgraded anything recently, but there may have been some auto updates??? Any ideas? Thanks, LK Hi, Have a read of this, http://peltiertech.com/Excel/ChartsHowTo/AxisLabelCutOff.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "LK" <LK@discussions.microsoft.com> wrote ...

Automatic Chart Expansion Problem
For years, I've be charting temperature variations. As I added new data points, the chart automatically expanded to accomdate the new datum. At 72 data points, the chart stopped expanding automatically. Anyone know if this is a standard limit to expanding charts? Did I accidently turn some feature off? Is there a way to fix this? I'm running MS Win and Office XP with all updates. Thanks Mike Hi, It really depends on how your expansion was working. Is your chart set to a fixed data range with 72 rows or columns that you have now filled up? If so you just need to extend the ra...

related to PIE charts N BAR charts
hello everyone, i am using office 2007... actually i have drawn some PIE charts and BAR charts with almost seven variable each.. they are looking good in colors.. however i want the black n white printout .. which is not giving the clear on the percentage in graphs.. is there any way to feed the PATTERNS in graphs for black n white picture.. like in books we have seen those things.. Hi, If what you are asking is can you apply Patterns to charts as in 2003 and earlier, the answer is no - that feature has been removed. I suggest that you TRY a gradient fill instead and see if that meets your...

Pick fields in dynamic export
This may be a simple question to the gurus, but here it is. I am running an advanced find against my activities, but want to include account information when I export to Excel. Based on what I've heard and read, it seems to me that, if I export to a dynamic pivot table, I get the opportunitiy to pick additional columns that are not available when exporting to static worksheets. The only place that I've seen where I can pick additional columns, is in the "edit columns" button within the actual advanced find. However, this does not give me the fields I want. Where ...

averaging a value between two serial dates
Hi all, I have two spreadsheets. The first spreadsheet contains two columns, one for start date/time (in serial format) and the second for stop date/time (in serial format). The second spreadsheet contains two columns. One containing a serial date/time and the other containing a meteorological value (eg. temperature). I want Excel to read the start and stop date/time from the first spreadhseet and then search the second spreadsheet for all temperature values which were recorded during this time frame. Lastly, I want Excel to average these values. Is this possible using a single Excel func...

Calling Lookup popup window and filter values based on parameters
Hi All, Need your all's help for the following. On one of the form we have four lookup controls in following order. Account, Project, Project Task, Incident. Last 3 are custom entities. We need a functionality where, - when i open a Account lookup, it gives me account only related to current crm user. - After that when i open a Project lookup it should give projects only relted to account we have selected. - In the same way ProjectTask should be as per Project - While case should be as per account. Whats the best way of doing it? Awaiting reply. Thanks and Regards, Darshan ...

charts #11
I inherited a multipage workbook with data and charts. Many of the charts do not allow me to access the chart options (data source, formatting, etc.). They are not protected, but the chart options simply are not available (the menu bar does not change from worksheet options to chart options). How do I gain access to modify the charts? There is a good chance that these are not really charts but pictures of charts. A lot of people will copy a chart as a picture and paste it in the page so that they have a record of what they saw. also it someone is reorganizing the data the charts d...

Chart formate changing when saved
I have over ten different charts that I copy into a report out workbook at then end of each month. The normal stuff Date, goal, actual. the data format is set for % so the the 0.15 will show up on the chart as 15% when I save it. When my boss opens the chart it show up as 0.15 again. How do I make it keep the format I created it in?? The Date does the same thing but only on some of the charts. Starts as a date (month-yr) then it changes to 12345 general number. If you're copying the charts into a report, I would suggest copying the charts as pictures to insure nothing cha...

Print Chart Across Multiple Pages
I have a 30K record engineering data that is plotted into a single chart, like the stripchart recorders of old. I want to print out the chart across multiple pages (probably around 20) in order to maintain the visual resolution. I can get either the first page with 19 blank pages (just 2 horizontal lines) or a single page with all the chart data squashed together. I've tried the typical page and print setup options to no avail. Does anyone have any ideas? Thanks. Hi Bruce - I suppose you could stretch your chart out so it's 20 pages wide (or tall) on a worksheet. I woul...

Find value in another column, change Interior.colorIndex
First post didn't work. I apologize if this is a duplicate. Hello there, I wrote the following code to find a value in column B if the cell in columnS has an Interior.color.Index = 44, transfer the color to the cell with same value in B. (calcLastRow declared already) Dim ColumnB As Range, SrcChk1 As Range Dim DestChk1 As String Dim DestChk2 As String Set ColumnB = Range("B14", "B" & calcLastRow) Range("N14").Select With ColumnB Do DestChk1 = (ActiveCell.Offset(0, 5).Interior.ColorIndex...

On Close assign a value
I would like to assign a value to a field when closing a form. I have three employees (buyers) that we manually assign records for them to work. I would like to do this automatically based on the ID# of the record. I have the idea of what to do, but not sure how to program it. In my table - tblreqs - I would like to automatically assign a buyer (# 10 or #26 or #29) based on the ReqHeaderID. On close, if ReqHeader ID ends in 1,4,7, or 0, Buyer = 26; If ReqHeaderID ends in 2,5,8 Buyer = 29; If ReqHeaderID ends in 3, 6, 9 Buyer - 10. Can someone tell me how to write this c...

How to change the values in xml for picklist
Under the accounts, I got a field country picklist and a state/province picklist. I wanted to have the country picklist onchange the state/province list will reveal the releveant states options. How am I going to that in xml? Which file should I change to alow the customization to work? Any solutions? Will the changes be brought forward throught migration? What are the consequences of the changes? Thanks for the help The solution provided will greatly benfiance all. Not in a supported way. However, Javascript does ive you the option to do this type of manipulation. You would proba...

Report Parameters forms value list set by VBA?
NOTE: I"m a VBA novice I have a set of reports that use a common Report Parameter form. When I first designed them it was convenient as the same set of filters were applicable to all the reports: Example: report for invoices, report for open orders, report for processing orders Filters were: Location, Manager & Employee Now they also want to filter by date range, which won't consistent between reports: IE. Invoices = 30, 60 or 90 days old vs Pending Orders = 10, 15, 30 days old I'd like to just add one more drop down box to the parameter report and have the value list ch...

Remove Connnecting Lines from Line Chart?
Help! I want a line chart that only shows data points, not lines in between. A scatter chart doesn't work correctly with the data. How do I remove the connecting lines from a line chart and just show the data points? Help is appreciated! "marti003" wrote: I am using Excel 2007. I want a line chart that only shows data points, not lines in between. A scatter chart doesn't work correctly with the data. How do I remove the connecting lines from a line chart and just show the data points? Help is appreciated! a) when you make the chart: select Insert...

Exporting excel charts with form controls (spinner) to Powerpoint
I have created an excel chart with a spinner which allows me to look at the sensitivity of a series of variables. I want to export this chart and the spinner capabilty to a power point presentation so that I can use the spinners to demonstrate the sensitivity of the variables to my audience. How do I do this? -- Mike B You would have to export the entire chart and its workbook into PowerPoint. Make it smaller by putting just the chart data and the chart into a new workbook, copy the new chart, and paste it into PowerPoint. Then to use the spinner, you have to activate the Excel object...

How to ask whether a value is in a range of values, and produce a YES/NO answer
I know there is a way to do this, but I can't quite come up with it. I have a table in which I am trying to match one of the fields to anothter table (i.e., to check to see if it exists in that table). I need a yes or no result. To illustrate, I am in Sheet1 E2, and need to know if Sheet1 B2 existsin in the range Sheet 5 B9:B100. Thanks for any suggestions. Mike C <js2k111@yahoo.com> wrote in news:40dd464e-6b0f-47f1-af5f- 2df85f0bb964@q78g2000hsh.googlegroups.com: > I know there is a way to do this, but I can't quite come up with it. > > I have a table in which I a...

Charts/ Graphs information from more then one worksheet
Well I´m starting to get deeper and deeper into things that I do not understand :) I have Excel worksheets for every month, each with the name of the corresponding month. What I need to know is if its possible to take information from all the worksheets of every month and put these into a chart or graph so we could track the progress of a sertain person. to be more specific. Example of John Month of May (May.xls) Week1 (numbers) John (#) (#) (#) (#) (all are in the same row as john) Week2 (numbers) Week3 " Week4 " Then we have Month of June(June.xls) and all...