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

Power Query and Zip Code Formatting

By Jeff Lenning | June 12, 2018 |

Power Query is an amazing tool, and I love learning about it. In the Power Query Editor, as you click the command icons, Excel is actually writing M code behind the scenes. M code has many more functions than are available in the ribbon. So, the thing to keep in mind is that if you…

Read More

Split Delimited List into Rows

By Jeff Lenning | June 6, 2018 |

This is the second of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists. In the first post, we combined rows into a delimited list. In this post, we’ll do the opposite and convert a delimited list into rows. Well, what are we waiting for … let’s…

Read More

Combine Rows into a Delimited List

By Jeff Lenning | May 31, 2018 |

This is the first of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists. In this first post, we’ll combine rows into a delimited list. In the second post, we’ll do the opposite and convert a delimited list into rows. Well, what are we waiting for ……

Read More

Compute Age from Dates

By Jeff Lenning | March 21, 2018 |

In this post, I answer a question from Emmanuel who asked how to compute the age when given a list of birth dates. We want the solution to be dynamic, so that it is easy to update the age calculation going forward. Since this is Excel, there are many ways to accomplish this. In this…

Read More

VLOOKUP on Multiple Columns and Return Text

By Jeff Lenning | March 15, 2018 |

You want to perform a lookup with VLOOKUP, but, there are multiple lookup columns. So, what are you supposed to do? Combine them into a single lookup column? That is certainly one option, but, as with just about anything in Excel, there are multiple ways. In a previous post, I showed one way to do…

Read More

PivotTable from Many CSV Files

By Jeff Lenning | March 7, 2018 |

In this post, we’ll summarize data from multiple CSV files with a PivotTable. Specifically, we’ll use a Get & Transform query (Power Query) to retrieve and prepare data from numerous CSV files. Then, we’ll send the query results into the data model (Power Pivot). Then, we’ll build our summary report using a PivotTable based on…

Read More

Import and Clean Bank Activity

By Jeff Lenning | February 15, 2018 |

If you have ever downloaded bank activity from your bank’s website, you know there is some amount of cleaning you need to do before the data is ready to use. Perhaps the next step is to summarize the bank activity with a PivotTable, or, perhaps to perform a bank rec. Regardless of your ultimate objective,…

Read More

VLOOKUP Hack #10: Maslow’s Hammer

By Jeff Lenning | January 10, 2018 |

Psychologist Abraham Maslow wrote a line that is often referred to as Maslow’s Hammer. It goes something like this: “I suppose it is tempting, if the only tool you have is a hammer, to treat everything as if it were a nail.” I’ve heard the simplified version more often: “If all you have is a…

Read More

Get & Transform: An Alternative to Simple PivotTable Reports

By Jeff Lenning | September 27, 2017 |

I love PivotTables, and use them all the time. But, when our needs are simple, we can easily summarize data with a Get & Transform query instead. Why? To simplify our workbooks and improve our efficiency. Let me demonstrate. Objective Before we dig into the mechanics, let’s just be clear about our goal. Let’s say…

Read More
FlashFill Meets GNT by Jeff Lenning

FlashFill Meets Get & Transform

By Jeff Lenning | June 20, 2017 |

Do you love FlashFill? Do you love Get & Transform queries? If so, you’ll love the most recent Excel update pushed out by Microsoft: New Column by Example. It is essentially FlashFill inside PowerQuery. It is way cool. Check it out. Objective Let’s say we have some data. It doesn’t matter where it is, it…

Read More