How To Format and Use Real-Time Data (RTD) in Excel

Updated September 22, 2023

People who work in various industries take advantage of many tools to source, store and organize data relevant to their organization's needs. One of these helpful tools is Microsoft Excel, which has a real-time data function that allows users to access live, online information and use it to populate their spreadsheets. Learning how to use real-time data in your spreadsheets could improve the capabilities and implementation of projects within the company for which you work.

In this article, we describe what Excel real-time data is and what it can do, explain how to set it up and use it and provide a few tips to optimize its functions.

What is Excel real-time data?

Excel real-time data (RTD) is a function in Excel workbooks that allows users to access live data from external sources. This function connects the workbook to the internet to deliver automatic updates of financial and geographical information. RTD may access a variety of online databases to update a workbook continuously. People who work in finance commonly use RTD to input data from the stock market. This helps them track the prices of particular stocks or assess future investments.

Using this function may save developers time and energy since they can use this technology instead of developing a real-time spreadsheet on their own. You may also consider placing parameters on the workbooks that access RTD by limiting the number of authorized users to view the particular data sets. Implementing this function into your Excel workbooks may provide new opportunities to access, analyze and incorporate real-time data into your work.

Who uses Excel real-time data?

While Excel has many functions, the RTD function has specific purposes. People who use RTD in their Excel workbooks may include:

How to make an Excel real-time data spreadsheet

Consider following these steps to create a real-time data spreadsheet within Excel:

1. Create a new table

The first step in using Excel's real-time data feature is to create a new table. This table is where you organize and store all your data. Try to input headings that correspond to the data you want to include. For example, if you want to track the market prices of five different stocks, consider using the shorthand name of those stocks as the headings. You may create a table to practice using the RTD function before you create one for your daily work. This ensures you understand the process and can equip you to troubleshoot and overcome potential challenges.

2. Select specific cells

The cells in the table store the data within the spreadsheet. The next step in implementing the RTD function is to select specific cells in which you want to store the real-time updates. For example, imagine you have three columns representing three different geographical locations. You may choose the cells underneath the headings to store the data. You select these specific cells to apply the RTD function. For example, you may select the first three cells to input pricing data about a company's investments in three different stocks. Your table may appear as:

	A	B
1	Stock (MountainTech)	$23.00
2	Stock (TECHSMYTH)	$12.54
3	Stock (RiverTech)	$15.43

3. Click on the "Data" tab

After selecting the cells you want to put the data into, click on the "Data" tab in the menu. Then, click either "Stocks" or "Geography" depending on your spreadsheet needs. This prepares the program to populate the selected cells with the correct data type. For example, if you wanted to source data based on specific cities, you might title the cells New York, Chicago and Miami. Then, the "Geography" option sources data from each location to input into the spreadsheet.

4. Add a new column to receive the data

Before the spreadsheet displays the real-time data, it's important to add a new column specifically for the datasets. Excel searches for a match between the text within the selected cells and the data it finds online. Then, it populates the cells with the related information. After adding this column, you might notice an icon next to the text. This communicates that the Excel program is actively accessing online data. Once RTD is operating, the server only updates the data when you're not actively working on the spreadsheet. This ensures that you can continue working without the data changing constantly.

If your headings are non-specific, you may notice a question mark next to the text within a cell. This means the program needs further clarification on what type of data to input into that cell. If you click on this question mark, itthe program likely promptsou to type in more specific details about the data you're searching for. Then, the program locates the corresponding data based on the updated information.

Upgrade your resume

Showcase your skills with help from a resume expert

Tips for using real-time data in Excel

Consider implementing these tips when you use real-time data within Excel:

Organize the data neatly. When you create your spreadsheet, try to arrange the data neatly to ensure it's easy to comprehend. It's important to have a clear organizational structure to streamline your analysis, especially as you add more data.
Use a comprehensive spreadsheet title. When you title the spreadsheet, try to use a comprehensive spreadsheet title to communicate the workbook's contents. This can help you stay organized when you revisit the file.
Use specific headings. When designing your spreadsheet, try to use specific headings. This can help you analyze the data quickly and remember the specifics of each column or category within the workbook.
Use a variety of spreadsheets. Another feature of Excel allows you to have multiple spreadsheets within the same workspace. If you want to use the RTD function with multiple categories of data, consider using a variety of spreadsheets to maximize the use of the function.
Experiment with the function. If you want to test the different abilities of the RTD function, consider creating a few different experimental spreadsheets to better understand the scope. This may help you find new ways to implement the function for the company's specific needs.