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.
Power Query
Imagine this scenario: There are 120 CSV files in a folder on your network. Each CSV export contains the transactions for a single month, and there are 10 years’ worth of files. Your mission, should you choose to accept it, is to combine all of the transactions in these 120 files into a single Excel…
Recently, I’ve been on this kick of using Get & Transform queries to accomplish tasks that we previously performed with other methods. It is clear that the Get & Transform commands provide new ways to solve old problem. In this post, I’ll demonstrate how we can use a Get & Transform query to transpose values.…
Alright my friends, this week I’ll tackle a question I received about retrieving values from workbooks. Here is the basic idea of the question. I have a folder with several hundred workbooks, and each workbook may contain any number of worksheets. For example, some workbooks have two sheets, some have three sheets, and some have…
I recently received a question about how to find the most recent transaction date of a list of items. There are a few fun ways to accomplish this, and so I thought I’d walk through three options. Thanks Darrell for your question! Objective The exact question from Darrell is: “I have a data table of…
In this post, we’ll use a Get & Transform query to help with our reconciliation. The idea for this post came from a question from Laura (thanks Laura!) The basic idea is that we have two worksheets. One contains the invoice totals and the other contains line item details, where there are many line items per…
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…
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…
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,…