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

Microsoft has always used data types in Excel such as text, number, et cetera to help you build complex data models in spreadsheets. But one of the key benefits of having the cloud-powered version of Excel in Microsoft Office 365 is your ability to leverage the global data stored in the Microsoft Cloud in real time.

Amaxra blogged about this productivity-enhancing ability in the past with Map Charts in Excel. Much like that feature, the October 2018 update of Excel in Office 365 enables you to pull real-time and constantly updated financial market and geographic data from the cloud into your spreadsheet. If you’re like me, then you are already thinking of all the ways that this new feature is going to change the way you look at your business data.

Microsoft Office Excel provides a worksheet function, RealTimeData (RTD). This function enables you to call a Component Object Model (COM) Automation server to retrieve data in real time.

Using RealTimeData (RTD) server for Excel

Who uses Excel real-time data?

While Excel has many different functions used by a variety of professionals, the RTD function has specific purposes. Professionals who implement RTD into their Excel workbooks may include:

What is Excel real-time data?

Excel real-time data, or 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 continuously update a workbook. Financial professionals commonly use RTD to input data from the stock market. This helps them track the prices of particular stocks or make assessments about future investments.

Utilizing this function may save developers time and energy, since they can use this technology instead of developing a real-time spreadsheet on their own. Additionally, you may consider placing parameters on the workbooks that access RTD by limiting the number of users with authorization 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.

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 Excels 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 types of data you want to include. For example, if you want to track the 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 use in your daily work. This ensures you understand the process and can equip you to troubleshoot potential issues.

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 that you want to store the real-time updates. For example, imagine you have three columns that represent 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 three different stocks your company invests in. Your table may look like this:

Company-wide stock investments**Price of the stock per unit**[Stock title #1][$XX.XX per unit][Stock title #2]
[$XX.XX per unit]
[Stock title #3][$XX.XX per unit]

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 the needs of your spreadsheet. This prepares the program to populate the selected cells with the correct type of data. 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 of those locations to input into the spreadsheet.

5. Add a new column to receive the data

Before the spreadsheet displays the real-time data, its 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 see 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 youre 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 see a question mark next to the text within a cell. This means that the program needs further clarification on what type of data to input into that cell. If you click on this question mark, its likely that the program prompts you to type in more specific details about the data youre looking for. Then, the program locates the corresponding data based on the updated information.

Tips for using real-time data in Excel

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

Please note that none of the companies mentioned in this article are affiliated with Indeed.


Can Excel pull live data?

You can easily import a table of data from a web page into Excel, and regularly update the table with live data. Open a worksheet in Excel. From the Data menu depending on version of Excel select Get & Transform Data > From Web (eg in Excel 2016) or Get External Data (eg in 2000) or Import External Data (eg in XP).

What is RTD function in Excel?

RTD uses Excel function-like syntax. The real-time data in placed directly in a cell and includes cell references that can be used in formulas, calculcations and charts. RTD keeps track of the state and location of real-time data formulas so that the server doesn’t have to.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *