• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional PIE Chart

Eyas Hawashin

New Member
Hey there,

I need Excel to create a pie chart for cells that contain "Yes" values as shown in the attached file. Thanks in advance.

Best,

Eyas
 

Attachments

  • Eyas - Excel Question 1.xlsx
    22.8 KB · Views: 6
Hi ,

Excel does not display chart values which have been filtered out.

If you can put a filter on column B selecting only Yes values , the chart will display only those values. All No values will be ignored.

See the attached file.

Narayan
 

Attachments

  • Book2 (3).xlsx
    23 KB · Views: 2
Was it just a coincidence the "No"es corresponded to zero values?

If one wishes to filter by formula rather than by interaction with the worksheet, the values are easier to work with than the text.
For example, using a new function available in Office 365, and could define filtered arrays using named formulae
= FILTER( Sheet1!Names, Sheet1!Values )
= FILTER( Sheet1!Values, Sheet1!Values )

so that the chart series formula becomes
=SERIES(,Sheet1!filteredNames,Sheet1!filteredValues,1)
 
Was it just a coincidence the "No"es corresponded to zero values?

If one wishes to filter by formula rather than by interaction with the worksheet, the values are easier to work with than the text.
For example, using a new function available in Office 365, and could define filtered arrays using named formulae
= FILTER( Sheet1!Names, Sheet1!Values )
= FILTER( Sheet1!Values, Sheet1!Values )

so that the chart series formula becomes
=SERIES(,Sheet1!filteredNames,Sheet1!filteredValues,1)
Thank you Peter. No it was not coincidence.
I have office 2019 (365) but i could not find the FILTER function. They also told me about XLOOKUP and did not find it. May I know how to do that?
 
@Eyas Hawashin
The dynamic arrays are new functionality that is currently being rolled out to Office 365 users. It has been available to insiders for over a year now and I think it has reached Monthly Channel (if you are on semi-annual update, you or your IT manager would need to access the account settings in order to see the new functions).

I have attached a copy of the workbook for whenever you get dynamic arrays up and going. Working directly with the Chart Series function is something of a pig; it is as fussy as hell and gives you no help whatsoever.

Do not think of dynamic arrays merely as a few fancy 'nice to have' functions. It can change spreadsheeting utterly with a top-down design leading to dynamic array solutions as the option of choice rather the option of last resort (paraded by annoying clever clogs ;)) and to be used when all else fails.

p.s. Setting Home or Personal copies is far easier and is done through account settitngs.
 

Attachments

  • FilteringChartsByFormula.xlsx
    34.6 KB · Views: 7
Last edited:
Back
Top