Editing performance in Excel
SurveyCTO's spreadsheet form templates and sample forms contain conditional formatting rules that will draw lines between lists on the choices tab and highlight fields on the survey tab. Generally, this formatting is helpful – particularly when you're starting out.
However, as you copy, paste, and insert rows in your form, Excel quietly duplicates all of the conditional-formatting rules. Everything might look fine on the surface, but behind the scenes Excel gets slower and slower as it struggles to handle a greater and greater number of duplicated formatting rules. You might also notice that your form takes longer and longer to load in the online form designer. At some point, this can become a real problem.
Automatic form optimization
When your XLSX form definition becomes too large, your server will automatically optimize it for you when you open that form in the online form designer. Behind the scenes, this optimization is doing the following things:
- Remove all worksheets beyond the survey, choices, and settings worksheets.
- Remove all comments and notes on cells.
- Remove all rows beyond the maximum of 50,000.
- Remove all columns beyond the maximum of 1,000.
- Remove all custom formatting except for the conditional formatting rules from the SurveyCTO form template.
- Remove all data that are not within the column range of the header row.
This optimization should make your form open much more quickly in both the online form designer as well as in Excel. All you have to do to keep the changes made during this process is save your form. If you don't want to keep the changes, don't worry – all you have to do is close the form designer without saving. For example, if you use extra worksheets in your form definition and you don't want those sheets to get removed, you can simply close the window without saving and your form definition with the extra worksheets will remain unchanged.
Manual form optimization
If you don't want to use the automatic form optimization process, there are many things you can do within Excel to optimize your form definition and speed up Excel.
-
Remove all conditional formatting rules
The most common cause of sluggish form definitions is many conditional formatting rules. It's a very quick and easy process to delete all of these at once. To do so, you can click the Conditional Formatting toolbar button, then select Clear Rules From Entire Sheet. You will lose the nice formatting, but Excel will become speedy again.
-
Remove duplicate conditional formatting rules
As mentioned above, copying and pasting cells and rows can duplicate their conditional formatting rules as well. If you are familiar with Excel, you can delete just the duplicate rules, which will improve Excel's performance without losing the helpful formatting.
-
Other options
If removing conditional formatting rules doesn't help, try some of the other things that the automatic form optimization process does (see above).
Migrate to Google Sheets
If Excel is still performing poorly after trying the steps listed above, and you have access to Google Sheets, you could also try migrating your form definition to Google Sheets format. Google Drive can convert Excel workbooks into Google Sheets format automatically, which you’ll want to do, rather than directly editing Excel format files stored in Google Drive. When you work in Google Sheets, you’re leveraging the computing power of Google servers, which can improve the performance of large form designs. Once you're done editing your form definition, you'll be able to upload it directly from Google Drive to your SurveyCTO server.
