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

List of Workbook Tables and References

By Jeff Lenning | December 8, 2020 |

John asked if there was a way to create a list of all table names in the workbook, along with the underlying table reference. In this post, I’ll demonstrate how this can be accomplished with Power Query and an Excel formula. Objective Before we get into the mechanics, let’s confirm our goal here. We have…

Read More

Power Query from Google Drive

By Jeff Lenning | December 2, 2020 |

These days, it is common to use online services from many providers. I’m sure that I use services from a dozen different providers every day. One of the services I use is Google Drive, and I have a variety of documents stored there. One day, I was in Excel and thinking to myself, “I wonder…

Read More

Treasure Maps 2

By Jeff Lenning | October 19, 2020 |

This is the second post in the Treasure Maps series, where we are discussing various ways to implement mapping tables. In the first post, we covered SUMIFS. In this post, we’ll talk about Power Query. Overview Let’s say we have a list of transactions in a CSV file, like this: We would like to use…

Read More

Create Calculated Columns without Writing Formulas

By Jeff Lenning | September 14, 2020 |

Historically, we have created calculated columns by writing formulas. In this post, we’ll see that in some cases, clicking a few buttons in Power Query can be a nice alternative. Objective Before we get into the mechanics, let’s confirm our objective. Let’s say we have a table full of transactions, like this: We would like…

Read More

Insert X Rows to Expand Table

By Jeff Lenning | July 15, 2020 |

I received a question from Sam, who asked how to insert a variable number of rows (X rows) into a table based on a column value. That is, by the number specified in a column. In this post, we’ll accomplish this request with Power Query. Objective Before we jump into Excel, let’s confirm our objective.…

Read More

Stack or Wrap Columns

By Jeff Lenning | July 8, 2020 |

I was asked how to prepare a table with 50 columns for printing. The specific request was to break each record in the data table into multiple printed rows with a blank row in between each record. Basically, we want the columns to wrap when printed. In this post, I’ll show how to define groups…

Read More

Import PDF Data into Excel with Power Query

By Jeff Lenning | June 4, 2020 |

Historically we have been able to get PDF data into Excel using a standard copy/paste or some third-party tools. Now we have another option. We can use Power Query to retrieve values from PDF files. This post walks through the basics. Video Objective We have a PDF file, perhaps it looks like this:   We…

Read More

List of Worksheets in a Drop Down

By Jeff Lenning | May 27, 2020 |

In this post, I’ll answer a question I received from Sara about how to create a drop down with each worksheet name and reference it in a formula to retrieve values from the selected sheet. As this is Excel, there are a variety of alternatives to accomplish this. In this post, I’ll demonstrate one such…

Read More

Retrieve First Occurrence After a Date

By Jeff Lenning | January 1, 2020 |

In this post, we’ll discuss one approach for retrieving the first occurrence (or min date) after a specified date. Let me back up. Michael asked for a way to find the first occurrence of an office visit (a date) after the date of being discharged from the hospital (and within 30 days). As this is…

Read More

Insert Repeating Items into a List

By Jeff Lenning | December 4, 2019 |

This post will demonstrate how to insert the same few items into a list and create a new row for each item (or each combination, if multiple items).  For example, let’s say we have a list of some sort … we’ll use a list of T-Shirts for this illustration. We have a few T-Shirt options…

Read More