Forum Discussion
How to protect respondents confidentiality?
I've been working on this file for a while now - I got help here to put it together, actually - but now I'm struggling to keep it confidential. That means that if # of respondents for a filter or group of filters Is lower than 10 results shouldn't show.
Is there a way to accomplish this?
link to file: https://netorgft1999977-my.sharepoint.com/:x:/g/personal/norberto_workplacechanges_com/EZew_zAFhXdJi4HHdp7VMOMBCxpOxQ49eLzGZnvlOD8H0w?e=jD9x1u
Thanks for reaching out. I've been thinking about it and after dynamic arrays hit Excel, I prefer not to use pivot tables but instead use pure calculations.
The functionality, to view results only if at least ten identities are in the selection, could start like this.
=IF(ROWS(UNIQUE(RespondentsTable[ID]))<=10,"2few2view","{show result}")One thing is that the table holds all the data all the time and you need to count the rows in the current selection.
Thus you have to filter the tables data to the wanted selection and count the rows in the filtered result, eg in cell M2 and thus replace the full set of RespondentsTable[ID]'s with M2#.
So far, it's a question of Excel functionality and I have attached a file that uses slicers to filter a table - not just hide rows outside the scope.
It's both all in one (g24) and splitted into different parts (columns M to Z).
A bigger issue is to distribute Excel files with confidential data.
From my point of view, Excel is not at all secure enough and I would not do it. If it had to be Excel, I would make the raw data anonymous eg by replacing sensitive information with a not related numbers.
The example data in the file is GDPR fines for some first years. Not taking care of confidential data may come costsome.
5 Replies
Thanks for reaching out. I've been thinking about it and after dynamic arrays hit Excel, I prefer not to use pivot tables but instead use pure calculations.
The functionality, to view results only if at least ten identities are in the selection, could start like this.
=IF(ROWS(UNIQUE(RespondentsTable[ID]))<=10,"2few2view","{show result}")One thing is that the table holds all the data all the time and you need to count the rows in the current selection.
Thus you have to filter the tables data to the wanted selection and count the rows in the filtered result, eg in cell M2 and thus replace the full set of RespondentsTable[ID]'s with M2#.
So far, it's a question of Excel functionality and I have attached a file that uses slicers to filter a table - not just hide rows outside the scope.
It's both all in one (g24) and splitted into different parts (columns M to Z).
A bigger issue is to distribute Excel files with confidential data.
From my point of view, Excel is not at all secure enough and I would not do it. If it had to be Excel, I would make the raw data anonymous eg by replacing sensitive information with a not related numbers.
The example data in the file is GDPR fines for some first years. Not taking care of confidential data may come costsome.
- This is cracking my head but is very helpful, thanks bosinander. I'm not that concerned about the distribution of information as it is my client's employee information they share with me for the project, then I do a culture survey and for the results I am trying to add graphs and more but I don't want them to be able to pinpoint respondents with answers - that's the piece I'm trying to take care of. Will keep learning about this, again, thanks for the information!
The solution is not Excel basics but I hope you can reuse parts of it and/or fetch the functions that will suite your solution.
As always, things can be done in different ways but I will use this one in some upcoming educations.
I am generally concerned about integrity and privacy so it is good that you already have that covered 🙂 Maybe you should set your data sheet with all records to "very hidden" and thus make it harder to find.alt+F11 opens the macro editor where you can set visibilty to _very_ hidden.
