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.

Posts

2020 Scholarship Winners

By Jeff Lenning | August 4, 2020 |

I am excited to announce this year’s Excel University scholarship winners! These scholarships are supported by the Excel University Alumni Association, so thanks to all of you who have helped make these awards possible. I’d also like to that Safe Harbor LLP for their generous contribution to the scholarship fund this year! Each will receive…

Read More

Find Last Occurrence

By Jeff Lenning | July 28, 2020 |

I was recently asked how to find the last occurrence of a value in a column. Although there are multiple ways to accomplish this, in this post, I’ll talk about how to use XLOOKUP. Objective Before we jump into Excel, let’s confirm our objective. Let’s say we have a bunch of transactions that look like…

Read More

CONVERT Units of Measure

By Jeff Lenning | July 22, 2020 |

There are times I need to quickly convert a quantity from one unit of measure to another (inch, foot), or from one system of measurement to another (metric, imperial). For example, when I am entering meals into MyFitnessPal, preparing a meal in the kitchen, or working on a project in the garage. When I need…

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

Link to Sheets using Defined Names

By Jeff Lenning | June 23, 2020 |

Hyperlinks are a useful way to expedite navigation. If you have ever created a hyperlink to a worksheet, you may have noticed that your hyperlink can break if you change the worksheet name. This post demonstrates how to use defined names to avoid this type of error. Objective Let’s say we want to make it…

Read More

Initial STOCKHISTORY Announcement

By Jeff Lenning | June 10, 2020 |

Microsoft just announced a new Excel function called STOCKHISTORY. This function provides a way to retrieve historical stock quotes directly into Excel. As it is being rolled out over time, I haven’t had a chance to use it yet. But as soon as my copy of Excel receives the function, I’ll write a post about…

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

Excel and Budgeting

By Jeff Lenning | May 19, 2020 |

I recently wrote an article for the Minnesota Society of CPAs about a technique that helps you create budgets faster. Even if you don’t prepare budgets, the underlying features and functions used can be applied elsewhere. In summary, we use a data validation drop-down to provide several Budget Methods. Once you select a budget method…

Read More