Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Charts using cells containing multiple values

Copper Contributor

Hi gurus,


I run a spreadsheet for my organisation that tracks reasons for incomplete orders. In my spreadsheet I can allocate one of 5 reasons for any incompletions and these reasons are contained within a cell that has the 5 reasons available to be selected from a drop-down; I can choose from 0 to all 5 reasons if I wish. The reasons in the drop down are listed in plain text e.g. "Unavailability" "Poor Quality" etc...


What I'd like to do is have a chart that 'looks into the cells' and will be able to show me the percentage of orders that were incomplete for >'X' reasons i.e. we can see how many times we're having multiple failures resulting in incomplete orders! I envisage a simple bar chart showing when reasons are >1, >2, >3, >4, and 5.


This spreadsheet is live and continually updated and so I'm trying to find a solution that will be able to look into the cells to work; a lot of the suggestions I have seen googling this refer to taking the information out of the cells into new columns etc... to use in charts which means it won't capture the on-going entries.


I'm not sure if this can be done but have come up with a blank in my searches and would appreciate any of the assembled experience's thoughts on if this is possible. We use MS Office Professional Plus 2013.


I hope I have explained this clearly and thanks very much for devoting some brain power to it!

1 Reply



I hope I have explained this clearly and thanks very much for devoting some brain power to it!


What would be helpful, in addition to the verbal explanation, is a look at how your data actually appears on the spreadsheet. How it appears, how it's organized, can be a big factor in what method is most suitable--there are undoubtedly several methods that could be used.


Ideally, unless this is highly confidential data, you would help us help you by posting a copy of the actual workbook on OneDrive or GoogleDrive, with a link pasted here that grants access to it. If the data are confidential/proprietary, create a quick representation of the actual that uses a fictional set of data but still arrayed the same way.