Are you planning to go from Excel to web app?
Here’s a quick and easy tutorial to explain how you can convert an Excel spreadsheet into a web application. We will be using Five’s free download to build our prototype application – so before we get started make sure to sign up and install Five for free.
This beginner’s guide does not require a background in coding.
Don’t Feel Like Reading?
Watch the YouTube Video and Learn How to Use Five’s Free Download to Convert Your Excel Spreadsheet Into a Web App.
This blog post is part 1 of a 5-part series on converting Excel spreadsheets to web apps. To view the other parts, follow the links here:
Before we start building our web app, it is important that our Excel spreadsheet stores data in the correct format.
To do so, make sure that your spreadsheet’s first row contains descriptive names of the data stored in each column.
For example, let’s say your spreadsheet contains information on Products, Prices, and Quantities.
Here’s what your Excel spreadsheet should look like:
Unfortunately, the real world is rarely as well-structured as this spreadsheet. So here are a few points to note:
1. Make sure that your header row (row 1) only contains descriptive names for what is stored in each column.
In our case, our header row contains Product, Price, and Quantity. Pick descriptive names for your columns and avoid adding unnecessary additional content to them.
2. Do not repeat the same information in multiple columns.
For example, it is not advisable to have one column for “Products (non-perishable)” and another column for “Products (perishable)“. Instead, have one column for “Product“, and another column that classifies them as “Perishable” or “Non-Perishable”.
3. Make sure that each cell only contains one piece of data.
For example, in our case, it might be tempting to add discount information to our pricing, such as “5.99 (discounted to 4.99)“. Don’t. If you do so, this data point is no longer usable in a calculation. Always make sure that every column only has one type of data such as text, numbers, options, or currencies.
4. Even if you have multiple values of the same item, do not put all of them into the same cell.
For example, sometimes we might have more than one email address for a customer. How should we deal with this scenario in our spreadsheet? The correct way to set this up is to have multiple “Email” columns, such as “Primary Email“, “Secondary Email“, or “Work Email” and “Personal Email”. Do not store multiple emails in one cell.
To go from Excel to web app without too much trouble, it is advisable to spend some time looking at your existing data and make sure it’s clean. As the saying goes: a stitch in time saves nine!
Here are our three favorite Excel functions for clean data:
Later in this article, we will introduce you to a few helpful features that will make it easier for you to collect clean data in your web app.
CHECKLIST: Does your Excel spreadsheet fulfill the criteria above?
If so, it’s time to move to Step 2 to convert your Excel to a web app: setting up your database.
Thanks to the work we just did in Step 1, going from Excel to a web app will be a lot easier now that we’re starting with Step 2: setting up your database.
Having a database as your application backend is a critical step in converting Excel spreadsheets to web apps. We recommend MySQL, the world’s most popular open-source database.
To create our database, we will use Five, which has a handy and easy-to-use visual database designer. Using Five’s database designer, we can create a database table entirely in point-and-click. Make sure to have Five installed and running on your computer to follow along.
After installing Five, launch it on your computer. You’ll be greeted by a screen that looks like this:
Here’s what your screen should now look like. If “Excel to Web App” is your first application, you will not see any other applications in the list on the left:
It’s time to build your database and import your data.
To start click the blue Manage button on the top right of the screen near the Five logo. Check out the image above to see where the Manage button is located.
Now click on Data and then on Table Wizard.
Let’s go ahead and create our first database table.
Now let’s import our data from our Excel spreadsheet.
Go to Data > Tables and click on the Import CSV into Table icon to get started. It’s located to the left of the yellow plus icon with a small arrow pointing down into a box. See the image below to locate it:
To import your data make sure it is stored in a CSV file. If you’d like to use the data provided above, download our CSV file here.
Congratulations! You have now created a database table in a MySQL database and have populated it with data!
In the third and last step, we will add a form to our application and launch and preview it.
We have already created our application backend: a relational database with a single table that stores information about our products, prices, and quantities. But a database is not a web app. Our database needs a front-end or graphical user interface so that users can interact with the data.
So let’s move on to step 3 of converting our Excel to a web app.
Five makes it easy to build a form for your end-users. To get started, click on Visual and then on Form Wizard.
There’s only one thing you need to do in the form wizard. Select Inventory as your Main Table.
Save by clicking the Tick mark.
It is time to preview our application!
Click on the Run icon at the top right corner. This will launch your application in a new window.
Here’s what your application looks like:
Note that Five gives you a fully auto-generated front-end, including the form that you have just developed using Five’s Form Wizard, as well as:
Well done! You have successfully built and deployed your first web application in Five. Instead of managing your data in an Excel spreadsheet, you can now use a modern web application to store, retrieve, and process your data. And it took only three steps to go from Excel to web app!
Five’s prebuilt user interface has automatically created an Inventories menu item for you. You can also see your Products in the list to the left of the screen. Last, by selecting one of the products, you can see its details, and make changes to them as necessary. Five also comes with a pre-built search and filtering feature.
What else can you develop in Five? Plenty of things! Check out this screenshot of a finished application to get a sense of what’s possible:
In short, we have barely scratched the surface of what’s possible in Five! For further inspiration on what can be built in Five, visit our Use Cases or keep following our tutorial by adding a calculation to your application.
Five lets you develop your application locally free of charge. Simply use our free download and build your application until it’s ready for your end-users.
To deploy your application, sign up for one of our paid plans, starting from US$29.99 per month and application. Your plan includes unlimited end-users and provides you with a custom URL to access the application online.