Forum Discussion
How to protect respondents confidentiality?
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.
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.
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.
- This looks fantastic! Thank you very much! I'll go in and try to work on this and come along with a file that I can share with my clients and brings value to them.
You're welcome 🙂 Just make sure that the client knows that with some effort, the base data can be revealed even if that sheet also has all columns and rows selected and hidden plus the sheet password being protected with a long+and+easy+2+remember+password.
Such an empty looking sheet may confuse a few more data seekers even if they find it.
Even a text like #Error 403. An unknown access error has occurred. That's all we know ¯\_(ツ)_/¯ in cell ERR403 may add an extra threshold, as well as naming the sheet to ThisWorkbook.
NB; Save a copy of your own before you add any onion layers in the distributed version :o)