Excel University Blog
Read on for in-depth articles, tutorials, and videos. Search or browse for specific topics. Be sure to subscribe if you'd like to be notified when we write something new.
Jeff Lenning
Sometimes we want to grab just the latest few rows of a report, remove some headers, or extract a specific section of data—like Q2 sales—from a larger table. Excel’s TAKE and DROP functions are perfect for these tasks. In this post, we’ll walk through real examples (based on our sample file) to see how these…
Combining text data from different cells is a common task in Excel, especially when dealing with employee directories, codes, or summarized reports. While we’ve often relied on functions like CONCATENATE or the ampersand (&) to accomplish this, Excel’s TEXTJOIN function provides a more flexible, powerful, and streamlined approach. Video What is the TEXTJOIN Function? In…
Breaking apart long strings of data into separate cells is a common task in Excel—and with the TEXTSPLIT function, we have an incredibly powerful and flexible tool to do just that. In this post, we’ll explore how TEXTSPLIT can help us split data into columns, rows, or both—all from a single cell entry! Video What…
Have you ever wanted to turn Excel into a fun, interactive trivia game? In this tutorial, we’ll walk through how to use Excel’s Power Query to pull trivia questions from an online API, randomize the answers, and display the correct answer with a reveal feature — all without using VBA. By the end, you’ll be…
Ever wish Excel could tell you a fun cat or dog fact every time you click refresh? Good news — it can! In this post, we’ll walk through a short, practical project that uses Power Query to pull random facts about cats and dogs from public APIs directly into your worksheet. Whether you’re a pet…
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…
Using Power Query to Get AM/PM Temperatures by Day in Excel Let’s be honest—weather data inside a spreadsheet might seem like magic. With Excel’s built-in tools like Power Query and the Geography Data Type, we can pull live weather forecasts, break them down by day, and split them into AM and PM temperatures. In this…
Create a Dynamic Weather Forecast in Excel This is the second post in the Weather series. In this post, we’ll walk through how to build a dynamic weather forecast tool using Excel and Power Query. By the end, we’ll be able to simply click Refresh, and Excel will pull in the latest weather forecast based…
How to Retrieve Latitude and Longitude in Excel from City and State This is the first post in a larger 3-part weather forecasting series we’ll build together. Along the way, we’ll sharpen our skills with the WEBSERVICE and FILTERXML functions, as well as utilize Power Query in upcoming tutorials. In this initial post, we’ll take…
Wouldn’t it be great if Excel could automatically generate a list of public holidays based on a single year input? And even better, have that holiday table update all related formulas and schedules across your workbook? Well, that’s exactly what we’re going to build in this post—an automatic calendar in Excel with public holidays, dynamically…