Excel 2007 remove zero value in labels on pie chart: excel 2007, remove zero value, pie chart

by: jppintoPosted on 2009-11-01 at 07:08:15ID: 25713909

It's not possible to do without VBA code...

With VBA code, here's a post where this was handled:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_21113454.html

Sorry, but without macros it's not possible, even in 2007 version.

jppinto

by: gharzPosted on 2009-11-01 at 08:43:48ID: 25714284

jppinto:

ah, ok. thanks for info. hope it's ok if i won't accept your message as a solution... at the moment.

thanks!

regards.

by: jppintoPosted on 2009-11-01 at 08:45:03ID: 25714288

OK, you can wait but as I told you: only with VBA code that it's possible to do that...

jppinto

by: byundtPosted on 2009-11-01 at 09:30:14ID: 25714454

Hello gharz,
Assuming that your cells contain formulas that return the count, then the pie chart will display the 0 even if the formula returns the =NA() function or an empty string ="". There are two workarounds for this without using VBA:
1) Clear the formula from the cells that have a count of 0. A truly blank cell won't show up in the pie chart.
2) Filter the data to hide the rows that contain a count of 0. You can do this manually or automatically with an AutoFilter. The manual procedure may be found on the Home menu, under Cells...Format...Hide and Unhide...Hide Rows.

Regards,

Brad

by: byundtPosted on 2009-11-01 at 09:35:25ID: 25714484

gharz,
After some further testing, I see that an exploded pie chart will display the sliver even if the count cell is completely blank. A non-exploded pie chart won't have this issue.

If you want to use an exploded pie chart, consider hiding the rows with 0 count using a filter. Neither the sliver nor the 0 value appear.

Brad

by: gharzPosted on 2009-11-01 at 21:07:41ID: 25717155

hi, brad.

thanks for the info.

my excel sheet has 5 charts and the data are taken from a SQL server. the data cells has formula to retrieve these data from the database.

i was just wondering if there is an easier way, i.e. without hiding the rows, adding a macro, etc., to hide the zero values from the chart.

i would like to create macros that would automatically retrieve the data and plot them on charts... the problem is i'm not that familiar and experienced vb programmer. i can only write simple procedures.

perhaps i'll just start from scratch and create the report.

thanks.

by: byundtPosted on 2009-11-02 at 10:06:12ID: 25721824

Here is a macro that will look at every chart on a worksheet and delete the formulas from those cells where the formula returns an empty string. By deleting the formulas, a pie chart won't display the 0 in the data label. You will still see that age range in the legend, however.

***Note: the macro parses the series formula using a comma as a separator. You will get an error if the worksheet name contains a comma or if the chart uses a compound range.***

To install a sub in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

To run a sub or macro:
5) ALT + F8 to open the macro window
6) Select the macro
7) Click the "Run" button

Optional steps to assign a shortcut key to your macro:
8) Repeat steps 5 & 6, then press the "Options" button
9) Enter the character you want to use (Shift + character will have fewer conflicts with existing shortcuts)
10) Enter some descriptive text telling what the macro does in the "Description" field
11) Click the "OK" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Brad

Sub NoZeros()
Dim cht As ChartObject
Dim ser As Series
Dim cel As Range, rg As Range
Dim v As Variant
Application.ScreenUpdating = False
For Each cht In ActiveSheet.ChartObjects
    For Each ser In cht.Chart.SeriesCollection
        Set rg = Nothing
        'On Error Resume Next
        v = Split(ser.Formula, ",")(2)
        Set rg = Range(v)
        If Not rg Is Nothing Then
            For Each cel In rg.Cells
                If cel = "" Then cel.ClearContents
            Next
        End If
        On Error GoTo 0
    Next
Next
Application.ScreenUpdating = True
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

Select all Open in new window

NoZerosQ24861756-.xls
- 28 KB
Sample file with macro installed

by: gharzPosted on 2009-11-02 at 19:47:17ID: 25725901

byundt,

thank you for the code. i'll try to upload images on the report that i use and i'll try explain how i really worked on it.

regards.

by: gharzPosted on 2009-11-04 at 02:26:20ID: 31648549

thanks, guys!

Question

Excel 2007 remove zero value in labels on pie chart

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

excel 2007

remove zero value

pie chart

Microsoft Excel Spreadsheet Software

Microsoft Applications

Microsoft Office Suite

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press