How to protect the Dashboard’s Source Excel File
This document is applicable only for the Dashboards that are built using Excel as source data.
Usually the source excel files given by client will be protected and there is no way to display the protected excel sheet data in the preview mode of dashboard.
In such case, this is an alternative method to use the protected excel data in the dashboard and make it display in the preview.
Step 1: Ask the client to Select ‘Protect sheet’ option, so that only the particular sheet in the Excel Workbook will be protected not the others.
Step 2: Insert a empty Excel Sheet named “Duplicate” in which you need to copy the entire data inside the ‘Protected Sheet’ and paste in this new Excel as in below image.
Note 1: Do not duplicate the protected sheet. Because, by default that new duplicated sheet will also be protected.
Note 2: For the cells selection option to be enabled in the “Protected Sheet” you need to check the option as shown in below image.
Step 3: Use this 2nd sheet data for components mapping.
In this way, the dashboard will work fine. As the main data provided by client will be in the ‘Main Sheet’ i.e., the protected sheet, it will not be disturbed at all. We can do all our data calculations using the duplicate sheet only. Finally the dashboard will look like this.