Excel 2007 : Splitting Checkbox/Choice Data in Pivot Table

**watsonlg** · 12-17-2010, 05:06 PM

I have exported data from SharePoint to Excel 07. Some of the data comes from a checkbox field, and thus has multiple entries. Once exported, these are broken out with semicolons and hash signs. Is there any way to break out this data into my PivotTable so that I can count separately whenever each entry is selected? To give a solid example, in the attached image, how would I count "PRS Training/PRS Demo" multiple times? Thanks!

**teylyn** · 12-18-2010, 04:34 AM

Hello watsonlg,

I'm afraid nothing much can be gleaned from your stamp sized screenshot. If you want help with an Excel problem, please post an Excel file, not an image (and an unreadable image at that).

We're here to help, but you need to provide us with enough information to let us help you.

**watsonlg** · 12-22-2010, 12:55 AM

Uploading Excel document to help with issue. Thanks!

**teylyn** · 12-22-2010, 06:14 AM

Hello,

the problem here is that Excel is not a relational database and can only display two-dimensional data sets.

I've come across this issue with SharePoint survey results before and what I normally do is to insert a few columns (as many as required to capture the maximum number of answers in any one row) and then do a text-to-columns on that row, delimited by the ; sign.

This will create a certain amount of overhead, but since a two dimensional table can not be normalised completely, this is just what you need to work with, unless you want to transfer the data into a true relational database system, like Access.

**watsonlg** · 01-05-2011, 11:23 PM

Thanks for the help. I actually figured out that instead of doing a text to columns, it makes more sense to do an ISNUMBER/SEARCH string to be able to pull in the data for each criteria.=IF(ISNUMBER(SEARCH("Increase usage",$V5)), "Increase usage", "")

**teylyn** · 01-05-2011, 11:37 PM

Hello,

when I do SP list analysis over time, with data refreshes in between, I always keep the raw data in one sheet and then use formulas on another sheet to do the splicing, similar to what you've posted above. For a one-off, text to columns works better for me.

cheers,