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

Sum Every Nth Row in a Column

By Jeff Lenning | December 19, 2018 |

Let’s say you have a single-column list of transactions. You want to add up the amount values, which are on every say 5th row. This is a perfect task for Power Query. So, in this post, I’ll demonstrate how to set up a query that removes and keeps a defined number of rows in a…

Read More

Identify Missing IDs and Sequence Gaps

By Jeff Lenning | December 10, 2018 |

There are several ways in Excel to find missing IDs (or gaps) in a big list of sequential IDs, such as check numbers or invoice numbers. In this post, we’ll use Power Query so that each time we have a new list, we simply click Refresh. Excel then creates an updated list of the missing…

Read More

Query Variables

By Jeff Lenning | September 27, 2018 |

Let’s say we are building a query to clean up and prepare some data for use, and we have a specific value we need to use within the query. But, we would like to be able to change the value as desired, before refreshing the query. One option would be to enter and update that…

Read More

Create Dynamic Rows for an Amortization Schedule with Power Query

By Jeff Lenning | June 19, 2018 |

Sometimes in Excel, we want to use formulas to compute row values, but, the number of rows is dynamic and changes periodically. For example, let’s say we want to create an amortization schedule and use it for a variety of loans. Some loans are paid in 36 months, some in 120 months, and some in…

Read More

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