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.
Named References
We all love Excel, don’t we? But it’s just not that simple when it comes to creating a 4-4-5 Financial Calendar! Got questions about how it’s done? You’re not alone! Today, we will walk you through the creation of a 4-4-5 (4 weeks, 4 weeks, and 5 weeks) calendar in Excel. By the end of…
Read More
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
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
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
The table feature introduced in Excel 2007 is amazing and has nearly eliminated the need to build dynamic named ranges since tables auto-expand. Beyond auto-expansion, tables offer numerous other benefits, including, structured references which allow us to refer to an area within the table, such as a specific column. In this post, we’ll examine a method…
Read More
In this post, we’ll explore a method to allow a user to select an item from a drop-down list and then use formulas with the VLOOKUP function to retrieve multiple values from the related item table. Objective Before digging into the mechanics, let’s review our objective with an example. We would like to allow our…
Read More
In this post, we’ll create two data validation drop-down cells that provide the ability to select From and To dates based on the transaction dates stored in the source data. This will ensure that the date selections fall within a valid range of dates, that is, those months with data in the table. This technique…
Read More
In this post, we’ll explore a method for generating a drop-down that contains a unique list of choices derived from a table column with duplicate values. Objective Before we get started on the mechanics, let’s take a look at what we are trying to achieve. We have a data table that is exported from our…
Read More
This post explores macro-free methods for using Excel’s data validation feature to create an in-cell drop-down that displays choices depending on the value selected in a previous in-cell drop-down. Overview As with just about anything in Excel, there are several ways to achieve the goal. This post explores three such solutions, and if you have a…
Read More
Have you ever called up the Go To dialog in Excel? There is a little button in it that enables you to go to special places in Excel. This post explores some of these special destinations. For starters, let’s call up the Go To dialog within an empty workbook. There are a few different ways…
Read More