Learn how some Excel tricks can help you in order to develop useful and user-friendly apps for your bussineses.
Borislav Kiprin
Guides
Ever wondered if there’s a better and more convenient way to use all those Excel sheets? Something that would still be as useful as Excel but would make it easier to work with the data, share it with more people, and protect it from breaking? That’s what Open as App’s automatic app creation platform is about. Just follow our Excel app tricks and start turning your data into apps.
To create an app with Open as App, you only need to import an Excel/Google Sheets spreadsheet, and the platform will take care of the rest. No coding skills or knowledge are required with our platform, and creating a straightforward list app, a simple calculation app or a chart dashboard can take as little as just a few minutes, and a couple of clicks.
To create a more sophisticated app, your Excel data should be well-formatted and structured. The more carefully you prepare your data source according to how you imagine your app to work and look like, the easier it is will be to create it afterward.
Yet, when planning this kind of Excel app, you are also given a lot of freedom. Open as App can recognize a large variety of Excel formulas, data formats, lists, and charts, and easily add them to your app. So once you know what the system needs to work with your data, creating apps becomes a simple task.
This guide will provide you with 5 examples of Excel features and formulas, from simple to more difficult ones, which you can use for your Excel app. With each example, we have also listed apps from the Open as App community that illustrate it practically.
Finally, in the last section, you can find several tutorials on how to use these functions when creating your app with our Excel app tricks.
To automatically create an app from your spreadsheet, the Open as App platform analyzes your spreadsheet’s logic.
During this analysis, the platform recognizes the different data types and formats, as well as the various dependencies. Once your spreadsheet is analyzed, you can pick the type of app you want to create and preview how it will look with your data. Depending on the type of app you are creating, during this stage, you can pick the specific cells or columns that you want to include in it. After that, you can proceed with designing and formatting your app according to how you envision it.
While it may seem that app creation happens in Open as App, the main part of creating your app happens in your spreadsheet.
For this reason, it is preferable that you carefully prepare the source file you will use for your app. For example, to make sure that a certain value will show up as a percentage in your app, format the cell in your spreadsheet accordingly, instead of adding the percentage symbol in the cell. The same applies to any other type of value, whether it’s currencies, dates, or something else.
Preparing your spreadsheet properly allows the platform to work its magic and provide with you all of its useful functionalities. Here are some basic spreadsheet features which you should use to create useful and dynamic apps.
While this list of features may seem simple, using them in your spreadsheets will help you significantly when it comes to creating your apps. So here is the first of our Excel app tricks.
Including a chart in your app will make the information in it much easier to understand.
If your spreadsheet has charts, these will be recognized by Open as App automatically, and you will be given the choice to add them to your app.
Apart from including a chart, you can also change the type of chart when creating your app, if you’d like it to appear differently. And even if your spreadsheet does not feature any charts, you can build a basic type of chart through the app creator.
In addition, you can also add interactive charts. Simply add the input fields for this chart to the app. By changing the values in the input fields, the chart will automatically change to show these values.
If you have a chart dashboard in Excel and Google Sheets sharing it as a mobile dashboard app can help to avoid the multiplication of unnecessary versions of the same file. It also helps you to keep control of your data at all times.
All charts are automatically recognized and transferred into your app after which you can edit the colors, types of charts, and more.
By formatting the different fields in your spreadsheet, you will make it easier for the Open as App platform to analyze the structure and suggest the best app type to you. This is the second of our Excel app tricks.
This is particularly valid if you have different types of data format and the app has to display them differently – for example, certain fields should display percentages, whereas others display currencies. Excel data formats for all fields and columns in your spreadsheet are recognized by Open as App. Image links can also be redefined to be displayed as the image if part of a list.
When creating a list app, if you have not defined the data format for a certain column in your sheet, this can be changed in the app creation platform through the “define column type” option. If a cell in your sheet has not been defined or has been incorrectly interpreted, you can also redefine the type of the cell in the editor. This also applies to new fields that you decide to add through the editor.
Clearly defining the type of your data allows you to create apps that make use of a variety of data formats and to pull them together. One such example is the loan calculator app created by the Open as App community.
It features several input fields that allow users to calculate the amount of a loan, including the interest rate, the payment frequency, payment amount, and more. It also includes many different data formats, such as currencies, percentages, and dates.
Any drop-down lists you include in your spreadsheet will also be added to your app automatically. The third excel app trick enables more user-friendly data selection in your apps.
An additional layer of usefulness can be added by making drop-down lists searchable. By typing in the first letters of their query, users will be offered matching results to choose from. This can be achieved through the IF function (see the video tutorial below).
Open as App can also recognize dependable drop-down lists – i.e. drop-down lists that provide different selection options based on a selection in another drop-down list. This option is particularly useful for survey apps where one choice may lead to different outcomes as well as for pure data collection apps.
An example of an app using a drop-down menu is the service quotation app. The purpose of the app is to allow users to easily create a quote for a cleaning service.
The app takes into account the type of cleaning that is offered, the duration of the contract, the type of machine being used, the frequency of visits, etc. The drop-down menus in the app help to filter the information and allow the user to easily pick the desired option and issue one or several quotes within seconds.
To learn how to create simple, searchable, or dependent drop-down lists in Excel, we found the following tutorials helpful:
Number four of our excel app tricks is a little bit more complicated. Using the INDEX and MATCH formulas, along with a drop-down list, allows you to select and display results or information by month, year, region, or any other filtering criteria. This way a huge array of information is presented in a user-friendly way and can be handled easily even on mobile screens.
This is particularly useful for reporting apps where comparisons between different periods are often required. It can also be helpful for product comparison tools that can sort information according to different stats.
By also including charts, you can then create an easy-to-use dashboard app that can filter and parse information according to various criteria and visualize it right away. Our dashboard app mentioned earlier is an example of such an app.
If you’ve never used the INDEX and MATCH formulas in Excel and don’t know how to implement them, see this tutorial.
One common way in which INDEX and MATCH formulas are used with Open as App is in reporting apps. See, for example, the dashboard reporting app created by the Open as App community.
This app is specifically used as a reporting tool for monthly company performance. It is based on the daily performance data collected in Excel sheets that is aggregated every month and displayed in charts and tables. Such an app allows decision-makers to quickly review the data and focus on their next moves.
Want to know more about how to create a dashboard app? See our guide on creating a financial plan dashboard app!
If you don’t know how to create charts in Excel, see this simple chart tutorial or a more complicated dashboard tutorial.
There are various ways in which conditional formatting in Excel can be used in apps. This is what excel app trick number 5 is all about. You can, for example, use conditional formatting of the background color of a certain cell, and have it change depending on the values that are displayed in that cell.
This feature can also be seen in our dashboard reporting app where the direction of the arrow in the box, and the color of the field signal revenue growth or loss.
This same formula can also be used to show or hide the content of a list or other elements, based on true or false values. That way, if data of a certain type needs to be displayed, the app will filter it according to the predetermined values and display only the relevant ones. This logic can also be extended to showing or hiding elements based on user-specific data.
For example, if you’d like certain fields to appear only for certain types of user roles in your app, you can apply conditional formatting. The app will then show or hide the formatted fields, based on the role of the user who is using the app.
Alternatively, this type of formatting may allow certain users only to view a given field, whereas it may grant others the right to view and edit that field.
One app from the Open as App community that makes use of the conditional formatting functionality is the printer dealers app. This app serves as a digital product catalog.
As you will see, depending on the type of printer dealer you select, you will be offered different models of printers. Deselecting a type of dealer in the first tab will hide the products associated with that dealer in the second tab.
If you’re curious to try creating an app with one of these Excel functions and formulas, we have a number of tutorials and Excel sheets that you can use for the purpose.
Our example spreadsheets are available at the bottom of each of the tutorial pages. Download them, study the tutorial, and try to replicate the process through our app creation wizard.
See the sections below for instructions.
The following tutorial will take you through the process of creating a list app based on a dummy stock inventory list.
This tutorial contains both information on how to format a list in Excel properly as well as what you need to do once you upload the sheet to Open as App. It is an exercise in formatting your sheet properly as well as in how you can format different elements and cells in Open as App too.
This example will take you through the process of creating an app that features a searchable drop-down menu.
The tutorial briefly explains how a searchable drop-down list is created in Excel as well as which fields you must select once you upload the spreadsheet to Open as App.
The following tutorial will take you through the process of creating an app from a monthly Excel report.
It will take you through the steps of using the INDEX and MATCH formulas which will allow you to filter information in the app, based on a period. The tutorial also features information on how to add charts to your app to make it more user-friendly.
Here are two tutorials on how you can create an app that features conditional formatting.
The first tutorial will show you how to use conditional formatting of the background color. As you will see, even if you have not formatted the cells in your spreadsheet accordingly, you can add this type of formatting in the Open as App platform itself.
The second tutorial demonstrates how you can format list contents to appear or disappear, based on certain conditions. In this particular example, certain contents will appear or disappear because of the values that have been ascribed to them in the spreadsheet.
These are some of the ways in which you can use these 5 basic Excel tricks to create a great and functional app.
These excel app tricks can make a huge difference in how your app operates and whether it is useful to users or not. Moreover, it will make the creation of your app a simpler and faster process. With a properly prepared spreadsheet, an app can be created within a matter of a few minutes.
Open as App recognizes many other types of functions and formulas. If you want to know more about how to implement a certain Excel formula, make sure to check our support pages or submit a request to get in touch with one of our tech experts!
Alexandra Müller
Alexandra Müller
Alexandra Müller
Alexandra Müller
Borislav Kiprin
Alexandra Müller
Alexandra Müller
Alexandra Müller
Alexandra Müller
Open as App GmbH
Amalienstraße 62, 80799 Munich
info@openasapp.com | www.openasapp.com | +49 (89) 2620417-00
@2023 Open as App GmbH
