Excel Weather Forecast: How to Pull Live Data

How-to basics · Updated November 22, 2026

Yes, you can get a live weather forecast directly inside Excel by using the built-in Stocks and Geography data types (for current conditions) or by connecting to a free weather API through Power Query (for forecasts). The fastest built-in method: type a city name in a cell, select it, go to Data > Geography, then insert the Temperature or Weather field from the cell’s data card. For a multi-day forecast, you’ll need Power Query and a free API like Open-Meteo, which requires no API key.

What the Built-In Geography Data Type Can Do

Excel’s Geography data type (available in Microsoft 365 and Excel 2021 for Windows and Mac) links a cell to a real-world place. When you type a city name and convert it, Excel fetches structured data from the cloud. For weather, the available fields are limited but useful:

  • Temperature – current temperature in the city’s local units (usually Fahrenheit in the US, Celsius elsewhere).
  • Weather – a text description like “Partly cloudy” or “Light rain”.
  • Wind Speed – current wind speed in miles per hour or kilometers per hour.
  • Humidity – relative humidity percentage.
  • Visibility – distance you can see, in miles or kilometers.

These fields update when you refresh the data (right-click the cell and choose Refresh). They reflect the current moment, not a forecast for tomorrow or next week.

How to Use the Geography Data Type (Step by Step)

  1. In a cell, type a city name, e.g., London. Make sure it’s a single cell, not part of a table.
  2. Select the cell, then go to Data > Geography (in the Data Types group).
  3. Wait a few seconds – a small map icon appears next to the city name.
  4. Click the Insert Data button (the small grid icon) that appears to the right of the cell.
  5. Choose fields like Temperature or Weather. Excel will add them as adjacent cells.
  6. To refresh, right-click any of these cells and select Refresh.

If the city name is ambiguous (e.g., “Paris” could be Paris, France or Paris, Texas), Excel shows a data selector – pick the correct one. On Mac, the path is the same: Data > Geography. Excel for the web also supports this, but you must have a Microsoft 365 subscription.

Getting a Real Forecast (Multiple Days) with Power Query

The built-in Geography type gives you current conditions only. For a 7-day forecast, you need to pull data from a weather API. A reliable, no-key option is Open-Meteo. Here’s how to do it with Power Query in Excel for Microsoft 365 (works on Windows and Mac):

  1. Go to Data > Get Data > From Other Sources > From Web.
  2. In the URL box, paste an API URL like:
    https://api.open-meteo.com/v1/forecast?latitude=51.5074&longitude=-0.1278&daily=temperature_2m_max,temperature_2m_min&timezone=GMT
  3. Click OK. Power Query will connect and show a preview.
  4. In the Navigator, select the Table (usually named daily) and click Transform Data.
  5. In the Power Query editor, click To Table if needed, then expand the columns by clicking the expand icon on the daily column.
  6. After cleaning up (remove extra columns, rename headers), click Close & Load to bring the forecast into a worksheet.

This example returns daily maximum and minimum temperatures for the next 7 days. You can change the latitude and longitude to any location. Open-Meteo is free for non-commercial use and requires no API key, but you must respect their usage policy (no heavy automated requests).

How to Choose Between the Two Methods

Your choice depends on what you need:

  • Current conditions only – Use the Geography data type. It’s simple, no formulas, and refreshes on demand.
  • Forecast for the next 7 days – Use Power Query with Open-Meteo. It gives you a table of daily highs/lows, and you can also get hourly data if you change the parameters.
  • Historical weather – Neither built-in method provides past data. For that, you’d need a paid API like Weatherbit or Visual Crossing, which require an API key and often have limits.

If you need the forecast in a cell for use in formulas, the Geography data type is easier because the values are live and can be referenced (e.g., =A2.Temperature). Power Query results are static until you refresh, but you can still reference them.

Common Problems and How to Fix Them

Here are typical issues you might run into:

  • Geography data type not showing – Make sure you have a Microsoft 365 subscription or Excel 2021. The feature isn’t in older versions like Excel 2016 or 2019.
  • City not recognized – Use the full name with country, e.g., “Cambridge, MA” instead of just “Cambridge”. Or use the data selector to pick the right place.
  • Power Query URL fails – Check that the URL is correct and that you’re not behind a firewall that blocks web requests. Also, ensure the API is up (Open-Meteo is generally reliable).
  • Refresh errors – If you get a “Data type refresh failed” error, try converting the cell to text and back, or close and reopen the workbook.
  • Time zone issues – In Open-Meteo, specify timezone=auto to get local times, or use a specific IANA timezone like America/New_York.

Costs, Limits, and Safety Considerations

The Geography data type is free with your Microsoft 365 subscription. Open-Meteo is free for non-commercial use, but they ask that you not exceed 10,000 requests per day and that you cache results if you’re building a serious app. For commercial use, you should check their license terms. Other APIs like WeatherAPI.com or OpenWeatherMap offer free tiers (e.g., 1,000 calls/day) but require an API key and have usage limits.

When using Power Query, be mindful of sensitive data: don’t embed API keys in shared workbooks. If you use a paid API, store the key in a secure location and reference it via a parameter, not hardcoded in the URL. Also, remember that weather data is not guaranteed – it’s a forecast, so always verify for critical decisions like travel or outdoor events.

Alternatives to Excel for Weather Data

If Excel isn’t your only tool, consider these options:

  • Google Sheets – Has a built-in =GOOGLEFINANCE() for stocks but not weather. You can use the IMPORTDATA function to pull from Open-Meteo: =IMPORTDATA("https://api.open-meteo.com/v1/forecast?latitude=...").
  • Power BI – Can connect to the same APIs and create interactive dashboards, but it’s heavier than Excel.
  • Python in Excel – If you have Microsoft 365, you can use Python to call weather APIs directly, but that’s more advanced.

Frequently Asked Questions

Can I get a 10-day forecast in Excel? Yes, Open-Meteo supports up to 16 days of daily forecasts. Just set forecast_days=10 in the API URL. The Geography data type does not provide forecasts.

Does the Geography data type work for any city in the world? It works for most populated places, but coverage isn’t perfect. For very small towns or non-English names, you may need to use the data selector or a more specific name.

Why is my temperature showing as a number like 15.5? That’s likely Celsius. To change to Fahrenheit, you can add a formula to convert: =A2*9/5+32 (assuming A2 is the Celsius value). Or, in the Geography data type, you can change the cell’s number format to show a degree symbol, but the unit is determined by your Windows region settings.

Can I make the forecast update automatically every hour? Yes, but not natively in Excel. You can use a VBA macro with a timer to refresh the query, but that’s complex. Alternatively, use Power Automate to refresh the workbook on a schedule. For most users, manual refresh is fine.

Is there a way to get weather alerts (like storm warnings) in Excel? The built-in Geography type doesn’t include alerts. You’d need to use an API that provides alerts, such as the National Weather Service API (for the US), and parse it with Power Query. That’s more advanced but doable.

What if I don’t have Microsoft 365? If you have Excel 2019 or earlier, the Geography data type isn’t available. Your best bet is to use Power Query with Open-Meteo, which works in Excel 2016 and later. Alternatively, use Google Sheets as a free workaround.

Remember to always refresh your data before making decisions based on it, and double-check the source if the numbers seem off.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.