How to create an app from a monthly Excel report

No one likes reading reports from an Excel Spreadsheet or a Google Sheet. However, making people to actually read the reports is often a challenge. With the Open as App Wizard, you'll be able to turn the monotonous monthly report Excel into an app with interactive dashboards and metrics which will help you draw conclusions from.

In this tutorial, you'll learn how to create a monthly report app that is visually engaging, easy to access and runs natively on any device. All based on your Excel spreadsheet! To train you can use the Excel file which we have attached to the article at the bottom.

Steps to the Reporting App based on your Excel

• Preparing the data source
• Building the app

The app creation already starts in your Excel spreadsheet. The cleaner the columns and cells are formatted there, the easier you will turn this file into an app. Format the cells according to their contents! Instead of writing "10%" as text to a cell, format it as a percentage to automatically add a "%" symbol to the 10. The same is true for currencies including symbols, text, date, decimal places, and so on... You want the values in a column to show the $ in front of the number? Format the cell accordingly in your Excel file.

Preparing the data source

With clean formatting, you not only work more effectively but also nearly error-free. By the way, you can make it easier for the wizard to adapt your app to your needs. It is not about colors or fonts in your file, but about the actual definition of the content. You can change the design of each element of your app in the Open as App web portal.

First, start the app creation with a look at the Excel file and make the first improvements, if necessary. You will find detailed information about the source file in the article "Do's and don'ts for your Excel Spreadsheet".

Let's focus on a typical report build with Excel. A typical monthly report looks like this:

Now let’s modify this Excel to convert into an app like this: "Monthy Report App Demo" 

Adding a drop-down field in the Excel

The first step is to create a common cell (highlighted in yellow in the image below) for all the months using a drop-down list.

To do this proceed as follows:

• Select a cell (C5), Click Data -> Data Validation (under Data Tools)
• In the Data Validation pop-up window, select "list" in Allow dropdown list
• In the source field, enter (or select) the cells which contain the months in the main table (G5:R5) and click OK

The drop-down should look like this:

You will find a detailed article about applying data validation to cells in the Excel Help & Training center: "Apply data validation to cells".

Show data from the table depending on the selected month

The next step is to display the data corresponding to the month selected in the drop-down list (previous step). To do this:

• INDEX and MATCH functions are used, basically selecting the parent table (INDEX) and matching the column values between parent and desired tables.
• Syntax: = INDEX (array,MATCH (lookup_value , lookup_array , 0 ), MATCH (lookup_value, lookup_array , 0 ) )

Once the index matching is done for the all the values, corresponding data is displayed for the selected month.

For a comprehensive tutorial on how to use INDEX and MATCH we recommend the following page: "INDEX MATCH Formula".

Building the app

The app creation process starts with selecting the data source and one of the suggested app templates.

Start the app creator and select the desired Excel file from your PC, or from one of the supported 3rd party cloud storage providers like Google Drive, OneDrive / SharePoint or Dropbox.

After selecting the Excel file (or Google Sheet) in the "Choose your data source" step, you will be automatically redirected to the next step in the app creation process, where you can select the template for your app.

Based on the data in the Excel spreadsheet, the Open as App Wizard suggests "Calculation" as the appropriate type for the app. This selection simplifies the rest of the design process for you.

• Select the desired app template.
• Confirm the selection by clicking on the "Next" button at the bottom of the website.

Select relevant input and output cells

After selecting the template for the app, you have to select the cells which you would like to see in the app. Open as App wizard automatically identifies the input and output cells. When selecting the month cell (C5), a drop-down is added automatically which is shown in the preview section. And other values are displayed as output fields.

After you have defined the first content and logic for your app as much as possible, in this step you have the option to select one of the prepared themes for your app. Detailed changes can be made in the next design step.

• Select (optional) a theme and click "Next".

Adding charts to the app

In the second design step, charts in the monthly report excel are added to the app. This can be done very quickly by clicking "Add chart" in the Open as App wizard after creating the app. Open as App wizard automatically detect and displays the charts in the excel. All these charts can be added to the app by clicking on "Import all charts" or can be selected individually (Select for import checkbox) and be added in the desired page of the app.

More details about charts can be found in the article "Add and edit charts".

Congratulations!

You have transformed your monthly report Excel into a native app that is now ready to use! Your app now runs on Android, iOS, and Windows and can be consumed and shared with colleagues, customers, and friends via the Open as App Container app.