Q: What do I need to do before onboarding a spreadsheet into Apparity?
Apparity’s Integrity Check feature is a recommended first step to remediating any errors and/or issues within the spreadsheet before the initial onboard (see the Detailed User Guide for more information on the Integrity Check feature). After addressing potential errors and issues, it is important to complete the following measures prior to onboarding:
- Clean the spreadsheet of any formatting issues. Formatting issues, like blank cells, can bloat the worksheets and cause unnecessary increases in file size which will require more resources for both Excel and Apparity to function. It is suggested that you use an Excess Format Analyzer prior to onboarding to determine where blank cells contain formatting. You can clear formatting by selecting the cells identified as having excess formatting (full rows, columns) and selecting 'Delete All'. You will need to close/reopen the spreadsheet to see the ranges reset in Excel.
- Remove workbook-level encryption. Passwords that encrypt the file at the workbook level (open password) is not compatible with Apparity, but all other passwords (modify, worksheet, range, macro, etc.) can remain.
- Remove .xlk back up settings.
- Convert the file to the latest supported file formats (i.e., .xls to .xlsx). Apparity is able to support .xls files, but it functions optimally on the latest supported file formats. If you onboard an .xls file and experience issues with comparisons and your macros, you should convert your file to the latest file format (e.g., .xlsx or .xlsm).
- Remove version control VB code and/or Macros.
Q: What is excess formatting in an Excel spreadsheet?
Per Microsoft, "formatting cells on your worksheet can make the right information easy to see at a glance, but formatting cells that aren't being used (especially entire rows and columns) can cause your workbook's file size to grow quickly. This can slow down not just Excel, but the overall performance of your computer when you have an excessively formatted worksheet open".
Cleaning excess formatting works by removing cells from the worksheet that are beyond the last cell that isn't blank. For example, if you apply conditional formatting to an entire row, but your data goes out only to column V, the conditional formatting may be removed from columns beyond column V. Apparity's Excess Format Analyzer can show you which worksheets appear to have excess formatting; users can use this information to then delete the formatting out of the cells. You will need to close/reopen the spreadsheet to see the ranges reset in Excel.
Q: Does Apparity provide a feature to help identify excess formatting?
Yes. Apparity’s Excess Format Analyzer feature works by identifying the last cell with data or formula and comparing that to the last cell reported by Excel to show the user areas of potential excess formatting. Apparity will then visually display the excess cell range and allow the user to be able to delete those cells from their worksheets. Apparity will not automatically delete excess formatting for the user because there are scenarios where worksheets contain excess formatting for a valid reason. To remove the excess formatting, highlight the cells you want to remove, and choose Delete All, and then save, close and reopen the file.
To access the format analyzer, click on the Excess Format Analyzer button on the Apparity ribbon. Please note that you must be logged into Apparity to gain access to the Excess Format Analyzer. To force a log in, you can first click on the Reviews icon in the Apparity ribbon before taking the above steps to access the Excess Format Analyzer.
Q: Why is the Onboard New Spreadsheet icon grayed out on the Apparity ribbon?
The Onboard New Spreadsheet may be grayed out on the Apparity ribbon for multiple reasons:
- The file is already onboarded into Apparity – To confirm if the spreadsheet is already onboarded into Apparity, look for the Version History pane on the right side of the Excel spreadsheet.
- The file is not saved on a Shared Drive – Apparity will not allow you to onboard spreadsheets that are saved on your local computer. To onboard the spreadsheet, conduct a SaveAs and save the spreadsheet to a location on your Shared Drive.
- The file is opened in read-only – Apparity will not allow you to onboard spreadsheets that are opened in read-only. To onboard the spreadsheet, reopen the file in edit mode.
Q: Are there any file size limits for onboarding?
No, Apparity does not have a restriction for managing certain file sizes. However, performance time will increase with the size of a spreadsheet. Please reference the information from the Prior to Onboarding section to minimize performance issues.
Q: How many times do I have to manually onboard my spreadsheet?
Once. The onboarding process for a file is a one-time event. Apparity will automatically register all other instances of modifications or new versions of the file.
Q: Should I make a copy of the spreadsheet prior to onboarding?
Yes, it is recommended that a copy of the spreadsheet is made prior to onboarding.
Q: How can I tell if my spreadsheet was successfully onboarded?
Besides the confirmation pop-up after submitting the metadata information during the initial onboard, you can also confirm that your spreadsheet was onboarded by locating the Managed File icon in the Apparity Ribbon. Additionally, Onboard New Spreadsheet should be grey and inactive.
Q: What if I onboarded the wrong file?
If you onboarded the wrong file, please contact the Support Center to have the file removed from the Apparity database.
Q: Why did my file size increase significantly after onboarding?
If your file size increased significantly after onboarding, it was likely due to excess formatting in your file. To identify excess formatting, reference the Does the Excess Format Cleaner work after I onboard my file? question above.
Q: How can I determine the file path of my onboarded file(s)?
From the Version History pane, users can right-click on a modification and select Check-in Comments > View Comments to see the file path.
Q: Can I onboard a workbook that requires a password to open it?
No, workbooks that require a password to open the file cannot be onboarded into Apparity. The password must be removed to enable onboarding.
Q: Are there restrictions on the types of passwords that I can keep on my file?
Yes. Users can apply the following levels of password restrictions with Apparity:
- Passwords to Modify the EUCA
- Passwords for specific tabs within the EUCA
- Passwords for cells
- Passwords for formulas
- Passwords for VBA code
Users will not be able to have the password to open functionality because it will prevent Apparity from monitoring and tracking the content of the file.