Weather Compilation

How to Get Live Weather Data in Excel Using Power Query and Geography Data Types (Includes a Summary of Posts 1-3)

Have you ever wanted to turn Excel into your very own weather dashboard? With a few powerful Excel features, like the Geography Data Type, the WEBSERVICE() and FILTERXML() functions, and Power Query, we can feed live weather data directly into the workbook. From just typing a city and state, we’ll get current forecasts, column-organized daily summaries, and even mini sparklines. In this comprehensive tutorial, we’ll walk step-by-step through the entire process with an emphasis on practicality, clarity, and functionality.

Whether you’re tracking weather for business logistics, personal travel planning, or just learning Excel integration skills, this guide will walk us through a fully automated live weather utility using nothing but Excel and its built-in tools. Let’s get started!

Video

Step-by-Step: Create a Live Weather Forecast in Excel

Step 1: Get Latitude and Longitude Using Geography Data Type

To begin, we’ll convert a city and state into coordinates, so Excel knows where to look on the map.

1. In a cell (say A2), type Reno, Nevada.
2. Go to the Data tab.
3. Under Data Types, choose Geography. Excel will convert the entry into a linked record.
4. Click the small card icon to view rich data—latitude, longitude, population, and more.
5. Use the Insert Data button to pull Latitude and Longitude into adjacent columns.

Pro Tip: If the city name is ambiguous (like “New York”), Excel may prompt you to confirm the correct location using the data selector pane.

Step 2: Use a Web API to Get Weather Forecast Links

Once we have coordinates, we’ll access a free weather API provided by weather.gov.

1. Use Excel’s CONCAT() function to create a URL combining the base API with latitude and longitude:
   

   =CONCAT("https://api.weather.gov/points/", B2, ",", C2)

2. Assign a name to this cell (i.e., WeatherURL) by entering it into the name box.
3. Go to Data > From Table/Range, convert the URL into a Power Query connection, remove column headers if needed.
4. Right-click the URL in Power Query, and select Drill Down to make it accessible as a single value in queries.

Step 3: Extract the Forecast URL Using Power Query

1. In Power Query, use Data > From Web and paste the WeatherURL.
2. Open the JSON structure, find and select the properties record.
3. Drill into the forecast field—this is the endpoint for your detailed forecast.
4. Save it as a connection-only query named ForecastURL.

Step 4: Load and Transform the Forecast Data

1. Start a new Power Query, go to Data > From Web, and use ForecastURL as the dynamic fetch link.
2. Open the JSON response, drill into properties > periods, and convert to table.
3. Expand the record to columns. Pick key fields: StartTime, Temperature, IsDaytime.
4. Build a custom column AM/PM using a conditional check on IsDaytime:
   

    = if [IsDaytime] = true then "AM" else "PM"

5. Delete unnecessary columns and clean up the StartTime to just the date using the Transform > Date > Parse.

Step 5: Pivot the Forecast into Columns for Daily View

1. Select StartTime and pivot it to columns.
2. Use AM/PM as your row label and Temperature as the values.
3. In Pivot settings, select Don’t Aggregate to preserve values in place.
4. Load the final table into Excel.

Step 6: Add Sparkline Charts for Visual Weather Trends

1. In the final table, add a row-level Sparkline in a new column.
2. Go to Insert > Sparklines > Line, and select the AM/PM temperature cells.
3. Optional: Enable High Point and Low Point highlights for visibility.

The result? A self-refreshing weather dashboard where you can just type a new city, click Refresh All, and get an up-to-date forecast with mini line charts. Weather data—on tap, inside Excel.

Try It Yourself

Download the sample file we used in this tutorial and customize it for your own use!

Summary

• Used Excel’s geography data type to convert city/state into coordinates.
• Constructed a dynamic API URL using CONCAT().
• Used Power Query and JSON parsing to fetch the weather forecast.
• Pivoted and transformed the data into a readable format.
• Visualized forecast using Sparkline charts.

Feeling inspired? This is just the beginning. With Power Query and a bit of Excel know-how, we can bring all sorts of live data into our workbooks.

Frequently Asked Questions (FAQs)

Can I use this setup without the Geography data type?

Yes! If your Excel version doesn’t support geography types, you can use a free API to fetch coordinates using city/state via web services.

Do I need to use JSON or XML?

Either can be used depending on the API’s options. JSON is often easier when working inside Power Query.

What if I get a Power Query firewall error?

Go to File > Options & Settings > Query Options > Privacy and select “Ignore” for this workbook.

How do I refresh the data?

Just go to Data > Refresh All. All queries reconnect and update live weather data.

Can this be used in Excel for Mac?

Power Query is supported in newer versions of Excel for Mac. Some functions like GETPIVOTDATA or FILTERXML() may vary by platform.

How often is the forecast updated?

Forecast data comes from the National Weather Service and typically updates every few hours.

Can I add charts other than sparklines?

Absolutely. Use any chart type in Excel by referencing your forecast table range.

How do I style the sparkline colors?

Select the sparkline, go to the Design tab, and choose marker colors (High, Low, First, etc).

Can I pull forecasts for multiple cities?

Yes. Create a table of cities and iterate with Power Query, or build a dropdown for interactive selection.

What if the forecast URL returns an error?

Ensure your latitude and longitude values are valid, and wrap formulas with IFERROR() to capture fallback values like “Not Available”.