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

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

VLOOKUP Return Multiple Matching Rows and Columns

By Jeff Lenning | November 13, 2019 |

In this post, we’ll discuss a way to simulate using VLOOKUP to return multiple matching rows and/or columns. What do you mean by “simulate” Jeff? Well, VLOOKUP is designed to return a single value, not multiple values. That is, VLOOKUP scans down the lookup range and stops at the first matching row … ignoring any…

Read More

Fuzzy Match with Power Query

By Jeff Lenning | July 19, 2019 |

A few years ago, I wrote a post about how to perform fuzzy lookups using the “Fuzzy Lookup Add-In for Excel.” However, this capability is now available in Power Query!! Wait, what? Yes! Performing a fuzzy match is so much easier and far more intuitive in Power Query. This post walks through the basics ……

Read More

Stock Quotes CalCPA Article

By Jeff Lenning | July 15, 2019 |

Microsoft recently updated the built-in tools for retrieving stock quotes into Excel. If you’re typing closing prices into Excel manually, my recent California CPA Magazine article may be able to help you get it done faster. Specifically, it talks about how the Stock data type retrieves current quotes and related information, and how Power Query…

Read More