In this Excel tutorial article, you will learn how to convert an Excel spreadsheet into an online database. What is an online database? It is a database that can be accessed by computers anywhere in the world.
Why Convert Your Excel Spreadsheet To An Online Database?
The first thing you need to know is what an online database is. As mentioned earlier, it is a database that can be accessed by computers anywhere in the world. This means that you can access your data from any computer with internet access. Furthermore, you can share your data with other people very easily. All they need is internet access and they can view your data. This is a great feature for businesses that want to share information with their employees or customers.
Reasons to convert spreadsheets to online databases include:
- Keeping data safe. You can give users access to a read only version of the database online and not worry about making changes in error.
- Creators can limit access to the database by requiring logins to access the site. So only readers cleared for access can visit the webpage.
- Online databases provide a central location for access, no need to physically post the sheet in a break room or share an Excel document link.
- Users can view these databases immediately; there is no need to distribute them, just provide the link to the site.
- Users have 24-hour access to the database.
- Converting your Excel spreadsheet to an online database can save you time and money. Printing out your data or sending it through email is not necessary. All you need to do is access your data from any computer with internet access.
- Once this data is converted, creators can use it for creating charts, pulling it into reports, create other databases.
- Finally, there are many real-world applications for using an online database. Here are just a few:
- Managing customer information
- Managing employee information
- Tracking inventory
- Tracking sales and revenue
How to convert your Excel spreadsheet to a database
Now that you know the reasons to convert your Excel spreadsheet to an online database, let’s discuss how to do so. To convert your Excel spreadsheet to a database, you need to follow these steps:
Getting The Embed Code
Open the Excel workbook you wish to convert. Open sample data file How To Convert Excel To An Online Database.xlsx
Click File, Save as choose OneDrive, click Save.
This saves your document to the OneDrive folder.
In File Explorer, click OneDrive.
The Excel file displays in OneDrive, right-click on the OneDrive-Personal folder and choose, View online.
The OneDrive window opens. Click to select the file, on the toolbar, choose Embed.
A pane opens to the right, which contains the HTML code needed to embed the spreadsheet into a webpage.
Towards the bottom of the pane is the option to how the database will appear on the webpage. Beneath the HTML code box, select Customize how this embedded workbook will appear to others.
The Embed window opens.
Under What to show, choose table 6. The preview area to the right shows the table only; the visible fields are limited to the table only.
Make the following selections on this page.
Under Appearance, deselect Hide gridlines and Include a download link
Including a download link in this scenario could lead an employee to save a version of the Rota, that may be later updated. In which case the version of the Rota they saved will be incorrect.
Under Interaction, select Let people sort and filter. The Sort and Filter option allows team members to filter to their name when viewing the Rota.
Under Interaction, select Always start with this cell selected:
This just ensures the table opens at the beginning of the data and not in the middle of the table. This gives the team members an overview of the table.
Under Dimension, make no changes. The maximum width is 700px. The wider the view the more of the table you can see without scrolling.
The smaller the width the less of the table you can see; scrolling is necessary to view all the cells.
At the bottom of the page, copy the text in the Embed code This code updates as you make the changes in the fields above it. We will use this code to insert the table into a webpage.
Inserting the Embed code
Go to the webpage where you wish to embed the database.
Create a new page and choose the options to add HTML code. The screenshots below will vary depending on the web development platform you are using.
Paste the HTML code into the frame, click In this initial view, you have options to format the view of the database. The scroll bars are not active until you choose to preview the database. Click the pencil option if you need to update the embed code.
Preview/Publish the database
In the preview or publish view we have three options.
- Refresh all data connections – lets the user refresh the database, the latest data appears.
- View full size workbook – opens the workbook in another window. The user can see the full view of the database and has options to save the workbook as another name.
When you view in full screen mode you have options to edit and open the database in the Desktop App.