preachermjs
New Member
Hi everyone! I'm trying to create pie charts which will ignore any categories with 0%. Can anyone help me understand how to do that?
Excel Charts without zeros
- Thread starter preachermjs
- Start date
Sajan
Excel Ninja
Hello preachermjs,
Here is one approach, using helper columns.
Assuming your categories are in A2:A6, and is named "Categories"
Assuming your values or percentages are in B2:B6, and is named "DataRng"
Put the following formula in C2, and copy down through C6
=INDEX(Categories, MATCH(SMALL(IF((DataRng+0)>0,ROW(DataRng)), ROW(1:1)), IF((DataRng+0)>0,ROW(DataRng))))
confirm with Ctrl + Shift + Enter
(You can ignore any errors returned, since the next formula will ignore them.)
Put the following formula in D2, and copy down through D6
=INDEX(DataRng, MATCH(C2,Categories,0))
(Again, you can ignore any errors.)
Create a dynamic Name called DataLabels and set it to the following formula:
=OFFSET(Sheet1!$C$2,0,0,SUM(SIGN(DataRng)), 1)
Create a dynamic Name called DataValues and set it to the following formula:
=OFFSET(Sheet1!$D$2,0,0,SUM(SIGN(DataRng)), 1)
In your pie chart, reference the name DataLabels for the Axis/Category. (Remember to use a fully qualified name that includes the sheet name, such as Sheet1!DataLabels
Similarly, reference the name DataValues for the Series. (Again, remember to use a fully qualified name, such as Sheet1!DataValues)
Now your pie chart should dynamically show categories and values only when the value is non-zero.
Cheers,
Sajan.
Here is one approach, using helper columns.
Assuming your categories are in A2:A6, and is named "Categories"
Assuming your values or percentages are in B2:B6, and is named "DataRng"
Put the following formula in C2, and copy down through C6
=INDEX(Categories, MATCH(SMALL(IF((DataRng+0)>0,ROW(DataRng)), ROW(1:1)), IF((DataRng+0)>0,ROW(DataRng))))
confirm with Ctrl + Shift + Enter
(You can ignore any errors returned, since the next formula will ignore them.)
Put the following formula in D2, and copy down through D6
=INDEX(DataRng, MATCH(C2,Categories,0))
(Again, you can ignore any errors.)
Create a dynamic Name called DataLabels and set it to the following formula:
=OFFSET(Sheet1!$C$2,0,0,SUM(SIGN(DataRng)), 1)
Create a dynamic Name called DataValues and set it to the following formula:
=OFFSET(Sheet1!$D$2,0,0,SUM(SIGN(DataRng)), 1)
In your pie chart, reference the name DataLabels for the Axis/Category. (Remember to use a fully qualified name that includes the sheet name, such as Sheet1!DataLabels
Similarly, reference the name DataValues for the Series. (Again, remember to use a fully qualified name, such as Sheet1!DataValues)
Now your pie chart should dynamically show categories and values only when the value is non-zero.
Cheers,
Sajan.
NARAYANK991
Excel Ninja
Hi ,
I am somewhat confused by what you want ; anyway you can see if any of this applies to your problem.
1. If it is a matter of the legend , hiding the row with 0 data will prevent it from being used in the chart.
2. If it is a matter of data labels , using a Custom Format such as #,##0;-#,##0; ( with an additional ; at the end of the format string ) will prevent the zeros from being displayed.
Narayan
I am somewhat confused by what you want ; anyway you can see if any of this applies to your problem.
1. If it is a matter of the legend , hiding the row with 0 data will prevent it from being used in the chart.
2. If it is a matter of data labels , using a Custom Format such as #,##0;-#,##0; ( with an additional ; at the end of the format string ) will prevent the zeros from being displayed.
Narayan