Have Excel ignore Pie Chart source data?

Hey folks!

I have a dumb question, but one I can't find an answer to anywhere online through my searches so far...

I have pie charts where the number of available answers will shift between 2 and 5, depending on the question. If I edit the pie chart to only select the data that has information in it, it displays how I want it to. If I select the data range that has blanks in it, the pie chart is all messed up.

My question is simply, how can I format the pie charts to look how they should without blank cells messing anything up? The source data is on a Master Data spreadsheet and I want users to just be able to copy and paste their answers without having to fiddle with pie chart settings each time.

Any suggestions would be great! I'm attaching an image to show what I mean. Top chart is with only visible data selected and looks how it should. Bottom chart is with the full range selected (to allow for multiple pasted answers in the future), with blank cells, and it's all wonky.

Originally Posted by FDibbins

arrange your data so that the blank cells appear as errors.. The chart will then ignore the error cells.

Hey there! I appreciate the suggestion, but in trying to make that work I'm getting the exact same issue. I have a formula in my data range that's like this:

Please Login or Register  to view this content.

If data is in my helper cell (A2), the code returns the value to be displayed on the pie chart, but if not it gives an #N/A error. Even with the blank cells now showing as errors, my pie chart still looks exactly the same (no chart, numbers at the bottom).

Am I doing something wrong?

Originally Posted by FDibbins

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Good call, example workbook is attached.

Just trying to set the data range for the pie chart to 5 total spaces, but if any of those spaces are blank the chart itself breaks and doesn't show the distribution of answers.
Making the blank cells error out results in the same exact issue.