How to Get Real-Time Data in Excel

Constantly exporting fresh data and copying and pasting it into your spreadsheet is one of the most tedious parts of manual reporting. Thankfully, Excel has powerful features that can pull live data directly from the web, keeping your dashboards and reports updated automatically. This article will show you three effective methods to connect Excel to real-time data sources.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Bother with Real-Time Data in Excel?

Before jumping into the "how," let’s briefly touch on the "why." Connecting live data to Excel transforms your static spreadsheet into a dynamic dashboard. Instead of your report being outdated the moment you save it, it becomes a living document that reflects current realities.

This is invaluable for:

• Financial Tracking: Monitor stock prices, cryptocurrency values, or currency exchange rates without manual lookups.
• Project Management: Pull task statuses, progress trackers, or resource availability directly from a web-based tool.
• Sales & Marketing Dashboards: Keep an eye on KPIs like website traffic or e-commerce sales figures that are publicly visible on a web page.
• Competitive Analysis: Track public data like competitor pricing or online reviews as they change.

Automating data updates not only saves you countless hours of mind-numbing work but also leads to faster, more informed decision-making. You're always working with the freshest information available.

Method 1: Connect to Web Data with Power Query

Power Query is Excel’s built-in data connection and transformation tool, and it's the most flexible and powerful way to pull data from a website. If the data you need exists in a table on a webpage, you can likely link it to Excel. This works great for information like currency exchange rates, lists of country populations, or sports statistics.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide to Using Power Query:

Let's use a relatable example: pulling a table of cryptocurrency prices from a public finance website.

1. Find Your Data Source URL First, find the webpage with the data table you want to track. For this example, we’ll use a page showing live market caps. The key here is to find data that's structured in an HTML table, which is very common on financial or reference websites like Wikipedia.

2. Connect Excel to the Web Page

• Open a new Excel workbook.
• Go to the Data tab on the Ribbon.
• In the "Get & Transform Data" group, click From Web.

3. Enter the URL A dialog box will pop up. Paste the URL of the webpage you found in step 1 and click OK.

4. Select Your Data Table Excel will analyze the page and show you a "Navigator" window. This window lists all the data tables it found on the page. Click through the table names on the left (e.g., Table 0, Table 1) until you see the data you want in the preview pane on the right.

5. Load the Data into Excel Once you’ve found the correct table, click the Load button at the bottom. The data will be imported into a new worksheet in a formatted Excel table.

Setting Up Automatic Refresh

Connecting the data is just the first step. The real magic happens when you tell Excel to update it automatically.

• Select any cell within your newly created data table.
• Go to the Query tab (this tab appears when you select a Power Query table).
• Click Properties. (Alternatively, right-click any cell in the table, go to Table > External Data Properties).
• In the "Query Properties" dialog box, you'll find the refresh controls. Check the box for Refresh every and set your desired interval, for example, 60 minutes.
• Pro Tip: Be considerate with your refresh rate. Hitting a website with requests every minute can be aggressive and may not be necessary. Only refresh as often as you genuinely need the new data.
• You can also check Refresh data when opening the file to ensure you have the latest data every time you start work.

That's it! Your Excel sheet is now connected to a live web source and will update itself based on your settings.

Method 2: Use an API with Power Query

Sometimes, data isn't available in a clean HTML table. For more direct and robust access, many services offer a free API (Application Programming Interface), which is a structured way to fetch data. Don't let the term intimidate you - it’s often just a special URL that returns raw data.

Let’s try this with an international time API to fetch the current time for different time zones.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide for an API Connection:

1. Find a Free API We'll use the World Time API, a free service that doesn’t require an API key. For instance, the URL http://worldtimeapi.org/api/timezone/Europe/London returns detailed information about the time in London.

2. Use the "From Web" Connector Again

• In Excel, go to Data > From Web.
• Enter the API URL: http://worldtimeapi.org/api/timezone/Europe/London.
• Click OK.

This time, Power Query sees the response is in a format called JSON, not a simple table. It will open the Power Query Editor automatically.

3. Transform the JSON Data The screen might look a bit intimidating at first, but it's simpler than it appears.

• Power Query will show the JSON data as a series of records. At the top, click the Into Table button. Click OK on the next dialog box.
• You now have a two-column table. Click the expand button (two opposite-facing arrows) in the header of the second column ("Value").
• Uncheck "Use original column name as prefix" and click OK.
• Voila! All the data points from the API are now separate columns. You can see fields like datetime, day_of_week, timezone, etc.
• Keep the columns you want and right-click to remove the others for a cleaner view.
• When you're happy, click Close & Load on the top-left of the Power Query Editor.

Just like with the previous method, you can set an automatic refresh schedule under the Query > Properties menu to keep your data current.

Method 3: Leverage Excel’s Built-in Data Types

If you're using a Microsoft 365 subscription, Excel has a fantastically simple feature called "Data Types." This feature can automatically recognize terms for stocks, currencies, and geography and enrich your spreadsheet with near real-time information.

How to Use the Stocks Data Type:

This is by far the easiest way to track financial markets.

1. List Your Companies or Tickers In a column, type the names of a few companies or their stock ticker symbols (e.g., MSFT, AAPL, Tesla, Netflix).

2. Convert to the "Stocks" Data Type

• Select the cells you just typed in.
• Go to the Data tab.
• In the "Data Types" group, click Stocks.

Excel will try to match your text with an exchange-listed company. If it's successful, a small "building" icon will appear next to the name. If it's ambiguous (e.g., "Ford"), a "Data Selector" pane will open on the right, allowing you to choose the correct one.

3. Add Live Data Columns Once your cells are converted, select them all. A small icon with a plus sign (the "Insert Data" button) will appear to the right of your selection. Click it to see a dropdown list of available data fields you can add, such as:

• Price
• Change (%)
• Market cap
• 52-week high/low
• P/E ratio

Select any field, and Excel will instantly add a new column with that data for all your selected stocks. You can repeat this to create multiple columns of live financial data very quickly.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Refreshing "Data Types"

This data isn't streaming second-by-second but is updated frequently from its source. To refresh it manually, go to the Data tab and click Refresh All. An update will happen automatically every few minutes by default as well, but this button ensures you have the absolute latest pull.

Final Thoughts

Connecting your spreadsheets to live, real-time data takes Excel reporting to the next level. Whether you're using the simple but powerful Data Types, or the more flexible Power Query method for web tables and APIs, you can save massive amounts of time and make quicker, data-driven decisions. This transforms Excel from a simple static ledger into a responsive and dynamic dashboard.

While these methods are great for public web data or data you can host on a webpage, pulling a unified view of your business data from tools like Google Analytics, Shopify, HubSpot, or Salesforce remains complicated. Often, it involves a messy process of exporting multiple CSV files and stitching them together. We built Graphed to solve this specific challenge. By securely connecting your marketing and sales platforms in a few clicks, you can instantly ask questions in plain English - like "create a dashboard of my ad spend vs. revenue this month" - and get a live, automated dashboard without ever touching a spreadsheet again.