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.

Get & Transform

Mind Blown CalCPA Article

By Jeff Lenning | June 1, 2017 |

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…

Read More

Use a Get & Transform Query to Transpose Values

By Jeff Lenning | April 26, 2017 |

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.…

Read More

Retrieve Values from Many Workbooks

By Jeff Lenning | April 6, 2017 |

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…

Read More

Most Recent Transaction Date

By Jeff Lenning | March 9, 2017 |

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…

Read More

Use Get & Transform to Reconcile Two Lists

By Jeff Lenning | February 2, 2017 |

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…

Read More

Get & Transform Article

By Jeff Lenning | September 8, 2016 |

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…

Read More
Combine binaries by Jeff Lenning

Get & Transform: An Alternative to Copy Paste Append

By Jeff Lenning | September 1, 2016 |

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…

Read More

Get & Transform: An Alternative to VLOOKUP List Comparisons

By Jeff Lenning | August 18, 2016 |

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…

Read More

Get & Transform: An Alternative to Finding the Last Delimiter with Formulas

By Jeff Lenning | July 28, 2016 |

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…

Read More
Import Web Data by Jeff Lenning

Get & Transform: An Alternative to Excel’s Web Browser

By Jeff Lenning | July 7, 2016 |

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…

Read More