Spreadsheets
This documentation provides guidance on the spreadsheet options and categorisation methods available. If you encounter any issues, refer to the troubleshooting sections in the relevant section.
Last updated
This documentation provides guidance on the spreadsheet options and categorisation methods available. If you encounter any issues, refer to the troubleshooting sections in the relevant section.
Last updated
There are two types of spreadsheets available for categorisation:
Macro-enabled spreadsheet (.xlsm) – This version includes a macro that assists in manual categorisation -
Non-macro-enabled spreadsheet (.xlsx) – This version does not include a macro but still allows manual categorisation. -
The spreadsheets are identical however the macro aids in manual categorisation, although it is possible to categorise without the use of a macro. There are two options for spreadsheets as not all institutions will allow the use of 'macro-enabled' spreadsheets, in this case the user should use the 'non-macro-enabled spreadsheet. Once you have formatted the data and are ready to upload, you should export the file to a csv format to upload into the application.
Macros are small programs embedded in a spreadsheet that automate repetitive tasks. In the macro-enabled spreadsheet, macros help streamline manual categorisation by providing dropdown selections and automatic population of related fields.
While macros can be useful, they also pose security risks if they come from an untrusted source. Malicious macros can execute harmful commands on your computer. To mitigate these risks only enable macros from trusted sources.
Each file contains a set of predefined headers, which structure the data for categorisation.
Header Name
Header Label
Requirement
SITEID
Study Site Identifier
Mandatory
STUDYID
Study Identifier
Mandatory
DVSPONDES
PD Descriptor Assigned by Sponsor
Mandatory
DVCAT
Category for Protocol Deviation
Non-mandatory
DVDECOD
Protocol Deviation Coded Term
Non-mandatory
DVTERM
Protocol Deviation Term
Non-mandatory (auto-populated
There are multiple ways to categorise data:
Uncategorised Data – Data can remain uncategorised.
Manual Categorisation – Users manually assign categories.
Multi-Categorisation – Users assign multiple categories to a single entry.
AI Categorisation – The system automatically categorises uncategorised data during upload.
Mixed Approach – Users can mix manual and AI categorisation, leaving some data uncategorised for AI processing.
Data can be left uncategorised if categorisation is not required. Uncategorised entries will remain unchanged unless AI categorisation is enabled during upload.
All data can be recategorised once uploaded. This allows uncategorised entries to be categorised after they are uploaded.
Both spreadsheets support manual categorisation, with the macro-enabled version making the process easier. Users manually select DVCAT and DVDECOD values from dropdown lists. If the selected DVCAT is changed, the DVDECOD must also be updated accordingly.
Multi-categorisation allows users to assign multiple DVCATs and DVDECODs to a single entry:
Separate multiple DVCATs using a semicolon (;).
Separate multiple DVDECODs using a semicolon (;).
If the DVDECODs are entered correctly, the DVTERM field will auto-populate with corresponding values.
The macro-enabled file provides dropdowns with multi-selection to facilitate multi-categorisation.
AI categorisation is available through the web application when uploading data. If enabled, any uncategorised entries (those without DVCATs and DVDECODs) will be processed automatically to predict a categorisation.
How to Use AI Categorisation
Simply leave entries uncategorised.
The AI model will assign categories during the upload process.
Users can apply a combination of manual and AI categorisation:
Some entries can be manually categorised while others remain uncategorised for AI processing.
This approach is useful when manual categorisation is preferred for certain data but AI assistance is needed for the rest.
If a categorisation is invalid, the data will be uploaded as uncategorised. Categorisation is considered invalid when:
The DVCAT(s) is unrecognised (only valid DVCATs are considered).
The DVDECOD is unrecognised or does not belong to the assigned DVCAT.
In the case of multi-categorisation, all assigned DVCATs are first processed, rejecting the invalid ones. The DVDECODs are then verified against the list of valid DVCATs.
Instructions
Enable Macros when opening the file. If prompted, allow macros. If no prompt appears, enable macros manually:
Go to File > Options > Trust Centre > Trust Centre Settings > Macro Settings > Enable all macros.
After using the file, revert this setting to "Disable VBA macros with notification" for security.
Enter Data:
Use the dropdowns to select a DVCAT.
The DVDECOD dropdown will populate based on the selected DVCAT.
Select a DVDECOD.
The DVTERM should populate automatically.
Add a new row for each additional entry.
Troubleshooting
If macros are blocked:
Right-click the file in File Explorer.
Select Properties.
Check Unblock and apply the changes.
Refer to Microsoft Support for further assistance.
Instructions
Enter Data:
Use the DVCAT dropdown.
The DVDECOD dropdown will populate based on the selected DVCAT.
Select a DVDECOD.
The DVTERM should populate automatically.
Multi-Categorisation:
Separate multiple DVCATs with a semicolon (;).
This disables the DVDECOD dropdown.
Manually enter multiple DVDECODs, separating them with a semicolon (;).
If correctly formatted, the DVTERM field will auto-populate with each corresponding DVTERM, separated by a semicolon.
Troubleshooting
Ensure DVCATs are spelled correctly and separated properly.
Check DVDECODs against DVTERMs:
If correctly entered, DVTERMs will populate automatically, separated by a semicolon.
If there is an error, the DVTERM field will remain empty.
Reference Sheet2 to view all valid DVCATs, DVDECODs, and DVTERMs.
Do not modify Sheet2.
Note: On Sheet2, commas in DVDECODs are replaced with underscores.