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.
Features
This is the second post in the Dynamic Arrays series. In the first post, we talked about how formulas can return multiple values, the resulting spill range, and two dynamic array functions. In this post, we’ll talk about how to refer to the spill range with other formulas. Video Spill Range The spill range includes…
During the years we have been using Excel, we have come to understand that a formula calculates a value. Meaning, a single value. Right? Like … we write a formula, hit Enter, and the result is displayed in the cell. Easy … that is how we have been using Excel for decades. Multiple Values But…
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.…
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…
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…
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…
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…
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…
Let’s say you need to take an amount and split it evenly into monthly columns. For example, perhaps you need to recognize revenue over time. Or, perhaps you have spent some money and you need to allocate the expense over time. There are other illustrations, but the basic idea is that you have a total…
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…