How to Create a Database That Updates Automatically in Excel

This article illustrates how to create a database in Excel that updates automatically with the help of 4 useful methods. It’s an important feature required while working with dynamic data. When the database depends on data from another source, automatic update according to the source data is very crucial. Let’s dive into the examples to get familiar with the methods.

Table of Contents hide

Download Practice Workbook

4 Useful Methods to Create a Database That Updates Automatically in Excel

1. Extract Data from the Web to Create a Database That Updates Automatically in Excel

2. Create a Database with Pivot Table That Updates Automatically in Excel

3. Auto Refresh a Pivot Table to Update Database Automatically Using VBA Code in Excel

4. Create a Database That Updates Automatically Having Data Referenced from Another Sheet in Excel

Things to Remember

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Database Updates Automatically.xlsm

4 Useful Methods to Create a Database That Updates Automatically in Excel

1. Extract Data from the Web to Create a Database That Updates Automatically in Excel

Task: Extract a 14-day weather forecast for New York, USA from the web and create an Excel database that’ll update automatically.

Problem Analysis: We’ve found several websites that provide weather forecasts online. For this example, we’ll use the forecast for New York, the USA from the https://www.timeanddate.com/weather/usa/new-york/ext website link. We could copy the table in the following screenshot into an Excel worksheet to create a database. But unfortunately, the database is not going to update according to the website data unless we connect it to the source link. If the database is connected to the source link, Excel facilitates us with options to update the database manually and automatically.

Solution: We need to follow the steps below to automatically update our weather forecasting database.

Step 1: Connect to the Web

Go to the Data tab in the Excel Ribbon.
Click on the From Web option from the Get & Transform tab.

In the dialogue box, paste the URL from where we want to extract data for our database and hit OK.

In the Navigator window, we can see the tables found on the website. With the help of the Table View, we can see the data extracted in a table form.
To edit the extracted data table before loading it into the Excel database, click on the Transform Data option.

Step 2: Transform Data

In the Power Query Editor, we can remove unwanted columns. To do that, right-click on the column header and choose the Remove option.

We’ve removed several columns and kept only 6 columns to import into the database.
Now, from the Home tab, click on the Close & Load button.
Choose the Close & Load option.

Step 3: Import Data into Excel Database

In the Import Data window, click on the Existing worksheet option and choose the starting cell to import data, and click OK to execute the command.

We’ve successfully created an Excel database with data that is connected to a website source.

Step 4: Enable Auto Update Feature

Click on the database table.
Go to the Data tab in the Excel Ribbon.
Click on the Refresh All button.
Choose the Connection Properties option.

Now, set the time after which we want to refresh the database automatically in the Refresh every input box and hit OK.

2. Create a Database with Pivot Table That Updates Automatically in Excel

In this illustration, we’ll create a pivot table based on a source dataset. As the pivot table serves as a database in Excel, enabling the auto refresh feature for the pivot table will update our Excel database automatically. The following steps will guide you through the process.

Step 1: Create a Pivot Table

Let’s say we have a dataset that represents sale details for a shop.

To make a pivot table,

Select the whole dataset.
Go to the Insert tab.
Click on the Pivot Table button.
Choose the From Table/Range option.

In the PivotTable from table or range window, click on the New worksheet option and hit OK.

A pivot table database has been created in a new worksheet.

Step 2: Enable Auto Refresh Feature for the Pivot Table to Update the Database Automatically

This method will update the pivot table each time the workbook is opened, not each time a change is made to the dataset. So, it is like partial automation of the pivot table. Let’s follow the steps to enable the auto–refreshing feature for a pivot table:

Right-click any cell of the pivot table to open the context menu.
Choose PivotTable Options from the context menu.

From the PivotTable Options window, go to the Data tab and check the Refresh data when opening the file option.

Finally, hit OK to close the window.

3. Auto Refresh a Pivot Table to Update Database Automatically Using VBA Code in Excel

Task: Using simple VBA code we can automatically update our pivot table when we change any source data. Most importantly it happens immediately unlike the previous method where we need to close and reopen the file again to see the updates.

Solution: To make it happen let’s follow the guide!

Right–click the worksheet name and choose the View Code option.

Code: Insert the following VBA code in the visual basic editor.

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub

Output: This VBA code will run anytime we change cell data in the source file. All the pivot tables related to the source will be updated accordingly and instantly.

To check the above procedure, we’ve created a pivot table in a sheet named pivot_table based on the source data in the sheet named VBA. We changed the quantity of Apple from 50 to 30. The database in the other sheet named pivot_table_VBA also updated accordingly automatically.

Notes: If we don’t want to auto–refresh all the pivot tables in the workbook rather just a specific one, we can use the following code. This code will only update the pivot table in the sheet pivot_table_VBA when we change the data source.

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("pivot_table_VBA  ").PivotTables("PivotTable2").PivotCache.Refresh
End Sub

In this code, pivot-pivot_table_VBA is the sheet name that contains the PivotTable2. We can easily check the name of a worksheet and a pivot table.

4. Create a Database That Updates Automatically Having Data Referenced from Another Sheet in Excel

Task: Create a database table having data coming from another worksheet table. With any change in the source data, the database should update automatically.

Solution: Here is a table in the sheet named source_table with the product name and their individual unit price.

Now, we’ve created a database (in sheet database) that represents sale details. The dataset contains a column named “UnitPrice”. We need to reference each of the data in the UnitPrice column to the table in the source_table sheet. To insert data in the UnitPrice column in the database table-

Type equal sign (=) in cell F3 (unit price of Apple).

Go to the sheet source_table.
Click cell C3 (unit price of Apple).

Press Enter.

Now, using the Fill Handle, fill up the UnitPrice column with reference data from the source table in sheet source_table.

Output: If we change any data in the source table in sheet source_table, the database gets updated automatically.

Things to Remember

Using VBA code in method 3 automates our pivot tables but it loses the undo history. If a change is made, we cannot go back to the previous stage. This is a disadvantage of using a VBA code to update pivot tables automatically.

Conclusion

Now, we know how to create a database in Excel that updates automatically with 4 suitable methods. Hopefully, it would encourage you to use this feature more confidently. Any questions or suggestions don’t forget to put them in the comment box below.