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.
Features
When you download banking activity, you typically get the transactions, but not the daily bank balance. To compute the daily bank balance, you need to summarize the transactions by day and display a running total for all days, even those without any activity. This post demonstrates how to build such a report using a PivotTable. Overview…
If you haven’t played with the Get & Transform commands in Excel 2016 for Windows, they’re probably worth checking out. I don’t want to sound overly dramatic here, but this set of capabilities is a game changer. These tools provide new ways to approach tasks, and enable us to do things that were previously time-consuming, impractical or required macros. If you’d like to…
Let’s say you have several data tables, and you need to combine them into a single table. One option would be to copy and paste to append them. But, depending on how many data tables there are, this type of manual process can be tedious. In this post, we’ll use a Get & Transform query…
I recently received an Excel question about how to perform a specific list comparison, and I thought I’d demonstrate how to use a Get & Transform query as an alternative to the formula-based list comparisons typically performed with functions such as VLOOKUP and COUNTIFS. The original question is: “I am trying to use a list…
Sometimes, our data has multiple delimiters. Finding the first delimiter with formulas has been pretty easy over the years. Finding the final delimiter hasn’t been as easy…until now. In this post, we’ll use a Get & Transform query to grab the characters after the last delimiter. Objective Before we jump into the mechanics, let’s be clear about…
Excel has a built-in web browser that can be used to view web pages and import selected web data. This browser worked well for many years, but, recently, script errors began appearing on some pages. This makes it challenging to import data from some web pages, and frustrates users. The good news is that we can…
Since many of Excel’s features are designed to work with data stored in a flat, tabular format, we sometimes need to flatten data that is received in other formats. In a previous post, we discussed a manual way to flatten data. In this post, we’ll use a Get & Transform Query as an alternative to…
Excel 2016 includes a set of features called Get & Transform. In previous versions of Excel, these capabilities were included in the Power Query Add-In. In this post, we’ll see how a Get & Transform Query can be used as an alternative to a VBA macro. Overview Here is the scenario. We export data out of…
In this post, we’ll create a drop-down that contains a unique list of choices derived from a column that contains duplicate values. This may sound familiar as we previously accomplished this with a PivotTable. However, the Power Query feature that’s built-in to Excel 2016 makes this process easier. Objective We have a data table that contains RepID,…
If you get stock quotes into Excel by typing or copy/pasting from your web browser, you may be able to get your work done faster if you use a built-in Excel feature. Excel’s external data feature enables you to retrieve data from a variety of sources—one of which happens to provide stock quotes. Check out…