How to Create a Database That Updates Automatically in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create a Database That Updates Automatically in Excel


How 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: For this example, we’ll use the forecast for New York, the USA. We could copy the table in the following screenshot into an Excel worksheet to create a database. 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 provides us with options to update the database manually and automatically.

Create Database in Excel that Updates 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 Data 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.

Create Database in Excel that Updates Automatically

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.

Create Database in Excel that Updates Automatically

  • 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.

Create Database in Excel that Updates Automatically

Step 3: Import Data into Excel Database

  • In the Import Data window, click on the Existing worksheet option, 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.

Create Database in Excel that Updates Automatically

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.

Create Database in Excel that Updates Automatically

  • 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.

Create Database in Excel that Updates Automatically

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.

Create Database in Excel that Updates Automatically

  • 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.

Create Database in Excel that Updates Automatically

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.

Create Database in Excel that Updates Automatically

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

Create Database in Excel that Updates Automatically

  • Finally, hit OK to close the window.

Read More: How to Create a Database with Form in Excel


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.

Create Database in Excel that Updates Automatically

Notes: If we don’t want to auto-refresh all the pivot tables in the workbook rather than 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.

Create Database in Excel that Updates Automatically

Read More: How to Create a Simple Database in Excel VBA


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).

Create Database in Excel that Updates Automatically

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

Create Database in Excel that Updates Automatically

  • Press Enter.

Create Database in Excel that Updates Automatically

  • 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.

Create Database in Excel that Updates Automatically

Read More: How to Maintain Customer Database in Excel


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.


Download Practice Workbook

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


Conclusion

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


Related Articles

<< Go Back To Database in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Arafat Siddique
Al Arafat Siddique

Al Arafat Siddique, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has worked on the ExcelDemy project for two years. He has written over 85+ articles for ExcelDemy. Currently, he is working as a software developer. He is leading a team of six members to develop Microsoft Office Add-ins, extending Office applications to interact with office documents. Other assigned projects to his team include creating AI-based products and online conversion tools using the latest... Read Full Bio

2 Comments
  1. best of luck brother JaZA KALA Kahir you are doing a great Job and May allah bless you Parents as well

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo