Do's and Don'ts for your Excel Spreadsheet

Open as App can automatically import most of your Excel logic into the app and recognize contexts - if you give it a chance.

The requirements are relatively simple and are similar to those of common export/import processes.

1. Consistent headings in lists.
   A list must contain the column headings in the top line of the list area. They are also automatically recognized by the wizard and assigned to the individual values. (Except for a pivot diagram where the headings are listed in the left column.)
2. Clear information only.
   Color highlights or crossed-out data will not be relevant for the data analysis that leads to your app. However, your highlights already show you what is most important for your app. You will find out, how to "Design" your App in this section: https://support.openasapp.net/hc/en-us/sections/202020989
3. Cells outside the list area. 
   Don't interrupt the flow of your list area by putting fields outside the list space. Try to put your tables on a separate sheet in your file - especially if you want to use your app for collecting data. Calculations based on the list area can be done on a different sheet in your file.
4. Avoid merged cells when working with lists. One cell - one input. Merged cells cannot be analyzed and should, therefore, be taken out in advance. This is, in particular, relevant for labels in address lists or similar.  
   
   The list below is perfect for Open as App.
   
5. Format the cells in the Excel file. You want the values in a cell to be displayed with a currency symbol? Format your Spreadsheet accordingly. The better the source file, the easier and faster you will convert your Excel file into an app.

   

6. Drop-down list. To use a drop-down list in the app instead of hardcoding the values in the Data validation (as shown in the 1st picture below) it is better to define a range for the drop-down values in the sheet itself (as shown in the 2nd picture below). By following this method, the drop-down values can be easily changed and errors can be avoided.
7. No Pivots. At least not when you use Google Sheets as source data for your app. We are working on it, but for now, the wizard is not able to understand and process this feature. You will see the error message "Value cannot be 0" when trying to upload a Google Sheet with a pivot table. In this situation, you need to remove the pivot table from your spreadsheet and take a "pivot-free" version of the file for the app creation. Excel spreadsheets do not cause this error.
8. No Macro. No VBA. For the use of macros, we would need a VBA scripting engine in our app. This would mean that Open as App could no longer be available for iOS devices.
9. Structured references. Regular A1 or R1C1 references (e.g. "A1:A4") work as usual in Open as App. However, there is an alternate form of referencing cells called "Structured References". These are of the format "TableName[ColumnName]" or "@[ColumnName]". Instead of using table and column names ("=SUM(DeptSales[Sales Amount])") please user explicit cell references ("=SUM(C2:C7)").
10. External references. If formula in a cell refers to a cell outside of the file, we cannot translate the formula. For example, if a cell refers to a column on a different sheet within Excel, that other sheet is not part of the Excel that is used as the apps' source file, and therefore cannot be referenced properly. Please update the cell in Excel to only reference cells within the file.
11. Array Formulas. Array formulas are not currently supported, and have limited support in Microsoft Excel.
12. Not supported formulas. If calculations in the app are not performed and errors are displayed, it may be because we do not support one of the formulas that should lead to the result. We support over 90% of common formulas. Please check in your source file if somewhere an unsupported formula is used. You will find a list of the formulas we support in the attachment of this article (see end).
13. Optional arguments must be specified. Functions that have a certain number of arguments, some of which are optional, are filled with a default value by Excel. We cannot evaluate this default value. Therefore, always specify all the arguments that a formula requires.
14. Specific formulas. There are a handful of formulas specific to Google Sheets that will not be transferred to Excel. This list of formulas might not be complete:
    • ADD
    • ARRAYFORMULA
    • CONCAT
    • CONTINUE
    • COUNTUNIQUE
    • DETECTLANGUAGE
    • DIVIDE
    • EQ
    • FILTER
    • GOOGLEFINANCE
    • GOOGLETOURNAMENT
    • GOOGLETRANSLATE
    • GT
    • GTE
    • IMAGE
    • IMPORTDATA
    • IMPORTFEED
    • IMPORTHTML
    • IMPORTRANGE
    • IMPORTXML
    • JOIN
    • LT
    • LTE
    • MINUS
    • MULTIPLY
    • POW
    • QUERY
    • SORT
    • SPARKLINE
    • SPLIT
    • UMINUS
    • UNARY_PERCENT
    • UNIQUE
    • UPLUS
15. ROUNDDOWN / ROUNDUP: When using this formula, be sure to specify the rounding information. The sheet also calculates with the formula ROUNDUP(VALUE), but the app needs the full information, so:

    ROUNDUP(<number>, <num_digits>)

    Example:

    ROUNDUP(VALUE, 0)

16. "Wingdings®" This font is not supported. You can, however, use emoji. Try https://emojipedia.org/ Copy the desired emoji into your data source. Emoji doesn't look very nice in Excel, but the native Emoji is always displayed on the devices.