How to Import Weather Data into Excel Hourly

Automating hourly weather data into your Excel spreadsheet might sound complicated, but it's a game-changer for anyone whose work is affected by the elements. Instead of manually checking and copying forecasts, you can build a live, updating report directly inside your workbook. This article will walk you through, step-by-step, how to use Excel’s built-in tools to pull live hourly weather data automatically.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Track Hourly Weather Data in Excel?

Connecting real-time weather data to your spreadsheets unlocks smarter, proactive decision-making. Manually checking a weather app is one thing, but integrating the data directly lets you analyze patterns and automate triggers. Imagine the possibilities for different industries:

• Event Planners: For outdoor weddings, festivals, or corporate events, knowing the hourly forecast for rain, wind, and temperature helps with scheduling, staffing, and having backup plans ready to go.
• Construction Managers: Track conditions like high winds that could affect crane operations, heavy rain that can delay concrete pours, or extreme temperatures that impact worker safety.
• Retail and Hospitality: Correlate foot traffic or patio seating availability with weather conditions. A sunny Saturday forecast might mean staffing up, while a rainy week could cue a special delivery promotion.
• Logistics and Transportation: Plan routes and anticipate delays based on forecasts for snow, ice, or heavy rain, optimizing delivery schedules and ensuring driver safety.
• Agriculture: Farmers can monitor hourly temperature, humidity, and rainfall predictions to optimize irrigation schedules, planting times, and pest control measures for better crop yields.

By bringing this data into a tool you already use, you can stop reacting to the weather and start making data-informed plans based on what's coming.

Finding a Reliable Weather Data Source (API)

To get data into Excel automatically, we need to connect to a service that provides it. The most common way to do this is through an Application Programming Interface, or API. Think of an API as a waiter in a restaurant. You don't go into the kitchen yourself, you give your order to the waiter (the API), who gets the food (the data) from the kitchen (the data source) and brings it back to you.

There are many weather API providers, but a great one to start with is OpenWeatherMap. They offer a generous free plan that gives you access to a 5-day, 3-hour forecast, which is more than enough for our needs. Here’s how to get set up:

1. Sign Up for a Free Account: Go to the OpenWeatherMap website and create a free account.
2. Get Your API Key: Once registered, navigate to your account dashboard and find the "API keys" tab. Your default key will be generated for you. Copy this long string of letters and numbers - it's your unique password for accessing the data.
3. Understand the API Call: An API call is just a specially formatted URL that tells the service what data you want. For OpenWeatherMap's 5-day/3-hour forecast, the URL structure looks like this:

https://api.openweathermap.org/data/2.5/forecast?lat={your_latitude}&lon={your_longitude}&appid={your_api_key}

To make this work, you just need to replace the placeholders with your actual location coordinates and your new API key.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step: Importing Weather Data with Power Query

Excel’s Power Query is a powerful data transformation tool hiding in plain sight. It’s perfect for connecting to APIs and shaping the raw data into a clean, usable table. Let's build our weather dashboard.

Step 1: Construct Your Full API URL

First, you need the latitude and longitude for your desired location. An easy way to find this is to go to Google Maps, right-click on a location, and the coordinates will be the first item in the menu. For example, the coordinates for the Empire State Building are roughly 40.7484 (Latitude) and -73.9857 (Longitude).

Now, build your URL. If your API key was 1234567890abcdefghijklmnopqrstuv, your URL would be:

https://api.openweathermap.org/data/2.5/forecast?lat=40.7484&lon=-73.9857&appid=1234567890abcdefghijklmnopqrstuv

Copy this URL. Paste it into your browser to make sure it returns a bunch of text (this is called JSON) - if it works, you’re ready for Excel.

Step 2: Open Power Query in Excel

Open a blank Excel workbook. Go to the Data tab on the Ribbon. From there, select Get Data > From Other Sources > From Web.

A dialog box will appear. Paste your complete API URL into the box and click OK.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 3: Navigate the Power Query Editor

Excel will open the Power Query Editor. At first, the data will look confusing - you might just see a few lines with the word "Record" or "List." This is the raw JSON output. Our job is to tell Power Query how to unpack it.

You may see a list record. Click on the word "List" next to its name. This will drill down into the forecast data for each time interval.

Next, you’ll see a list of "Records". This is what we want. In the top-left, click the Into Table button. Click OK on the dialog box that appears.

Step 4: Expand the Records to Get Your Data

You now have a single column called "Column1" full of records. To see what's inside, click the expand icon in the column header (it looks like two arrows pointing in opposite directions).

A menu of all available data fields will appear (like dt, main, weather, wind, etc.). Uncheck the box that says "Use original column name as prefix" to keep your column names clean. Click OK.

Voilà! The data is starting to look like a real table. You now have columns for different weather attributes.

Step 5: Expand the Nested Data

Some of the new columns like main, weather, and wind still say "Record" or "List." That’s because the API groups related data together. We just need to repeat the expansion process for these columns.

• Expand main: Click the expand icon on the main column header. Select the fields you want, like temp (temperature), feels_like, humidity, and pressure. Click OK.
• Expand weather: The weather column is a 'List'. Click the expand button, and choose Expand to New Rows. Now you have Column1.weather containing "Record" cells. Click expand icon again and select the fields you need, like main (e.g., "Clouds," "Rain") and description.
• Expand wind: Click the expand icon on the wind column header and select speed and deg (degrees).

Step 6: Clean Up and Format Your Data

Now we just need to tidy things up.

1. Convert the Date/Time: The column dt contains a Unix timestamp (the number of seconds since January 1, 1970). To convert it to a readable date and time, go to the Add Column tab and click Custom Column. Name the new column "DateTime" and enter this formula:
2. Rename Columns: Double-click any column header to rename it to something more intuitive (e.g., change main.temp to Temperature).
3. Change Data Types: Power Query often guesses data types correctly, but you can double-check. For example, ensure your new DateTime column is set to the Date/Time type. You can change a column's data type by clicking the icon (e.g., "ABC" or "123") in the column header.
4. Remove Unneeded Columns: Right-click the header of any column you don't need and select Remove.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 7: Load Your Weather Data into Excel

Once you’re happy with the table, go to the Home tab in the Power Query Editor and click Close & Load. The data will be loaded into a new sheet in your workbook as a formatted Excel Table.

Making Your Report Dynamic and Automatic

Your data is now in Excel, but the real power comes from making it refresh automatically and easy to update for different locations.

Set Automatic Refresh

You don't want to repeat these steps every time you need an update. Let's make it refresh automatically.

1. In the Excel sheet containing your data table, right-click anywhere in the table.
2. Go to Table > Edit Query. You will be back in Power Query. Alternatively, find the Queries & Connections pane on the right. Right-click your query and choose "Properties."
3. In the Properties window, check the box for ‘Refresh every’ and set your desired interval, such as 60 minutes.
4. You can also check "Refresh data when opening the file."
5. Click OK. Now, Excel will automatically fetch the latest forecast for you without you lifting a finger.

Final Thoughts

By connecting to a weather API through Power Query, you can transform a static spreadsheet into a powerful, live dashboard that pulls in hourly forecast data automatically. This process gives you the raw data you need to build charts, create conditional alerts, and make informed strategic decisions based on up-to-the-minute information.

While setting up connections in Excel is a great skill, sometimes you need to combine weather data with other business metrics from platforms like Google Analytics, Shopify, or your CRM. At Graphed, we make this process radically simpler. Instead of fiddling with APIs and Power Query, you just connect your sources and ask questions in plain English, like "Show me a chart of Shopify sales vs. hourly rainfall for last month." We build the dashboards and reports for you in seconds, so you can spend less time wrangling data and more time acting on the insights it provides.